Mysql – time out the scripted “FLUSH TABLES WITH READ LOCK;”

backupMySQLsnapshot

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
FLUSH TABLES WITH READ LOCK;
SYSTEM /usr/local/bin/mysql-create-lvm-snapshot;
UNLOCK TABLES;
\q
MYSQL_INPUT

Best Answer

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.

Related Question