mysqldump --single-transaction and Consistent Backups: “It Finished” Is Not “It Restores”

The 3-2-1 rule tells you where to keep backups. It says nothing about whether the contents are correct.

mysqldump is a common way to back up a database. But if you dump a database that is still being written to, the file can finish without any error and still restore into data that disagrees across tables. This article reproduces that on a local MySQL and explains what --single-transaction is for. It is a general explanation, not a description of any specific product.

Note: The experiment ran on October 17, 2026 on a local MySQL 9.6 (InnoDB). Counts vary with environment and timing, so read the numbers as one observed example.

What “consistent” means

Many WordPress tables depend on each other: posts and post meta, orders and order items. A row on one side is meaningless without its counterpart.

A consistent backup is a snapshot of the whole database at one point in time. All tables must be in the state they had at the same moment.

The catch is that mysqldump writes tables one after another. On a large database the dump takes minutes. If the site keeps writing meanwhile, the first table and the last table are captured at different times.

Experiment: dump while writes continue

We created two tables:

• orders: the parent
• items: exactly three rows per order, referencing orders through order_id

We loaded 300,000 orders and 900,000 items, then ran another connection that repeatedly adds one order plus its three items in a single transaction. At every instant, the writer keeps the data consistent.

We then dumped with --skip-lock-tables, without a transaction. mysqldump printed this warning:

In order to ensure a consistent backup of the database, pass
--single-transaction or --lock-all-tables or --source-data.

The dump finished with no error. We restored it into a separate database and checked:

Items are written after orders was already dumped, so items for orders added in between ended up orphaned. The writer was always consistent; the way the dump was cut created a state that never existed.

The worrying part is that this is invisible until you restore. File size and exit code look normal.

What --single-transaction does

With --single-transaction, mysqldump opens a transaction first and reads every table inside it. InnoDB keeps serving the data as of the transaction’s start, so changes made by other connections during the dump are not visible to it. The result is a cut at one point in time across tables.

It also does not block writes, unlike approaches that lock tables.

We did not finish the post-restore check for the --single-transaction run in this experiment. This explanation rests on MySQL’s documented behavior, so confirm it in your own environment with a restore test.

Where it does not apply

1. Tables must be InnoDB. Non-transactional engines such as MyISAM are not covered by the snapshot.
2. No schema changes during the dump. ALTER TABLE or DROP TABLE can make the dump fail or its content inaccurate.
3. Long transactions have a cost. The longer the dump, the more change history must be retained.

Check your engines like this:

SELECT table_name, engine
FROM information_schema.tables
WHERE table_schema = DATABASE();

A practical routine

1. Check the engines. Look for anything other than InnoDB.
2. Fix and record the command. For example:

mysqldump --single-transaction --set-gtid-purged=OFF \
  -u USER -p DBNAME > backup.sql

--set-gtid-purged=OFF keeps GTID information out of the restore target. In our experiment, restoring without it produced an error. Whether you need it depends on your setup.

1. Restore into a separate database. Compare counts and check cross-table relations, such as rows without parents.
2. Record that it restored. Note when, which dump, and where.

Step 3 matters most. A correct command is not proof of a working backup until you have actually restored it.

Summary

• A dump that finishes is different from a backup that restores consistently
• mysqldump writes tables in order, so with live writes the tables can be captured at different times; here that produced 4,239 orphaned items
• --single-transaction reads all tables in one transaction, giving a single point-in-time cut without blocking writes
• It assumes InnoDB and no schema changes during the dump
• Include restoring elsewhere and checking in your backup routine