gihyo.jpサイトのロゴ

MySQL道普請便り

第46回特定テーブルをロールバックする方法

2017-05-30

運用中に誤って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としています⁠）⁠。

[root]# mysqlbinlog mysql-bin.000010 | grep -i -B6 truncate

# at 29935
#170515 10:36:32 server id 1  end_log_pos 3025 CRC32 0x9518b1e8         Anonymous_GTID  last_committed=11       sequence_number=12
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 30000
#170515 10:36:32 server id 1  end_log_pos 3106 CRC32 0x1ebd34e9         Query   thread_id=7     exec_time=0     error_code=0
SET TIMESTAMP=1494812192/*!*/;
truncate table USER_LOG

表示された内容に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に対して適用します。

該当のバイナリログをコピーしてきて、以下のように実行します。

# mysqlbinlog --start-position=1000  mysql-bin.000008 | mysql -uroot -p
# mysqlbinlog  mysql-bin.000009  | mysql -uroot -p
# mysqlbinlog --stop-position=30000  mysql-bin.000010 | mysql -uroot -p
  • バイナリログ:mysql-bin.000008、ポジション:1000を開始ポジションとする
  • バイナリログ:mysql-bin.000010、truncate文を実行するポジション:30000を終了ポジションとする

ここで注意点がいくつかあります。

  1. --start-positionは指定したポジションから開始され、そのポジションの処理を含めて出力される
  2. --stop-positionは指定したポジションで終了され、そのポジションの処理は含まれない

よって、--stop-positionに30000を指定することで、そのポジションとそれ以降の出力が行われません。

また、CREATE TEMPORARY TABLE文がバイナリログを跨ぐ場合は、以下のように1行で記述しなければなりません。細かい内容は前述のマニュアルをご参照ください。

# mysqlbinlog --start-position=1000 --stop-position=30000 mysql-bin.000008 mysql-bin.000009 mysql-bin.000010 | mysql -uroot -p

このように記述すると、--start-positionは最初に指定した引数のバイナリログに有効で、--stop-positionは最後に指定した引数のバイナリログに有効となります。記述ミスなどでバイナリログの順番を誤ってしまうと、データが壊れてしまいます。よって、これらのオプションを使用し、CREATE TEMPORARY TABLE文が存在しない場合は複数回に分けて実施する方が安全かもしれません。

mysqlbinlogコマンドを使用する方法は簡単に実行できますが、いくつかのデメリットがあります。

  • どのバイナリログのどのポジションまで適用されているかなどの途中経過を把握できない。
  • フルリカバリとなり特定のテーブルのみをリカバリできない。

レプリケーション機能を使用してリカバリする

これはtruncateしてしまったMySQLをマスターにして、リストアしたMySQLをスレーブとして動作させることでリカバリする方法です。そのためにはSTART SLAVE UNTIL文を使用してバイナリログとポジションを指定します。その指定した箇所に到達することで、SQL_THREADが自動で停止されます。

まずは、リストアしたMySQLに対して、CHANGE MASTER文にmysqldumpで取得したバックアップのポジションを指定してレプリケーション開始前まで準備します。レプリケーションするための権限は、すでに作成済のものとします。

mysql> CHANGE MASTER TO
           master_host='hostname',
           master_port=3306,
           master_user='user',
           master_password='xxxx',
           master_log_file='mysql-bin.000008',
           master_log_pos=1000;

このあとに、バイナリログがmysql-bin.000010でポジションが29935で停止するように、START SLAVE UNTIL文を実行します。mysqlbinlogコマンドでは--stop-positionに30000を指定しましたが、ここではその直前の処理の29935を指定する必要があります。なぜなら、mysqlbinlogコマンドと違って、START SLAVE UNTIL文の場合は指定したポジションを含めて実行してからSQL_THREADが停止するためです。

mysql> START SLAVE UNTIL MASTER_LOG_FILE='mysql-bin.000010', MASTER_LOG_POS=29935;

そうすると、対象のポジションまで適用が完了すればSQL_THREADが自動で停止します。

mysql> SHOW SLAVE STATUS¥G
（一部割愛）
              Master_Log_File: binary_log.000015
          Read_Master_Log_Pos: 150989977
        Relay_Master_Log_File: binary_log.000010
             Slave_IO_Running: YES
            Slave_SQL_Running: No
・
          Exec_Master_Log_Pos: 29935
・
               Until_Log_File: mysql-bin.000010
                Until_Log_Pos: 29935

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でデータを確認するなりして復旧させることで完了となります。

最後に

今回は特定テーブルをロールバックさせる方法について説明しました。通常の運用ではこのようなことは絶対にしたくない作業ですが、ヒューマンエラーなどが原因で行うことがあると思います。その際はこの記事を参考にしてください。

おすすめ記事

記事・ニュース一覧