6

I am performing regular backups using mysqldump on a schema consisting entirely of InnoDB tables. The mysqldump documentation has this to say about --single-transaction:

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

Yet approx 1 bazillion blog posts and answers here (and elsewhere on the stack exchange network) recommend:

mysqldump --single-transaction --skip-lock-tables my_database > my_database.sql

If the two options are mutually exclusive, I would think that specifying --single-transaction should be enough. However the docs also state this about --opt:

This option, enabled by default, is shorthand for the combination of --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset.

Which leads me to believe that --lock-tables is on by default.

Do I need to specify --skip-lock-tables along with --single-transaction or is merely setting the latter enough to ensure tables are not locked during a dump?

0
6

If you use the --single-transaction option, it turns off --lock-tables. That's what the documentation means when it says they are mutually exclusive.

https://github.com/mysql/mysql-server/blob/8.0/client/mysqldump.cc#L1076

  if (opt_single_transaction || opt_lock_all_tables) lock_tables = false;
3
  • 1
    source code for the win. thank you!
    – billynoah
    Sep 28 at 14:28
  • it's amazing how hundreds of people writing about this are adding both options and this isn't documented anywhere that I could find. if you add up all of the wasted keystrokes i'm pretty sure it amounts to at least an afternoon at the park with your family :-)
    – billynoah
    Sep 28 at 14:31
  • 1
    How would your family feel about having to spend 10 seconds in the park with each of hundreds of strangers? (j/k) Sep 28 at 14:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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