Take a consistent logical backup with GTID info using mysqldump

Command

mysqldump -h db1 -u backup -p \
  --single-transaction --source-data=2 --set-gtid-purged=ON \
  --routines --events --triggers --hex-blob \
  --all-databases | gzip > /backup/full_$(date +%F_%H%M).sql.gz

# Restore: GTID sets must not overlap on the target
zcat /backup/full_2026-10-08_0200.sql.gz | mysql -h db2 -u root -p

What it does

Dumps InnoDB tables within a single consistent snapshot and writes the binlog file / position (as a comment) and a SET @@GLOBAL.GTID_PURGED statement at the top, so the dump can seed a replica or serve as the starting point for binlog-based PITR.

When to use it

For databases up to a few tens of GB, for moving schemas / tables, and for logical migration between versions.

Caution

--single-transaction is consistent for InnoDB only; MyISAM tables come out inconsistent. DDL (ALTER / RENAME / TRUNCATE) on a table during the dump makes the dump fail or miss the table. --source-data / --set-gtid-purged take a brief global read lock at the start (RELOAD privilege required); if a long query is running at that moment, all writes stall while the lock waits. A partial dump (--databases) still writes all GTIDs; use --set-gtid-purged=OFF when moving a subset to another server. The dump is single-threaded; for large data prefer MySQL Shell dump or XtraBackup.