経緯 とある動画をラジオ感覚で流していた

 

• スタートアップに在籍
• 有料会員数は100程度のサービスを運用
• メンテナンス作業のミスによるインシデントが発生

事故の内容

1件の特定の有料会員を無料会員に修正するメンテナンスを行う時に

// WHERE句の付け忘れ

UPDATE users SET is_paid = false;

// ;の位置が早くてWHEREが入らないケース

UPDATE users SET is_paid = false; WHERE id = 100

// 改行されている && 1行目のみ選択されている && 「;」がなくても保管してくれるSQLエディタを利用したケース

UPDATE users SET is_paid = false
WHERE id = 100

 

条件

• バイナリログは存在しないものとする😹
• 作業前に関連テーブルのdumpは取得している

 

アラートをあげる

即座に社内共有して相談し、対応方針の合意を取る。
1人エンジニアなので、「どうでも良い！今すぐ復旧しろ！早く！」となるとは思う。

復旧する

1.メンテナンスを可能であれば入れる

メンテナンスが入った時点から書き込みは行われなくなる

2.全件更新のインシデント発生から、現在までに有料会員になっている会員を調べる

 

SELECT * FROM users WHERE is_paid = true;

存在した場合は別途テーブルを作成する

CREATE TABLE is_paid_after_incident_users LIKE users;

INSERT INTO is_paid_after_incident_users
SELECT * FROM users WHERE is_paid = true;

3.現時点の異常状態のDBをスナップショットを取得する

4. スナップショットから移行先DBを新規作成で複製

以降は移行先DBで作業を行います。

5.一時テーブルの作成

CREATE TABLE temp_users LIKE users;

6.users_202306031200.dumpファイルの書き換え

sed -e '/CREATE TABLE `users`/ s/`users`/`temp_users`/g' \
    -e '/INSERT INTO `users`/ s/`users`/`temp_users`/g' users_202306031200.dump > temp_backup.sql

usersをsedコマンドでtemp_usersに書き換えます。

7.エディタでtemp_backup.sql開いて更新がうまくいっているか確認

8.一時テーブルにインポート

mysql -u {username} -p{password} {database_name} < temp_backup.sql

9.一時テーブルからusersに上書き更新でリストア

UPDATE users
INNER JOIN temp_users ON users.user_id = temp_users.user_id
SET users.is_paid = temp_users.is_paid;

// 全件更新のインシデント発生から、現在までに有料会員になっている会員がいた場合の対応

UPDATE users
INNER JOIN is_paid_after_incident_users ON users.user_id = is_paid_after_incident_users.user_id
SET users.is_paid = is_paid_after_incident_users.is_paid;

10. DBの接続先を移行先DBにアプリケーションを書き換える

接続変更

 .env

- DB_HOST=db.example.local
+ DB_HOST=db.v2.example.local

 

感想

1.命綱の作業前スナップショット

この操作は作業前にテーブルのdumpを、誤操作する前に取っていたからできたこと。

UPDATE users SET is_paid = false; WHERE id = 100

手動でSQLでUPDATEやDELETEを行う場合には、関連のテーブルのdumpを取得しておくのは大事😺

もし1行の書き換えだからと、作業前のtableのdumpがなかったらこの復旧はできなかった🥶

2.TRANSACTIONを使っていれば確認できる

START TRANSACTION;

SELECT COUNT(*) FROM users
WHERE is_paid = true
AND id = 100;
-- 1

UPDATE users SET is_paid = false WHERE id = 100;
-- 反映された件数が1であること

-- 確認
SELECT COUNT(*) FROM users
WHERE is_paid = true
AND id = 100;
-- 期待する結果
-- 0
SELECT * FROM users WHERE id = 100;
-- 反映を確認


-- 異常時 | トランザクションをロールバック
ROLLBACK;

-- 正常時 | トランザクションをコミット
COMMIT;

もし期待する結果と異なる場合はROLLBACK; 期待する結果通りであればCOMMIT;で安全に実行できる。

3.能力高い

• 冷や汗だらだらで、普段行わない操作を1人で冷静に短時間としれっと対処できた動画のエンジニアさんの能力が高い。
• 簡単なSQLだからと甘くみずに、作業前にスナップショットを取得していていえらい！
  「たまたまdumpをとっていたので…」たまたま作業直前に取っていたなんてことはない。えらい！

4.ここはダメ！Badなニュースこそすぐに共有すること

しれっと対応してはダメ。クリティカルな状況になったら周りに共有すること。

• 動画のエンジニアさんは、１時間で復旧できたので何事もなく午後を過ごしたと笑い話にしていたが(面白くするために冗談かも？)、悪いニュースほど関係者にすぐに共有しなくてはいけない。
• 間違うこともいけないが、黙ってしれっと復旧させてはいけない。
• 責任者が責任を持てなくなるし、作業者に任せられなくなるから。

5.決済系はログが欲しい

• usersテーブルは状態でしかないので、決済の履歴テーブルが欲しい

6.レビューが欲しい。修羅場の1人判断は危険

冷静さを失っている場合がある。ビジネスサイドの怒号が飛んでいたり、電話が鳴り止まない状況もあり得る

音のない環境での作業が望ましい

 

怠惰を求めて勤勉に行き着く

事前に「どのような事態が起きうるか」「起きたときにどう対応するか」を様々な角度から想定し、あらゆる「準備」をしておく
@see https://kinacoinu.com/blog-laziness-to-diligently/