mysqldump --single-transaction に --flush-logs をつけてはいけない

(いまだに時々ブクマされていたりしますが、これはバグで MySQL 5.5.21 以降では修正されています。)

mysqldump は MySQL のデータのバックアップを取得するコマンドです。

mysqldump に --single-transaction を指定すると一貫性を保持したバックアップを取得することができます*1
この時に mysqldump が発行しているクエリは次のような感じです。

[mysqldump --single-transaction DB名]

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
START TRANSACTION WITH CONSISTENT SNAPSHOT
UNLOCK TABLES
DB選択
テーブルからデータの読み込み

「START TRANSACTION WITH CONSISTENT SNAPSHOT」を実行すると、その瞬間のデータのスナップショットが保持され、トランザクションが終了するまでの間は、たとえ他の接続からデータが更新されたとしても、この接続からはスナップショット時点のデータが読み込まれます。

--single-transaction をつけない場合、mysqldump があるテーブルをダンプしている間に、まだダンプしてない別のテーブルが他の接続により更新されると、取得したバックアップは更新前と後のテーブルが混在することになってしまいます。

さらに --master-data をつけると次のような感じになります。

[mysqldump --single-transaction --master-data DB名]

FLUSH LOCAL TABLES
FLUSH TABLES WITH READ LOCK
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
START TRANSACTION WITH CONSISTENT SNAPSHOT
SHOW MASTER STATUS
UNLOCK TABLES
DB選択
テーブルからデータの読み込み

「FLUSH TABLES WITH READ LOCK」により全テーブルがロックされます。既にロックされているテーブルがある場合はロックが解放されるのを待つため、一貫性を高めるのにおすすめです。その後「UNLOCK TABLES」でロックを解放していますが、「START TRANSACTION WITH CONSISTENT SNAPSHOT」しているので、その時点(ロックされている時)のデータをちゃんと読み込むことができます。

mysqldump に --flush-logs オプションをつけると、バイナリログがローテーションされます。バックアップ取得のタイミングでバイナリログが新しくなるので、データベースのリカバリ時に mysqldump で取得したバックアップを戻して、バイナリログを利用してロールフォワードリカバリを行うことができます。

[mysqldump --single-transaction --master-data --flush-logs DB名]

FLUSH LOCAL TABLES
FLUSH TABLES WITH READ LOCK
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
START TRANSACTION WITH CONSISTENT SNAPSHOT
FLUSH LOGS                    ← ログのローテーションが行われる
SHOW MASTER STATUS
UNLOCK TABLES
DB選択
テーブルからデータの読み込み

ところが MySQL 5.5 ではこの組み合わせはうまく動きません。MySQL 5.5 では「FLUSH LOGS*2」でトランザクションが暗黙的に終了(COMMIT)してしまうようになっているためです。
「START TRANSACTION WITH CONSISTENT SNAPSHOT」でせっかく一貫性を保持するようにトランザクションを開始したのに、直後にトランザクションを終了してしまってます。

MySQL 5.1 までは「FLUSH LOGS」でもトランザクションが終了しなかったため期待通りに動きます。

--single-transaction と --flush-logs は便利なので組み合わせて使われてることが多いと思いますが、MySQL 5.5 では注意が必要です。

[追記]

バグとして登録されてました(http://bugs.mysql.com/61854)が、MySQL 5.5.21 で修正されました。

*1:InnoDB のテーブルについてのみ

*2:実際には FLUSH LOGS クエリではなくて、mysql_refresh() が使用されてます