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

[Daniel Black]

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.

[Daniel Black (daniel-black)]

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

[Daniel Black (daniel-black)]

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

[Max Bubenick (max-bubenick)]

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