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:
Category:MySQL Server: mysqldump Command-line Client Severity:S4 (Feature request)
Version:all OS:Any (all)
Assigned to: CPU Architecture:Any

[5 Sep 2005 22:28] Timothy Smith

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.
[7 Oct 2005 11:09] MySQL Verification Team
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;
[5 Aug 2010 13:03] Valeriy Kravchuk
Bug #23344 was marked as a duplicate of this one. See https://launchpad.net/mydumper for possible solution.