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の設定をする。
基本レプリケーションの設定とか、してたら書いてるはず。
log-bin=mysql-bin
expire_logs_days=7
定期バックアップ
定時でバックアップをとるスクリプト
適当な場所に配置。とりあえず、『/root/sh/db_backup.sh』に書いておいた。
これがないと人生詰む。DBバックアップは絶対やっておこう。
#!/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使った。