Backup and Recovery

Full, incremental (XtraBackup/MEB), and binlog-based strategies are combined to meet RPO (acceptable data loss) and RTO (acceptable downtime).

Concept

Logical (SQL/data files) mysqldump, MySQL Shell util.dumpInstance() / util.dumpSchemas() / util.dumpTables() (+ util.loadDump())

Portable across versions/platforms; slower restore for large DBs Physical (data files)

Percona XtraBackup (open source), MySQL Enterprise Backup (commercial), Clone plugin (8.0.17+), filesystem/volume snapshots

Fast backup and restore for large DBs; same major version Binary logs

Continuous change stream; enables point-in-time recovery (PITR)

Full, incremental (XtraBackup/MEB), and binlog-based strategies are combined to meet RPO (acceptable data loss) and RTO (acceptable downtime).

(mysqlpump was deprecated in 8.0.34 and removed in 8.4; use mysqldump or MySQL Shell utilities.)

Why It Matters

Hardware fails, people run DELETE without WHERE, and ransomware exists. Backups that have been restored and tested are the only defense. Replication is not a backup.

Syntax

mysqldump [options] db_name [tables] > file.sql
mysql db_name < file.sql
mysqlbinlog [--start-datetime=...] [--stop-datetime=...] binlog.00001 ... | mysql

Example

Consistent InnoDB snapshot without locking tables --routines --triggers --events

Include stored programs (triggers are included by default; routines and events are not)

--source-data=2 (--master-data before 8.0.26) Write the binlog coordinates as a comment, for PITR/replica setup

Control GTID info when GTIDs are enabled Safe dumping of binary columns

Backup user privileges (typical): SELECT, SHOW VIEW, TRIGGER, EVENT, PROCESS, RELOAD, LOCK TABLES, REPLICATION CLIENT, and for 8.0 dumps with GTIDs, BACKUP_ADMIN where required by the tool.

30.2 MySQL Shell dump utilities (parallel, faster) 30.3 Physical backups

30.4 Binary logs and point-in-time recovery Scenario: full backup at 02:00; at 10:15 someone runs DELETE FROM orders;.

With GTIDs, --exclude-gtids='uuid:1234' skips exactly the bad transaction.

30.5 Backup strategy Small DB, RPO 24 h Nightly mysqldump/util.dumpInstance, 14-day retention, off-site copy Medium DB, RPO 15 min, RTO 2 h

Nightly XtraBackup full + hourly incremental + binlogs shipped every 5 min Critical, RPO ≈ 0, RTO minutes

InnoDB Cluster / semi-sync replication + physical backups + binlog archiving + tested failover

Follow the 3-2-1 rule (3 copies, 2 media, 1 off-site), encrypt backups, and test restores regularly (restore into a scratch server, run row counts and checksums, measure restore time against the RTO).

# Consistent logical backup of one database (InnoDB) including routines, triggers, events
mysqldump -u backup_user -p \
  --single-transaction --routines --triggers --events \
  --set-gtid-purged=OFF \
  eh_company > eh_company_2026-09-29.sql

# All databases, with binary-log position recorded for PITR (8.0.26+ option name)
mysqldump -u backup_user -p --all-databases --single-transaction \
  --source-data=2 --routines --triggers --events > full_2026-09-29.sql

# Compressed
mysqldump -u backup_user -p --single-transaction eh_company | gzip > eh_company.sql.gz

# Schema only / data only / specific tables
mysqldump -u backup_user -p --no-data eh_company > schema.sql
mysqldump -u backup_user -p --no-create-info eh_company orders order_items > orders_data.sql

mysql -u root -p -e "CREATE DATABASE eh_company_restore"
mysql -u root -p eh_company_restore < eh_company_2026-09-29.sql
gunzip < eh_company.sql.gz | mysql -u root -p eh_company_restore

mysqlsh backup_user@localhost -- util dump-instance /backups/full_2026-09-29 --threads=8 --compression=zstd
mysqlsh root@newhost -- util load-dump /backups/full_2026-09-29 --threads=8

MySQL  Py > util.dump_schemas(["eh_company"], "/backups/eh_company", {"threads": 4})
MySQL  Py > util.load_dump("/backups/eh_company", {"threads": 4})

# Percona XtraBackup (hot backup of InnoDB)
xtrabackup --backup --user=backup_user --password=... --target-dir=/backups/base_2026-09-29
xtrabackup --prepare --target-dir=/backups/base_2026-09-29         # apply redo → consistent
# Incremental
xtrabackup --backup --target-dir=/backups/inc_2026-09-30 --incremental-basedir=/backups/base_2026-09-29
# Restore: stop mysqld, empty datadir, then:
xtrabackup --copy-back --target-dir=/backups/base_2026-09-29 && chown -R mysql:mysql /var/lib/mysql

-- Clone plugin: copy a running instance's data to a local directory (or provision a replica)
INSTALL PLUGIN clone SONAME 'mysql_clone.so';
CLONE LOCAL DATA DIRECTORY = '/backups/clone_2026-09-29';

SHOW VARIABLES LIKE 'log_bin';                        -- ON (default in 8.0)
SHOW VARIABLES LIKE 'binlog_format';                  -- ROW (default)
SHOW VARIABLES LIKE 'binlog_expire_logs_seconds';     -- 2592000 (30 days default)
SHOW BINARY LOGS;
SHOW BINLOG EVENTS IN 'binlog.000042' LIMIT 10;
FLUSH BINARY LOGS;                                    -- start a new binlog file

# 1. Restore the 02:00 full backup (on a recovery server ideally)
mysql -u root -p < full_2026-09-29.sql

# 2. Find the binlog position of the bad statement
mysqlbinlog --base64-output=DECODE-ROWS -v --start-datetime="2026-09-29 10:10:00" binlog.000042 | less

# 3. Replay changes from the backup's position up to just before the bad statement
mysqlbinlog --start-position=157 --stop-datetime="2026-09-29 10:14:59" binlog.000041 binlog.000042 | mysql -u root -p

# 4. Optionally skip the bad transaction and replay what came after it (by position or GTID)

-- Verify data after restore
CHECKSUM TABLE eh_company.orders, eh_company.order_items;
SELECT COUNT(*) FROM eh_company.orders;

Expected Result

After restoring into eh_company_restore:

SELECT (SELECT COUNT(*) FROM eh_company_restore.orders)    AS orders,
       (SELECT COUNT(*) FROM eh_company_restore.customers) AS customers;

+--------+-----------+
| orders | customers |
+--------+-----------+
|      6 |         5 |
+--------+-----------+

Common Mistakes

mysqldump without --single-transaction (inconsistent or locking dumps).

Forgetting --routines --events.

Binary logs expiring before the next full backup (breaks PITR).

Backups stored on the same disk/server.

Never testing restores.

Treating replicas as backups.

Best Practices

Define RPO/RTO; choose tools accordingly.

Automate backups, monitoring, retention, encryption, and off-site copies.

Keep binlogs at least as long as the interval between full backups (plus margin).

Restore-test regularly; document runbooks.

Interview Relevance

High for DBA/backend roles: "How do you back up MySQL?", "mysqldump vs XtraBackup?", "How does PITR work in MySQL?", "What does --single-transaction do?".

Practice Questions

Back up eh_company with mysqldump and restore it into a new database.

Enable and inspect binary logs, then recover rows deleted by mistake using PITR.

Design a backup plan for a 500 GB test-results database with RPO 15 min and RTO 1 h.