Dumping a busy Magento store without locking it

I wrote a short note on this back in 2013. I have used the same approach on every Magento store since, and the template has grown a little, so here it is properly.

The problem: a plain mysqldump locks each table while it reads it. On a store taking orders around the clock that means checkouts and admin saves hang until the dump moves on, and on a large catalogue that can be minutes.

The two options that matter

mysqldump -u username -p --single-transaction --skip-lock-tables dbname > backup.sql

--single-transaction starts a transaction with a consistent snapshot before it reads anything. For InnoDB tables, which is all of a normal Magento install, the dump sees the database as it was at that moment, and other sessions carry on writing without waiting.

--skip-lock-tables makes sure no LOCK TABLES is issued. --single-transaction already turns it off, so this is belt and braces, but I leave it in so the intent is obvious to whoever reads the command later.

Two caveats:

• The snapshot only covers transactional tables. Any MyISAM table (some old extensions created them) is read without a lock and may be inconsistent.
• Do not run schema changes while the dump is going. An ALTER TABLE, TRUNCATE or DROP on a table the dump has not reached yet breaks the snapshot. That includes reindexing and extension installs, so pick a quiet time to start the dump.

Leave out the logs on Magento 1

Magento 1 writes a row for every visitor and URL into its log_* tables. On a store that has never had log cleaning enabled they can be bigger than everything else put together, and nobody needs them back after a restore.

My template, with the database name and table prefix to change:

mysqldump -u username -p \
  --single-transaction \
  --skip-lock-tables \
  --ignore-table=dbname.prefix_dataflow_batch_export \
  --ignore-table=dbname.prefix_log_customer \
  --ignore-table=dbname.prefix_log_quote \
  --ignore-table=dbname.prefix_log_summary \
  --ignore-table=dbname.prefix_log_summary_type \
  --ignore-table=dbname.prefix_log_url \
  --ignore-table=dbname.prefix_log_url_info \
  --ignore-table=dbname.prefix_log_visitor \
  --ignore-table=dbname.prefix_log_visitor_info \
  --ignore-table=dbname.prefix_log_visitor_online \
  dbname > backup.sql

Each --ignore-table needs the database name in front of the table name, and the table prefix if the install uses one. A typo is not reported; the table is just dumped anyway. Check the names with SHOW TABLES LIKE '%log\_%'; first.

Prefix the command with time the first time you run it so you know what to expect. On one store, a 4 GB database came out in under a minute this way.

Keep the empty tables

There is a catch with --ignore-table: the ignored tables are left out completely, structure and all. Restore that file to an empty database and Magento will error the moment it tries to write a log row.

So for a backup I mean to restore, I do it in two passes. The first dumps the structure of every table; the second dumps the data with the same --ignore-table list as above (shortened here):

mysqldump -u username -p --single-transaction --no-data --routines dbname > structure.sql

mysqldump -u username -p --single-transaction --skip-lock-tables \
  --no-create-info --skip-triggers \
  --ignore-table=dbname.prefix_log_url \
  --ignore-table=dbname.prefix_log_url_info \
  --ignore-table=dbname.prefix_log_visitor \
  --ignore-table=dbname.prefix_log_visitor_info \
  dbname > data.sql

Load structure.sql first, then data.sql. Every table exists; the log tables are just empty. The two dumps are separate snapshots, which is fine here because the second one is the only one with data in it.

The same reasoning applies to core_url_rewrite. It is often huge and can be rebuilt by reindexing, so it is tempting to skip it, but the table has to exist and the store will have broken product URLs until the reindex has finished.

Magento 2

Magento 2 dropped the old log_* tables, so on the 2.x stores I just run the plain version:

mysqldump -u username -p dbname --single-transaction --skip-lock-tables > backup.sql

Compress on the way out

Writing the uncompressed dump to the same disk as the database doubles the I/O while the store is busy. Pipe it through gzip instead:

mysqldump -u username -p --single-transaction --skip-lock-tables dbname | gzip > backup.sql.gz

And use -p with no value so you are prompted for the password, rather than leaving it in shell history.