I have a single Enterprise Linux 6 system running MySQL 5.5. I am attempting to use LVM to capture a weekly filesystem snapshot.

However, as many of us have experienced, "FLUSH TABLES WITH READ LOCK;" can lead to a deadlock situation, create a backlog of queries, fill up the connection pool and effectively knock the system offline. In fact last night my "FLUSH TABLES WITH READ LOCK;" didn't complete until 8 hours after execution due to a long running query.

If I perform the MySQL snapshot using a script like this, can I somehow time out the "FLUSH TABLES WITH READ LOCK;". If it doesn't execute after a period of time I would like to kill it and avoid the inevitable deadlock.

mysql -u root <<-MYSQL_INPUT
SYSTEM /usr/local/bin/mysql-create-lvm-snapshot;

3 Answers 3


There is no reliable way of interrupting FLUSH TABLES WITH READ LOCK. I disagree with the previous answer. You can try KILLing the FLUSH query all you want. Typically this will just hang till the original command completes.

However, some good news for you. You should be aware that FLUSH TABLES WITH READ LOCK is not strictly necessary in order to take LVM snapshot.

It is required if:

  • You are interested in point in time recovery (applying incremental restore via binlogs)
  • You have lots of non-transactional (ie MyISAM) tables being written to
  • You are running the snapshot against the master and want to create a slave based on the snapshot.

If your database is InnoDB oriented, and nothing interesting is hapenning with your MyISAM system tables (ie no one creating a procedure, GRANTing privileges etc.) then it's enough that you FLUSH only those tables.

All things InnoDB, you can just take the snapshot. There is no benefit in FLUSHing. InnoDB will make proper recovery anyhow.

The need for FLUSH TABLE comes from the requirement of getting the binary log file and position when you want to be able to rebuild a slave or otherwise use binary logs.

  • 1
    Thanks Shiomi. This database hosts a hodge-podge of MyISAM and InnoDB tables, so I believe I need to stay with the FLUSH TABLES WITH READ LOCK. Oct 25, 2013 at 17:15

Mysql 5.6 introduced FLUSH TABLES FOR EXPORT which might be a better option for your use case if you use InnoDB exclusively (apart from the internal mysql database which is always MyISAM):


Unfortunately I cannot comment here (yet), but I did quite some research on the answer of Shlomi Noach above. I hope he is right, but I could not find a confirmation of his statement that taking a filesystem snapshot of InnoDB tables does never lead to data corruption.

From the comments of https://dev.mysql.com/doc/refman/5.6/en/lock-tables.html:

For a filesystem snapshot of innodb, we find that setting innodb_max_dirty_pages_pct to zero; doing a 'flush tables with readlock'; and then waiting for the innodb state to reach 'Main thread process no. \d+, id \d+, state: waiting for server activity' is sufficient to quiesce innodb.

From http://www.mysqlperformanceblog.com/2012/03/23/how-flush-tables-with-read-lock-works-with-innodb-tables/:

If you’re just using Innodb tables and you’re not actively changing users, stored procedures etc (which are stored in MyISAM tables anyway) you can consider using –no-lock option with Percona Xtrabackup.

Which would imply that a flush/lock is not necessary if you could rule out administrative changes.


Here is my version

Call lock + bash script (mysql_lock_wait.sql):

SYSTEM bash /opt/mysql_wait.sh;

Lock script (mysql_wait.sh)



touch ${LOCK_FILE}

while [ -f ${LOCK_FILE} ]; do
    sleep 0.5

So now to use that, just run mysql < /tmp/lock_wait.sql & and next do whatever you need like taking volume snapshot.

When you want to release the lock, rm -f /tmp/mysql_lock.

Voila !

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.