MySQLのbinlogとredo logについて

１．基本知識

MySQLは、SQLの解析・実行する機能を実現するServer層とデータアクセス機能を提供するストレージエンジン層で構成されています。ストレージエンジンには、MyISAM、InnoDB、Memoryなどが存在します。binlogは、Server層が出力するログです。redo logは、InnoDBエンジンが出力するログです。二つのログは、ともにDBテーブルの更新時に出力されます。

ディスクアクセスに時間がかかるため、InnoDBエンジンはまずメモリにあるレコードを更新します。次に、redo log bufferに記録します。redo log bufferに記録したら、レコード更新操作が完了とします。この仕組みは、WAL（Write　Ahead Logging）といいます。別の専用スレッドが適当のタイミングでredo log bufferの内容をディスクにあるredo logファイルに書き出します。redo logファイルの個数とサイズが固定であり、ループで使用します。最後のファイルが一杯になったら、最初のファイルに戻って、再利用します。MySQLが異常終了して再起動する時に、redo logを用いて、コミット済みでディスクに保存されていないレコードのデータを復元します。

binlogは、レコードの更新内容を保存します。誤って削除したデータを復元する場合、binlogを利用します。データベースのバックアップをベースに、バックアップ時点から指定時点の間のbinlogを適用することで、指定時点のデータを復元できます。また、マスタスレーブ構成で２つ以上のデータベースサーバが存在する場合、マスタサーバからスレーブサーバにbinlogの内容を転送します。スレーブサーバがbinlogを適用して、マスタサーバのデータと一致性を保ちます。

binlogとredo logの違いは、下表で示します。

２．更新SQL文の実行プロセス

下図にて、更新SQL文の実行プロセスを説明します。

上図の最後の三つのステップはredo logとbinlogを２フェースコミットの形で更新します。これは、redo logとbinlogの一致性を保つための仕組みです。MySQLが再起動する時に、redo logをスキャンして、コミット状態であれば、redo logに記述されたレコードの更新内容をコミットします。prepared状態であれば、まずbinlogの状態をチェックします。該当binlogが完了していない場合は（上図①のタイミングでクラッシュ）、redo logに記述されたレコードの更新内容をロールバックします。該当binlogが完了している場合は（上図②のタイミングでクラッシュ）、更新内容をコミットします。 ※statementモードのbinlogの最後に「COMMIT」句があり、rowモードのbinlogの最後に　XIDイベントが存在します。MySQL 5.6.2以後のバージョンでは、binlog-checksumを導入しました。checksumの値を検証してbinlogが完了しているか判定できます。
※トランザクションにトランザクションIDが割り振られて、トランザクションIDがredo logとbinlogに記録されます。

３．redo logの書出し

redo logに三つの状態があります。下図にて三つの色のあるボックスで表します。

① redo log buffer。MySQLプロセスのメモリに存在します。
② Page cache。ファイルシステムのCache（メモリ）に存在します。writeシステムコールの呼び出しでデータをPage Cacheに書き出します。
③ Hard Disk。ハードディスクに存在します。fsyncシステムコールを呼び出して、データをHard Diskに保存します。

パラメータ「innodb_flush_log_at_trx_commit」の値でredo logの書出しを制御します。
① 0:トランザクションコミット時に、redo logをredo log bufferにのみ保存
② 1:トランザクションコミット時に、redo logをHard Diskに保存
③ 2:トランザクションコミット時に、redo logをPage cacheに保存

InnoDBにバックグランドスレッドがあって、１秒毎に、redo log bufferにあるデータをwriteの呼出しでPage cacheに書出します。 次にfsyncを呼び出してHard Diskに保存します。 注意すべき点として、まだコミットしていないトランザクションの更新データもredo log bufferにあるので、同じタイミングでHard Diskに保存されます。上記以外、以下の場合でも未コミットデータがディスクに書き出されます。
① redo log bufferの使用済みサイズがパラメータ「innodb_log_buffer_size」の半分になる直前に、バックグランドスレッドがディスクにredo logを書出します。ただ、この場合、writeのみを呼び出し、fsyncを呼び出しません。
② 平行実行のトランザクションがコミットされた場合、パラメータ「innodb_flush_log_at_trx_commit」の値が「1」であれば、redo log bufferの内容がDBに書き出されます。