mysqldump defaults to locking tables so the dump is consistent. On a small database it takes a second. On a large one the site is frozen for minutes, and the nightly backup becomes a nightly outage.

The flag

mysqldump --single-transaction --quick --routines --triggers \\n          --default-character-set=utf8mb4 shop | gzip > shop.sql.gz

--single-transaction takes a consistent snapshot using InnoDB's own versioning: readers and writers carry on and the dump still sees one point in time.

Its two conditions

  • InnoDB only. A MyISAM table in the same database is dumped without the guarantee. Convert them: ALTER TABLE t ENGINE=InnoDB;
  • No schema changes during the dump. An ALTER TABLE while it runs can break it - so do not run migrations and backups at the same time.

If the database is large

# dump from a replica instead of production\nmysqldump --single-transaction -h replica.internal shop | gzip > shop.sql.gz\n\n# or use a physical tool\nxtrabackup --backup --target-dir=/srv/backup/full

A replica takes the load entirely off the live database. Physical tools copy the files rather than generating SQL, which is far faster to restore for anything above a few tens of gigabytes.

Watch what it costs while it runs

mysql -e "SHOW PROCESSLIST" | head -20\nmysql -e "SHOW ENGINE INNODB STATUS\G" | grep -A5 "TRANSACTIONS"
A long --single-transaction dump makes InnoDB keep old row versions for its whole duration, so the undo log grows. It is fine for minutes and a problem for hours - which is the point at which a replica or a physical backup is the answer.

What to check in the finished file is in dumps that actually restore.