MySQL Bugs: #12999: mysqldump consistent dump of MyISAM and InnoDB tables with minimal locking

Bug #12999	mysqldump consistent dump of MyISAM and InnoDB tables with minimal locking
Submitted:	5 Sep 2005 22:28	Modified:	4 Oct 2005 21:46
Reporter:	Timothy Smith	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: mysqldump Command-line Client	Severity:	S4 (Feature request)
Version:	all	OS:	Any (all)
Assigned to:		CPU Architecture:	Any

Description:

Right now, it isn't possible with mysqldump to take a consistent dump of a database containing MyISAM and InnoDB tables without locking all tables for the duration of the dump.

There is a method that would make it possible to unlock the tables once the MyISAM tables are dumped, which in common cases will be a huge win.

The proposed --smart-lock option works like this:

First it locks all the tables which are to be dumped consistently.  It sorts tables based on type, and dumps out all non-InnoDB tables.

It then opens a second connection (keeping the first open), and issues START TRANSACTION WITH CONSISTENT SNAPSHOT.  Once this returns OK, it closes the first connection, which unlocks all tables.  Then it dumps all InnoDB tables via the second connection.

How to repeat:

The current --single-transaction option of mysqldump turns off all table locking, so it does not produce a consistent dump if MyISAM tables are involved.

Other current options lock all tables.

Suggested fix:

See above.

Here is another solution how it could be implemented (in this way InnoDB table are locked only for a short period of time, not for duration of dumping all MyISAM tables):

connection 1) lock all myisam tables;
connection 2) set autocommit=0; lock all innodb tables; Grab master and slave positions;
connection 3) set autocommit=0; start transaction with consistent read;
connection 2) close connection;
connection 1) dump myisam tables;
connection 3) dump innodb tables;

Bug #23344 was marked as a duplicate of this one. See https://launchpad.net/mydumper for possible solution.