この記事は本番環境でやらかしちゃった人のアドベントカレンダー9日目の記事です。
https://qiita.com/advent-calendar/2020/yarakashi-production
去年に引き続き、今年も参加させてもらいました。
※去年の記事はこちら→ データ移行をしただけなのに…（起こってしまったメール誤配信）
今年のネタも15年くらい前の事で、且つ自分が直接関わった事案ではないのですが、「そういやあの事件、今MySQLだったらどうするかな」と思い書くことにしました。
何があったか
もうタイトルで出落ちしていますが本番でUPDATE文を実行する際にWHERE句を付け忘れたという事故です。
当時の状況を整理するとこんな感じだったと思います。
対象サービス：
年商10億円くらいの自社サービス
作業内容：
仮登録されている顧客の情報を指定された情報で更新する
作業環境：
DBMSはPostgreSQL
本番DBに接続し、予めテンプレートとして用意されたUPDATEのSQLに必要な情報を埋めて実行
担当者は新卒1年目が依頼を受けて1人で実施する
もうツッコミどころが多すぎて困ると思います。
何も言わないでください…
因みにbegin transactionはしていませんでした。まぁ、それ以前の問題が多いので些細なことですね。
という訳で、不幸にも顧客情報が全て同じデータになるという事故が発生しました。
詳細不明ですが毎時取得していたfulldumpを適用して復旧としていた気がします。（つまり最大1時間分のデータロストがある）
Point In Time Recovery（PITR）とは何か
という訳でPoint In Time Recoveryの出番です。
PITRは過去の特定の時点の状態に戻す、という意味（の総称）です。
今回のネタのように、データファイルの破損などのような障害とは違い、運用ミスや操作ミスに対するリカバリ手段として適切な手法だと思います。
実際にMySQLでやってみる
1. 検証環境を用意する
dockerでMySql8を用意する
$ docker run --name mysql-pitr -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:8.0.22
$ docker exec -it mysql-pitr bash
root@a34d3f26bb3d:/# mysql -uroot -pmy-secret-pw
mysql> SHOW VARIABLES LIKE '%version%';
+--------------------------+-------------------------------+
| Variable_name | Value |
+--------------------------+-------------------------------+
| admin_tls_version | TLSv1,TLSv1.1,TLSv1.2,TLSv1.3 |
| immediate_server_version | 999999 |
| innodb_version | 8.0.22 |
| original_server_version | 999999 |
| protocol_version | 10 |
| slave_type_conversions | |
| tls_version | TLSv1,TLSv1.1,TLSv1.2,TLSv1.3 |
| version | 8.0.22 |
| version_comment | MySQL Community Server - GPL |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
| version_compile_zlib | 1.2.11 |
+--------------------------+-------------------------------+
検証用のDB、テーブル、データを作成する
mysql> CREATE DATABASE pitr;
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| pitr |
| sys |
+--------------------+
mysql> USE pitr;
mysql> CREATE TABLE IF NOT EXISTS `users`
-> (
-> `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
-> `name` VARCHAR(255) NOT NULL,
-> `created_at` DATETIME NOT NULL,
-> `updated_at` DATETIME NOT NULL,
-> PRIMARY KEY (`id`)
-> ) ENGINE = InnoDB;
mysql> INSERT INTO `users` (`name`, `created_at`, `updated_at`) VALUES
-> ('user1', NOW(), NOW()),
-> ('user2', NOW(), NOW()),
-> ('user3', NOW(), NOW()),
-> ('user4', NOW(), NOW()),
-> ('user5', NOW(), NOW());
mysql> SHOW TABLES;
+----------------+
| Tables_in_pitr |
+----------------+
| users |
+----------------+
mysql> SELECT * FROM users;
+----+-------+---------------------+---------------------+
| id | name | created_at | updated_at |
+----+-------+---------------------+---------------------+
| 1 | user1 | 2020-12-09 09:24:35 | 2020-12-09 09:24:35 |
| 2 | user2 | 2020-12-09 09:24:35 | 2020-12-09 09:24:35 |
| 3 | user3 | 2020-12-09 09:24:35 | 2020-12-09 09:24:35 |
| 4 | user4 | 2020-12-09 09:24:35 | 2020-12-09 09:24:35 |
| 5 | user5 | 2020-12-09 09:24:35 | 2020-12-09 09:24:35 |
+----+-------+---------------------+---------------------+
2. データを操作します
本来やりたかったこと
mysql> UPDATE users SET name='USER-1', updated_at=NOW() WHERE id=1;
mysql> SELECT * FROM users;
+----+--------+---------------------+---------------------+
| id | name | created_at | updated_at |
+----+--------+---------------------+---------------------+
| 1 | USER-1 | 2020-12-09 09:24:35 | 2020-12-09 14:06:31 |
| 2 | user2 | 2020-12-09 09:24:35 | 2020-12-09 09:24:35 |
| 3 | user3 | 2020-12-09 09:24:35 | 2020-12-09 09:24:35 |
| 4 | user4 | 2020-12-09 09:24:35 | 2020-12-09 09:24:35 |
| 5 | user5 | 2020-12-09 09:24:35 | 2020-12-09 09:24:35 |
+----+--------+---------------------+---------------------+
やっちまったぜ…
mysql> UPDATE users SET name='USER-1', updated_at=NOW();
mysql> SELECT * FROM users;
+----+--------+---------------------+---------------------+
| id | name | created_at | updated_at |
+----+--------+---------------------+---------------------+
| 1 | USER-1 | 2020-12-09 09:52:21 | 2020-12-09 14:06:31 |
| 2 | USER-1 | 2020-12-09 09:52:21 | 2020-12-09 14:06:31 |
| 3 | USER-1 | 2020-12-09 09:52:21 | 2020-12-09 14:06:31 |
| 4 | USER-1 | 2020-12-09 09:52:21 | 2020-12-09 14:06:31 |
| 5 | USER-1 | 2020-12-09 09:52:21 | 2020-12-09 14:06:31 |
+----+--------+---------------------+---------------------+
3. PITRを行う
MySQLではPITRの方法として時間の指定とログ位置による指定の2つの指定ができます。
今回のようにやっちまった時間がはっきりしている（updated_at）場合はその直前までの時間指定が良いでしょう。
まずバイナリログの状態を確認します
mysql> SHOW VARIABLES LIKE 'log_bin%';
+---------------------------------+-----------------------------+
| Variable_name | Value |
+---------------------------------+-----------------------------+
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/binlog |
| log_bin_index | /var/lib/mysql/binlog.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
+---------------------------------+-----------------------------+
mysql> SHOW BINARY LOGS;
+---------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 | 3107351 | No |
| binlog.000002 | 7722 | No |
+---------------+-----------+-----------+
mysql> SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000002 | 7722 | | | |
+---------------+----------+--------------+------------------+-------------------+
バイナリログは有効で場所は/var/lib/mysql/、ファイルは2つありbinlog.000002を使用中のようです。
binlogの中身を確認します
mysqlbinlogコマンドに-vを付けてやらかしたUPDATE文の箇所を探しに行きます。
$ mysqlbinlog -v /var/lib/mysql/binlog.000002 > binlog.txt
$ grep -i update ./binlog.txt
抽出したbinlog.txtの中身を検索するとこんな感じで見つかりました。
#201209 14:06:31 server id 1 end_log_pos 7691 CRC32 0x6550b37b Update_rows: table id 94 flags: STMT_END_F
### UPDATE `pitr`.`users`
### UPDATE `pitr`.`users`
### UPDATE `pitr`.`users`
### UPDATE `pitr`.`users`
### UPDATE `pitr`.`users`
確かに14:06:31にUPDATEが記録されています。
なのでこの手前まで戻してあげれば良さそうです。
今回は検証用に新規に作った環境なので雑にDBを消して指定の時間までbinlogを当てるやり方を採用します。
実際の運用ではfulldumpしたファイルとそこからの差分をbinlogの位置を指定して適用することになるのではないでしょうか。
DBを削除します
これは今回適用するbinlogにCREATE DATABASE pitr;があり、残っていると
ERROR 1007 (HY000) at line 37: Can't create database 'pitr'; database exists のようなエラーが出てしまうためです。
mysql> DROP DATABASE pitr;
binlogを指定の時間まで適用します
$ mysqlbinlog --stop-datetime="2020-12-09 14:06:30" /var/lib/mysql/binlog.000002 | mysql -u root -pmy-secret-pw
適用結果の確認
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| pitr |
| sys |
+--------------------+
mysql> SELECT * FROM users;
+----+-------+---------------------+---------------------+
| id | name | created_at | updated_at |
+----+-------+---------------------+---------------------+
| 1 | user1 | 2020-12-09 09:24:35 | 2020-12-09 09:24:35 |
| 2 | user2 | 2020-12-09 09:24:35 | 2020-12-09 09:24:35 |
| 3 | user3 | 2020-12-09 09:24:35 | 2020-12-09 09:24:35 |
| 4 | user4 | 2020-12-09 09:24:35 | 2020-12-09 09:24:35 |
| 5 | user5 | 2020-12-09 09:24:35 | 2020-12-09 09:24:35 |
+----+-------+---------------------+---------------------+
以上、無事戻りました。
改めて本来適用したかったUPDATEを実施すればOKです。
まとめ
- MySQLのPITRを適用するためにもbinlogは必ず生成しましょう（まぁわざわざoffにすることは無いでしょうが）
- binlogを最初から全部適用するのはしんどいので、fulldumpと組み合わせて最適化をはかりましょう
- 本番環境での作業は指差し確認大事
- そもそも本番環境でのワンオペ＆手作業、ダメゼッタイ
因みに件の顧客情報更新作業は応急対応として、テンプレにbegin transactionが追加され必ずペア作業するようになり、その後無事システム化されました。
ご静聴ありがとうございました。m(_ _)m