RPO
How much data? · acceptable loss
RTO
How fast? · time to restore
Rule
3-2-1-1-0 · 3 copies, 2 media, 1 off-site
+1 / +0
immutable · 0 restore-test errors
Golden Rule
An untested backup = no backup
01
Fundamentals & Strategy
| Concept | Description |
|---|
| Logical | SQL / object-level dump. Version & platform independent, easy selective restore; slow restore on large data (indexes are rebuilt). |
| Physical | Block copy of the data files. Fast restore, fits TB scale; requires same major version + platform. |
| Snapshot | LVM / ZFS / EBS / SAN snapshot. Crash-consistent; safe if WAL/redo is on the same volume or backup mode is used. |
| Full / Incr / Diff | Everything / changes since last backup / since last full. The longer the incremental chain, the higher restore time and risk. |
| Hot / Warm / Cold | Reads+writes allowed / reads only / server stopped. Production target: hot. |
| Continuous Archive | Full backup + continuous binlog stream → PITR: return to any moment. Brings RPO down to seconds. |
Choosing a Method
| Scenario | Recommendation |
|---|
| < 100 GB, version migration, single schema | util.dumpSchemas / mydumper |
| TB scale, short RTO | XtraBackup / MEB + binlog |
| RPO in seconds | Semi-sync replica + binlog stream |
| "I deleted it by mistake" protection | PITR + SOURCE_DELAY |
Tool Map
| Logical | mysqldump · MySQL Shell util.* · mydumper |
| Physical | XtraBackup · MEB (mysqlbackup) · CLONE |
| Binlog | mysqlbinlog · SHOW BINARY LOGS |
| InnoDB Only | Hot physical backup and consistent dumps are guaranteed only for InnoDB |
Replication is not a backup: DROP TABLE or a bad UPDATE reaches every replica within milliseconds. HA ≠ DR.
02
Logical Backup
| Tool | Parallel | Note |
|---|
| mysqldump | ✗ | Available everywhere; single thread, small/medium data |
| mysqlpump | ✓ | deprecated in 8.0.34, removed in 8.4 — avoid |
Shell util.* | ✓ | dumpInstance / dumpSchemas / dumpTables + loadDump; zstd, chunking, resume |
| mydumper | ✓ | Open source, parallel restore with myloader |
mysqldump — Production Flags
$ mysqldump --single-transaction --source-data=2 \
--routines --events --triggers --hex-blob \
--set-gtid-purged=ON --all-databases \
| zstd -T0 > /bk/full_$(date +%F).sql.zst
# --source-data=2 → binlog position written as a comment
# (before 8.0.26: --master-data=2)
MySQL Shell Dump & Load (JS)
util.dumpInstance('/bk/inst_2026-10-13',
{threads: 8, compression: 'zstd', consistent: true})
util.dumpSchemas(['shop'], '/bk/shop', {threads: 8})
// target needs local_infile=ON
util.loadDump('/bk/inst_2026-10-13',
{threads: 8, deferTableIndexes: 'all'})
Restore
$ zstd -dc full.sql.zst | mysql -u root -p
$ myloader -d /bk/mydumper -t 8 --overwrite-tables
Caution: --single-transaction is consistent only for InnoDB; MyISAM tables stay inconsistent. Do not run DDL (ALTER/TRUNCATE) during the dump — it breaks consistency.
Speed: Shell util.loadDump and myloader are much faster than a plain SQL restore: they load in parallel and build indexes last.
03
Physical Backup
| Tool | License | Note |
|---|
| XtraBackup | GPL | Hot, full + incremental, stream (xbstream), compression, encryption |
| MEB | Enterprise | mysqlbackup: image backup, incremental, TDE support |
| CLONE | Built-in | 8.0.17+: CLONE LOCAL DATA DIRECTORY — local physical copy |
XtraBackup — Full + Incremental
$ xtrabackup --backup --target-dir=/bk/full
$ xtrabackup --backup --target-dir=/bk/inc1 \
--incremental-basedir=/bk/full
# PREPARE: --apply-log-only on all but the last incremental
$ xtrabackup --prepare --apply-log-only --target-dir=/bk/full
$ xtrabackup --prepare --target-dir=/bk/full \
--incremental-dir=/bk/inc1
# RESTORE: empty datadir, server stopped
$ xtrabackup --copy-back --target-dir=/bk/full
$ chown -R mysql:mysql /var/lib/mysql
Stream → Remote Server / Object Storage
$ xtrabackup --backup --stream=xbstream --compress \
--parallel=4 | ssh bk01 "xbstream -x -C /bk/db1"
Local Copy with CLONE & MEB
INSTALL PLUGIN clone SONAME 'mysql_clone.so';
CLONE LOCAL DATA DIRECTORY = '/bk/clone_20261013';
$ mysqlbackup --backup-dir=/bk/meb \
--backup-image=full.mbi backup-to-image
| Position Info | xtrabackup_binlog_info → binlog file, position and GTID set — the starting point for PITR. |
Caution: The XtraBackup version must match the server (8.0 ↔ 8.0, 8.4 ↔ 8.4). An unprepared backup cannot be restored — test the prepare step right after taking the backup.
MySQL Backup & Recovery Reference · Binlog PITR, Verification & Disaster Scenarios
04
Binlog & Point-in-Time Recovery
Critical
| Parameter | Recommended |
|---|
| log_bin | ON (8.0+ default) |
| binlog_format | ROW |
| sync_binlog | 1 — to disk on every commit |
| innodb_flush_log_at_trx_commit | 1 — full durability |
| binlog_expire_logs_seconds | ≥ age of the oldest backup |
| gtid_mode | ON + enforce_gtid_consistency |
Live Binlog Backup (on a separate server)
$ mysqlbinlog --read-from-remote-server --raw \
--stop-never --host=db1 --user=bkp -p \
--result-file=/binlogs/ binlog.000001
Step by Step PITR
1Restore the full backup (dump / XtraBackup / CLONE).
2Find the start position: dump header or xtrabackup_binlog_info.
3Find the position of the bad event (below).
4Replay binlogs in a single command up to just before the event.
Find the Event & Replay
$ mysqlbinlog -v --base64-output=DECODE-ROWS \
--start-datetime="2026-10-13 09:50:00" binlog.000043 \
| grep -n -B5 -i "DROP TABLE" # → end_log_pos
$ mysqlbinlog --start-position=157 --stop-position=48211 \
binlog.000042 binlog.000043 | mysql -u root -p
# With GTID: skip the bad transaction, replay the rest
$ mysqlbinlog --exclude-gtids='3E11FA47-…:1234' \
binlog.000042 binlog.000043 | mysql -u root -p
Trap: Do not replay binlog files one by one with mysqlbinlog | mysql — temporary tables and transactions spanning files break. Pass them all in one call.
05
Verification, Testing & Monitoring
| Check | Tool / Method |
|---|
| Backup Integrity | xtrabackup --prepare → "completed OK!", MEB validate |
| Data Integrity | CHECK TABLE, mysqlcheck, CHECKSUM TABLE |
| Replica Consistency | pt-table-checksum → if drift found pt-table-sync |
| Binlog Health | binlog stream process, SHOW BINARY LOGS, gtid_purged |
| Restore Test | Automated, at least weekly; restore on a separate server → validate with queries → measure RTO and record it. |
Binlog Health Check
SHOW BINARY LOGS;
SHOW BINARY LOG STATUS; -- 8.4 (8.0: SHOW MASTER STATUS)
SELECT @@gtid_executed, @@gtid_purged,
@@binlog_expire_logs_seconds;
Integrity Checks
$ mysqlcheck --all-databases --check -u root -p
$ mysqlbackup --backup-image=/bk/full.mbi validate
$ pt-table-checksum --replicate=percona.checksums h=db1
CHECKSUM TABLE shop.orders;
Set Up Alerts
| Age of last successful backup > 26 hours | Critical |
| Binlog stream process down / lag > RPO | Critical |
| Binlog purged before the next backup | Critical |
| Sudden ±30% change in backup size | Important |
| Backup disk usage > 80% | Important |
MySQL 8.4: SHOW MASTER STATUS was removed → use SHOW BINARY LOG STATUS (8.2+). Update monitoring scripts before upgrading.
06
Disaster Scenarios & Checklist
| Scenario | Solution |
|---|
| Accidental DROP / DELETE | Binlog PITR (--stop-position / --exclude-gtids) or pull the table from a delayed replica |
| Disk / server loss | Fail over to a replica → new replica: CLONE INSTANCE FROM |
| Ransomware | Immutable (object lock) off-site copy + xtrabackup --encrypt + separate credentials |
| Corrupt page | CHECK TABLE detects it → restore from backup; last resort innodb_force_recovery |
| Region loss | InnoDB ClusterSet / cross-region backup copy + DR runbook |
Delayed Replica (1 hour)
STOP REPLICA SQL_THREAD;
CHANGE REPLICATION SOURCE TO SOURCE_DELAY = 3600;
START REPLICA SQL_THREAD;
-- during an incident: apply up to just before the bad GTID
START REPLICA SQL_THREAD UNTIL
SQL_BEFORE_GTIDS = '3E11FA47-…:1234';
| RPO / RTO documented and signed off by the businessSLA → method choice | Critical |
| Binlog archiving enabled and monitoredsync_binlog=1 · mysqlbinlog --stop-never | Critical |
| Automated restore testweekly · RTO measurement | Critical |
| Off-site + encrypted copyxtrabackup --encrypt · S3 object lock | Critical |
| Retention policy enforcedbinlog_expire_logs_seconds ≥ backup age | Important |
| Users & grants backed uputil.dumpInstance · SHOW CREATE USER / SHOW GRANTS | Important |
| Config files version-controlledmy.cnf · mysqld-auto.cnf (SET PERSIST) | Recommended |
| DR runbook current and rehearsed2 drills per year | Recommended |
Critical now Important this quarter Recommended plan it