以前の記事 MySQLのログの種類とログの仕方を調べてみた（実施例） で、MySQLのログの採取方法について簡単に解説してみました。
その中のバイナリログは、ロールバック（リストア）できることをちょっとだけ振れていました。
MySQLでバイナリログを使って、誤ったオペレーションなどでデータベースを壊してしまったりした場合、そ
の誤ったオペレーションの直前まで戻す（ロールバック（リストア））ことができます。
ただ、一般的にMySQLを最初に起動したときから、ずーーーっと、全バイナリログをとって（保存）いるなんてことは、ほとんどないですよね。
一般的には、バイナリログからのロールバック（リストア）といっても、バイナリログだけから復元するわけではありません。
（もちろん、先に記述したようにMySQLの最初の起動時からのバイナリログがあれば別です。）
通常は、以下の２点を使ってロールバック（リストア）を行います。
- フルバックアップ(mysqldump あるいは停止してのバイナリファイルバックアップ）
- フルバックアップ採取後のバイナリログ
今回は、このフルバックアップ＋バイナリログによるロールバック（リストア）の手順について簡単に解説してみます。
- 目次
- 履歴
2012年11月12日 初版
MySQLフルバックアップ＋バイナリログによるロールバック（リストア）の手順
テスト用にデータベースのバイナリログを採取するように設定します。
まずは、常にMySQLでバイナリログをとるように設定しておきます。
そのためにMySQLの設定ファイル ( /etc/my.cnf ) を編集します。
|
これは、RedHat系(CentoOS,Scientific Linuxなど)のデフォルトの設定ファイルになります。
Deabin系(Debian,Ubuntuなど)では、/etc/mysql/my.cnf というディレクトリ配下がデフォルトになっています。ファイルの場所の違いに注意してください。 もちろん、設定する内容は同じです。
ここでは、最低限の設定のみ行っています。詳しくは、MySQLのログの種類とログの仕方を調べてみた（実施例） を参照してください。
設定を終えたら、mysqlをリスタートしてきます。
|
データベースとテーブルを作成します。
次に、ここではあくまでテスト用ですが、必要なデータベース、テーブルを作成します。
|
ここでは、
データベース名: sample_db
テーブル名: animals
6レコードを追加してみました。
データベースのフルバックアップを作成します。
次に、ここではあくまでテスト用ですが、データベース、テーブルのバックアップを作成します。
cronなどを使って、常にバックアップをとるようにしておきましょう。(参考 : mysqldumpを使ってデータベースの30日分をバックアップする)
|
フルバックアップを作成した時点で、バイナリログの更新（ファイルの入れ替え）を行っておきます。
|
以下は、flush logs; を実行する前が、bin.000002.log というバイナリログファイル名だった時に、flush logs; を実行した場合のログファイル名です。
|
これを実施するか、バイナリログ位置をダンプしておくか、いずれかをやっておくと後で楽です。
|
データベースのアップデートを行います。
次に、ここではあくまでテスト用ですが、データベース、テーブルの情報を更新します。
|
ここでの例では、単純に以下のようにデータをアップデートしています。
“dog” → “wolf” → “giraffe”
“cat” → “lion” → “tigger”
データベースのロールバックを行います。
先のデータベースの更新の中で、
“dog” → “wolf” → “giraffe”
の赤文字の箇所が誤っていたとしましょう。
ここでは、“wolf” → “giraffe” へ変更する前までデータベースをロールバックしてみます。
- データベースのフルバックアップをとる。
最悪失敗した時のために、現状復旧するためのバックアップをとります。あくまで最悪の場合のためのバックアップなので、以降の作業でこのバックアップを使用することはありません。
$ mysqldump -uroot -p sample_db > sample_db_backup_latest.sql
Enter password:
- バイナリログファイルのバックアップを保存しておく。
これから、リストア（ロールバック）作業を行う上で、ロールバックといってもデータベースが昔のデータベースになるわけではなく、データベースを更新して、昔のデータベースの内容に合わせることになります。 つまり、データベースは更新されますから、バイナリログも更新されます。
バイナリログが更新されると、ロールバックする際に非常に面倒ですし、何がなにやら混乱のもとです。
そのため、現状のバイナリログを一旦コピー保存します。
$ mkdir /var/log/mysql/rollback/
$ cp /var/log/mysql/bin* /var/log/mysql/rollback/.
- データベースを正常なフルバックアップがとれている箇所までロールバック（リストア）する。
以前の手順の中で、正常なデータベースのフルバックアップから、 その正常な位置までリストア（復元）します。
$ mysql -uroot -p -Dsample_db < sample_db_backup.sql
Enter password:
上の最悪のためのバックアップではないことに注意してください。あくまで、正常な状態でとったフルバックアップからリストアします。
- バイナリログからロールバックしたい位置を確認する。
先の手順でフルバックアップした際に、
flush logs; あるいは、show master status; を実行したことと思います。
そこから、探す開始位置を決め、あとは、日時から、探します。- バイナリログの対象ファイルを探す
バイナリログファイルは、一つとは限りません。そのため、どのファイルを探せば良いか苦労することがあります。 そこで、先に実行したflush logs; あるいは、show master status; が役に立ちます。
それぞれで探す対象となるバイナリログファイル名を探します。
- flush logsを実行した場合は、
ファイルの更新日時で、フルバックアップ以降のバイナリログを確認できます。
$ ls -l /var/log/mysql/
-rw-r----- 1 mysql adm 36532 2012-11-08 03:38 bin.000001
-rw-r----- 1 mysql adm 786 2012-11-08 04:15 bin.000002
-rw-r----- 1 mysql adm 3555 2012-11-09 11:06 bin.000003
上記のような場合、mysqldump を実行した日付が 2012-11-08 04:15 なら、bin.000003 以降のファイルがバイナリログの対象となります。
- show master statusを実行した場合は、
出力情報のバイナリログファイル名と位置情報から、フルバックアップ以降のバイナリログを確認できます。
show master status の出力情報が以下のような場合、$ mysql -u root -p sample_db -e "show master status;"
Enter password:
+------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------+----------+--------------+------------------+
| bin.000003 | 1904 | | |
+------------+----------+--------------+------------------+
単純に、bin.000003 の 位置 : 1904 以降のファイルがバイナリログの対象となります。
- flush logsを実行した場合は、
- ロールバック位置を特定します。
ロールバック位置を探すための対象となるバイナリログファイルが決まったら、そのファイルの中からロールバック位置を特定します。
$ mysqlbinlog /var/log/mysql/rollback/bin.000003 --start-position 1904
...
# at 2261
#121112 11:05:16 server id 1 end_log_pos 2334 Query thread_id=143 exec_time=0 error_code=0
SET TIMESTAMP=1352685916/*!*/;
BEGIN
/*!*/;
# at 2334
#121112 11:05:16 server id 1 end_log_pos 2453 Query thread_id=143 exec_time=0 error_code=0
SET TIMESTAMP=1352685916/*!*/;
update animals set name="giraffe" where name="wolf"
/*!*/;
# at 2453
#121112 11:05:16 server id 1 end_log_pos 2480 Xid = 685
COMMIT/*!*/;
...
ある程度日時で絞り込めるようなら、以下のように指定するこができます。
$ mysqlbinlog /var/log/mysql/rollback/bin.000003 --start-datetime "2012-11-09 09:00:00" --stop-datetime "2012-11-09 10:00:00"
...
# at 2261
#121112 11:05:16 server id 1 end_log_pos 2334 Query thread_id=143 exec_time=0 error_code=0
SET TIMESTAMP=1352685916/*!*/;
BEGIN
/*!*/;
# at 2334
#121112 11:05:16 server id 1 end_log_pos 2453 Query thread_id=143 exec_time=0 error_code=0
SET TIMESTAMP=1352685916/*!*/;
update animals set name="giraffe" where name="wolf"
/*!*/;
# at 2453
#121112 11:05:16 server id 1 end_log_pos 2480 Xid = 685
COMMIT/*!*/;
...
できれば、ファイルに出力して確認するのが良いでしょう。
ここで
end_log_pos 2453
が確認できたので、その手前までをリストア（復元）すればOKです。
- バイナリログの対象ファイルを探す
- バイナリログからロールバックする。
先の手順から、ロールバックする位置が確定したら、あとは、バイナリログからSQLダンプし、それを流し込むだけです。
$ mysqlbinlog /var/log/mysql/rollback/bin.000003 --start-position 1904 --stop-position 2334 | mysql -uroot -p -Dsample_db
Enter password:
これでOKのはずです。
データを出力して確認してみます。
$ mysql -uroot -p -Dsample_db
Enter password:
...
mysql> SELECT * FROM animals;
+----+---------+
| id | name |
+----+---------+
| 1 | wolf |
| 2 | lion |
| 3 | penguin |
| 4 | lax |
| 5 | whale |
| 6 | ostrich |
+----+---------+
6 rows in set (0.00 sec)
mysql>
確かに “dog” → “wolf” までで、
→ “giraffe”は、アップデートされていませんね。
ちゃんと、思った位置へ戻ったようです。
本当のデータベースなら、もっと複雑なSQL文になりますから、位置を探すのが難しいことでしょう。
上記の手順でのポイントは、やっぱりフルバックアップ時のバイナリログ位置をどうするか？です。これさえできていれば、あとでロールバックする際に、位置を見つける時に一手間楽になるはずです。
いずれにしろバイナリログは、何かと役に立つのは間違いないでしょう。バックアップ＋バイナリログによるセキュリティ強化は、行っておくべきでしょうね。
このサイトでは、コンテンツの一部が非表示、あるいは、コメント、お問い合わせの投稿ができない、検索ができないことがあります。
)
コメントを投稿 :