MySQL Data Dumper

flush tables db.tb1, db.tbl2 with read lock

Bug #1292279 reported by Daniel Black
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
MySQL Data Dumper
New
Undecided
Unassigned

Bug Description

Thanks so much for the less-locking option in mydumper-0.6.

I'm wondering if in this less-locking mode (or generally) if the FLUSH TABLES WITH READ LOCK, can be replaced by FLUSH TABLES {iterative list of non-Innodb tables} WITH READ LOCK? This may require something specific for the binlog handling for innodb tables.

The FLUSH TABLES for EXPORT ( https://mariadb.com/kb/en/flush-tables-for-export/ ) in mariadb-10.x looks promising too though I can't quite tell what the remaining global read/write access is left on these tables.

Revision history for this message
Daniel Black (daniel-black) wrote :

note - galera bug: https://bugs.launchpad.net/codership-mysql/+bug/1292286 makes this less reliable. Probably will be fixed soon.

Revision history for this message
Daniel Black (daniel-black) wrote :

Can the "unlock tables" occur after the non_innodb_tables have been dumped (non_innodb_table_counter = 0 or some signalling semaphore)?

Revision history for this message
Max Bubenick (max-bubenick) wrote :

Hi Daniel, thanks for your comments

We don't need to FLUSH TABLES for a logical backup, but we need to use FTWRL to coordinate all threads.
What less locking does is to get a LOCK TABLE after that for non-innodb tables, so in this way we can release the FTWRL just before that instead of wait until all non-innodb were dumped.

About FLUSH TABLES for EXPORT is great, but still I need FTWRL for innodb and multiple threads coordination.

For logical backups the only way today to avoid flushing tables is with new percona backup locks (http://www.percona.com/doc/percona-server/5.6/management/backup_locks.html)

a) LOCK TABLES FOR BACKUP
b) LOCK BINLOG FOR BACKUP
   ... start transaction WCS (for each thread)
   ... get binlog coordinates
c) UNLOCK BINLOG
d) UNLOCK TABLES -> when non-innodb done

in this way we are not flushing any table, we can coordinate all threads and innodb locks are almost instant.

Also about your last question, mydumper is doing that
less locking -> when all threads got the LOCK TABLES
no less locking -> when all threads dumped all non innondb

To post a comment you must log in.
This report contains Public information  
Everyone can see this information.
Subscribing...

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.