Consistent InnoDB dumps with mysqldump

Question

The MySQL documentation recommends that when dumping an InnoDB with mysqldump to use --single-transaction to get a transactionally consistent snapshot of the database with minimal blocking of other transactions. Unfortunately, this only works if you can somehow prevent any other connection from executing a schema change during the dump:

https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html

While a --single-transaction dump is in process, to ensure a valid dump file (correct table contents and binary log coordinates), no other connection should use the following statements: ALTER TABLE, CREATE 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 that is performed by mysqldump to retrieve the table contents to obtain incorrect contents or fail.

What is a good way of ensuring that no schema changes happen during a dump? My use case is that I periodically (cronjob) back up my database every hour using the mysqldump utility.

I thought that maybe using mysqldump's default of --lock-tables instead would give me transactional consistency, but:

The --single-transaction option and the --lock-tables option are mutually exclusive because LOCK TABLES causes any pending transactions to be committed implicitly.

Is that saying that if someone runs a mysqldump --lock-tables against an InnoDB that it can actually cause an ongoing transaction in another session to no longer be transactional? I hope not -- that seems insane. Elsewhere:

https://dev.mysql.com/doc/refman/8.0/en/lock-tables.html#lock-tables-and-transactions

LOCK TABLES and UNLOCK TABLES interact with the use of transactions as follows:

LOCK TABLES is not transaction-safe and implicitly commits any active transaction before attempting to lock the tables.

This makes sense within the context of a single DB session. If you in the middle of a transaction on your session and do this, then it implicitly commits your open transaction first. It isn't clear what it does, if anything, to transactions on other sessions. I'm hoping that it wouldn't do anything, or rather that it would be consistently ordered with respect to any ongoing transactions on other sessions.

Am I just reading too much into the mysqldump docs and using --lock-tables achieves transactional consistency?

Are the mysqldump docs simply explaining that doing --single-transaction together with --lock-tables is nonsensical because --lock-tables would immediately and implicitly close the single transaction? (Couldn't the reverse order work? But maybe it would be pointless?)

Is mysqldump --lock-tables otherwise transactionally consistent?

Thanks!

Answer 1

DDL changes implicitly commit only if they are in the same connection. As they are in a different connection from the mysqldump they lock wait until the dump is complete and then they will complete.

As such hopefully DDL changes aren't as time critical and --single-transaction will suit your needs without any addition fiddling.

Alternate would be to create a replica server there and backup from that. An DDL in the replication stream will wait however it won't impact the user or the backup.