ちょっとまえに同僚がやらかして、本番のうん百万行くらいあるテーブルをTRUNCATEしちゃった時の復旧方法書いときます。
気づいた時には消した後で、更に300件ほど新しくデータが入っている状態からのスタートでした。。。
復旧手順は以下のとおり
- まず落ち着く
- 本番メンテナンス
- 現在入っている300件のデータをdump
- 定期dumpデータからとりあえず過去分を復旧
- binlogからやっちまう直前まで復旧
- チェックしてオープン
- 反省会
ダンプデータからとりあえず過去分を復旧
このプロジェクトでは毎朝5時に定期メンテナンスがあり、その間にDB全体のデータをmysqldumpしていたので、とりあえずその日の朝5時までのデータを取得することにした。
ただ、このダンプデータは、特にオプションを指定せずmysqldumpを実行していたようで、スキーマ情報も取得していたので、とりあえず下記コマンドでINSERT文を抜き取ることにした。
# dumpファイルからLOCKとついた文字列ごとにファイルを分割する
csplit dump.sql /^LOCK/ {*}
# 分割されたxx*のファイルから指定のテーブルへのINSERT文がどのファイルかを調べる
egrep -rn 'INSERT INTO `テーブル名`' xx* | sed -e "s/[:].*//"
less -S 見つけたファイル
# INSERT文のみコピペでどっか退避させておく
binlogからやっちまう直前まで復旧
次に朝5時からメンテナンス入れたところまでのSQLをbinlogから抜き取る。
どこかの天才が作ってくれていたperlコマンドを元になんとなく書き換えて、改行なども考慮して、特定のテーブルへ発行されたSQLのみを抜き取ることに成功。←おそらく・・・
mysqlbinlog --start-datetime="その日の定期メンテ明けの時間" binログファイル | perl -e 'while(<>){ chomp; next if m!^#!; if ( m{/*!*/;$} ) { $p .= $_; print "$p\n"; $p="" } else { $p .= $_." "} }' | grep 指定テーブル名
当然その中には、やっちまった
truncate テーブル名
もあるので、そのSQLの一つ前までのSQLを取得。
あとは、流す順番を間違えずに、
- 朝五時までのSQL
- やっちまう直前までのSQL
- やっちまってからの300件のデータ
を入れてあげて、復旧完了。
ほかにいい方法あれば、ぜひ教えて下さい。