I must be missing something. The line I keep seeing everywhere for a consistent backup is:

mysqldump --single-transaction --all-databases > alldbs.sql

Yet the MySQL docs indicate --opt is on by default. In that is --lock-tables.
Also at MySQL docs under --single-transaction that the use of --single-transaction & --lock-tables are mutually exclusive.

It seems to me the command line should be:

mysqldump --skip-opt (add in all the ones it uses except --lock-tables) 
    --single-transaction --all-databases >  ....

We have production servers that can't be locked for very long; ideally less than a minute. Any suggestions or can you educate me on what I may be missing here?

1 Answer 1


The manual states:

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

The code for mysqldump.c has the following near line 963 (MySQL 5.5.33 source code):

if (opt_single_transaction || opt_lock_all_tables)
  lock_tables= 0;

This is evaluated after all defaults have been loaded and command-line options have been parsed.

So even though --opt is enabled by default, and therefore it enables --lock-tables, this is later changed if you use --single-transaction.

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.