Backups That Actually Restore

You have a cron job that dumps the database every night. It has run without error for eight months. Then a migration ships with the wrong WHERE clause, you reach for last night's dump, and it aborts before it creates a single table:

ERROR 3546 (HY000) at line 24: @@GLOBAL.GTID_PURGED cannot be changed: the added gtid set must not overlap with @@GLOBAL.GTID_EXECUTED

Nothing is wrong with the dump file. Line 24 sets @@GLOBAL.GTID_PURGED to the set of transactions the server had already executed when the dump was taken, and a server refuses a set that overlaps its own. The command that wrote it, mysqldump --single-transaction --source-data=2 --databases m6lab, is the one most tutorials give, and it is correct for restoring onto a server with an empty transaction history. It is wrong for the machine the dump came from, which is where you will be standing.

One flag fixes this wall: --set-gtid-purged=OFF leaves the GTID statements out and the same file restores. The flag is not the lesson. A backup nobody has restored is not evidence of anything, and the failure surfaces at the worst possible moment because that is the only moment anyone tests it. Everything below ran on MySQL 9.6.0 with InnoDB and a 5,000-row table. PostgreSQL 16.14 is installed here with no running server, so its behaviour comes from its own manual and is named as such.

A logical dump is a query result, and it inherits the engine's consistency rules

mysqldump reads your tables and writes SQL statements that rebuild them. That is the whole mechanism. The output is portable, readable and compressible, and the restore is a full replay of index builds and constraint checks rather than a file copy. On this schema the dump is 252,636 bytes and takes 0.57 seconds; restoring it into a second schema on the same server takes 0.086 seconds and produces identical counts:

mysqldump -uroot --single-transaction --set-gtid-purged=OFF --source-data=2 m6lab > dump.sql
mysql -uroot -e "DROP DATABASE IF EXISTS m6lab_restore; CREATE DATABASE m6lab_restore;"
mysql -uroot m6lab_restore < dump.sql

src_orders  dst_orders  src_audit  dst_audit
      5000        5000        500        500

The dump uses no --databases flag, and that matters for the restore. With it, the file contains CREATE DATABASE m6lab and USE m6lab, so loading it recreates the schema you are trying to recover from under the name you already have. Without it, the file has no schema name and loads anywhere. That is what a rehearsal needs, and a dump that only ever loads into its own schema has not been tested at all.

A consistent snapshot comes from a transaction, so MyISAM tables do not get one

--single-transaction does not stop your writes and does not lock the tables. It opens one repeatable-read transaction with a consistent snapshot and reads every InnoDB table inside it, so the dump is the state of those tables at the moment the transaction started, whatever else commits while it runs. That is what makes an online dump of a busy InnoDB schema possible.

It works because InnoDB gives transactions a snapshot. MyISAM has no transactions, so a MyISAM table read inside that same transaction is read live. Here is the demonstration: one session opens a repeatable-read transaction, another session inserts into both tables and commits, and the first session reads both tables again before committing.

-- session 1
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION WITH CONSISTENT SNAPSHOT;
SELECT COUNT(*) FROM orders;        -- InnoDB
SELECT COUNT(*) FROM audit_events;  -- MyISAM
-- session 2, while the transaction above is open
INSERT INTO audit_events (order_id, note) VALUES (1, 'inserted-during-dump');
INSERT INTO orders (customer_id, status, amount_cents, created_at) VALUES (1, 'paid', 1, NOW());
-- session 1, before COMMIT
SELECT COUNT(*) FROM orders;
SELECT COUNT(*) FROM audit_events;

first read,  orders (InnoDB): 5000
first read,  audit_events (MyISAM):  500
... a commit happens in another session ...
second read, orders (InnoDB): 5000
second read, audit_events (MyISAM):  501

The InnoDB count is stable and the MyISAM count moved inside a transaction that is supposed to be looking at one instant. So --single-transaction gives you a consistent dump of your InnoDB tables and an inconsistent one of everything else in the same run, with no warning in the output. If a schema mixes engines, the MyISAM tables come back with a foreign key pointing at a row deleted mid-dump. Check what you are dumping with SELECT table_name, engine FROM information_schema.tables WHERE table_schema = 'm6lab' before you trust the flag; MySQL's manual scopes --single-transaction to transactional tables, which is the scope it has.

A physical backup moves the problem instead of removing it

A physical backup copies the files the engine reads: data files, redo logs, undo logs. Restoring is a file copy, so it is fast and preserves everything. It is also engine-version specific and useless for moving one table to another server.

Before trusting a file copy, watch what one does. SQLite is the cheapest place to see it, because WAL mode keeps committed transactions in a side file until a checkpoint. This session inserts 2,001 rows with automatic checkpointing off, then copies the database file while the connection is still open:

sqlite3 s2.db <<'SQL'
PRAGMA journal_mode=WAL;
PRAGMA wal_autocheckpoint=0;
CREATE TABLE t(id INTEGER PRIMARY KEY, v TEXT);
INSERT INTO t(v) SELECT 'row-'||value FROM generate_series(1,2000);
INSERT INTO t(v) VALUES('committed but not checkpointed');
SELECT COUNT(*) FROM t;
.shell cp /tmp/sql-m6-lab/s2.db /tmp/sql-m6-lab/copy2.db
SQL
sqlite3 copy2.db "SELECT COUNT(*) FROM t;"

2001
Error: in prepare, no such table: t

The copy was 4,096 bytes and every row was still in the -wal file, so the copy on its own opens as a database with no table in it. A logical dump from the same live connection at the same moment contained all 2,001 rows: sqlite3 s2.db .dump wrote 75,886 bytes in 0.009 seconds. This is the hazard in every file-copy backup. The engine's definition of "the database" is more than the main file, and a copy of the main file alone is a copy of a prefix of the truth.

MySQL has the same property with more moving parts. The data directory here is 930 MB, and a copy taken while the server runs contains torn pages and a redo log that does not match the data files. The supported ways out are to stop the server first, or to use a hot-backup tool that coordinates with InnoDB. Neither Percona XtraBackup nor MySQL Enterprise Backup is installed here, so an online physical backup is not available on this machine and I will not describe one as if I had run it. PostgreSQL's equivalent is pg_basebackup, which its manual describes as copying the cluster's files while putting the server into and out of backup mode automatically.

Binary logs are what turn a backup into a recovery point

A nightly dump restores you to last night. Everything committed since is gone, which for a busy schema is the part people care about. The binary log records those commits, and a point-in-time recovery is a restore followed by a replay to a chosen position. Here is the whole thing on this server. The dump records the coordinate it was taken at, so the replay starts exactly there:

mysqldump -uroot --single-transaction --set-gtid-purged=OFF --source-data=2 m6lab > pitr_base.sql
grep SOURCE_LOG_FILE pitr_base.sql
# 100 rows are written after the backup
mysql -uroot -N -e "SHOW BINARY LOG STATUS;"     # last good position
mysql -uroot m6lab -e "DROP TABLE orders;"       # the incident
mysql -uroot m6lab < pitr_base.sql               # restore the dump
mysqlbinlog --skip-gtids --database=m6lab \
  --start-position=322434570 --stop-position=322437566 \
  /opt/homebrew/var/mysql/binlog.000134 | mysql -uroot m6lab

backup coordinate recorded in the dump: binlog.000134 @ 322434570
dump size: 252636 bytes
writes after the backup: 100 rows; last good position binlog.000134 @ 322437566
incident: DROP TABLE orders
restored dump:            5000 rows in 0.089812 s
replayed binary log:      5100 rows in 0.017718 s
recovered post-backup rows: 100
whole rehearsal:          9.870768 s
replay script size:       5871 bytes

The replay is 5,871 bytes of events and it puts back the 100 rows the dump did not have. Two flags in that command are not optional. --database=m6lab filters the replay to one schema, because the binary log is server-wide and contains every other database's writes. --skip-gtids is what makes the replay work on this server, which has gtid_mode=ON.

That second flag is worth pausing on, because without it the failure is silent. The replay script then contains SET @@SESSION.GTID_NEXT='...' for each transaction, the server sees GTIDs it has already executed, and it skips every one of them. mysql exits zero. I ran the same replay both ways:

mysqlbinlog --database=m6lab --start-position=322434570 --stop-position=322437566 binlog.000134 | mysql -uroot m6lab
SELECT COUNT(*) FROM orders;   --> 5000   (nothing applied, no error)

mysqlbinlog --skip-gtids --database=m6lab --start-position=322434570 --stop-position=322437566 binlog.000134 | mysql -uroot m6lab
SELECT COUNT(*) FROM orders;   --> 5100   (100 rows recovered)

A recovery procedure that reports success while restoring nothing is the worst version of this failure, and it is why the rehearsal is not paperwork. PostgreSQL reaches the same place by a different road. Continuous archiving keeps WAL segments and replays them onto a base backup, and the manual's claim is that this "supports point-in-time recovery: it is possible to restore the database to its state at any time since your base backup was taken." The stopping point is configured through settings such as recovery_target_time and recovery_target_lsn. Note also that pg_dump has no --single-transaction option; pg_dump --help on 16.14 lists --snapshot and --serializable-deferrable instead, because a pg_dump is internally consistent by default. Carrying the MySQL flag across is a habit rather than a feature.

Record the recovery point you achieved, not the one you intended

Two numbers come out of a rehearsal, and teams write down the wrong one. The intended recovery point objective is a policy: "we dump every night, so we can lose a day". The achieved one is a measurement of the procedure you have rehearsed. On this schema it is not the dump interval, because the binary log closes the gap. If the binlog files sit on the same disk as the data and the dump, the achieved objective is zero and all three fail together.

What the rehearsal measured is the shape of the honest answer table:

Intended	Measured on this schema
Nightly dump, RPO 24 hours	Dump takes 0.57 s and is 247 KiB
Restore "quickly"	0.086 s for 5,000 rows into a second schema
Binlogs close the gap	5.9 KB replayed in 0.018 s; 100 rows recovered
Zero data loss	True only while the binlog files survive the incident

Those numbers are small because the schema is small, and the ratios are what transfer. A dump is roughly proportional to data size, a restore is dominated by index builds, and a replay is proportional to the number of changes rather than the amount of data. Time all three at production scale. The first time you learn that a restore takes four hours should not be during the incident.

The thing this cannot fix is the case people ask about first. If a bad migration drops a column and the application keeps writing for two days, the binlog will faithfully replay those writes into a schema that no longer has the column. Recovery to a point in time is not repair, and the rehearsal shows you how long the window between an incident and its discovery actually is.

Show the solution and the reasoning

Any restore that ends with matching row counts is fine; what matters is which failure the rehearsal found. If it aborted with error 3546, the cause is the SET @@GLOBAL.GTID_PURGED statement that --source-data=2 writes when GTID mode is on, and the fix is --set-gtid-purged=OFF. Do not respond by deleting --source-data without thinking: you need that coordinate for the replay.

The row counts are the part people skip. A dump that restores without error into a schema with the wrong number of rows has told you nothing, and the counts are how you catch the MyISAM problem, where a table can come back with a plausible row count and a foreign key pointing at nothing. If any table is not InnoDB, either move it or accept that its restore may be internally inconsistent and write that into the runbook.

On the replay, the two flags are the answer, not incidental detail. --database=m6lab exists because the binary log is shared. --skip-gtids exists because with gtid_mode=ON the server silently skips GTIDs it has already executed, so the replay applies nothing and exits successfully. If your recovery test passes on the first try without --skip-gtids, check the row count before believing it; a replay that restored nothing looks exactly like a replay that had nothing to restore.

The last question has no comfortable answer. If the dumps and the binary logs live on the same disk as the data, losing that disk loses all three, and the achieved recovery point objective is "everything", whatever the policy document says. The number that matters is the one you can prove, and the only way to have it is to run the procedure end to end, including the replay, against a copy of production data.