MySQL : "FLUSH TABLES WITH READ LOCK" started automatically I would like to understand how this happened. I was running a query that would take a long time, but should not lock up any table. However, my dbs were practically down - it seems like it was being locked up by FLUSH TABLES WITH READ LOCK


  03:21:31  select type_id, count(*) from guid_target_infos group by type_id
  02:38:11  select type_id, count(*) from guid_infos group by type_id
  02:24:29  FLUSH TABLES WITH READ LOCK


But i did not start this command. can someone tell me why it was started automatically?
Some people use FLUSH TABLES WITH READ LOCK to lock a DB Server and perform mysqldumps. Doing this will trigger a full flush of any InnoDB table that has dirty pages in the InnoDB Buffer Pool.

Over the years, I have scripted some backups using mysqldump prefaced with FLUSH TABLES WITH READ LOCK. Within one DB thread, I would run FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400);. I would then capture the MySQL ProcessID running the SLEEP function. When the mysqldump was finished, I would kill the MySQL ProcessID running the SLEEP function.

I knew some clients that manually ran the FLUSH TABLES WITH READ LOCK by hand in one session and ran the mysqldump in a separate ssh session.

Either way, FLUSH TABLES WITH READ LOCK was issued with this purpose in mind. Please look over the slow query logs for SLEEP function calls. Also, check coworkers for any crontab jobs doing backups with it or any adhoc backups requiring FLUSH TABLES WITH READ LOCK. The mysqld process would not issue that command on its own by design.