SRGについて技術記事採用情報
SRGについて技術記事採用情報
HOME/技術記事/誤って消したDBデータを消す直前の状態まで復旧する方法

誤って消したDBデータを消す直前の状態まで復旧する方法

技術本部 サービスリライアビリティグループ（SRG）の鬼海（@fat47）です。
#SRG（Service Reliability Group）は、主に弊社メディアサービスのインフラ周りを横断的にサポートしており、既存サービスの改善や新規立ち上げ、OSS貢献などを行っているグループです。
本記事は、SRG 内にある DBWG（DBワーキンググループ）が全社内向けに提供しているデータベースに関する資料を公開します。
なにかの役に立てば幸いです。
 

概要

オペレーションミスでTABLE DROPや、アプリケーションのバグによりDELETEのWHERE条件が外れてレコード全消しになった等の操作が行われたMySQLを、その操作が行われる直前まで復旧する方法のまとめです
私達が管理しているサービスのMySQLでは、日次のPercona XtraBackupによるフルバックアップに加えて、5分毎にbinlogファイルのバックアップを取得しています。
それらのバックアップファイルを使用した復旧方法をご紹介します。
 
今回の内容は主にオンプレミス環境でMySQLを運用している場合の内容となります。
Amazon Auroraの場合は少し話が変わってきますので、下記のサイバーエージェントデベロッパーズブログの記事をご参照ください。
Aurora MySQL のバックアップは本当にそれでいいのだろうか？ | CyberAgent Developers Blog
技術本部 サービスリライアビリティグループ（SRG）の長谷川 @rarirureluis です。 #SRG （Service Reliability Group）は、主に弊社メディアサービスのインフラ周りを横断的にサポートしており、既存サービスの改善や新規立ち上げ、OSS貢献などを行っているグループです。 また Amazon Aurora MySQL（以下：Aurora MySQL）の話です。何でこんなに Aurora MySQL に関する記事ばっか書いてるのか僕も分かりません。 前回の Aurora MySQL のアップグレード方法のベストプラクティスはこちらです。 RDS Graviton2 に少ないリスクで切り替える方法を考えてみる【アップグレード編】 | CyberAgent Developers Blog 今回はバックアップについてです。 例えばテーブルを間違えて DROP してしまったとき、DROP する直前までの状態に戻すことはできますか？ もしくは直前まで戻す方法はドキュメント化されていますか？ これは AWS などのパブリッククラウドに限った話ではないのですが、フルバックアップだけを取っていても ある特定のクエリまで戻すことはできません。 そこで必要になるのがバイナリログです。 最新のフルバックアップと、そのバックアップ以降に取られたバイナリログさえあれば、特定のクエリまで復元することができます。 Aurora MySQL には復元に関する機能に バックトラック と ポイントインタイムリカバリ の２つ機能があります。 これら２つの機能は特定のクエリの時点で復元するのではなく、 日時指定で復元する機能です。 そのため間違ったクエリの直前まで戻すといったことはできません。 バックトラックは指定した時間までクラスターを
https://developers.cyberagent.co.jp/blog/archives/29925/
 

全体の流れ

  1. フルバックアップデータ、バックアップbinlogの置き場所を確認
  1. 誤って実行したクエリと実行された時間を確認
  1. そのクエリが実行されるより前の時間のフルバックアップ取得時のbinlog position確認
  1. 誤って実行したクエリの直前のbinlog position確認
  1. binlogファイルから復旧用クエリを生成
  1. フルバックアップデータをサービスインしている全DBに展開
  1. 復旧用クエリをSET sql_log_bin = 0してから全DBに適用
  1. 最後にレプリケーションを再構築
 

手順

フルバックアップデータ、バックアップ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などの外部ネットワーク上にバックアップがある場合は以下の方法が良いと思います。
  1. S3からどれか1台のスレーブサーバにフルバックアップデータをダウンロード
  1. そのスレーブサーバでxtrabackupデータの展開
    1. 展開が完了したらそのスレーブサーバの/var/lib/mysqlのDBデータを全DBにコピー転送

    復旧用クエリをSET sql_log_bin = 0実行してから全DBに適用

    全DBにフルバックアップデータが展開されたら、全DBにバイナリログから抽出したSQLを適用していきます。
    普通にDBマスターにだけ復旧用クエリを流し込んだ場合、レプリケーション遅延の収束までにさらに時間がかかることになるので、sql_log_bin = 0してから全DB同時に流し込んでいきます。

    最後にレプリケーションを再構築

    全DBデータコピー完了したら、マスタースレーブ間でレプリケーションの設定を行います。
    これでDBは指定したクエリが実行される直前の状態に復元されました。
     

    終わりに

    バックアップデータからの復元は数年に一度あるかないかのオペレーションになると思います。
    万が一の時に落ち着いてオペレーションができるように、普段からリカバリ方法のドキュメント化や実施訓練をしておくことをオススメします。
     
    SRG では一緒に働く仲間を募集しています。 ご興味ありましたらぜひこちらからご連絡ください。