MySQLdump and consistency

from the Artful MySQL Tips List

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); 

Now run ...

mysqldump --databases a b --single_transaction

The dump file has DDL and DML. Only DML can be transactional. For each database in the order listed on the command line, mysqldump issues a USE, does the DDL, issues a table write lock, does the inserts, unlocks. No cross-database integrity. This is so even when DDL is skipped with --nocreate-db --no-create-info.

Mysqldump does not respect cross-database table relations.

Return to the Artful MySQL Tips page