とあるテーブルの中身を一括更新した話から学ぶPITR

この記事は本番環境でやらかしちゃった人のアドベントカレンダー9日目の記事です。
https://qiita.com/advent-calendar/2020/yarakashi-production

去年に引き続き、今年も参加させてもらいました。
※去年の記事はこちら→ [データ移行をしただけなのに…（起こってしまったメール誤配信）](https://qiita.com/madai0517/items/8f4e4834c508052d2ddf)

今年のネタも15年くらい前の事で、且つ自分が直接関わった事案ではないのですが、「そういやあの事件、今MySQLだったらどうするかな」と思い書くことにしました。

## 何があったか

もうタイトルで出落ちしていますが**本番でUPDATE文を実行する際にWHERE句を付け忘れた**という事故です。
当時の状況を整理するとこんな感じだったと思います。

```markdown
対象サービス：
　年商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を用意する
```bash
$ 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
```
```sql
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、テーブル、データを作成する
```sql

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. データを操作します

#### 本来やりたかったこと

```sql
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 |
+----+--------+---------------------+---------------------+
```

#### やっちまったぜ…

```sql
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）場合はその直前までの時間指定が良いでしょう。

#### まずバイナリログの状態を確認します

```sql

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文の箇所を探しに行きます。

```bash
$ mysqlbinlog -v /var/lib/mysql/binlog.000002 > binlog.txt
$ grep -i update ./binlog.txt
```

抽出したbinlog.txtの中身を検索するとこんな感じで見つかりました。

```sql
#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` のようなエラーが出てしまうためです。

```sql
mysql> DROP DATABASE pitr;
```

#### binlogを指定の時間まで適用します
```bash
$ mysqlbinlog --stop-datetime="2020-12-09 14:06:30" /var/lib/mysql/binlog.000002 | mysql -u root -pmy-secret-pw
```

#### 適用結果の確認
```sql
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

## 参考情報

[バイナリログを使用したポイントインタイム (増分) リカバリ](https://dev.mysql.com/doc/refman/5.6/ja/point-in-time-recovery.html)