Magento 2.4, 2.3 mysqldump done right

Magento 2.3 introduced a view table, which causes issues with the 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 like 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 import a fresh database or move 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 Adobe Commerce (the enterprise version of Magento 2) and Commerce Cloud, and 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 optimized 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 a password in a prompt.

--triggers: this option will make mysqldump 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.