Backup-Restore Drills: Prove Your Backups Restore

A backup you have never restored is a hope, not a backup. Every horror story we have been called into follows the same shape: backups ran for years, the one night they were needed, the restore failed — wrong flags, missing pieces, dead disk, or a cron job that had been failing silently since a password rotation eighteen months earlier. The fix is not better backup software. It is a drill.

The drill

Once a month (weekly for businesses where the database is the business), an engineer restores the latest production backup onto a scratch machine and proves it works. Not "the file exists and is large" — a full restore, measured:

1. Restore the dump onto a clean MySQL of the same version. Record how long it takes; that number is your real recovery time, and it grows with the data.
2. Verify shape: row counts on the five biggest tables against production (within the delta the backup's age explains), CHECKSUM TABLE on anything critical, and confirm stored procedures, triggers, and views exist.
3. Boot the application against the restored database — a staging copy pointed at the scratch DB — and log in, place a test order, load the admin. The application is the only validator that checks what actually matters.
4. Write down the result: date, backup file, restore duration, failures, fixes. Two lines in a log. When an auditor, an insurer, or a due-diligence process asks about recovery, this log is the answer.

The first drill almost always finds something. That is the point — you want to find it on a Tuesday afternoon, not during the incident.

What the drill catches

• Missing objects. mysqldump does not include stored routines or events unless you pass --routines --events. Triggers are included by default but get dropped by some wrapper scripts. Applications that lean on procedures restore into a database that looks complete and does not work.
• Consistency flags. InnoDB dumps need --single-transaction for a consistent snapshot without locking the site. Without it you get either table locks in production or a dump whose tables disagree with each other.
• Charset mangling. A dump taken as latin1 and restored as utf8mb4 (or vice versa) turns every accented character into mojibake. Pin --default-character-set in both directions.
• Silent cron failure. The classic: backups stopped after a credentials rotation, and the cron job's error went to a mail spool nobody reads. The drill catches this within a month. Better: make the backup job write a success marker (a timestamped file, a dead-man's-switch ping to a monitor like a healthcheck URL) and alert on absence, not failure.
• Retention that outgrew the disk. The backup volume filling up fails the newest backup first — precisely the one you will want.

Logical vs physical, briefly

mysqldump (and its faster successor mysqlpump, or mydumper) produces logical SQL: portable across versions, human-inspectable, slow to restore at scale. Percona XtraBackup and MySQL 8's clone plugin copy data files: restores are enormously faster, but version-matched and all-or-nothing.

Rule of thumb: under ~20 GB, mysqldump --single-transaction --routines --events is fine and the simplicity is worth it. Above that, restore time becomes the constraint — drill it, look at the number, and if it is longer than the business can tolerate, move to physical backups. The drill gives you the number; without it you are guessing.

Point-in-time recovery

A nightly backup means a bad UPDATE at 4 p.m. costs you the day. Binary logs close the gap: with log_bin enabled, you restore last night's backup and replay the binlog up to 3:59 p.m. with mysqlbinlog --stop-datetime. It costs disk space and a retention setting. Include one binlog replay in your drills at least twice a year — it has more sharp edges (server IDs, GTID modes) than the plain restore, and you do not want your first attempt to be the real one.

Files too

The database is half the application. Uploaded files, generated documents, TLS keys, the .env nobody remembers writing, crontabs, and the Apache and PHP-FPM configs that took years to tune — back them up on the same schedule and restore them in the same drill. "We have the data but the site takes three days to rebuild" is a failed recovery with extra steps.

One afternoon to set up, an hour a month to run. Nothing else in operations buys as much certainty per hour spent.