InnoDB guarantees consistency within a transaction, so if all tables of a given database 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.

But a table may reference tables in other databases, and of course MySQL will enforce those references. Does mysqldump --single-transaction respect those relationships? A simple test case ...

create schema a; 
use a; 
create table a.a( i int primary key ); 
insert into a.a values(1); 

create schema b; 
create table b.b ( j int primary key, i int, foreign key(i) references a.a(i) ); 
insert into b.b values(1,1); 

create table b.c( k int primary key, j int, foreign key(j) references b(j) ); 
insert into b.c values(1,1); 

mysqldump --databases a b --single_transaction

Mysqldump does not respect cross-database table relations.

Last updated 16 Aug 2019