Go to Qiita Advent Calendar 2024 Top
LoginSignup
84
45

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

本番環境でやらかしちゃった人Advent Calendar 2020

Day 9
@madai0517

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

Last updated at Posted at 2020-12-09

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

参考情報

バイナリログを使用したポイントインタイム (増分) リカバリ

84
45
3

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
84
45

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?