How to obtain a correct dump using mysqldump and single-transaction when DDL is used at the same time?

Question

I'm new to MySQL and I'm figuring out the best way to perform an on-line hot logical backup using mysqldump. This page suggests this command line:

mysqldump --single-transaction --flush-logs --master-data=2
          --all-databases > backup_sunday_1_PM.sql

but... if you read the documentation carefully you find that:

While a --single-transaction dump is in process, to ensure a valid dump file (correct table contents and binary log position), no other connection should use the following statements: ALTER TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE. A consistent read is not isolated from those statements, so use of them on a table to be dumped can cause the SELECT performed by mysqldump to retrieve the table contents to obtain incorrect contents or fail.

So, is there any way to prevent this possible dump corruption scenario? I.e. a commands that could block those statements temporarily.

PS: MySQL bug entry on this subject http://bugs.mysql.com/bug.php?id=27850

Answer 1

Open a mysql command window and issue this command:

mysql> FLUSH TABLES WITH READ LOCK;

This will lock all tables in all databases on this MySQL instance until you issue UNLOCK TABLES (or terminate the client connection that holds these read locks).

To confirm this, you can open another command window and try to do an ALTER, DROP, RENAME or TRUNCATE. These commands hang, waiting for the read lock to be released. Hit Ctrl-C to terminate the waiting.

But while the tables have a read lock, you can still perform a mysqldump backup.

The FLUSH TABLES WITH READ LOCK command may be the same as using the --lock-all-tables option of mysqldump. It's not totally clear, but this doc seems to support it:

Another use for UNLOCK TABLES is to release the global read lock acquired with FLUSH TABLES WITH READ LOCK.

Both FLUSH TABLES WITH READ LOCK and --lock-all-tables use the phrase "global read lock," so I think it's likely that these do the same thing. Therefore, you should be able to use that option to mysqldump and protect against concurrent ALTER, DROP, RENAME, and TRUNCATE.

---

Re. your comment: The following is from Guilhem Bichot in the MySQL bug log that you linked to:

Hi. --lock-all-tables calls FLUSH TABLES WITH READ LOCK. Thus it is expected to block ALTER, DROP, RENAME, or TRUNCATE (unless there is a bug or I'm wrong). However, --lock-all-tables --single-transaction cannot work (mysqldump throws an error message): because lock-all-tables locks all tables of the server against writes for the duration of the backup, whereas single-transaction is intended to let writes happen during the backup (by using a consistent-read SELECT in a transaction), they are incompatible in nature.

From this, it sounds like you cannot get concurrent access during a backup, and simultaneously block ALTER, DROP, RENAME and TRUNCATE.