バイナリログからのロールフォワードについて

みなさんこんにちは。
上長からDTBを布教されたのでレート1700まではあげました、ディーネット谷口です。
なお、ポケ森はあまり続きませんでした。
さて今回は、MySQLのバイナリログを使ったロールフォワードについてご紹介致します。
レプリケーション構成を実装している場合は、スレーブも復旧させないといけなくなるので
別の手法で戻すほうが多いかもしれませんが、作業によってはこちらを使う方が早い場合もあります。

そもそもロールフォワードとは？

ログファイルに残っているチェックポイント後のデータを元に処理を再現し、特定タイミングの状態にまで戻すことである。
障害発生直前までリカバリを実施する為には、下記のデータが必要です。
mysqldumpの全体バックアップ
全体バックアップ以降のバイナリログ

バイナリログ出力設定

バイナリログはデフォルトでは作成されないので、my.cnfにて設定する

[mysqld]
log-bin=mysql-bin　　　←追記

※ /var/lib/mysql/配下にmysql-bin.000001のファイル名でバイナリログファイルが作成される。

ロールフォワードまでの流れ

実際に障害発生から復旧までのテストを以下順序で行ってみます。

1. ① データベース作成（testdb）
2. ② テーブル作成（jpzipcode）
3. ③ データを5件書き込む
4. ④ 全体バックアップ(mysqldump)
5. ⑤ データを5件書き込む
6. ⑥ データベース削除（testdb）
7. ⑦ 復旧作業実施(6の作業直前の状態に戻します)
   

   ロールフォワードの実践

   ① データベース作成（testdb）

   mysql> create database testdb;
   Query OK, 1 row affected (0.00 sec)
   

   ② テーブル作成（jpzipcode）

   mysql> use testdb;
   Database changed
   mysql> create table jpzipcode(
   ->   oldpost text,
   ->   newpost text not null,
   ->   pref text,
   ->   area text,
   ->   addr text not null
   -> );
   Query OK, 0 rows affected (0.02 sec)
   

   ③ データを5件書き込む

   mysql> INSERT INTO jpzipcode (oldpost,newpost,pref,area,addr)
   VALUES('154','154-0002','東京都','世田谷区','下馬');
   Query OK, 1 row affected (0.00 sec)
   mysql> INSERT INTO jpzipcode (oldpost,newpost,pref,area,addr)
   VALUES('468','468-0039','愛知県','名古屋市天白区','西入町');
   Query OK, 1 row affected (0.00 sec)
   mysql> INSERT INTO jpzipcode (oldpost,newpost,pref,area,addr)
   VALUES('053','053-0022','北海道','苫小牧市','表町');
   Query OK, 1 row affected (0.00 sec)
   mysql> INSERT INTO jpzipcode (oldpost,newpost,pref,area,addr)
   VALUES('036','036-8096','青森県','弘前市','表町');
   Query OK, 1 row affected (0.00 sec)
   mysql> INSERT INTO jpzipcode (oldpost,newpost,pref,area,addr)
   VALUES('984','984ｰ0055','宮城県','仙台市若林区','表柴田町');
   Query OK, 1 row affected (0.00 sec)
   

   現在のバイナリログ確認

   # ll /var/lib/mysql/mysql-bin*
   -rw-rw---- 1 mysql mysql 1294  12月 10 10:37 2017 /var/lib/mysql/mysql-bin.000001
   -rw-rw---- 1 mysql mysql   19  12月 10 10:16 2017 /var/lib/mysql/mysql-bin.index
   

   ④ 全体バックアップを取得

   # mysqldump -u root -p -x --all-databases --flush-logs > /tmp/mysqldump.sql
   

   全体バックアップ後のバイナリログ確認

   # ll /var/lib/mysql/mysql-bin*
   -rw-rw---- 1 mysql mysql 1408  12月 10 10:41 2017 /var/lib/mysql/mysql-bin.000001
   -rw-rw---- 1 mysql mysql  106  12月 10 10:41 2017 /var/lib/mysql/mysql-bin.000002
   -rw-rw---- 1 mysql mysql   38  12月 10 10:41 2017 /var/lib/mysql/mysql-bin.index
   

   flush-logsによりバイナリログがmysql-bin.000001からmysql-bin.000002に切り替わっている事が確認出来ます。
   全体バックアップ以降の更新はmysql-bin.000002に記載されます。

   ⑤ データを5件書き込む

   mysql> INSERT INTO jpzipcode (oldpost,newpost,pref,area,addr)
   VALUES('312','312-0047','茨城県','ひたちなか市','表町');
   Query OK, 1 row affected (0.00 sec)
   mysql> INSERT INTO jpzipcode (oldpost,newpost,pref,area,addr)
   VALUES('339','339-0008','埼玉県','さいたま市岩槻区','表慈恩寺');
   Query OK, 1 row affected (0.00 sec)
   mysql> INSERT INTO jpzipcode (oldpost,newpost,pref,area,addr)
   VALUES('940','940-0218','新潟県','栃尾市','表町');
   Query OK, 1 row affected (0.00 sec)
   mysql> INSERT INTO jpzipcode (oldpost,newpost,pref,area,addr)
   VALUES('468','468-0069','愛知県','名古屋市天白区','表山');
   Query OK, 1 row affected (0.00 sec)
   mysql> INSERT INTO jpzipcode (oldpost,newpost,pref,area,addr)
   VALUES('612','612-8219','京都府','京都市伏見区','表町');
   Query OK, 1 row affected (0.00 sec)
   

   ⑥ データベース削除（testdb）

   mysql> DROP DATABASE testdb;
   Query OK, 1 row affected (0.01 sec)
   

   ⑦ 復旧作業実施

   まずはバイナリログを退避します

   # cd /var/lib/mysql
   # cp -f mysql-bin.* /tmp/
   # ll /tmp/mysql-bin.*
   -rw-r----- 1 root root 1408  12月 10 10:55 2017 /tmp/mysql-bin.000001
   -rw-r----- 1 root root 1127  12月 10 10:55 2017 /tmp/mysql-bin.000002
   -rw-r----- 1 root root   38  12月 10 10:55 2017 /tmp/mysql-bin.index
   

   全体バックアップからのリストア

   削除したデータベースのみ復旧も可能ですが、今回は全体を復旧させます。

   # mysql -u root -p < mysqldump.sql
   # mysql -u root -p testdb -e "show tables; select count(*) from jpzipcode;"
   Enter password:
   +------------------+
   | Tables_in_testdb |
   +------------------+
   | jpzipcode        |
   +------------------+
   +----------+
   | count(*) |
   +----------+
   |        5 |
   +----------+
   

   テーブルの中身が5件しか格納されていない事を確認
   全体バックアップ時点では5件しか入力していないので問題ないですね。
   バイナリログ内容を確認し、データベースを削除する手前までの処理を適用させる。
   適用前にバイナリログの見方

   # at 667
   #171210 10:52:40 server id 1  end_log_pos 856   Query   thread_id=4     exec_time=0     error_code=0
   SET TIMESTAMP=1701250760/*!*/;
   INSERT INTO jpzipcode (oldpost,newpost,pref,area,addr) VALUES('468','468-0069','愛知県','名古屋市天白区','表山')
   /*!*/;
   

   at 667が本クエリの開始位置
   end_log_pos 856　が本クエリの終了位置
   上記バイナリログから分かる内容としては、
   INSERTクエリが、2017年12月10日10時52分40秒に、本バイナリログのポジション667～856に記録されているという事になります。
   本クエリだけを適用させたい場合は以下コマンドを実行すればよいです。

   mysqlbinlog --start-position=667 --stop-position=856 /tmp/mysql-bin.000002 | mysql -u root -p
   

   バイナリログの最初とデータベースを削除した部分を抜粋したものが以下になります。

   # mysqlbinlog /tmp/mysql-bin.000002
   # at 4
   #171210 10:41:55 server id 1  end_log_pos 106   Start: binlog v 4, server v 5.1.73-log created 171210 10:41:55
   # at 1042
   #171210 10:53:50 server id 1  end_log_pos 1127  Query   thread_id=4     exec_time=0     error_code=0
   SET TIMESTAMP=1701250830/*!*/;
   DROP DATABASE testdb
   /*!*/;
   DELIMITER ;
   # End of log file
   

   今回復旧させたいのはデータベースを削除する手前までなのでバイナリログのポジションが
   4～1042までのクエリを適用してやればよい。

   # mysqlbinlog --start-position=4 --stop-position=1042 /tmp/mysql-bin.000002 | mysql -u root -p
   # mysql -u root -p testdb -e "show tables; select count(*) from jpzipcode;"
   Enter password:
   +------------------+
   | Tables_in_testdb |
   +------------------+
   | jpzipcode        |
   +------------------+
   +----------+
   | count(*) |
   +----------+
   |       10 |
   +----------+
   

   テーブルの中身が10件になった事が確認出来ます。
   データベースを削除した手前までの状態に復旧出来ました。
   以上にてバイナリログを利用したロールフォワード作業は完了となります。
   何か問題が起こった時に対応出来るように、
   定期的なバックアップ(データベース、バイナリログファイル)を実施し、
   データベースサーバとは別のサーバにバックアップを退避するようにしましょう。