Why does mysqldump use --single-transaction here instead of locking the tables?

--single-transaction opens one REPEATABLE READ transaction and dumps every table from that same consistent snapshot, so InnoDB tables can keep accepting writes from other sessions during the dump without a stale or torn backup. It only produces a consistent result for InnoDB tables — a database with non-transactional tables still needs table locking to guarantee consistency.

Does restoring a mysqldump file undo only the accidental change?

No — this lesson demonstrates that directly. Restoring replaces the entire dumped database with its state at dump time, which undoes the accident but also erases every legitimate change made between the backup and the restore, with no way to keep one and discard the other from the dump file alone.

What is source-data=2 actually recording in the dump file?

It writes the primary's exact binary log coordinates (file name and byte position) at the moment the dump's snapshot was taken, as a commented-out CHANGE REPLICATION SOURCE TO statement. That position is exactly what a point-in-time recovery needs to know where binary log replay should start from — this lesson shows where that number comes from even though replaying it wasn't possible in this environment.

How would you actually recover the legitimate change this lesson lost?

By replaying the binary log with mysqlbinlog, using --start-position from the dump's recorded coordinate and --stop-position set to just before the mistaking statement's position, then piping that output into mysql — applying every real change in between while excluding the mistake. This lesson documents that command rather than running it, because the mysql:8.4 image used to build these examples does not ship the mysqlbinlog binary.

Is a schema-only dump ever useful without the data?

Yes — mysqldump --no-data captures table structure, indexes, and the current AUTO_INCREMENT counter with no rows, which is enough to recreate an empty copy of a database's shape for a new environment or to diff against a previous schema, without moving any actual data.

Backup and Recovery: What a Restore Actually Gives Back

A real mysqldump, a real DELETE with no WHERE clause, and a real restore — which undoes the mistake but also erases legitimate work done after the backup, exposing exactly what a full logical restore can't do alone.

By SkillByExample · Published Sep 13, 2026

mysql backup mysqldump recovery

mysqldump is one command. What it actually restores — and what it quietly does not — only shows up once a real mistake and a real restore happen back to back.

A real backup, with its exact position recorded

$ mysqldump -uroot -proot --single-transaction --source-data=2 bookshop > bookshop_backup.sql

-- inside bookshop_backup.sql
CHANGE REPLICATION SOURCE TO SOURCE_LOG_FILE='binlog.000002', SOURCE_LOG_POS=14580;

--source-data=2 writes that line as a comment — it’s the binary log coordinate that corresponds exactly to this dump’s snapshot, captured automatically rather than read off a status command by hand.

A real mistake

DELETE FROM accounts;
SELECT COUNT(*) FROM accounts;

COUNT(*)
0

No WHERE clause, no error, no confirmation prompt — three rows gone in one statement, exactly the kind of accident a backup exists for.

The restore — and what it doesn’t give back

$ mysql -uroot -proot -e "DROP DATABASE bookshop; CREATE DATABASE bookshop;"
$ mysql -uroot -proot bookshop < bookshop_backup.sql

SELECT * FROM accounts;
SELECT * FROM books;

id  name   balance
1   Alice  500.00
2   Bob    300.00
3   Carol  1000.00

id  title        genre  stock
1   Dune         SciFi  45
2   Foundation   SciFi  30
3   Neuromancer  SciFi  116

accounts is back — the mistake is undone. But between taking this backup and running the DELETE, a real row had been legitimately added to books: a fourth title, Snow Crash. It is not in this result. The restore didn’t distinguish “the row I need back” from “the row I didn’t” — it replaced the whole database with exactly what existed at dump time, so anything written after the dump and before the restore is gone regardless of whether it was the mistake or not.

What actually closes that gap

The dump’s recorded position — binlog.000002 at 14580 — is exactly where a point-in-time recovery would start. The real command is:

$ mysqlbinlog --start-position=14580 --stop-position=<position just before the mistake> \
    binlog.000002 | mysql -uroot -proot bookshop

That replays every change recorded between the backup and the mistake — including the Snow Crash insert — while stopping short of the DELETE itself. This lesson documents that command rather than running it: the mysql:8.4 image used to build every example on this site does not include the mysqlbinlog binary (mysqldump is present at /usr/bin/mysqldump; mysqlbinlog is not, and installing MySQL’s client package inside the container found no matching package in the image’s configured repositories). A full server install or MySQL’s own client-tools package provides it in a normal deployment.

Practice

1. Confirm the dump file recorded a binary log position rather than requiring you to look it up separately.

$ grep CHANGE bookshop_backup.sql
CHANGE REPLICATION SOURCE TO SOURCE_LOG_FILE='binlog.000002', SOURCE_LOG_POS=14580;

The coordinate travels with the backup file itself — anyone restoring it later has exactly what a point-in-time recovery needs, without depending on separately-kept notes from when the dump was taken.

2. Take a schema-only backup of books and check what it captures without any row data.

$ mysqldump -uroot -proot --no-data bookshop books > books_schema_only.sql

CREATE TABLE `books` (
  `id` int NOT NULL AUTO_INCREMENT,
  `title` varchar(200) NOT NULL,
  `genre` varchar(50) NOT NULL,
  `stock` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Structure, types, and the current AUTO_INCREMENT counter are all present — zero rows are. AUTO_INCREMENT=4 here reflects that ids 1 through 3 were already used at dump time, which is metadata about the table, not about any specific row.

3. Explain why restoring dropped and recreated the whole bookshop database instead of restoring only the accounts table.

The mistake was scoped to one table, but mysqldump bookshop (with no table name) captured every table in the database, and restoring it replays every CREATE TABLE and INSERT statement in the file — there’s no “restore just the part that changed” step in a plain SQL dump. Restoring only accounts would have meant dumping and restoring that single table specifically (mysqldump bookshop accounts), which would have left books, order_items, and stock_log completely untouched instead of being reset to their dump-time state as a side effect.

This closes out the practical MySQL series: measuring what a query actually does with EXPLAIN, indexing to change what it does, joins that silently multiply instead of matching, transaction isolation, locking and deadlocks, procedures and triggers that run without being asked, replication, and now the backup that gives some of it back — and the real gap this lesson exposed in doing so.

A real backup, with its exact position recorded

A real mistake

The restore — and what it doesn’t give back

What actually closes that gap

Practice

Frequently Asked Questions