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