The change nobody could undo
A database backup is a copy of your data made before you change it, so a wrong change can be undone. A database has no undo button, and the backup is the only way back.
On a Friday afternoon an engineer fixes the prices of one supplier. The UPDATE is right. The WHERE has a typo and matches the whole table.
In a job, making that copy is the first step, every time.
By the end of this lesson
Note
I can back up a table before a change and restore it if the change is wrong.
Key words
| Word | What it means |
|---|---|
| Backup | a copy of data made so you can get it back |
| Dump | a text file of the SQL that rebuilds a table and its rows |
| Restore | put the data back from a backup |
| Rows affected | how many rows a change touched |
| Dry run | trying a change in a transaction and rolling it back |
The cells students fill in are left blank in this preview.
Work on a copy of the shop
You will change data and then repair it. The shop must stay as it is.
These Runs make a table called price_list in your own database, copied from the shop's products.
Run them again at any time to start over.
Accept the server's certificate
printf '[client]\nssl-verify-server-cert=0\n' > ~/.my.cnfShown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Make a short name for the client
alias db='mariadb -h db -u student -pstudent -t -e'Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Make your own database
db "CREATE DATABASE IF NOT EXISTS student_depot"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Throw away an old copy
db "DROP TABLE IF EXISTS student_depot.price_list"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Copy the products
db "CREATE TABLE student_depot.price_list AS SELECT id, name, price, stock FROM shop.products"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Note how it looks now
db "SELECT COUNT(*) AS rows_now, MIN(price) AS cheapest, MAX(price) AS dearest FROM student_depot.price_list"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
A dump is a backup you can read
The tool mariadb-dump asks the server for a table and writes the SQL that rebuilds it. The file holds a CREATE TABLE line and one INSERT for each row.
The flag --skip-extended-insert puts one row on each line. --no-tablespaces is needed because your account may not read the server's storage files.
The > sends the output into a file instead of the screen.
Back up the table to a file
mariadb-dump -h db -u student -pstudent --skip-extended-insert --no-tablespaces student_depot price_list > price_list.sqlShown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Count the INSERT lines in the file
grep -c "^INSERT" price_list.sqlShown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Read the first row it saved
grep -m1 "^INSERT" price_list.sqlShown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Make the mistake, then repair it
Now do what the engineer did. Run an UPDATE with no WHERE on the copy.
Then look at the prices. Then restore the file. The dump begins each table with DROP TABLE IF EXISTS, so loading it replaces the damaged table.
Set every price to zero
db "UPDATE student_depot.price_list SET price = 0"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
See the damage
db "SELECT COUNT(*) AS rows_now, MIN(price) AS cheapest, MAX(price) AS dearest FROM student_depot.price_list"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Restore from the file
mariadb -h db -u student -pstudent student_depot < price_list.sqlShown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
See it repaired
db "SELECT COUNT(*) AS rows_now, MIN(price) AS cheapest, MAX(price) AS dearest FROM student_depot.price_list"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
A common mistake
Warning
Many people think a backup is safe once the file exists. A file you have never restored is only a hope. It may be empty or cut short. Count the rows in it, and practise a restore on a copy.
Check how many rows a change touches
A backup is for when things go wrong. Better still is to see the damage before it is permanent.
Start a transaction. Run the UPDATE. Ask ROW_COUNT() how many rows it changed. If the number is wrong, ROLLBACK. If it is right, run it again and COMMIT.
This is a dry run.
Try a change and roll it back
db "START TRANSACTION; UPDATE student_depot.price_list SET stock = stock + 5 WHERE stock < 10; SELECT ROW_COUNT() AS changed; ROLLBACK"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Count the rows the WHERE matches
db "SELECT COUNT(*) AS low_stock FROM student_depot.price_list WHERE stock < 10"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Worked example: a safe change in five steps
| Step | What you do | What it protects against |
|---|---|---|
| 1 | Dump the table to a file | a change that cannot be undone |
| 2 | SELECT COUNT(*) with the same WHERE | a WHERE that matches too much |
| 3 | START TRANSACTION and run the UPDATE | a change that is already permanent |
| 4 | Compare ROW_COUNT() with step 2 | a number you did not expect |
| 5 | COMMIT, or ROLLBACK and look again | finishing a wrong change |
The cells students fill in are left blank in this preview.
Going further
Dump the whole student_depot database by leaving out the table name. Count its CREATE TABLE lines.
In a real job you would copy the file somewhere else too, and test a restore on a different server. A backup in the same place as the data fails together with it.