【MySQL】ロールバックとロールフォワードによるデータリカバリ #MySQL

はじめに

DBのデータリカバリを行う際の「ロールバック」や「ロールフォワード」について、資格の勉強で単語やその意味は知っているものの実際に使ったことは無かったので、データリカバリの流れを実践してみる。

用語解説

まずは用語をおさらいする。

ロールバック

チェックポイント時点のデータ（バックアップ）に戻すこと。
リストアとも呼ぶ。

ロールフォワード

チェックポイント時点のデータにバイナリログから抽出したクエリを反映し、データベースを特定タイミングの状態に戻すこと。
リストア＋αの作業によりデータを障害が発生した直前まで復元することを、リカバリと呼ぶ。

バイナリログ

create/drop/insert/update/deleteの、作成・削除・挿入・更新に関係するイベントが記録される。
※MySQLは8.0からデフォルトでバイナリログが有効になっている。

設定ファイルのlog-binで、バイナリファイルのベース名を指定することができる。
私の環境では、デフォルトで下記のように設定がされていた。

MySQL Server 8.0/my.ini

# ***** Group Replication Related *****
# Specifies the base name to use for binary log files. With binary logging
# enabled, the server logs all statements that change data to the binary
# log, which is used for backup and replication.
log-bin="NOSCL235-bin"

データリカバリの流れ

データリカバリ作業前に、フルバックアップを取得しておく。
チェックポイントから特定のタイミング（データを復旧したいタイミング）までのクエリを、バイナリログから抽出する。
※定期的にチェックポイントを設けてフルバックアップを取得しているものとする。
ロールバック（チェックポイント時点のフルバックアップを投入する）
ロールフォワード（チェックポイントから特定のタイミングまでのデータを復旧する）

データリカバリの実践

下準備①：チェックポイント時点のフルバックアップ作成

テーブルに下記の通りテストデータを用意しておく。

mysql> select * from users;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | ユーザー1 |
|  2 | ユーザー2 |
+----+-----------+
2 rows in set (0.00 sec)

この状態で、チェックポイント時点の体でフルバックアップを取得する。

MySQL Server 8.0>mysqldump -u root -p sample --flush-logs --master-data=2 > ./tmp/sample_backup.sql
WARNING: --master-data is deprecated and will be removed in a future version. Use --source-data instead.
Enter password: ********

flush-logsオプション
バイナリログがローテートされて新しいファイルが作成されるので、このオプションを指定しておけば復元時は新しいバイナリログの先頭からリカバリを行えば良い。
　
master-dataオプション
バックアップ取得時点のバイナリログの位置がダンプファイルに出力される。
(※v8.0.21以降「source-data」オプションという表記に変わったらしい。)
　・master-data=1とすると、「CHANGE MASTER TO …」がそのまま出力される。
　・master-data=2とすると、「CHANGE MASTER TO …」がコメントアウトされて出力される。

下準備②：誤操作発生

今回は下記のような状況を想定してデータリカバリを行う。

①チェックポイント時点では、「ユーザー1」「ユーザー2」のデータが入っていた。
↓
②チェックポイントの後、「ユーザー3」「ユーザー4」のデータが挿入された。
↓
③誤操作によりテーブルが削除されてしまった！
↓
④「ユーザー3」「ユーザー4」のデータが挿入された時点にデータを復旧する。

①のチェックポイント時点に見立てたフルバックアップを先ほど作成したので、②と③の操作を行う。

mysql> INSERT INTO users VALUE (3,'ユーザー3');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO users VALUE (4,'ユーザー4');
Query OK, 1 row affected (0.01 sec)

mysql> select * from users;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | ユーザー1 |
|  2 | ユーザー2 |
|  3 | ユーザー3 |
|  4 | ユーザー4 |
+----+-----------+
4 rows in set (0.00 sec)

mysql> drop table users;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from users;
ERROR 1146 (42S02): Table 'sample.users' doesn't exist

手順①：データリカバリ作業前にフルバックアップを作成する

データリカバリ作業前に、mysqldumpでフルバックアップを行う。

MySQL Server 8.0>mysqldump -u root -p sample --flush-logs --master-data=2 > ./tmp/sample_backup_before_recovery.sql
WARNING: --master-data is deprecated and will be removed in a future version. Use --source-data instead.
Enter password: ********

※チェックポイントの体で取得したフルバックアップと同じファイル名にすると、ファイルが上書きされてしまうので注意。

手順②：必要なバイナリログの位置を確認する

チェックポイント時点のバックアップファイルで、バイナリログの位置を確認する。
※master-dataオプションを付けてdumpを行ったので、「CHANGE MASTER TO …」という表記でバイナリログの位置が出力されている。

\MySQL Server 8.0> cat ./tmp/sample_backup.sql
-- MySQL dump 10.13  Distrib 8.0.31, for Win64 (x86_64)
--
-- Host: localhost    Database: sample
-- ------------------------------------------------------
-- Server version       8.0.31

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Position to start replication or point-in-time recovery from
--

-- CHANGE MASTER TO MASTER_LOG_FILE='NOSCL235-bin.000051', MASTER_LOG_POS=157;  //バイナリログの位置

チェックポイント以降のバイナリログのファイル名は「NOSCL235-bin.000048」、バイナリログの位置は「157」ということを確認したので、下記のコマンドを実行してバイナリログの中身を確認する。

# at 157 からファイルを下っていくと、チェックポイント以降にINSERTしたログと、下記の通りDROPのログがある。

# at 838
#221230 14:20:53 server id 1  end_log_pos 972 CRC32 0x1ba355b8  Query   thread_id=10    exec_time=0     error_code=0    Xid = 285
use `sample`/*!*/;
SET TIMESTAMP=1672377653/*!*/;
DROP TABLE `users` /* generated by server */

バイナリログのファイルで、DROP TABLEのログポジションは「838」、またそのひとつ前のログポジションは「761」と確認した。

下記コマンドを実行して、バイナリログファイルから、DROP TABLEのひとつ前（ログポジション「761」）までのクエリを抽出したsqlファイルを作成する。

MySQL Server 8.0>mysqlbinlog --database="sample" --start-position=157 --stop-position=761 .\Data\NOSCL235-bin.000051 > .\tmp\sample_recovery.sql

手順③：ロールバック実行（フルバックアップのリストア）

チェックポイント時点のフルバックアップをリストアする。

MySQL Server 8.0>mysql -u root -p sample < .\tmp\sample_backup.sql
Enter password: ********

フルバックをリストアした後でDBを確認すると、削除したテーブルが復活し、チェックポイント時点で存在していたレコードが入っている。

mysql> select * from users;
ERROR 1146 (42S02): Table 'sample.users' doesn't exist
mysql> select * from users;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | ユーザー1 |
|  2 | ユーザー2 |
+----+-----------+
2 rows in set (0.00 sec)

手順④：ロールフォワード実行（チェックポイント以降のデータを復旧する）

チェックポイント時点からDROPのひとつ前までのクエリを抽出したsqlファイルを実行する。

MySQL Server 8.0>mysql -u root -p sample < .\tmp\sample_recovery.sql
Enter password: ********

チェックポイント以降にINSERTしたレコードが復元されていることが確認できる。

mysql> select * from users;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | ユーザー1 |
|  2 | ユーザー2 |
|  3 | ユーザー3 |
|  4 | ユーザー4 |
+----+-----------+
4 rows in set (0.00 sec)

さいごに

今回は簡単な例で、チェックポイント以降のログが全て入ったログファイルを使ってデータリカバリの手順を行ったが、もし増分バックアップの形式でバックアップを取っており、複数の増分バックアップを復元する必要があるとなると、手順が増え作業の時間も掛かることになる。
またmysqldumpを取ったりリストアを行う際のサービスへの影響も考慮しないといけないので、本番でデータリカバリを行うのは本当に難しい作業だろうと思う。。。

今だとAWS Backupというフルマネージドのサービスもあるらしいので、便利なサービスを上手く活用していくことも大事だと思った。

ただ便利なサービスを使いこなす為には土台として従来的な手法も学んでおきたいし、一方で最新のクラウドサービスにアンテナを張って情報収集することも大事だし、勉強したいことは尽きないなぁ、、、

【MySQL】ロールバックとロールフォワードによるデータリカバリ