When dumping an InnoDB database using mysqldump, it is often recommended to use the --single-transaction and --skip-table-locks options. This allows for minimal blocking of other concurrent reads + writes to the database.
However, there is the following nasty gotcha in the documentation:
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.
My backups are automated and periodic. I have no easy way to ensure that no one is going to issue such a command against a table while the dump is in progress.
So, I was wondering if there is a simple way to raise the isolation level of the dump from 'repeatable read' to 'serializable' and whether or not this would solve this gotcha while still minimizing blockage of concurrent reads + updates?
I believe that running with --lock-tables or --lock-all-tables instead doesn't suffer from the above gotcha, but I believe these approaches cause far more blocking.
I'm wondering if there is a happy middle ground between the two approaches? Or, if I want a surely safe and consistent dump then I still need to go with --lock-tables, or even maybe --lock-all-tables, instead of --single-transaction --skip-lock-tables?