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. Last updated 16 Aug 2019 |
 |