【MySQL】データベースの安全なデータ更新のためにおさえておきたい手順

MySQL

自分のために。横着しないように、油断しないように。
MySQLでなくても、基本的な手順は同じなはず。

ざっくりな手順

  1. 実行予定のSQLを自分の開発環境で実行したあとレビューしてもらう
  2. 更新前データのバックアップを取る
  3. SELECT文を実行して更新前データを抽出・確認する
    • データ件数
    • 内容
  4. BEGINする
  5. 更新SQLを実行する
  6. SELECT文を実行して更新後データを抽出・確認する
    • データ件数
    • 内容
  7. COMMIT（もしくはROLLBACK）する
  8. システムの動作確認

各手順詳細

1. 実行予定のSQLを自分の開発環境で実行したあとレビューしてもらう

簡単なUPDATE文を実行するだけだとしても、事前にSQLは用意して開発環境で試しに実行し、動作を確認する。
ぶっつけ本番など以ての外。

可能ならば準備したSQLを誰かにレビューしてもらうと良い。
ただし、必ずレビューを受ける前にSQLを実行して、自分の中で大丈夫だと思える準備をしてからレビューをしてもらうこと。
レビュアーに甘えてはいけない。

2. 更新前データのバックアップを取る

定期的なバックアップ環境が整っているかどうかは事前に把握しておく。
もし万が一、バックアップが取られてないのであれば、何かしらの手段を使って更新前データのバックアップをとっておく。

# 例: データベース全体のダンプファイルを作成
$ mysqldump -u user_name -p -h host_name db_name > ./dump.sql

バックアップから復元する場合は以下の通り。

$ mysql -u user_name -p -h host_name db_name < ./dump.sql

3. SELECT文を実行して更新前データを抽出・確認する

更新SQLと同じ条件でSELECT文を実行して、更新対象となっているデータを抽出する。

例えば、次のようなUPDATE文を実行しようとしている場合、

UPDATE post
SET author = '太郎'
WHERE author = '次郎';

以下のようにWHERE句はそのままにSELECT文を作る。

# UPDATE post
# SET author = '太郎'
SELECT count(*) FROM post
WHERE author = '次郎';

件数やデータの内容から、今から更新しようとしているデータで合っているかどうかを確認しておきたいし、抽出しておけば、データを更新したあとに更新前のデータを確認することもできる。

SQLファイルから実行して出力結果を書き出したい場合はこう。

$ mysql -u user_name -p -h host_name db_name < ./select.sql > ./select_result.txt

直接SQL文を書いて結果を書き出し場合はこう。

$ mysql -u user_name -p -h host_name db_name -e 'SELECT count(*) FROM post WHERE author = "次郎"' > ./select_result.txt

4. BEGINする

さて、いよいよSQLを実行してデータ更新をするわけだが、その前に必ず BEGIN と叩いて新しいトランザクションを開始する。
こうすることで、SQLの実行に失敗しても COMMIT さえしなければ変更は永続化されず、そのトランザクションにとどまる。
「失敗した！」と思ったら ROLLBACK を叩けばいい。

$ mysql -u user_name -p -h host_name db_name
mysql> BEGIN;

5. 更新SQLを実行する

忘れずに BEGIN できたら、データ更新のSQLを実行する。

source で、SQLスクリプトファイルを実行しよう。

mysql> source ./update.sql

6. SELECT文を実行して更新後データを抽出・確認する

SQLを実行したら、期待通り更新されているか、SELECT文を叩いてデータを確認する。
これも、状況に応じて結果を保存しておくと良い。

tee を使えば、実行時の操作と結果を記録できる。

mysql> tee ./select_updated.txt
mysql> SELECT count(*) FROM post WHERE author = '太郎';
mysql> notee

7. COMMIT（もしくはROLLBACK）する

更新後の確認ができたら COMMIT する。
これで、データ更新は永続化され、MySQLの接続から抜けても反映されたままになる。

mysql> COMMIT;

戻したければ ROLLBACK 。

mysql> ROLLBACK;

8. システムの動作確認

最後にシステムがちゃんと動いているか確認する。
エラーログが吐かれてないか tail -f や less +F などで監視する。

終わりに

万が一のためのリカバリを意識して、事前の準備をしっかりとやっておきたい。