Most teams have backups. Far fewer have backups they have actually restored. The difference only becomes visible on the worst day, when a table has been dropped or a disk has failed. A sound MySQL backup strategy covers what to back up, how often, where to store it and, crucially, how you will prove it works.
Start with recovery goals
Two questions shape everything else. How much data can you afford to lose? That is your recovery point objective. How long can you afford to be down? That is your recovery time objective. A hobby project might tolerate a day of lost data; an order system might tolerate only minutes. Write these targets down before choosing tools.
Logical backups
Logical backups export data as SQL statements or delimited files. mysqldump and the faster, parallel mysqlpump or MySQL Shell dump utilities are common choices.
mysqldump --single-transaction --routines --triggers \
--databases appdb | gzip > appdb-$(date +%F).sql.gzThe --single-transaction flag gives a consistent snapshot of InnoDB tables without locking them for the whole dump. Logical backups are portable across versions and easy to inspect, but they can be slow to create and very slow to restore for large databases.
Physical backups
Physical backups copy the underlying data files. Tools such as Percona XtraBackup and MySQL Enterprise Backup can do this while the server is running. Restores are much faster because MySQL does not have to replay every insert, which matters once your database grows into hundreds of gigabytes. Managed cloud databases usually offer storage snapshots that serve the same purpose.
Binary logs for point-in-time recovery
A nightly backup alone means you could lose up to a day of changes. Enabling binary logging records every change, so you can restore the last full backup and then replay the binary logs up to just before the mistake.
mysqlbinlog --stop-datetime="2026-10-12 14:05:00" \
binlog.000231 binlog.000232 | mysql -u root -pKeep binary logs at least as long as the gap between full backups, and copy them off the server regularly.
Follow the 3-2-1 idea
- Three copies of your data, including production.
- Two different storage types, such as local disk and object storage.
- One copy offsite, in a different region or account, so a single compromised account or data centre problem cannot remove everything.
Encrypt backups at rest and in transit, and restrict who can delete them. Backups protected by the same credentials as production can be wiped in the same incident.
Automate and monitor
Run backups from a scheduler, not from memory. Every job should report success or failure to a monitoring system, and alert when a backup is missing, unusually small or took far longer than normal. A backup job that quietly failed three weeks ago is a common discovery during an outage.
Test restores on a schedule
This is the step teams skip. At a regular interval, restore the latest backup to a separate server, replay binary logs to a chosen time and run a few sanity queries: row counts on key tables, the most recent order, a known customer. Time the whole process so you know whether you can meet your recovery time objective.
- Document the restore steps in a runbook anyone on the team can follow.
- Rotate who performs the drill so knowledge is not held by one person.
- Record how long each step took and fix anything that was confusing.
Do not forget the rest of the system
A database restore is only part of recovery. Make sure you also have configuration, user grants, encryption keys and uploaded files backed up, and that application migrations match the restored schema.
Wrapping up
Define how much data and downtime you can tolerate, combine full backups with binary logs, store copies offsite and encrypted, monitor every job and, above all, practise restoring. A backup becomes trustworthy only after you have brought it back to life.
