Help us understand the problem. What is going on with this article?

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件のデータ

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

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

kojirock5260
底辺のエンジニアです。 底の底でもがいています。
http://kojirooooocks.hatenablog.com/
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away