【爆笑必須】エンジニアのやらかしをまとめました2020年アドベントカレンダー編

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

この記事は本番環境でやらかしちゃった人のアドベントカレンダー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

引用元：https://qiita.com/madai0517/items/8fa283d8dc8bb49d81f7

単語メモ

• UPDATE（文）：RTBの行を更新するためのSQL文です。参照制約が設定されている被参照列の値を更新する場合、参照列も連鎖的に同じ値に更新してデータの不整合を生じないようにできます。
• DBMS（data base management system）：データベース管理システムです。データベースに格納しているデータを適切に扱うために必要です。データ管理の手間が大幅に削減できます。
• PostgreSQL（ポストグレスキューエル）：リレーショナルデータベース管理システムです。用途を問わず無料で利用できます。
• begin transaction：接続で参照されるデータが論理的にも物理的にも一貫している位置を表します。エラーが発生すると、begin transaction以降に加えられたすべてのデータ修正をロールバックし、一貫性が確認されている状態にデータを戻します。
• dump（ダンプ）：コンピュータのメインメモリやレジスタなどのある瞬間の内容をストレージ上のファイルに丸ごと写し取ったものです。
• docker：完全仮想化のサービスを提供するのではなく、コンテナ型の仮想化サービスを提供しています。OSを複数サーバーが共通して利用しているので、軽量で高速に起動・停止ができます。
• updated_at：アップデータの時間を示すコマンド
• バイナリログ：テーブルやデータの更新操作が格納されています。SQL文の実行が完了し、複数の処理を一つの処理にまとめて行う単位（トランザクション）をした時にバイナリログに書き込まれます。
• binlog：サーバー層が出力するログで、DBテーブルの更新時に出力されます。レコードの更新内容を保存するので、誤って削除したデータを復元する時に利用します。