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