MySQLのバイナリログを使った復旧手順

  • 投稿日:
  • by ライブラリ編集局

MySQLのバイナリログを使った復旧手順についてご紹介致します。

レプリケーション構成を実施されている場合は、スレーブ⇒マスター昇格により
復旧されるので、バイナリログを利用したロールフォワードを扱う事は少ないかと思います。

そもそもロールフォワードとは?
  • ログファイルに残っているチェックポイント後のデータを元に処理を再現し、障害発生の直前の状態にまで戻すことである。
本件の動作確認は、次の環境にて行いました。
  • OS:CentOS6.5 64bit
  • MySQL:5.1.73-3.el65.x8664

障害発生直前までリカバリを実施する為には、下記のデータが必要です。

  • mysqldumpの全体バックアップ
  • 全体バックアップ以降のバイナリログ

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

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

上記設定により、/var/lib/mysql/配下にmysql-bin.000001のファイル名でバイナリログファイルが作成される。

実際に障害発生から復旧までの流れを確認してみたいと思います。

データベースとテーブルの作成

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

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 5月 20 10:37 2014 /var/lib/mysql/mysql-bin.000001
-rw-rw---- 1 mysql mysql 19 5月 20 10:16 2014 /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 5月 20 10:41 2014 /var/lib/mysql/mysql-bin.000001
-rw-rw---- 1 mysql mysql 106 5月 20 10:41 2014 /var/lib/mysql/mysql-bin.000002
-rw-rw---- 1 mysql mysql 38 5月 20 10:41 2014 /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)

データベースを削除します

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 5月 20 10:55 2014 /tmp/mysql-bin.000001
-rw-r----- 1 root root 1127 5月 20 10:55 2014 /tmp/mysql-bin.000002
-rw-r----- 1 root root 38 5月 20 10:55 2014 /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 
#140520 10:52:40 server id 1 end_log_pos 856 Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1400550760/*!*/;
INSERT INTO jpzipcode (oldpost,newpost,pref,area,addr) VALUES('468','468-0069','愛知県','名古屋市天白区','表山')
/*!*/;

at 667が本クエリの開始位置
end_log_pos 856 が本クエリの終了位置
上記バイナリログから分かる内容としては、
INSERTクエリが、2014年05月20日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
#140520 10:41:55 server id 1 end_log_pos 106 Start: binlog v 4, server v 5.1.73-log created 140520 10:41:55
# at 1042
#140520 10:53:50 server id 1 end_log_pos 1127 Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1400550830/*!*/;
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件になった事が確認出来ます。
データベースを削除した手前までの状態に復旧出来ました。


以上にてバイナリログを利用した復旧手順の説明は終わりになります。


何が問題が起こった時に対応出来るように、
定期的なバックアップ(データベース、バイナリログファイル)を実施し、
データベースサーバとは別のサーバにバックアップを退避しておくという事を実施しましょう。