2

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?

Hannah Vernon
  • 70,041
  • 22
  • 171
  • 315
Patrick Thrapp
  • 21
  • 1

1 Answers1

2

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.

Bill Karwin
  • 14,693
  • 2
  • 30
  • 42