Magento 2.3 mysqldump done right

Magento 2.3 introduced a view table which causes issues with import of mysql dump. It’s part of the new Magento 2 Multi Source Inventory feature.

A view was added for performance reasons and maintainability. An index table may sound a better idea, but it needs to be managed and there’s no point when the data is already there but in other tables. An alternative could have been different SQl queries to retrieve the stock data. It was really just a design decision made by the Magento team.

Remember this when you imported a fresh database or moved Magento to a new server. If you can’t see any products on the frontend or everything appears to be out of stock, then the issue is the missing view in mysql.

There’s another error that’s possible with Magento Commerce (the enterprise version of Magento 2) and Magento Cloud, these are the DEFINERs. A definer requires elevated privileges in mysql so in most scenarios, you can’t import these as a regular user.

Over time, I developed a quick mysqldump command that will dump the Magento 2 database in a nice, easy to import format without the DEFINERS and one that will also include the inventory_stock_1 view. Note the _1 at the end. That means one view for each store id.

Here is the mysqldump optimised for Magento 2:

mysqldump --single-transaction -u username -p database_name --triggers | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' | gzip > database_name_.`date +"%Y%m%d"`.sql.gz

Some short explanation:

--single-transaction is required when you are running this on a production environment. Not using it may lead to database locks and inconsistencies in your dump.

-u username -p: change it to your username. -p will ask you for password in a prompt.

--triggers: this option will make mysqldump to include the view in the dump.

Then in a pipe I run the whole dump through sed to filter out any definer, then gzip the dump in one go to a file with a timestamp (Y-m-d). These pipes are really useful if there’s limited disk space available on the server or I just need to copy-paste a single command that does everything I need. When it’s done, I scp the file to the other server or to my local environment and I know it will import easily without problems.