こんにちは satoです。

オペミスで update に where句を付け忘れたり、プログラムのバグでデータが破損してしまったりした場合でも、バイナリログには更新SQLがすべて書き込まれるので、バックアップデータからオペミスが起こるまでの全てのSQLを流し込めれば、元の状態に戻すことは可能です。

　•バイナリログを取っている
　•オンラインバックアップをとっている(mysqldumpやMySQLを止めた状態でのcpによるバックアップとバイナリログ)
　•バックアップ時点でのバイナリログの書き込み位置を保存している

以上のような状態でデータが壊れた時の復旧手順をまとめてみました。シナリオとして

　•ある1カラム email をupdateしようとしたら、間違ってwhere 句を付け忘れ 全レコードをupdateしてしまった
　•気がついたのが半日後

というオペミスが発生したとします

1) データベースが更新されない状態にします(メンテナンス画面など)

2) オペミスをしてしまった binlog のバッックアップをとり、オペミスのsqlのblog上での位置を調べます

mysqlのバイナリログを残す設定にしておくと、mysqlは更新クエリーが発生すると、バイナリログに書き込みます mysqlbinlog という mysqlに付属しているツールを使用すると、バイナリログの中身を閲覧することができます

 sudo -u mysql mysqlbinlog /var/log/mysqld/blog.000001
 
 # at 461758345
 #070821 16:00:35 server id 1  end_log_pos 127   Query   thread_id=147523        exec_time=0     error_code=0
 実行したSQL文
 # at 461758472
 #070821 16:00:35 server id 1  end_log_pos 461758499     Xid = 14244002
 実行したSQL文

#at と書いてあるのがバイナリログの位置になります。

mysqlbinlog には 便利なオプションがついていて、
ある一定の時間から一定の時間までのログを表示する (--start-datetime,--stop-datetime)
ある一定の位置から一定の位置までのログを表示する (--start-position, --stop-position)
などがあります。これを利用して、オペミスの時間から問題のSQLのバイナリログの位置を割り出します。オペミスが 2007-07-20 11:27:00 頃発生したとすると

 sudo -u mysql mysqlbinlog /var/log/mysqld/blog.000001 --start-datetime 2007-07-20 11:25:00 --end-datetime 2007-07-20 11:30:00
 # at 561758345
 #070821 11:26:46 server id 1  end_log_pos 127   Query   thread_id=147521        exec_time=0     error_code=0
 update user set email = '''

原因のSQLのバイナリログ上の位置は 561758345 となります。

3) 一番最近のバックアップ状態に戻します

4) バックアップを取ったオペミスしてしまったバイナリログから、バックアップ復元時のスタート位置と オペミス直前までの位置を指定してSQLをすべて流し込みます。

バックアップ時のバイナリログの位置は

 mysql> show master status;
+-------------+-----------+------
| File        | Position  
+-------------+-----------+------
| blog.000002 | 462862334 
+-------------+----------

と調べることができます

あとはsql文を流し込みます。mysql コマンドは標準出力のSQLを流し込むことができるので、mysqlbinlog コマンドと組み合わせ使います。

sudo -u mysql mysqlbinlog /var/log/mysqld/blog.000001_bak --start-possion 461764451 --end-possion 561758345 | mysql -u ユーザ名 -p DB名

今回の場合バイナリログが同じファイルだったのでよかったのですが、再起動したり、バイナリログのサイズがmax_binlog_sizeに達すると、自動的にファイルがローテートしてしまう場合が有るので、その場合は全てのファイルを使う必要があります。また設定によってCREATE DATABASEなどがバイナリログに書き込まれない場合がありますのでここを参照してください。今回は masterだけのサーバでしたが、スレーブからバックアップを取っている場合は show slave status　でマスターの位置を調べなくてはならないとかありそうですが、方法はほとんど同じ感じだと思います。

あと、どうでもいいことなんですが、DBAって性格的な向き不向きがかなりあるようなきがします。