Edited at

MySQLで、特定のテーブルをTRUNCATEしちゃった時の復旧方法

More than 3 years have passed since last update.

ちょっとまえに同僚がやらかして、本番のうん百万行くらいあるテーブルをTRUNCATEしちゃった時の復旧方法書いときます。

気づいた時には消した後で、更に300件ほど新しくデータが入っている状態からのスタートでした。。。

復旧手順は以下のとおり


  1. まず落ち着く

  2. 本番メンテナンス

  3. 現在入っている300件のデータをdump

  4. 定期dumpデータからとりあえず過去分を復旧

  5. binlogからやっちまう直前まで復旧

  6. チェックしてオープン

  7. 反省会


ダンプデータからとりあえず過去分を復旧

このプロジェクトでは毎朝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を取得。

あとは、流す順番を間違えずに、


  1. 朝五時までのSQL

  2. やっちまう直前までのSQL

  3. やっちまってからの300件のデータ

を入れてあげて、復旧完了。

ほかにいい方法あれば、ぜひ教えて下さい。