mysql binlogからDB復旧

mysqlbinlogでDB復旧した時の自分用メモ。繰り返す自分用のメモ。

依頼内容

依頼者「2014-02-14の20時台に作業間違えた。なかったコトにしてもらえませんか。」
俺「・・・」

やりたいこと

2014-02-14の20時台に投げられたクエリをすべてなかったことにしたい。

作業手順

• バックアップしたデータベースをリストアする
• mysqlbinlogで、リストアした時間（2014-02-13 03:30）から該当時間（2014-02-14 20:00）まで、ログ吐き出して適用
• 該当時間後の正常なデータを吐き出し。具体的に2014-02-14 20:59:59以降のデータを吐き出して適用
• データ確認 && 差し替え

前提条件

• binlogが吐かれていること
• 定期バックアップが取られていること

binlogを吐く方法

/etc/my.confにbinlogの設定をする。
基本レプリケーションの設定とか、してたら書いてるはず。

/etc/my.conf

log-bin=mysql-bin
expire_logs_days=7

##定期バックアップ
定時でバックアップをとるスクリプト
適当な場所に配置。とりあえず、『/root/sh/db_backup.sh』に書いておいた。
これがないと人生詰む。DBバックアップは絶対やっておこう。

/root/sh/db_backup.sh

#!/bin/sh

DATE=`date '+%Y%m%d'`
DUMPCMD="/usr/local/mysql/bin/mysqldump"

# 危険だよねわかる。
PASS="urpasswd"

$DUMPCMD -uroot -p${PASS}  \
target-db1 target-db2 target-db3 | gzip > /urbackupdir/UR-IMPORTANT-DB.${DATE}.sql.gz

# 一応echoって書いてるけど、hipchatとかに飛ばすとたのしい
echo "dump completed"

cronで定時バックアップ。

$ sudo crontab -e
30 03 * * 0 /bin/sh /root/sh/db_backup.sh

# 動作確認用
02 12 17 2 * /bin/sh /root/sh/db_backup.sh

障害時対応

定時バックアップで保存した、リカバリ用のデータを持ってくる。(recovery.sqlとする)

必ず、障害対応を行いたいサーバー以外で、データの復旧を行う。
binlogのposition変わってめんどくなるし、焦ってbinlog消したりするとクビになる。注意。

もっというと、recovery.sqlの中で、
INSERT INTO targetDB.targetTable (...) VALUES(...)って書いてるから、recoveryに向けて、
食わせたつもりでも、本番用と混ざるのでヤバイことになる。

/usr/local/mysql/bin/mysql -uroot -p recovery < recovery.sql

dbのダンプ前にぜひとも書き換えを発生させる元凶を止めておこう、
例えば、ウェブサーバー、slaveのDB、定期バッチも気にしておく。

# 今回はwebサーバーだけ止めた。
service httpd stop

バックアップした時間から、20時までのデータを吐き出し。
今回の最終binlogは「mysql-bin.000044」だった。タイムスタンプとかで判断
事前にdumpしておいたファイル(例えば先程のrecovery.sql)の最終行あたりを読んで、節目となる正確な時間は掴んどいた方がいい。

/usr/local/mysql/bin/mysqlbinlog -uroot -p -D mysql-bin.000044  \
--start-datetime="2014-02-11 03:00:00" \
--stop-datetime="2014-02-14 20:00:00" > ~/binlogbk/recover-head.sql

同様に。21時からのデータを吐き出し

/usr/local/mysql/bin/mysqlbinlog -uroot -p -D mysql-bin.000044 \
--start-datetime="2014-02-14 21:00:00" > ~/binlogbk/recover-tail.sql

吐き出したファイルをまとめたら、作業は概ね終了だ。

/usr/local/mysql/bin/mysql -uroot -p recover < ~/binlogbk/recover-head.sql
/usr/local/mysql/bin/mysql -uroot -p recover < ~/binlogbk/recover-tail.sql

リカバリ用に作ったDBを確認して、大丈夫そうだったら本番用と差し替えよう。

/usr/local/mysql/bin/mysql -uroot -p

なお、確認と差し替えはphpmyadmin使った。