Thinking a Nightly mysqldump Is Consistent Without --single-transaction

A common assumption goes like this: a cron job runs mysqldump every night, the file restores without errors, so there is a usable snapshot of the database. On a quiet blog, that is usually true. On a store that keeps taking orders while the dump runs, the file can hold the orders table as it looked at one moment and the order items table as it looked a minute later. It imports cleanly, and the data inside does not agree with itself. Whether that happens depends on a few options in the backup command that most people never look at after the job is first set up.

What mysqldump does by default

mysqldump is a logical backup tool. It connects like any other client, reads each table with ordinary SELECT queries, and writes out SQL statements that recreate the schema and rows. It does not freeze the server or copy data files. Tables are read one after another, so without help, each table reflects a different moment in time. The default settings do provide that help, in a blunt way. mysqldump enables --opt out of the box, a bundle of options that includes --lock-tables. With --lock-tables, mysqldump locks all tables of a database with a READ LOCAL lock before dumping that database. Nobody can change those tables until the dump of that database finishes, so the result is consistent. The cost is that writes wait. The LOCAL part allows concurrent inserts only for MyISAM tables. For InnoDB tables, the lock behaves like a plain read lock. On a store, that means checkouts, cart updates, and session writes queue up behind the backup. PHP workers sit waiting on the database, and the site looks hung for as long as the dump takes. The lock is also scoped per database. A dump with --all-databases locks and dumps each database in turn, so two databases that depend on each other (a shop and a separate inventory or CRM database, for example) are still captured at different moments.

How backup jobs lose consistency without anyone noticing

If the default is slow but consistent, the inconsistent dumps usually come from changes made for reasonable reasons. The most common one is a site that freezes every night at backup time. Someone traces it to the dump, adds --skip-lock-tables, and the freeze goes away. Another is a backup user without the LOCK TABLES privilege. The job fails with an Access denied ... when using LOCK TABLES error, and the quickest fix found online is the same flag. A third pattern is a script that loops over tables and runs one mysqldump per table to make splitting or compression easier. Each call is a new session, so locking inside one call does nothing for the others. In all three cases, each table is read at its own moment while the application keeps writing. An order row can exist without its line items. Stock levels can disagree with completed orders. A row can reference a user that was created after the users table was already dumped. Nothing complains during the restore, either. mysqldump output starts by turning off foreign key checks so tables can be loaded in any order, which also means broken references load silently. The problem tends to surface later, when a restored site looks fine at first but some recent orders are incomplete or totals do not match. From the support desk, that is the point where someone finally opens the backup script.

Why InnoDB tables need --single-transaction

InnoDB uses MVCC (multi-version concurrency control). When a row changes, InnoDB keeps the older version around for a while so that transactions already in progress can keep seeing a stable view of the data. --single-transaction uses this. Before reading anything, mysqldump sets the session to REPEATABLE READ isolation and starts a transaction with a consistent snapshot. Every table it reads after that comes from the same point in time, even though other sessions keep inserting and updating. No table locks are taken, so the store keeps working during the backup. --lock-tables and --single-transaction are mutually exclusive (LOCK TABLES would implicitly commit the transaction), and mysqldump turns off table locking when --single-transaction is given. The option has limits worth knowing:

It only protects transactional tables. MyISAM and MEMORY tables do not take part in the snapshot and can still drift during the dump. A database with mixed engines is only partly consistent.
It is not isolated from schema changes. If another session runs ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, or TRUNCATE TABLE during the dump, the affected table can come out wrong or the dump can fail. Plugin updates and migrations often run exactly these statements, so keep them away from the backup window.
A long snapshot is not free. On a busy server, InnoDB has to keep old row versions until the dump finishes, and the dump still adds read I/O. --quick, also part of --opt, streams rows instead of buffering whole tables in memory.
Options that record binary log or replication positions (--source-data on newer MySQL, --master-data on older MySQL and MariaDB) or --flush-logs take a short global read lock at the start. That is normally brief, but it is not zero.

Check what your backup job really runs

The flags that matter are often not visible where people look. The job might be a cron line, a wrapper script, a control panel task, or a backup plugin, and mysqldump also reads options from my.cnf files. Start by finding the actual command.

BASH

# Cron entries for the current user and system-wide (paths vary by distro)
crontab -l
sudo grep -rn 'mysqldump' /etc/cron* /var/spool/cron 2>/dev/null

# While the job is running, show the exact arguments it was started with
ps -eo pid,etime,args | grep '[m]ysqldump'

# Options mysqldump picks up from option files such as my.cnf and ~/.my.cnf
mysqldump --print-defaults

Look for --skip-lock-tables, --lock-tables=0, --skip-opt, or a loop calling mysqldump per table. If the ps output shows a password on the command line, fix that too: any local user can read process arguments, so the credentials belong in an option file with tight permissions. If a control panel or plugin builds the dump, check its documentation or logs for the options it uses. Some WordPress backup plugins export tables through PHP instead of mysqldump, in which case none of these flags apply and you need to find out how that plugin reads the tables. Next, check which storage engines you actually have, because --single-transaction only covers InnoDB.

SQL

-- Count tables per engine in each application database
SELECT table_schema, engine, COUNT(*) AS tables
FROM information_schema.tables
WHERE table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
  AND table_type = 'BASE TABLE'
GROUP BY table_schema, engine
ORDER BY table_schema, engine;

If everything is InnoDB, --single-transaction gives a consistent dump without blocking writes. If a few old MyISAM tables remain, either convert them (test first, and do it outside peak hours) or accept that those tables can drift.

The production pattern, and a restore test that proves it

For an all-InnoDB database, the dump should use --single-transaction, and the pipeline should not hide failures. Without pipefail, a mysqldump that dies halfway still produces a valid-looking gzip file and a successful exit code from gzip. A dump only counts once it has been restored somewhere, so the same block includes a test restore.

BASH

# Dump one database with a consistent InnoDB snapshot
set -o pipefail
mysqldump --single-transaction shopdb | gzip > /backup/shopdb-$(date +%F).sql.gz

# Test restore into a separate, empty database (file name is a placeholder)
mysql -e 'CREATE DATABASE restore_test'
gunzip < /backup/shopdb-YYYY-MM-DD.sql.gz | mysql restore_test

# Illustrative check: line items whose parent order is missing (adjust table and column names)
mysql restore_test -e 'SELECT COUNT(*) FROM order_items oi LEFT JOIN orders o ON o.id = oi.order_id WHERE o.id IS NULL'

One warning before running a test restore. If the dump was made with --databases or --all-databases, the file contains CREATE DATABASE and USE statements with the original names. Piping it into mysql will write over the live database, regardless of which database you name on the command line. Restore those dumps on a separate server or a throwaway instance, never on production. In the restored copy, compare row counts for the busiest tables against production and run a few relationship checks like the one above: line items without orders, order meta without orders, sessions without users. Use the real table names for your application. A dump taken during real traffic is the meaningful test, because a dump taken while nothing writes looks consistent no matter which flags were used. A clean check does not prove the snapshot was consistent, only that this particular dump happened to be. The proof is the combination of the right flags and an all-InnoDB schema. The restore test confirms that the file is complete and usable, which is something a backup is never trusted for until it has been done. For the broader question of which copies count as backups at all, see why replication to a second server is not a backup. If you only do one thing this week, find the actual command your nightly backup runs and look for --skip-lock-tables or a per-table loop. If the database is InnoDB, switch to --single-transaction, add pipefail, keep schema changes out of the backup window, and restore the next dump into a scratch database to confirm it holds together.