誤って消したDBデータを消す直前の状態まで復旧する方法
技術本部 サービスリライアビリティグループ（SRG）の鬼海（@fat47）です。
#SRG（Service Reliability Group）は、主に弊社メディアサービスのインフラ周りを横断的にサポートしており、既存サービスの改善や新規立ち上げ、OSS貢献などを行っているグループです。
なにかの役に立てば幸いです。
概要全体の流れ手順フルバックアップデータ、バックアップbinlogの置き場所を確認誤って実行したクエリと実行された時間を確認そのクエリが実行されるより前の時間のフルバックアップ取得時のbinlog position確認誤って実行したクエリの直前のbinlog position確認binlogファイルから復旧用クエリを生成フルバックアップデータをサービスインしている全DBに展開復旧用クエリをSET sql_log_bin = 0実行してから全DBに適用最後にレプリケーションを再構築終わりに
概要
オペレーションミスでTABLE DROPや、アプリケーションのバグによりDELETEのWHERE条件が外れてレコード全消しになった等の操作が行われたMySQLを、その操作が行われる直前まで復旧する方法のまとめです
私達が管理しているサービスのMySQLでは、日次のPercona XtraBackupによるフルバックアップに加えて、5分毎にbinlogファイルのバックアップを取得しています。
それらのバックアップファイルを使用した復旧方法をご紹介します。
今回の内容は主にオンプレミス環境でMySQLを運用している場合の内容となります。
Amazon Auroraの場合は少し話が変わってきますので、下記のサイバーエージェントデベロッパーズブログの記事をご参照ください。
全体の流れ
- フルバックアップデータ、バックアップbinlogの置き場所を確認
- 誤って実行したクエリと実行された時間を確認
- そのクエリが実行されるより前の時間のフルバックアップ取得時のbinlog position確認
- 誤って実行したクエリの直前のbinlog position確認
- binlogファイルから復旧用クエリを生成
- フルバックアップデータをサービスインしている全DBに展開
- 復旧用クエリをSET sql_log_bin = 0してから全DBに適用
- 最後にレプリケーションを再構築
手順
フルバックアップデータ、バックアップbinlogの置き場所を確認
バックアップのデータを置いている場所を確認します。
S3にアップロードしているのか、どこかの物理サーバにアップロードしているのかなどは普段から把握しておきましょう。
誤って実行したクエリと実行された時間を確認
実行されたどのクエリの直前までデータを戻すのかを確定させます。
サービス関係者や開発者に確認をしましょう。
そのクエリが実行されるより前の時間のフルバックアップ取得時のbinlog position確認
XtraBackupで取得したバックアップファイルの中にあるの中身を確認すると、バックアップが取られた時点のbinlogファイル名とpositionがわかります。
誤って実行したクエリの直前のbinlog position確認
特定のクエリの直前を指定する場合はバックアップしたbinlogファイルの中から、そのクエリを特定する必要があります。
binlogはそのままでは読めないので、mysqlbinlogコマンドでSQL形式に戻してから該当のクエリを検索します。
上記のDELETE文の直前まで戻したいとなったら、その上ブロックのをメモしておきます。
binlogファイルから復旧用クエリを生成
「バックアップが取られたポジション」と「戻したい時点のポジション」を確認することができたので、フルバックアップをリストアしたDBに適用するためのクエリを生成します。
これで先ほどのDELETE分の直前まで適用するSQLが生成されました。
上記のようにある特定のクエリまで戻すのではなく、ある時間までの状態に戻したい場合は下記のような記述もできます。
この場合はフルバックアップデータから、3月4日の13時5分までという指定の例です
フルバックアップデータをサービスインしている全DBに展開
XtraBackupのフルバックアップのデータをDBサーバに展開させます。
S3などの外部ネットワーク上にバックアップがある場合は以下の方法が良いと思います。
- S3からどれか1台のスレーブサーバにフルバックアップデータをダウンロード
- そのスレーブサーバでxtrabackupデータの展開
- 展開が完了したらそのスレーブサーバの/var/lib/mysqlのDBデータを全DBにコピー転送
復旧用クエリをSET sql_log_bin = 0実行してから全DBに適用
全DBにフルバックアップデータが展開されたら、全DBにバイナリログから抽出したSQLを適用していきます。
普通にDBマスターにだけ復旧用クエリを流し込んだ場合、レプリケーション遅延の収束までにさらに時間がかかることになるので、sql_log_bin = 0してから全DB同時に流し込んでいきます。
最後にレプリケーションを再構築
全DBデータコピー完了したら、マスタースレーブ間でレプリケーションの設定を行います。
これでDBは指定したクエリが実行される直前の状態に復元されました。
終わりに
バックアップデータからの復元は数年に一度あるかないかのオペレーションになると思います。
万が一の時に落ち着いてオペレーションができるように、普段からリカバリ方法のドキュメント化や実施訓練をしておくことをオススメします。
SRG では一緒に働く仲間を募集しています。
ご興味ありましたらぜひこちらからご連絡ください。
