誰も元に戻せなかった変更
金曜の午後、エンジニアが、ある仕入れ先の値段を直します。UPDATE は正しいのですが、WHERE に打ち間違いがあり、表の全部の行に当たってしまいました。
データベースには、元に戻すボタンがありません。戻る方法は、変更の前に取っておいた写しだけです。
仕事では、この写しを取ることが、毎回の最初の一歩です。
このレッスンのゴール
メモ
変更の前に表のバックアップを取り、変更が間違っていたら元に戻せる。
重要な言葉
| ことば | いみ |
|---|---|
| バックアップ | データを取り戻せるように取っておく写し |
| ダンプ | 表と行を作り直す SQL を書いた、文字のファイル |
| リストア | バックアップからデータを元に戻すこと |
| 影響を受けた行 | 変更が触れた行の数 |
| 予行演習 | トランザクションの中で変更を試し、取り消すこと |
学生が入力するセルは、このプレビューでは空白です。
ショップの写しで練習する
このレッスンでは、データを変え、それを直します。ショップは元のままにします。
次の Run で、自分のデータベースに price_list という表を作り、ショップの商品を写します。
やり直したいときは、何度でも最初から Run できます。
証明書を受け入れる設定
printf '[client]\nssl-verify-server-cert=0\n' > ~/.my.cnfここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
クライアント用の短い名前
alias db='mariadb -h db -u student -pstudent -t -e'ここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
自分のデータベースを作る
db "CREATE DATABASE IF NOT EXISTS student_depot"ここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
古い写しを捨てる
db "DROP TABLE IF EXISTS student_depot.price_list"ここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
商品を写す
db "CREATE TABLE student_depot.price_list AS SELECT id, name, price, stock FROM shop.products"ここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
今の状態を控える
db "SELECT COUNT(*) AS rows_now, MIN(price) AS cheapest, MAX(price) AS dearest FROM student_depot.price_list"ここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
ダンプは、読めるバックアップ
mariadb-dump は、サーバーから表を受け取り、それを作り直す SQL を書き出します。ファイルには、CREATE TABLE の行と、行ごとの INSERT が入ります。
--skip-extended-insert は、表の1行を、ファイルの1行にします。--no-tablespaces は、あなたのアカウントがサーバーの保存ファイルを読めないために要ります。
> は、出力を画面ではなくファイルへ送ります。
表をファイルにバックアップする
mariadb-dump -h db -u student -pstudent --skip-extended-insert --no-tablespaces student_depot price_list > price_list.sqlここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
ファイルの INSERT の行を数える
grep -c "^INSERT" price_list.sqlここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
最初に保存された行を読む
grep -m1 "^INSERT" price_list.sqlここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
間違えて、直す
エンジニアと同じことをします。写しに、WHERE のない UPDATE を実行します。
次に値段を見ます。それから、ファイルをリストアします。ダンプは、どの表も DROP TABLE IF EXISTS から始まるので、読み込むと、壊れた表が置き換わります。
全部の値段を0にする
db "UPDATE student_depot.price_list SET price = 0"ここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
被害を見る
db "SELECT COUNT(*) AS rows_now, MIN(price) AS cheapest, MAX(price) AS dearest FROM student_depot.price_list"ここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
ファイルからリストアする
mariadb -h db -u student -pstudent student_depot < price_list.sqlここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
直ったことを見る
db "SELECT COUNT(*) AS rows_now, MIN(price) AS cheapest, MAX(price) AS dearest FROM student_depot.price_list"ここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
よくある間違い
注意
ファイルができれば、バックアップは安全だと思う人が多くいます。一度もリストアしていないファイルは、願いにすぎません。空だったり、途中で切れていたりします。中の行を数え、写しでリストアの練習をしましょう。
変更が触れる行数を確かめる
バックアップは、失敗したときのためです。もっと良いのは、確定する前に被害を見ることです。
トランザクションを始め、UPDATE を実行し、ROW_COUNT() で何行変わったかを聞きます。数が違えば ROLLBACK です。合っていれば、もう一度実行して COMMIT します。
これが予行演習です。
変更を試して、取り消す
db "START TRANSACTION; UPDATE student_depot.price_list SET stock = stock + 5 WHERE stock < 10; SELECT ROW_COUNT() AS changed; ROLLBACK"ここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
WHERE が選ぶ行を数える
db "SELECT COUNT(*) AS low_stock FROM student_depot.price_list WHERE stock < 10"ここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
例: 安全な変更の5つの手順
| 手順 | すること | 何を防ぐか |
|---|---|---|
| 1 | 表をファイルにダンプする | 元に戻せない変更 |
| 2 | 同じ WHERE で SELECT COUNT(*) をする | 広すぎる WHERE |
| 3 | START TRANSACTION して UPDATE を実行する | もう確定した変更 |
| 4 | ROW_COUNT() を手順2と比べる | 思いがけない数 |
| 5 | COMMIT するか、ROLLBACK してもう一度見る | 間違った変更を終えてしまうこと |
学生が入力するセルは、このプレビューでは空白です。
さらに進むなら
表の名前を付けずに、student_depot の全体をダンプしてみましょう。CREATE TABLE の行を数えます。
実際の仕事では、ファイルを別の場所にも置き、別のサーバーでリストアを試します。データと同じ場所のバックアップは、データと一緒に失われます。