Backups and Restores

Article · 3:00

mysqldump, what it does and does not capture, and the only way to know a backup works.

mysqldump produces a logical backup: a text file of SQL statements that recreates the schema and reinserts the data. It is portable across versions and platforms, readable, and easy to restore in part. It is also slow on large databases and produces large files, which is why physical backups - Percona XtraBackup, or a filesystem snapshot - take over past a certain size.

Two options matter more than the rest. --single-transaction takes the dump inside one transaction so the result is consistent without locking the tables - essential on InnoDB and on a live database. --routines --triggers --events includes stored routines, triggers and scheduled events, which are not included by default; a backup taken without them restores a database that is missing half its behaviour, and the discovery is usually made during a restore nobody planned.

A restore is mysql < dump.sql, and the dump may or may not create the database depending on how it was taken.

The point that matters more than any flag: an untested backup is not a backup. Restore it, on a schedule, onto a scratch server, and check that the row counts and the routines are there. The failure mode for backups is not usually that they were never taken - it is that they were taken wrong for a year and nobody looked.

# One database, consistently, with everything that is not included
# by default. Without --routines you restore a database with no
# procedures, functions, triggers or events - and find out later.
mysqldump -u root -p \
  --single-transaction \
  --routines --triggers --events \
  --default-character-set=utf8mb4 \
  shop > shop-2026-11-17.sql

# All databases, including the users in mysql.user.
mysqldump -u root -p --all-databases --single-transaction \
  --routines --triggers --events > full.sql

# Just the schema, or just the data.
mysqldump -u root -p --no-data shop > schema.sql
mysqldump -u root -p --no-create-info shop > data.sql

# One table, and only some of its rows.
mysqldump -u root -p shop orders --where="placed_at >= '2026-01-01'" > recent.sql

# Restore. The dump may or may not create the database - check the top.
mysql -u root -p shop < shop-2026-11-17.sql

# The part everyone skips. Restore onto a scratch server and COUNT.
mysql -u root -p -e "CREATE DATABASE restore_test;"
mysql -u root -p restore_test < shop-2026-11-17.sql
mysql -u root -p -e "
  SELECT table_name, table_rows FROM information_schema.tables
  WHERE table_schema = 'restore_test';
  SELECT COUNT(*) AS routines FROM information_schema.routines
  WHERE routine_schema = 'restore_test';"