MySQL backup strategies
The decision tree for backing up MySQL/Percona Server — companion to MySQL logical vs physical backups (the mechanism comparison) and part of the Betting on Percona lane (XtraBackup ships with it). Design first, tool second: pick RPO (acceptable data loss) and RTO (acceptable downtime), then choose.
The tool map
| Tool | Type | Use when |
|---|---|---|
mysqldump | logical, single-threaded | Small DBs (<tens of GB); the always-available baseline |
MySQL Shell util.dumpInstance / util.loadDump | logical, parallel, chunked | The modern logical default — 8.0+, much faster than mysqldump, per-thread load |
mydumper/myloader | logical, parallel | Same lane as Shell dump, community staple |
| Percona XtraBackup | physical (hot, lockless w/ backup locks) | Everything larger; the PS/PXC-native answer; incrementals, compression, streaming (xbstream) |
| Clone plugin (8.0.17+) | physical, native, over the network | Provisioning replicas and fast full copies without any external tool — CLONE INSTANCE FROM |
| Binlog archive | transaction log | Not a backup by itself — but PITR (point-in-time recovery) turns any backup into “any second” RPO |
| Delayed replica | live, lagged copy | OOPS-recovery (dropped table an hour ago) without touching backups |
mysqlpump is dead (deprecated 8.0.34, removed later) — don’t reach for it.
The strategy patterns
- Small fish (≤ ~50 GB, minutes-tolerant RTO): Shell dump (parallel logical) + nightly binlog purge discipline. One script, one restore path, restore rehearsed.
- The workhorse: weekly XtraBackup full + daily incrementals + binlogs kept ≥ full-backup age → RPO measured in seconds (PITR), RTO = prepare + copy-back. The chain discipline is the cost (MySQL logical vs physical backups §point 3).
- Cluster/PXC: XtraBackup is also the SST mechanism — the backup tool and the cluster’s data plane are the same tooling; Percona Backup for MySQL orchestrates cluster-wide consistent backups.
- OOPS lane: delayed replica (e.g.
CHANGE REPLICATION SOURCE TO ... SOURCE_DELAY=3600) so a badDROPis a promote-away, not a restore-event. - Everything above is worthless without: off-server (and off-site) copies, encrypted at rest, and a rehearsed restore — a backup that has never been restored is a rumor (Semantic anchors for agents language: the restore is the verification anchor).
The version/upgrade interplay
Backups are also the migration vehicle — and here type matters: logical backups cross any version boundary (5.7→8, 8→9), physical ones don’t (MySQL logical vs physical backups §point 1). Plan DR and upgrade strategy together: the logical/physical choice is the upgrade-path choice.
Kin
- MySQL logical vs physical backups — the mechanism comparison, with the USER take validated point-by-point
- Betting on Percona — XtraBackup/PXC/PS lane this rides on
- Open source backup software — the storage side (restic/borg class)
- High Availability — replication/delayed-replica lane