MySQLのバックアップと"FLUSH TABLES WITH READ LOCK"について

MySQLで整合性のあるバックアップを取得するのに、

  1. "FLUSH TABLES WITH READ LOCK"でテーブルロック
  2. バックアップ処理(mysqldumpやスナップショット)
  3. "UNLOCK TABLES"でロック解除

みたいなスクリプトを実行していたが、"FLUSH TABLES WITH READ LOCK"って必ず数ミリ秒みたいなレベルでロックできるのかなと疑問に思った。
結構時間のかかるクエリが処理されていたりすると、すぐにロックできないんではないかと思った。
ちょっと調べてみると、やはりすぐにロックされない可能性もあるようだ。*1


ということで、"FLUSH TABLES WITH READ LOCK"後に、以下のような確認処理を追加した。

    • "SHOW PROCESSLIST"を実行して、"FLUSH TABLES WITH READ LOCK"が実行されていること
    • "SHOW STATUS"を実行して、"Key_blocks_not_flushed"が"0"であること


で、InnoDBについては、"FLUSH TABLES WITH READ LOCK"した時点で、ログからテーブルスペースへ書き込み中の可能性もあると考えて、以下の確認処理も追加した。

    • "SHOW ENGINE INNODB STATUS"を実行して、"Log sequence number"と"Log flushed up to"の値に差異がないこと

まとめ

"FLUSH TABLES WITH READ LOCK"で瞬時にロックできない可能性もあるので、以下のような感じでバックアップすることにした。

  1. "FLUSH TABLES WITH READ LOCK"
  2. "SHOW PROCESSLIST"で"FLUSH TABLES WITH READ LOCK"が実行されたことを確認(ロックできた)
  3. "SHOW STATUS"で"Key_blocks_not_flushed"が"0"であることを確認(フラッシュされた)
  4. "SHOW ENGINE INNODB STATUS"で、"Log sequence number"="Log flushed up to"であることを確認(InnoDBログがテーブルへ書き込まれた)
  5. バックアップ処理
  6. "UNLOCK TABLES"

*1:ロックする時間は分からない