目黒川の桜きれいですね〜(*^^*)…なーんてガラじゃないことを言いたくなるくらい良い咲きっぷりでしたよ、エエ。で、来週末、花見に行くんだけど、まだ散らないでほしいっすねー。
えーっと、久しぶりにMySQLの記事。binlogを使ったリストア手法について。ネットを漁るとMySQLの運用に関する記事は多くヒットするんだけど、障害からのデータリカバリ、特にロールフォワードを扱った記事が思ったより多くない。おれは運が良いのか悪いのかMySQLのデータリカバリをしなければならないような局面に何度か直面しているので、手順について書いてみようかな、と。ここではMySQL〜5.5を対象にしている。直近での最新のメジャーバージョンはMySQL5.6なんだけど、おれはまだ5.6について大して知らない。5.6ならもっとイケてるやりかたがあるかもしれない。あったらいいな。



0. 環境

次のような環境を前提として話を進める。マスタにバックアップ取得専用のスレーブ(slave for backup)がぶらさがっていて、そこでmysqldumpやらxtrabackupを使ってある時点のバックアップを取得している。さらに世代管理のためにmysqldump/xtrabackupのデータ、およびバックアップ取得時点までのバイナリログがbackup serverに転送されている。まあ大抵のシステムはこんな構成になってるんじゃあないかと思う。マスタで日次dumpを取得しているケースも見たことはあるけど、ハードウェア予算に余裕があるのであれば、オンライン処理から切り離されたバックアップ取得専用のスレーブサーバが欲しいな。

mysql_backup

いずれにせよ、データリカバリのためには下記の条件を満たしている必要がある。


  • ある時点のバックアップが取得されて世代管理されている
    • これがないとお話にならない。オペミスしたら死亡。DBに保存されているデータによっては、執行役員全員焼き土下座した上に会社が消えてなくなる可能性もあるので、もしバックアップを取ってない場合は今すぐ取得した方が良い。オペミスの契機としては、エンジニアによるDBの直接操作、非エンジニアによる管理ツールからの操作(特定のユーザidのデータを削除する際に、ツールに入力するidを間違えた、そしてDBで物理削除が行われた…)などがある。バックアップを取得する手段としてはmysqldump、xtrabackup、ファイルシステムのスナップショット、mysqlを止めてデータディレクトリごとコピー・・・など様々な手段がある。システムの特性や制約に応じて好みの手法を選択すれば良いと思う。おれはxtrabackupが好きかな。
  • mysqldump/xtrabackupを取得した時点のバイナリログが管理されている
    • dump/xtrabackupを世代管理していればロールバックはできる。しかしdump/xtrabackup取得時点のログがわからないとロールフォワードができない。
    • また、バイナリログの出力フォーマットであるが、MIXED(my.cnf : binlog_format=MIXED)ないしSTATEMENT(my.cnf : binlog_format=STATEMENT)が良いと考えている(MIXEDは4.1系では使えない)。たまにROWを推す記事も見かけるが、ROWにするとバイナリログの内容が人間には解読できなくなってしまうため、オペミスで流れた更新クエリの特定が困難になってしまう。
1. リカバリの大まかな手順

例えばオペミスをしてしまい、データが壊れてしまったときを想定する(誤ったレコードに対してdelete 文を発行してしまった、drop database/tableしてしまった)。リカバリ手順は次のようになる。
  1. バップアップ取得時点まで戻す。
  2. バイナリログを使って任意の更新を当てる。
どんな障害が起きようと基本的な手順はコレ。状況によっては、さらにbinlogを加工したり、SQL発行してウマいことやらなければならないケースもあるけど。

1.1 バックアップ取得時点まで戻す

バックアップサーバに保存されているデータ(ダンプやxtrabackupによるスナップショットなど)をマスタDBに放り込む。いわゆるロールバック。mysqldumpまたはxtrabackupでバックアップしている場合の戻し方の例を簡単に書いておくと、xtrabackupの場合はinnobackupex –apply-logの後innobackupex –copy-back、mysqldumpの場合はmysql -uroot xxx_db < xxxx.dumpなどとなる。このへんはMySQL公式サイト、xtrabackup公式サイトに詳しい。

これでバックアップ取得時点までデータが戻ったことになる。ロールバック完了。

1.2 バイナリログを使って任意の更新を当てる。

ここからはロールフォワード。バックアップサーバに保存されたバイナリログ、マスタやスレーブに残っているバイナリログを参照して、オペミス以外の更新クエリをDBに当てる。これが完了すればロールフォワード完了。

Ex) バイナリログの内容&バイナリログの当てかた

バイナリログの読み方とmysqlbinlogコマンドについて述べる。データリカバリのオペレーションの肝はmysqlbinlogコマンドの扱いを習得していることに尽きると思う。習得するというと大げさかもね。そんなに難しいものではない。

ログの読み方についてだけど、適当なバイナリログ(例えばmysql-bin.000010)に対して、mysqlbinlog mysql-bin.000010とかしてやると、ログの内容が標準出力される。下記の画像に例を示す。

  01_binlog_example

画像を見るとinsert文などが記録されていることがわかると思う。ログの構造は下記のようになっている。

1
2
3
4
5
6
# at 2161 ★ 下記クエリの開始ポジション ★
#130320 22:15:11 server id xxxxx end_log_pos 2264 ★ 下記更新クエリの終了ポジション ★ Query thread_id=16 exec_time=0 error_code=0
SET TIMESTAMP=1363785311/*!*/;
insert into ore_no_homepage.t set id = 10000
/*!*/;
# at 2264 ★ 次の更新クエリの開始ポジション ★

この部分からわかることは、insert into ore_no_homepage.t set id = 10000というクエリが、2013年3月20日22:15:11に、このバイナリログのポジション2161〜2264に記録されていることを意味する。ここで、insert into ore_no_homepage.t set id = 10000だけをDBに当てたい場合は、mysqlbinlog –start-position=2161 –stop-position=2264 mysql-bin.000010 | mysql -urootを実行してやれば良い。これによって、対象のDBにinsert into ore_no_homepage.t set id = 10000が実行されることになる。

↓によく使うと思うコマンドを書いておく。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# バイナリログmysql-bin.000002の内容を標準出力する
mysqlbinlog mysql-bin.000002

# バイナリログmysql-bin.000002の内容をテキストファイルに保存する
mysqlbinlog mysql-bin.000002 > mysql-bin.000002.txt

# バイナリログmysql-bin.000002の、ポジション79967149〜80129966の内容をテキストファイルに保存する
mysqlbinlog --start-position=79967149 --stop-position=80129966 mysql-bin.000002 > mysql-bin.000002_79967149-80129966.txt

# バイナリログmysql-bin.001355に記録されている更新クエリ当てる。
mysqlbinlog mysql-bin.001355 | mysql -uroot

# バイナリログmysql-bin.000002の、ポジション79967149〜80129966に記録されている更新クエリを当てる
mysqlbinlog --start-position=79967149 --stop-position=80129966 mysql-bin.000002 | mysql -uroot

こんだけ覚えておけばとりあえずは大丈夫だと思う。このコマンドを駆使して任意の更新クエリをDBに当ててやることができる。ちなみに、startとstopには時刻を指定することもできる。時刻を使ってやるのもいいけど、ポジションを見て確実に当ててやる方が安心できるんじゃないかな。また、–startを省略するとそのログの先頭から、–stopを省略するとそのログの最後まで、と見なされる。

2. ケーススタディ(たとえばdrop table しちゃった)

さて、能書きが終わったところで実際にありそうな状況を想定した場合のケーススタディ。テーブルをdrop tableしてしまったときのケースについて簡単に試してみる。流れとしては何度も述べているように次の通りとなる。
  1. バップアップ取得時点まで戻す。
  2. バイナリログを使ってdrop table直前までの更新を当てる。
↓の画像を見ながら….左ペインがSQLの実行、右ペインが対応するバイナリログとなっている。

study

適当なテーブルore_no_homepage.tに対して次の操作を行う。
  1. id = 111を挿入
  2. id = 222を挿入
  3. mysqldumpでバックアップを取得(左ペインの青い囲み)
  4. id = 333を挿入
  5. id = 444を挿入
  6. ここでオペミスと称してdrop table ore_no_homepage.tを実行(赤い囲み)
そしてここからがリカバリ。
  1. mysql -uroot ore_no_homepage < /tmp/dump.dumpを実行してバックアップを戻す(ロールバック)。
  2. ここでselect * from ore_no_homepage.tを実行すると、バックアップ取得直前までのid = 111, id = 222が入っていることがわかる。
  3. そしてバイナリログを参照してポジションを特定。mysqlbinlogコマンドにて、バックアップ取得直後のポジション589から、オペミスであるdrop tableの直前のポジション973を当てる(左ペイン黄色の囲み、右ペインにて対応するポジションを同じく黄色い線で囲う)
  4. で、再びselect * from ore_no_homepage.tを実行すると、オペミスのdrop tableの直前までの更新クエリが対象のテーブルに反映されていることがわかる。
以上となる。ここではおそらく最も簡単な例を示したが、mysqlbinlogコマンドを駆使すれば余計なクエリを省いて任意のクエリだけを当てることができることがわかったと思う。

3. まとめ

DB障害からのリカバリに関する簡単な手順を示した。次のことを確実にしておくだけで割と冷静に対応できる。
  • バックアップを取っておく。
  • バックアップ取得時点のバイナリログを取っておく。
  • バイナリログを読めるようになっておく。
  • mysqlbinlogコマンドを扱えるようになっておく。
まぁ、だいたい最初に書いた通りですね…。

4. ボヤキ
  • ちなみにおれは、mysqlbinlogコマンドで数時間かかってログを当ててる最中、–stop-positionの値を間違えて目的のポジションを通り過ぎてしまったという自殺モンのオペミスをキメたことがある(当然、最初からやりなおし)。mysqlbinlogコマンドはおれにとってはトラウマコマンドでもある。
  • mysqlbinlogコマンドでログを当てると、ログの量や記録されている更新データによっては結構時間がかかったりするんだけどioDriveやFlashMAX上で実行すると爆速です。フラッシュを搭載したサーバを所持していると思わぬところで役に立つ・・・。
  • AWSのRDSはロールフォワードできねえ(2013/3/21現在)。サーバに入れないのでバイナリログを参照できない。なんとかしてよAmazon…。
 

 

One Response to MySQL バイナリログを使ったデータリカバリ

  1. iad_otomamay より:

    うれしいまとめ!これでDropもTruncateもし放題!!

iad_otomamay にコメントする コメントをキャンセル

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

Set your Twitter account name in your settings to use the TwitterBar Section.