MySQLdump and consistency

from the Artful MySQL Tips List

InnoDB guarantees consistency within a transaction, so if all tables being dumped are InnoDB, use the single-transaction option to keep a read lock long enough to start a transaction.

The lock-all-tables option imposes a global read lock to guarantee consistency. In contrast, the lock-tables option issues LOCK TABLES for each database it dumps. Each database is locked and unlocked in turn, allowing a race condition (mods to db A can occur while db B is locked). Therefore if you have only InnoDB tables, single-transaction is a much better option.

Return to the Artful MySQL Tips page