MySQL Backup and Restore

Logical backup

mysqldump --host=db.internal --user=backup \
  --single-transaction \       # consistent snapshot without locking InnoDB tables
  --source-data=2 \            # records the binlog position as a comment
  --routines --events --triggers \
  --databases shop \
  | zstd -T0 > /backups/shop-$(date -u +%Y%m%dT%H%M%SZ).sql.zst

--single-transaction gives a consistent view for InnoDB without blocking writes. It does not apply to MyISAM tables, which need a lock — another reason to keep everything on InnoDB.

mysqlpump and mysqlsh's util.dumpInstance() parallelise dump and load, which matters substantially above a few tens of gigabytes.

Physical backup

Percona XtraBackup copies InnoDB data files while the server runs, then applies the redo log collected during the copy to make the result consistent.

xtrabackup --backup --target-dir=/backups/base \
           --user=backup --password=… --parallel=4 --compress
 
# Prepare makes the copy consistent; it must run before a restore.
xtrabackup --prepare --target-dir=/backups/base
 
# Incremental against a previous backup.
xtrabackup --backup --target-dir=/backups/inc1 --incremental-basedir=/backups/base

Point-in-time recovery

A physical or logical backup plus the binary logs since it was taken:

1. Restore the backup and note the binlog file and position (or GTID set) it corresponds to.
2. Extract binlog events from that position up to the target time.
3. Replay them.

mysqlbinlog --start-position=194 --stop-datetime="2026-07-30 14:22:00" \
  mysql-bin.000042 mysql-bin.000043 > /tmp/replay.sql
 
mysql --host=restored.internal < /tmp/replay.sql

With GTIDs, use --exclude-gtids to skip the specific transaction you are recovering from — precise removal of one bad transaction rather than a time-based cut.

Verification

-- After a restore, confirm the instance is consistent and complete.
SELECT @@global.gtid_executed;
SELECT table_schema, COUNT(*) FROM information_schema.tables GROUP BY table_schema;
CHECK TABLE orders;

Compare row counts against the source where possible, and run pt-table-checksum when both instances are available. Automate a restore drill on a schedule; see Restore Testing.

What else to back up

Users and grants live in the mysql system schema and are not in a per-database dump. Capture them explicitly:

mysql -N -B -e "SELECT CONCAT('SHOW CREATE USER ''', user, '''@''', host, ''';') \
  FROM mysql.user WHERE user NOT IN ('mysql.sys','mysql.session','mysql.infoschema')"

Back up the configuration file and any TLS material alongside the data.