MySQL backups that actually restore — XtraBackup, binlogs, and the quarterly drill

A backup you've never restored is hope, not a backup. Every managed MySQL database we run has Percona XtraBackup taking physical backups on a schedule, binlogs archived continuously for point-in-time recovery, and a quarterly restore drill that proves both still work.

The common failure mode: backups succeed for a year, then a binlog gap from a network blip three months ago means PITR can only recover to before the gap. By the time anyone notices, the gap is too old to matter.

The base XtraBackup command

xtrabackup --backup \
  --target-dir=/var/backups/mysql/full-$(date +%F) \
  --user=backup --password=... \
  --parallel=4 --compress --compress-threads=4 \
  --no-version-check
 
# Then ship + verify
aws s3 sync /var/backups/mysql/full-$(date +%F) s3://backups/mysql/full-$(date +%F)/
xtrabackup --prepare --target-dir=/var/backups/mysql/full-$(date +%F)

--prepare is what makes the backup actually usable — and it's the step that surfaces corruption, missing redo log, or version mismatches before you need to restore.

The full write-up covers:

• Why physical backups beat mysqldump for any non-trivial database
• Continuous binlog archiving — the rsync/inotify pattern we run
• Point-in-time recovery to a specific binlog coordinate
• The quarterly restore drill (provisioning a clean host, restoring, smoke-testing)
• Backup verification — xtrabackup --prepare and the catastrophes it catches early
• Encrypting backups at rest, key rotation, and offsite storage

We run this for every managed MySQL customer.