MySQL Backup, Restore, and Recovery
Same rule as everywhere else on this site: a backup only counts if you can restore it, fast enough and recent enough to matter. And with databases, don't forget that the database alone usually isn't enough. You need the app, its config, and its uploaded files from the same point in time too.
Define RPO and RTO
| Measure | Question |
|---|---|
| Recovery Point Objective | How much recent data can the business afford to lose? |
| Recovery Time Objective | How long can the application remain unavailable? |
Logical backup example
mysqldump \ --single-transaction \ --routines \ --triggers \ --events \ --set-gtid-purged=OFF \ --default-character-set=utf8mb4 \ --databases customer_portal \ | gzip > customer_portal-2026-07-16.sql.gz
--single-transaction gets you a consistent snapshot of InnoDB tables without locking everything for the whole dump. It does nothing for old MyISAM tables, though. If you've still got some of those hanging around, they're not consistent.
Restore example
gunzip -c customer_portal-2026-07-16.sql.gz \ | mysql --host=127.0.0.1 --user=restore_operator --password
Point-in-time recovery
A full backup plus binary logs lets you restore to a specific moment, like 10:41 a.m., right before someone ran that UPDATE without a WHERE. For that to work you need binary logging turned on, kept long enough, backed up, and a restore process you've actually practiced.
Recovery drill
- Provision an isolated database instance.
- Restore the most recent full backup.
- Apply incremental or binary-log recovery to the target time.
- Run integrity checks and application smoke tests.
- Measure actual recovery time and data gap.
- Record failures and update the runbook.
What must be backed up together
- Database data and required binary logs
- Application releases and dependency lock files
- Environment configuration and secret-recovery process
- User uploads and generated documents
- Web server, PHP-FPM, scheduler, and queue configuration
- Migration history and operational runbooks