Backing Up and Restoring MySQL Databases

A backup you've never restored is a hope, not a backup. Here's how to take consistent MySQL backups and prove they work.

Logical backups with mysqldump

mysqldump --single-transaction --routines --triggers --events \
  --set-gtid-purged=OFF -h db -u backup -p shop | gzip > shop-$(date +%F).sql.gz

• --single-transaction gives a consistent snapshot of InnoDB tables without locking them.
• Include routines, triggers and events, or they'll be missing after a restore.
• Simple and portable, but slow to dump and restore very large databases.

Faster logical backups with MySQL Shell

// mysqlsh --js
util.dumpInstance('/backups/2026-10-11', { threads: 8, compression: 'zstd' });
util.loadDump('/backups/2026-10-11', { threads: 8 });

Parallel dumping and loading can be many times faster than mysqldump for large datasets.

Physical backups

Tools like Percona XtraBackup copy the data files while the server runs. Backups and restores of multi-terabyte databases are much faster than logical dumps. Managed services take storage snapshots for you.

Point-in-time recovery

A nightly backup alone loses up to a day of data. Keep binary logs too, and you can replay changes up to just before a mistake:

# 1. Restore last night's backup
# 2. Replay binlogs up to the moment before the bad DELETE
mysqlbinlog --stop-datetime="2026-10-11 14:31:00" binlog.000412 binlog.000413 | mysql -u root -p

A backup policy

• Automated daily full backups plus continuous binary log archiving.
• Copies stored off the server, ideally in another region or account.
• Encrypted, with access restricted.
• Retention that matches business needs (for example 30 days of daily backups).
• Regular restore tests: restore into a fresh server on a schedule and check row counts and app behaviour. Measure how long it takes; that's your real recovery time.