運用中に誤ってdropやtruncateなどしてしまい、そのテーブルのみ復旧しなければならないことがあると思います(いや、できる限りあってほしくないですが)。しかしながら、MySQLは特定のテーブルのみを復旧する手段は提供していません。復旧にはその誤った作業以前のフルバックアップとバイナリログを使用して、ポイントインタイムリカバリを行い特定のテーブルのみを抽出するということになります。
今回は特定テーブルをロールバックする流れを、以下2つのリカバリ方法を使用して紹介いたします。
- バイナリログを直接適用してリカバリする方法
- レプリケーション機能を使用してリカバリする方法
フルバックアップについて
今回はフルバックアップの方法について深くは説明しないので、種類と手段を少し紹介します。
バックアップ取得には、データベースのデータのみを取得する論理バックアップと、データベースファイルをコピーして取得する物理バックアップがあります。また、MySQLを稼働したまま取得するオンラインバックアップと、MySQLを停止してから取得するオフラインバックアップがあります。それぞれの手段としていくつかツールがあります。
-
論理バックアップ
- mysqldump(オンラインバックアップ)
- mysqlpump(オンラインバックアップ)
- mydumper(オンラインバックアップ)
-
物理バックアップ
-
- MySQL Enterprise Backup(オンラインバックアップ)
- xtrabackup(オンラインバックアップ)
- MySQLを停止して、cpコマンドなどで物理コピー(オフラインバックアップ)
特定のテーブルをロールバックする
ここからは例を交えて説明したいと思います。
運用中にUSER_LOG
テーブルを誤ってtruncateしてしまい、このテーブルのみロールバックするとします。前提として誤った作業以前のフルバックアップとそのバックアップ時点から該当のtruncate文のトランザクションまでを含むバイナリログが必要となります。
該当のtruncate文を探す
まずは、該当のtruncate文を含むトランザクションを、バイナリログからmysqlbinlogコマンドとgrepコマンドなどを駆使してポジションまたはGTIDを確認します。今回はGTIDではなくポジションを利用します。どのバイナリログに対象のトランザクションが含まれているかは、ファイルの更新日付などで判断しましょう(バイナリログ名はmysql-bin
としています)。
表示された内容にat 30000
とあるので、対象のtruncate文のポジションは30000であることがわかります。また、at 29935
からそのtruncate文の直前の処理のポジションもわかります。今回は2つのリカバリ方法を紹介するため、両ポジション把握しておきます。ここからバイナリログはmysql-bin.000010で、ポジションは30000のtruncate文の実行前まで巻き戻していきます。
フルバックアップを使用してリストアする
今回はこの手順の詳しい内容は割愛しますが、別のMySQLインスタンスを起動してmysqldumpで取得済であったバックアップファイルを使用してリストアしました。このバックアップのバイナリログはmysql-bin.000008で、ポジションは1000であったとします。
該当のtruncate文直前までリカバリを行う
今回は2つの方法を紹介します。
バイナリログを直接適用してリカバリする
こちらはマニュアルに記載された一般的方法です。詳しくは7.5 バイナリログを使用したポイントインタイム(増分)リカバリをご覧ください。
mysqlbinlogコマンドを使用して、バイナリログをリストアしたMySQLに対して適用します。
該当のバイナリログをコピーしてきて、以下のように実行します。
- バイナリログ:mysql-bin.000008、ポジション:1000を開始ポジションとする
- バイナリログ:mysql-bin.000010、truncate文を実行するポジション:30000を終了ポジションとする
ここで注意点がいくつかあります。
--start-position
は指定したポジションから開始され、そのポジションの処理を含めて出力される
--stop-position
は指定したポジションで終了され、そのポジションの処理は含まれない
よって、--stop-position
に30000
を指定することで、そのポジションとそれ以降の出力が行われません。
また、CREATE TEMPORARY TABLE
文がバイナリログを跨ぐ場合は、以下のように1行で記述しなければなりません。細かい内容は前述のマニュアルをご参照ください。
このように記述すると、--start-position
は最初に指定した引数のバイナリログに有効で、--stop-position
は最後に指定した引数のバイナリログに有効となります。記述ミスなどでバイナリログの順番を誤ってしまうと、データが壊れてしまいます。よって、これらのオプションを使用し、CREATE TEMPORARY TABLE
文が存在しない場合は複数回に分けて実施する方が安全かもしれません。
mysqlbinlogコマンドを使用する方法は簡単に実行できますが、いくつかのデメリットがあります。
- どのバイナリログのどのポジションまで適用されているかなどの途中経過を把握できない。
- フルリカバリとなり特定のテーブルのみをリカバリできない。
レプリケーション機能を使用してリカバリする
これはtruncateしてしまったMySQLをマスターにして、リストアしたMySQLをスレーブとして動作させることでリカバリする方法です。そのためにはSTART SLAVE UNTIL
文を使用してバイナリログとポジションを指定します。その指定した箇所に到達することで、SQL_THREADが自動で停止されます。
まずは、リストアしたMySQLに対して、CHANGE MASTER
文にmysqldumpで取得したバックアップのポジションを指定してレプリケーション開始前まで準備します。レプリケーションするための権限は、すでに作成済のものとします。
このあとに、バイナリログがmysql-bin.000010でポジションが29935で停止するように、START SLAVE UNTIL
文を実行します。mysqlbinlogコマンドでは--stop-position
に30000を指定しましたが、ここではその直前の処理の29935
を指定する必要があります。なぜなら、mysqlbinlogコマンドと違って、START SLAVE UNTIL
文の場合は指定したポジションを含めて実行してからSQL_THREADが停止するためです。
そうすると、対象のポジションまで適用が完了すればSQL_THREADが自動で停止します。
IO_THREADは停止しないため、Master_Log_File
とRead_Master_Log_Pos
は進んでいますが、Relay_Master_Log_File
とExec_Master_Log_Pos
からUSER_LOG
テーブルをtruncateする直前の状態のデータになっていることがわかります。
この方法は開始するまでが少し手間ですが、前述の方法のデメリットを補うことができます。
- どのバイナリログのどのポジションまで適用されているか、などの途中経過を
SHOW SLAVE STATUS
から確認可能
- MySQL起動時に
Replicate_Do_Table
オプションに対してUSER_LOG
テーブルを指定することで、特定のテーブルのみリカバリすることも可能
START SLAVE UNTIL
文はGTID有効化でも使用できます。詳しくはマニュアル13.4.2.5 START SLAVE 構文をご確認ください。
注意点として、マルチスレッドスレーブが有効な環境では使用できないため、こちらはOFFにしてください。そして、MySQL起動時に自動でスレーブが起動しないように、skip-slave-start
オプションを指定しておくとより安全です。
対象のテーブルを確認する
ここまで終わると、あとはUSER_LOG
テーブルをmysqldumpでエクスポート、トランスポータブルテーブルスペースやselectでデータを確認するなりして復旧させることで完了となります。
最後に
今回は特定テーブルをロールバックさせる方法について説明しました。通常の運用ではこのようなことは絶対にしたくない作業ですが、ヒューマンエラーなどが原因で行うことがあると思います。その際はこの記事を参考にしてください。