More than 5 years have passed since last update.

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

@madai0517

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

Last updated at2020-12-10Posted 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-eMYSQL_ROOT_PASSWORD=my-secret-pw-d mysql:8.0.22$dockerexec-it mysql-pitr bashroot@a34d3f26bb3d:/# mysql-uroot-pmy-secret-pw

mysql>SHOWVARIABLESLIKE'%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|MySQLCommunityServer-GPL||version_compile_machine|x86_64||version_compile_os|Linux||version_compile_zlib|1.2.11|+--------------------------+-------------------------------+

検証用のDB、テーブル、データを作成する

mysql>CREATEDATABASEpitr;mysql>SHOWDATABASES;+--------------------+|Database|+--------------------+|information_schema||mysql||performance_schema||pitr||sys|+--------------------+mysql>USEpitr;mysql>CREATETABLEIFNOTEXISTS`users`->(->`id`INTUNSIGNEDNOTNULLAUTO_INCREMENT,->`name`VARCHAR(255)NOTNULL,->`created_at`DATETIMENOTNULL,->`updated_at`DATETIMENOTNULL,->PRIMARYKEY(`id`)->)ENGINE=InnoDB;mysql>INSERTINTO`users`(`name`,`created_at`,`updated_at`)VALUES->('user1',NOW(),NOW()),->('user2',NOW(),NOW()),->('user3',NOW(),NOW()),->('user4',NOW(),NOW()),->('user5',NOW(),NOW());mysql>SHOWTABLES;+----------------+|Tables_in_pitr|+----------------+|users|+----------------+mysql>SELECT*FROMusers;+----+-------+---------------------+---------------------+|id|name|created_at|updated_at|+----+-------+---------------------+---------------------+|1|user1|2020-12-0909:24:35|2020-12-0909:24:35||2|user2|2020-12-0909:24:35|2020-12-0909:24:35||3|user3|2020-12-0909:24:35|2020-12-0909:24:35||4|user4|2020-12-0909:24:35|2020-12-0909:24:35||5|user5|2020-12-0909:24:35|2020-12-0909:24:35|+----+-------+---------------------+---------------------+

2. データを操作します

本来やりたかったこと

mysql>UPDATEusersSETname='USER-1',updated_at=NOW()WHEREid=1;mysql>SELECT*FROMusers;+----+--------+---------------------+---------------------+|id|name|created_at|updated_at|+----+--------+---------------------+---------------------+|1|USER-1|2020-12-0909:24:35|2020-12-0914:06:31||2|user2|2020-12-0909:24:35|2020-12-0909:24:35||3|user3|2020-12-0909:24:35|2020-12-0909:24:35||4|user4|2020-12-0909:24:35|2020-12-0909:24:35||5|user5|2020-12-0909:24:35|2020-12-0909:24:35|+----+--------+---------------------+---------------------+

やっちまったぜ…

mysql>UPDATEusersSETname='USER-1',updated_at=NOW();mysql>SELECT*FROMusers;+----+--------+---------------------+---------------------+|id|name|created_at|updated_at|+----+--------+---------------------+---------------------+|1|USER-1|2020-12-0909:52:21|2020-12-0914:06:31||2|USER-1|2020-12-0909:52:21|2020-12-0914:06:31||3|USER-1|2020-12-0909:52:21|2020-12-0914:06:31||4|USER-1|2020-12-0909:52:21|2020-12-0914:06:31||5|USER-1|2020-12-0909:52:21|2020-12-0914:06:31|+----+--------+---------------------+---------------------+

3. PITRを行う

MySQLではPITRの方法として時間の指定とログ位置による指定の2つの指定ができます。

今回のようにやっちまった時間がはっきりしている（updated_at）場合はその直前までの時間指定が良いでしょう。

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

mysql>SHOWVARIABLESLIKE'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>SHOWBINARYLOGS;+---------------+-----------+-----------+|Log_name|File_size|Encrypted|+---------------+-----------+-----------+|binlog.000001|3107351|No||binlog.000002|7722|No|+---------------+-----------+-----------+mysql>SHOWMASTERSTATUS;+---------------+----------+--------------+------------------+-------------------+|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の中身を検索するとこんな感じで見つかりました。

#20120914:06:31serverid1end_log_pos7691CRC320x6550b37bUpdate_rows:tableid94flags: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>DROPDATABASEpitr;

binlogを指定の時間まで適用します

$mysqlbinlog--stop-datetime="2020-12-09 14:06:30" /var/lib/mysql/binlog.000002 | mysql-u root-pmy-secret-pw

適用結果の確認

mysql>SHOWDATABASES;+--------------------+|Database|+--------------------+|information_schema||mysql||performance_schema||pitr||sys|+--------------------+mysql>SELECT*FROMusers;+----+-------+---------------------+---------------------+|id|name|created_at|updated_at|+----+-------+---------------------+---------------------+|1|user1|2020-12-0909:24:35|2020-12-0909:24:35||2|user2|2020-12-0909:24:35|2020-12-0909:24:35||3|user3|2020-12-0909:24:35|2020-12-0909:24:35||4|user4|2020-12-0909:24:35|2020-12-0909:24:35||5|user5|2020-12-0909:24:35|2020-12-0909:24:35|+----+-------+---------------------+---------------------+

以上、無事戻りました。
改めて本来適用したかったUPDATEを実施すればOKです。

まとめ

MySQLのPITRを適用するためにもbinlogは必ず生成しましょう（まぁわざわざoffにすることは無いでしょうが）
binlogを最初から全部適用するのはしんどいので、fulldumpと組み合わせて最適化をはかりましょう
本番環境での作業は指差し確認大事
そもそも本番環境でのワンオペ＆手作業、ダメゼッタイ

因みに件の顧客情報更新作業は応急対応として、テンプレにbegin transactionが追加され必ずペア作業するようになり、その後無事システム化されました。

ご静聴ありがとうございました。m(_ _)m

参考情報

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

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up

Movatterモバイル変換