Logical Backups

Logical Backups

Logical Backups with mysqldump and MySQL Shell

A logical backup is SQL or text that re-creates the data: portable across versions, slow to restore at scale. A physical backup copies InnoDB's files: fast, but only to a compatible server.

A consistent dump of shop with its routines and eventsSQL

mysqldump -P 3317 --single-transaction --source-data=2 --routines --events \
  --databases shop > full.sql 2> dump.err; echo "exit status: $?"
grep -n -E "GTID_PURGED|CHANGE REPLICATION|^-- Dump completed" full.sql | cut -c 1-90

Output

exit status: 0
24:SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ 'f8b4dc41-b72a-11f1-9c16-a282e0d51493:1-25';
30:-- CHANGE REPLICATION SOURCE TO SOURCE_LOG_FILE='binlog.000003', SOURCE_LOG_POS=762;
260:-- Dump completed on 2026-09-23 16:56:44

--single-transaction reads every InnoDB table from one snapshot without blocking writers; --source-data=2 and GTID_PURGED record where the dump stands in the binary log (Point-in-Time Recovery). dump.err held one warning: a partial dump carries the server's GTIDs unless you pass --set-gtid-purged=OFF.

MySQL Shell 524 's dump utilities replace mysqlpump (removed in 8.4) with parallel, zstd-compressed dumps and loads (Replication and GTIDs builds a replica with one):

A parallel instance dump with MySQL Shell 26.7.1SQL

mysqlsh root@127.0.0.1:3317 -- util dump-instance /backup/shop-full --threads=4

Output

...
1 out of 5 schemas will be dumped and within them 7 tables, 0 views.
...
Running data dump using 4 threads.

A backup is proven only by a restore: full.sql loaded on the spare server with exit status 0, and CHECKSUM TABLE gave identical values for all four tables on both servers.

For physical backups, Percona XtraBackup 1,558 (github.com/percona/percona-xtrabackup (https://github.com/percona/percona-xtrabackup 1,558 ), GPL-2.0) copies a running server's files and replays the redo log over them. Its 9.7 series is only a release candidate (9.7.1-rc1, 15 July 2026), so test it before trusting it on 9.7. MySQL Enterprise Backup 524 is the commercial equivalent; CLONE and EBS 24 snapshots (EBS Volumes and Snapshots) also work. Schedule backups from cron (Cron, Logs and journalctl) and copy them off the server, for example to S3 (Essential AWS Services).

MENU