意外と知らない？MySQLのリカバリー方法について
障害発生！！！どうする！？でも・・MySQLだと、レプリケーション（スレーブ→マスターの昇格））でほぼなんとかなっちゃうのであまり使われることはないとは思いますが、実はロードフォワード（roll forward)回復もできちゃうんすよｗｗというほぼ僕のメモです。
表題で「意外と知らない？」なんて書いちゃいましたけど、知らないのはきっと私だけ・・。でも、そんなMySQLのロールフォワード手順をまとめてみました。 ロールフォワードとは、ログファイルに残っているチェックポイント後の処理を再現し、障害直前の状態にまで戻すことができる機能のひとつです。商用DBでは、まず入っているでしょうといった機能です。ORACLEでもSQL SERVERでもIBM DB2でもちゃんと実装しております、IBM DB2のロールフォーワード手順については以前、某F氏が手順をまとめてくれました（http://www.s-quad.com/wordpress/?p=638）あたりですかね？今回はMySQLでそれをやってみようと思います。
・・・とその前に・・ リストア（restore)とリカバリ(recovery)は意味が違うので注意してくださいね。一般的な障害復旧手順というのは、リストア→リカバリの一連の作業のことを示します。 たとえば、データベース破壊、ディスククラッシュ等の重度の障害から復旧するといったケースでは、まずバックアップファイルからバックアップ時点でのデータで復旧した後、障害発生直前のデータまで復旧します。
・・というわけで障害発生から復旧までのシナリオを作ってみました。ざっくり、こんな感じでやります。
recovery というスキーマを作成
recovery.tab1というテーブルを作成
tab1にデータを10件 insert する
バックアップする
tab1にデータを10件 insert する
recovery スキーマをosの思い切り壊す！！
7.結果的には、6の作業をする直前の状態までリカバリすること目標にしております。
前準備（検証のための）
スキーマとテーブルを作成します
mysql> create database recovery; Query OK, 1 row affected (0.00 sec) mysql> use recovery; Database changed mysql> create table tab1 (id int,detail char(10)) engine=innodb; Query OK, 0 rows affected (0.02 sec)
上記で作成したtab1テーブルにデータを10件書き込みます
insert into tab1 values(1,'a'), (2,'a'), (3,'a'), (4,'a'), (5,'a'), (6,'a'), (7,'a'), (8,'a'), (9,'a'), (10,'a'); mysql> insert into tab1 -> values(1,'a'), -> (2,'a'), -> (3,'a'), -> (4,'a'), -> (5,'a'), -> (6,'a'), -> (7,'a'), -> (8,'a'), -> (9,'a'), -> (10,'a'); Query OK, 10 rows affected (0.04 sec) Records: 10 Duplicates: 0 Warnings: 0 mysql> select * from tab1; +------+--------+ | id | detail | +------+--------+ | 1 | a | | 2 | a | | 3 | a | | 4 | a | | 5 | a | | 6 | a | | 7 | a | | 8 | a | | 9 | a | | 10 | a | +------+--------+ 10 rows in set (0.00 sec)
・ちょっとここで話がずれますが、mysqlのログはバイナリログと言いまして、mysql-bin.xxxというファイルに出力されます。この部分は、ibm db2ですと、アーカイヴログ、oracle ですと、redo ログという部分です。ちなみに、デフォルトの設定ですと、mysqlはバイナリログを出力しません。
# pwd /var/lib/mysql # ll mysql-bin* -rw-rw---- 1 mysql mysql 2451 8月 24 17:49 mysql-bin.000001 -rw-rw---- 1 mysql mysql 19 8月 18 11:28 mysql-bin.index
バイナリログを出力するようにするためには、my.cnfに以下のような記述をする必要がございます。
# cat /etc/my.cnf | grep -i bin ----- # binary logging is required for replication log-bin=mysql-bin # binary logging format - mixed recommended binlog_format=mixed -----
・一旦ここでバックアップを取ります
# mysqldump -u root -p recovery > /tmp/recovery.dump
・ログをフラッシュします。
# mysql -u root -p recovery -e "flush logs;"
・バイナリログがどうなったか確認してみる flush logs コマンドを実行したことにより、前回と比較してmysql-bin.000002というバイナリログが増えているのがわかるかと おもいます。ここで何が言いたいかといいますとですね・・。障害発生時にバックアップを行ったかと思いますが、バックアップ後にログをflushしているので、障害発生時のロールフォワードリカバリの際は、mysql-bin.000002さえあれば復旧できるという意味なんです！mysql-bin.000001は、バックアップを取った時点でのトランザクションログなので、もう必要ないですよね？^^
# ll mysql-bin.* -rw-rw---- 1 mysql mysql 2494 8月 24 18:35 mysql-bin.000001 -rw-rw---- 1 mysql mysql 106 8月 24 18:35 mysql-bin.000002 -rw-rw---- 1 mysql mysql 38 8月 24 18:35 mysql-bin.index
・さらにデータを書き込む 上記で作成したtab1テーブルにデータを10件書き込みます、さきほど10件書き込んでおりますので、これで20件になっているかと思います。
insert into tab1 values(11,'a'), (12,'a'), (13,'a'), (14,'a'), (15,'a'), (16,'a'), (17,'a'), (18,'a'), (19,'a'), (20,'a'); mysql> insert into tab1 -> values(11,'a'), -> (12,'a'), -> (13,'a'), -> (14,'a'), -> (15,'a'), -> (16,'a'), -> (17,'a'), -> (18,'a'), -> (19,'a'), -> (20,'a'); Query OK, 10 rows affected (0.00 sec) Records: 10 Duplicates: 0 Warnings: 0 mysql> select count(*) from tab1; +----------+ | count(*) | +----------+ | 20 | +----------+ 1 row in set (0.00 sec)
ついでにバイナリログのサイズとタイムスタンプも見てみましょうか。mysql-bin.000002のサイズもタイムスタンプも変わっているのが分かるかと思います。
# ll mysql-bin.* -rw-rw---- 1 mysql mysql 2494 8月 24 18:35 mysql-bin.000001 -rw-rw---- 1 mysql mysql 414 8月 24 18:44 mysql-bin.000002 -rw-rw---- 1 mysql mysql 38 8月 24 18:35 mysql-bin.index
でわ、データベースを破壊しましょうｗ
では思い切ってデータベースを壊してみましょう！！！ただ、ひとつだけ注意！！バイナリログまで破壊してしまいますと、本格的に、リカバリーできなくなりますので、必ず退避しておきましょう！！以下コマンドを箇条書きします・・・あと大丈夫かと思いますがデータベースを破壊している部分の説明は、今回の検証だけにやっているだけなのでリカバリ手順の一環ではないことにご注意ください。
・バイナリログの退避（実際は、同じサーバ、同じディレクトリ内なんかに入れちゃだめです。だめな理由は後述します）
# pwd /var/lib/mysql # mkdir binlog/ # cp -f mysql-bin.* binlog/ # ll binlog/ 合計 12 -rw-r----- 1 root root 2494 8月 24 18:52 mysql-bin.000001 -rw-r----- 1 root root 414 8月 24 18:52 mysql-bin.000002 -rw-r----- 1 root root 38 8月 24 18:52 mysql-bin.index
・スキーマを破壊！！mysqld起動中ですが、程よく壊してみます。ついでにバイナリログも消してみました。
# rm -f mysql-bin.* # pwd /var/lib/mysql # rm -f recovery/ db.opt tab1.frm # rm -f recovery/ db.opt tab1.frm # mv -f recovery/tab1.frm crash.xxxyyy # rm -f recovery/db.opt
・mysqlの状態を確認してみます。どうでしょう？さきほど作成したtab1テーブルがなくなってしまっているのがわかるかと思います。
mysql> use recovery; Database changed mysql> show tables; Empty set (0.00 sec)
リストアを開始する。
・バックアップから復旧 とりあえずバックアップからデータを復旧させましょう。併せてこの時点で、tab1が10件し格納されていないことも確認します。
# mysql -u root -p -e "drop database recovery; create database recovery; show create database recovery;" Enter password: +----------+-------------------------------------------------------------------+ | Database | Create Database | +----------+-------------------------------------------------------------------+ | recovery | CREATE DATABASE `recovery` /*!40100 DEFAULT CHARACTER SET utf8 */ | +----------+-------------------------------------------------------------------+ # mysql -u root -p recovery < /tmp/recovery.dump Enter password: # mysql -u root -p recovery -e "show tables; select count(*) from tab1;" Enter password: +--------------------+ | Tables_in_recovery | +--------------------+ | tab1 | +--------------------+ +----------+ | count(*) | +----------+ | 10 | +----------+
ロールフォワードリカバリを開始する
障害直前までのtab1には、20レコード入っていたはずです。前述の通り、リストアだけですと、バックアップ時点でのデータしか戻せません。ここで、さきほど退避したバイナリログを利用して、障害直前までの状態まで復帰させる作業を行います。
※ mysql-bin.000002 のバイナリログをテキスト形式に変更し、recover.sql に出力させております ※ -Dは、バイナリログの出力を無効するオプションです # mysqlbinlog -D -u root -p /var/lib/mysql/binlog/mysql-bin.000002 > /tmp/recover.sql Enter password: # mysql -u root -p recovery < /tmp/recover.sql Enter password: # mysql -u root -p recovery -e "select count(*) from tab1;" Enter password: +----------+ | count(*) | +----------+ | 20 | +----------+
どうでしょうか？見事に障害発生直前の状態まで復旧できたことがわかるかと思います。 ちなみに、mysqlbinlog コマンドは、バイナリログファイルを処理するためのユーティリティであり、バイナリーのフォーマットをテキスト形式に変更してくれるユーティリティです。詳細なオプションにつきましては、http://dev.mysql.com/doc/refman/5.1/ja/mysqlbinlog.htmlをごらんください。
以上、障害からのリカバリ手順の説明は終わりですが、実際、こんな中途半端な壊れ方はしませんし、仮にサーバのディスク自体が破損したとしたら、最悪ＯＳから入れなおしなんてことのほうが多いかもしれません。 通常のシステム運用ですと、ほぼ、レプリケーションスレーブのマスター昇格で復旧できるレベルです。（いまどきのMySQLサービス運用でレプリケーションを使わないサービスもまあ、ほぼ無いでしょう・・・
じゃあ、このケースってどこで活用できるの？の部分ですが、私個人的には、障害っつーか、むしろヒューマンエラーで、間違えて全部データ消しちゃったよ（汗 なんてときに活用できるのかな？と思っています。mysqlbinlogでテキストフォーマットに変換したテキストから、delete from tab1;の行を消して再度実行すればうまくいくんじゃね？みたいなそんな感じです。（もちろんそんなに簡単な作業ではないけど）
以上でリストア、リカバリーの話は終わります。万が一の「大障害」で頭が真っ白にならないように、必ず
定期的にバックアップをとっておく（データベースと、バイナリログファイル共に）
上記バックアップファイルは、該当サーバとは別のファイルサーバに退避しておく
ということは必ずやっておきましょう。 * ？
