意外と知らないMySQLのリストアとロールフォワード回復

9862 ワード

MySQL MariaDB10 db2 MySQL テキストリンク

障害発生！！！どうする！？でも・・MySQLだと、レプリケーション（スレーブ→マスターの昇格））でほぼなんとかなっちゃうのであまり使われることはないとは思いますが、実はロードフォワード（roll forward)回復もできちゃうんすよｗｗというほぼ僕のメモです。

表題で「意外と知らない？」なんて書いちゃいましたけど、知らないのはきっと私だけ・・。でも、そんなMySQLのロールフォワード手順をまとめてみました。
ロールフォワードとは、ログファイルに残っているチェックポイント後の処理を再現し、障害直前の状態にまで戻すことができる機能のひとつです。商用DBでは、まず入っているでしょうといった機能です。ORACLEでもSQL SERVERでもIBM DB2でもちゃんと実装しております、今回は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;の行を消して再度実行すればうまくいくんじゃね？みたいなそんな感じです。（もちろんそんなに簡単な作業ではないけど）

以上でリストア、リカバリーの話は終わります。万が一の「大障害」で頭が真っ白にならないように、必ず

定期的にバックアップをとっておく（データベースと、バイナリログファイル共に）
上記バックアップファイルは、該当サーバとは別のファイルサーバに退避しておく

ということは必ずやっておきましょう。

Author And Source

この問題について(意外と知らないMySQLのリストアとロールフォワード回復), 我々は、より多くの情報をここで見つけました https://qiita.com/hit/items/c1ee4108428c637a0659

著者帰属：元の著者の情報は、元のURLに含まれています。著作権は原作者に属する。

Content is automatically searched and collected through network algorithms . If there is a violation . Please contact us . We will adjust (correct author information ,or delete content ) as soon as possible .