チョコチョコMySQLを勉強する「２回目」ログによりデータ復旧

はじめに

「MySQLは過去の特定の時刻までの状態に復旧までできる」という話をDBAからよく聞いていますが、「凄いなぁ」と思いながら、MySQLは一体どんな仕組でリストアやリカバりをできているのか。

UPDATE文から

問題の答えを探すため、まずUPDATE文から行きましょう。
仮に下記のようなテーブルがあります。

mysql> create table T(ID int primary key, c int);
mysql> insert into T (ID,c) VALUES (1,2),(2,3);
mysql> select * from T;
+----+------+
| ID | c    |
+----+------+
|  1 |    2 |
|  2 |    3 |
+----+------+

2行目レコードに１を増やす場合、下記のSQL文を使います。

 mysql> update T set c=c+1 where ID=2;

前回にSELECT文の実行流れからMySQLのアーキテクチャーを覗きましたが、UPDATE文の流れは図のように基本的同じです。

前の図をそのまま持ってきましたが、SELECTクエリとの違いはクエリキャッシュにある。
UPDATEクエリはクエリキャッシュにヒットする必要はないが、前回に言った通りこのSQL文を実行すれば、テーブル「T」に関するクエリキャッシュは全て無効になりますので是非ご注意ください。これはクエリキャッシュが廃棄される重要な理由でもあります。
クエリキャッシュ以外、もう一つとても重要な違うところがあります。あれは今回の主役です。UPDATEクエリは二つログに関わっている----InnoDB（REDO）ログとバイナリログです。

図書館問題

この二つログは何物かを紹介する前に、分かりやすく説明するため図書館によくある問題から見てみましょう。
仮にある図書館に管理員は一人しかいない、お客様が本を返却にくる時、管理員は大体二つ選択がある。
・本を置くべき本棚に着いてから本を戻すからお客さんの返却を記録する
・一旦本をカートに入れてお客さんの返却を記録して、手が空いてる時カートに置ける本を本棚に戻す
お客さんがいっぱいある時、管理員は間違いなく後者にします。だって前者は時間がかかるのでお客さんたちを待たせないといけない。

ログ先行書き込み(WAL)

MySQLには同じ問題を持っている。もし更新ごとにデータファイルまで書き込むとディスクI/Oコストは非常に高くなってしまい、とても非効率になる。この問題を避けるため、MySQLのデザインナーは「本棚とカートの組み合わせ」に似たような方法を使いました。その方法はWAL(Write Ahead Logging,ログ先行書き込み)という技術と呼ばれる。
簡単にいうと、データファイルへの変更はログに記録され、つまり、変更内容を記述したログレコードが永続格納領域に書き出され、その後にのみ(テーブルやインデックスがある)データファイルに書き出されなければならないということです。
WAL技術のメリットは効率向上に限らないが、今回の主役InnoDB（REDO）ログと共に紹介しましょう。

InnoDB（REDO）ログ

主役登場！InnoDBログ（REDO）はWAL技術をInnoDBに実装されるもの。つまり、InnoDBのテーブルに対して行われた更新は、いったんInnoDB（REDO）ログに書き込まれてデータをメモリ（InnoDBバッファー）に保存します。データファイルへの書き込みは後回します。ディスクへの書き込み回数を減りながら、InnoDBログ（REDO）はデータの一貫性及びリカバリにもサポートします。ログファイルに書く以上、MySQLに障害があった時ログからデータを復旧することも可能です。データの一貫性については後で説明するので一旦飛ばしても大丈夫です。InnoDBにはREDOログ以外、UNDOログというログも存在します。UNDOログはトランザクションをロールバックするためのものですので、今回は展開せず、次回に説明します。

「図書館のカート」と同じメリットを持った同時に、InnoDB（REDO）ログはカートと同じ制限がある。サイズは限られています。InnoDB（REDO）ログの大きさはinnodb_log_files_in_groupとinnodb_log_file_size二つパラメタで指定されています。デフォルトで前者は2、後者は 5MBに設定されています。意味としては、InnoDB（REDO）ログを２個に分けて、それぞれの大きさは５MBにする。この設定でInnoDB（REDO）ログのファイル名はib_logfile0とib_logfile1です。

InnoDB（REDO）ログは図のようにサークルの形で動いている。write_posはREDOログを記録する位置を示して、REDOログを貯まるごとに進んでいます。check_pointはディスクへ反映されたREDOログの位置を示して、REDOログに関するメモリをデスクへフラッシュしづつ進んでいます。write_posからcheck_pointまでは利用できる空間です。ということで、もしwrite_posはcheck_pointを追いかけると「カートはいっぱいになってる」状態と同然、check_pointからログをディスクに反映しないと更新操作は仕方なくてブロックされる。図書館のケースで「お客さんを待たせないといけない」状態。この時更新のパフォーマンスは悪くなってしまうのでご注意ください。これもMySQLをチューニングする時確認ポイントの一つです。

バイナリログ

そろそろもう一つ主役ーバイナリログを紹介しましょう。バイナリログを知っている人は多いと思います。知っている通り、バイナリログには、テーブルやデータの更新操作が格納される。リカバリ。やることから見るとREDOログと似ているが、疑問①：REDOログとの違いところは何か、疑問②：二つログを存在する意味は何かを持ってるからこれから答えみましょう。

疑問①：InnoDB（REDO）ログとバイナリログの違いところは

１．InnoDB（REDO）ログはInnoDB、つまり、ストレージエンジン層なログです、InnoDBストレージエンジンではない（MYISAM、MEMORYなど）と、InnoDB（REDO）ログはありません。バイナリログはストレージエンジンにかかわらず、サーバ層のログです。
2．InnoDB（REDO）ログに書くのは「あるデータページに何を更新した」という物理的な内容です。バイナリログは「クエリ(statementフォーマット)あるいはレコード(rowフォーマット)」という論理的な内容です。
3．InnoDB（REDO）ログはサークルのかたちて空間が限られてうわ書きや再利用こともあります。バイナリログはずっとアペンドで書き込みます。

疑問②：InnoDB（REDO）ログとバイナリログ同時に存在する意味は

結論から言うと、２点があります。
1．二つログ（複雑なトラザンクションがある場合InnoDB UNDOログも含めて三つログ）に合わせてMySQLに「クラッシュセーフ」という能力をつけてデータの一貫性を保つこと。
わかり難いと思いますが、まずさっきのUPDATEクエリロジックの流れのをもっと具体的に見てみましょう。

図から見ると⑥InnoDB（REDO）ログへの書き込む完了する時点でコミットがまだ完成していなくて、⑦バイナリログへの書き込む完了次第UPDATEクエリがコミット完了。これは２フェイズこっミットと呼ばれる機能です。
リカバリする時、REDOログとバイナリログ両方に一致したことしか復旧しません。これでデータの一貫性を保証する。MYISAM、MEMORYなどREDOログを持ってないストレージエンジンはクラッシュセーフではない。

・もしバイナリログがなければ、REDOログの大きさが限られて空間が再利用されてるので、MySQLはクラッシュした直前の状態しか復旧しない、「特定の時刻までの状態に復旧」というリストアは不可能です。例としては誤操作であるデータをDELETEされた時救うてはなくなる。
・もしREDOログがなければ、データの一貫性を保証できません。上のフローチャートの例を見てください。もし⑥データ更新が成功して、⑦バイナリログへの書き込む前にクラッシュが発生すると、MySQLサーバはストレージエンジン側の変更を知らず、操作を無視した。例のUPDATEクレリにする場合、cはバイナリログの記録より1を大きくなってしまう。例としてはMySQLクラッシュで再起動によりデータの正確性はなくなる。


2. データの一貫性を保つ上、データの復旧は可能です。ついに最初の問題を答えるようになりました。実は仕組を理解した上で、リストア手順の理解はそんないに難しくない。リストアするため、まず定期的にダンプ（全データベースのバックアップ）しないといけない。頻度は週に一回や毎日など業務により違います。特定の時刻までの状態に復旧するため、

ステップ１：特定の時刻前に一番近いのダンプを見つけだす。
ステップ２：ダンプの時点から特定の時刻までのバイナリログを取得。
ステップ３：バイナリログを実行する。
ざっくりで操作手順ではないですが、やってみたければこの記事を参考すれば大丈夫だと思います。

まとめ

今回はリカバリやリストアの問題を踏まえてInnoDB（REDO）ログとバイナリログを紹介しました。
InnoDB（REDO）ログ物理的なログでMySQLにクラッシュセーフ能力を提供します。innodb_flush_log_at_trx_commitというパラメタでREDOログをディスクへ書き込み存続します。おすすめの設定は1で、意味としてはトラザンクションごとにディスクへ書き込む。これで障害によりデータを失な割れない。
バイナリログは論理的なログでMySQLにリストア能力を提供します。ダンプファイルと合わせて過去の特定の時刻までの状態に復旧までできます。