DBのテーブルを消した想定で、ロールフォワードする

はじめに

間違えてデータを消してしまった場合のテーブルのリストア → リカバリを行い、ロールフォワードをしてみたいと思います

環境

[環境]
docker
mysql8

大まかな手順

1. dbとテーブル作成して、1件データをinsertする
2. バックアップをとる
3. 再度、1件データを投入
4. テーブルを削除する
5. リストアと、リカバリを行ってデータの復元をする

テーブル復旧まで実際に行う

1. dbとテーブル作成して、1件データをinsertする

docker-compose exec db bash
mysql -u [DB_USER] -p

mysql> use practice;
mysql> CREATE TABLE ice_cream (id int, name varchar(36));
mysql> INSERT INTO ice_cream (id, name) VALUES ("1", "haagen dazs");
mysql> SELECT * FROM ice_cream;
+------+-------------+
| id   | name        |
+------+-------------+
|    1 | haagen dazs |
+------+-------------+

2. バックアップをとる

バイナリログはデフォルトでは作成されないです。
バイナリログを出力するために、/etc/my.cnf以下の設定をします

/etc/my.cnf

[mysqld]
# binary logging is required for replication
log-bin=mysql-bin
# binary logging format - mixed recommended
binlog_format=mixed

そして、バックアップを取ります

mysqldump -u root -p practice > backup.dump

ログをフラッシュする

mysql -u root -p practice -e "flush logs;"

フラッシュする理由としては、リストア（復旧）がスムーズに行えます。

• フラッシュする事で、バイナリログがローテートされて新しいログファイルが作成されます
• 新しいバイナリログからロールフォワードを行うことで、バックアップ直後 ~ 障害直前までの分も戻すことが可能です
• フラッシュを行わないと、復元箇所を探す必要があるので作業手順が増えます

ls -la var/lib/mysql/binlog.*
-rw-r----- 1 mysql mysql 942 Feb 18 10:47 var/lib/mysql/binlog.000001
-rw-r----- 1 mysql mysql 157 Feb 18 10:47 var/lib/mysql/binlog.000002
-rw-r----- 1 mysql mysql  32 Feb 18 10:47 var/lib/mysql/binlog.index

binlog.000002が増えました

本来は、バイナリログは同一サーバ内に置いておくべきではないようです。
AWS S3など外部のファイルサーバーに置きます

3. 再度、1件データを投入

バックアップを取った後にDBを更新されたことを再現するため 1件データを追加します

mysql> INSERT INTO ice_cream (id, name) VALUES ("2", "sou");

mysql> SELECT * FROM ice_cream;
+------+-------------+
| id   | name        |
+------+-------------+
|    1 | haagen dazs |
|    2 | sou         |
+------+-------------+

ls -la var/lib/mysql/binlog.*
-rw-r----- 1 mysql mysql 942 Feb 18 10:47 var/lib/mysql/binlog.000001
-rw-r----- 1 mysql mysql 453 Feb 18 10:51 var/lib/mysql/binlog.000002
-rw-r----- 1 mysql mysql  32 Feb 18 10:47 var/lib/mysql/binlog.index

Feb 18 10:51：バイナリログの日時が更新されました

バイナリログをbinlog/へコピーして退避します

mkdir /var/lib/mysql/binlog/
cp -f /var/lib/mysql/binlog.* /var/lib/mysql/binlog/

4. テーブルを削除する

誤ってテーブルを削除してしまった場合を再現します

mysql> drop table ice_cream;
mysql> show tables;
Empty set (0.02 sec)

5. リストアと、リカバリを行ってデータの復元をする

まずは、バックアップデータからリストアを行います

mysql -u root -p practice < backup.dump

mysql -u root -p practice -e "show tables; select * from ice_cream;"
+-------------------+
| Tables_in_failure |
+-------------------+
| ice_cream         |
+-------------------+
+------+-------------+
| id   | name        |
+------+-------------+
|    1 | haagen dazs |
+------+-------------+

バックアップ時点のデータまで復旧することが出来ました
続いて、ロールフォワードリカバリを行います

テーブルが消える前には、| 2 | sou | もデータとして存在していたので戻します

バイナリ形式 → テキスト形式 へと変換し、/tmp/recover.sqlへと出力します。
その後、practiceへのリカバリを行います

mysqlbinlog --no-defaults -D -u root -p var/lib/mysql/binlog/binlog.000002 > /tmp/recover.sql
mysql -u root -p practice < /tmp/recover.sql
mysql -u root -p practice -e "select * from ice_cream;"
+------+-------------+
| id   | name        |
+------+-------------+
|    1 | haagen dazs |
|    2 | sou         |
+------+-------------+

これで、復旧完了しました

（本来は、障害対策としてマスター/スレーブ構成にしてレプリケーションを行うようです）

参考