0

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?

Thanks!

Improve this question

1 Answer 1

Reset to default
1

You could isolate the dump by placing it on a replication slave and stopping the replication before the dump.

As table modification isn't transactional, changing the isolation level won't help.

If you keep the binary logs (and use --master-data) with mysqldump, you could revert to the previous backup and replay the binary logs. This would be immune to the impacts to an single inconsistent dump.

Improve this answer
4
  • Thanks for the info! I'm interested in your comment that "table modification isn't transactional" and have found some reading on that. Do you know what happens if I'm in the midst of a transaction and someone else executes a DDL change? From what I've read, I'm scared that maybe whatever I've done in my transaction so far is implicitly committed, even if I eventually abort / rollback. Is that right? hashrocket.com/blog/posts/mysql-has-transactions-sorta
    – jschultz410
    Feb 26, 2019 at 8:20
  • The blog article only covered a DDL in the same connection as the transaction. Recommend you do your own test on a test table using two connections.
    – danblack
    Feb 26, 2019 at 9:34
  • @jschultz410 - I think the DDL change cannot start until your transaction finishes. "Isn't transactional" refers to the lack of a ROLLBACK of any DDL statement. MySQL 8.0 may fix that.
    – Rick James
    Mar 4, 2019 at 23:51
  • @RickJames The documentation seems to say otherwise: "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." Then the analysis in the link I posted in the comment above doubles down on that.
    – jschultz410
    Mar 15, 2019 at 20:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.