A dump is a text file of SQL statements. Whether it restores depends on how it was written, and the defaults are not the right ones for a live site. Most import failures are decided at dump time, weeks earlier.

The command

mysqldump \\n  --single-transaction \\n  --quick \\n  --routines --triggers --events \\n  --default-character-set=utf8mb4 \\n  --set-gtid-purged=OFF \\n  dbname | gzip > db-$(date +\%F).sql.gz

What each one prevents

  • --single-transaction - a consistent snapshot with no table locks. Without it, a busy site is frozen for the length of the dump.
  • --quick - streams row by row instead of buffering a large table into memory.
  • --routines --triggers --events - not included by default. A restore without them looks complete and quietly loses every stored procedure and every scheduled event.
  • --default-character-set=utf8mb4 - Arabic and emoji survive the round trip.
--single-transaction is consistent for InnoDB only. A MyISAM table in the same database is dumped without that guarantee, so convert them or accept an inconsistent copy of those tables.

Verify the file, not the exit code

gzip -t db-2026-09-05.sql.gz               # the archive is intact\nzcat db-2026-09-05.sql.gz | tail -5       # ends with "Dump completed"\nzcat db-2026-09-05.sql.gz | grep -c "CREATE TABLE"

A dump cut off by a disk filling up is still a valid gzip for most of its length. The completion line at the end is the proof that mysqldump reached the end - and it costs one command to check.

Restore it somewhere else, on a schedule

mysql -e "CREATE DATABASE restore_test CHARACTER SET utf8mb4"\nzcat db-2026-09-05.sql.gz | mysql restore_test\nmysql restore_test -e "SELECT COUNT(*) FROM orders"
Compare that row count against production. A dump that imports without error and arrives with half the rows is the failure nobody catches, because the import said nothing at all.