Backup and restore

Why backups matter

Hardware fails, people run DELETE without a WHERE, software has bugs, and ransomware exists. Whatever the cause, the answer is the same: a recent copy of the data kept somewhere else. Replication, RAID or a cloud provider's reliability do not replace backups, because they copy mistakes as faithfully as they copy data.

Think about two numbers when planning:

• RPO (recovery point objective): how much data can you afford to lose? Backups every night means up to a day.
• RTO (recovery time objective): how long can you afford to be down while restoring?

Logical and physical backups

This lesson uses mysqldump, which ships with MySQL and is the best place to start. The commands below are typed in your system shell (not inside the mysql prompt) and are shown for you to run.

A consistent dump of one database

mysqldump -u root -p --single-transaction --routines --triggers --events --databases shop > shop-backup.sql

What the options do:

• --single-transaction runs the whole dump inside one transaction with a consistent snapshot, so InnoDB tables are not locked and the dump is internally consistent even while the shop is taking orders.
• --routines, --triggers and --events include stored procedures and functions, triggers (included by default) and scheduled events. Without --routines your stored code would be missing.
• --databases shop adds CREATE DATABASE and USE lines, so the file recreates the database by itself.

Open the file in a text editor. It is plain SQL: DROP TABLE IF EXISTS, CREATE TABLE, then big INSERT statements. That is why it is portable and readable.

Other useful variations:

mysqldump -u root -p --no-data shop > shop-structure.sql
mysqldump -u root -p --single-transaction shop customers --where="country='India'" > india-customers.sql
mysqldump -u root -p --single-transaction --all-databases > everything.sql
mysqldump -u root -p --single-transaction shop | gzip > shop-$(date +%F).sql.gz

Respectively: structure only, one table filtered by a condition, every database on the server, and a compressed dump named with today's date.

Restoring

Because the dump is SQL, restoring means running it through the mysql client:

mysql -u root -p < shop-backup.sql

For a compressed file: gunzip < shop-2024-08-01.sql.gz | mysql -u root -p shop. If the dump has no CREATE DATABASE (you dumped without --databases), create the database first and name it on the command line.

Always test a restore into a different database first. Create an empty database, pipe the dump into it and compare:

mysql -u root -p -e "CREATE DATABASE shop_check"
mysqldump -u root -p --single-transaction shop | mysql -u root -p shop_check

Then check row counts, as the verification step:

SELECT 'orders' AS table_name, COUNT(*) AS rows_in_table FROM shop.orders
UNION ALL
SELECT 'customers', COUNT(*) FROM shop.customers
UNION ALL
SELECT 'order_items', COUNT(*) FROM shop.order_items;

+-------------+---------------+
| table_name  | rows_in_table |
+-------------+---------------+
| orders      |            20 |
| customers   |            12 |
| order_items |            34 |
+-------------+---------------+

Run the same query against shop_check and compare the numbers. Careful: restoring a dump that contains DROP TABLE IF EXISTS over a live database overwrites the current tables. Check the database name before pressing Enter.

Quick safety copies inside SQL

Before a risky change, you can copy one table in seconds:

USE shop;
DROP TABLE IF EXISTS products_backup;
CREATE TABLE products_backup LIKE products;
INSERT INTO products_backup SELECT * FROM products;
SELECT COUNT(*) AS copied_rows FROM products_backup;

+-------------+
| copied_rows |
+-------------+
|          15 |
+-------------+

If the change goes wrong, you can compare or restore from the copy. This is not a replacement for real backups (it lives on the same server), but it is a good habit before a bulk UPDATE or ALTER.

DROP TABLE products_backup;

Point-in-time recovery

A nightly dump leaves a gap. Suppose someone deletes data at 14:03 and your last backup is from 02:00. The binary log (binlog) records every change since the server started logging, so you can restore the backup and then replay the log up to 14:02. Binary logging is on by default in MySQL 8:

SELECT @@log_bin AS binary_logging_enabled;

+------------------------+
| binary_logging_enabled |
+------------------------+
|                      1 |
+------------------------+

The idea: restore shop-backup.sql, then replay the binary log with the mysqlbinlog tool, stopping just before the mistake:

mysqlbinlog --stop-datetime="2024-08-01 14:02:00" binlog.000042 | mysql -u root -p

Keep the binary logs on a different disk from the data and set a retention period (binlog_expire_logs_seconds) that fits how long you keep backups.

A sensible routine

1. Automate a full backup (a scheduled job: cron on Linux, Task Scheduler on Windows, or your hosting provider's backup feature).
2. Name files with dates and compress them.
3. Copy them off the server: another machine, region or storage service. Encrypt them if they hold personal data.
4. Keep several generations (daily for a week, weekly for a month, monthly for a year).
5. Monitor the job. A backup that silently stopped running three months ago is worse than none.
6. Test a restore on a schedule, and write down the steps and how long it took.

Never put real credentials into the backup command. Use an option file (~/.my.cnf with restricted permissions) or mysql_config_editor so the password is not typed on the command line.

Common mistakes

• Keeping the only backup on the same server.
• Never testing a restore.
• Forgetting --routines and losing stored procedures.
• Backing up with locks (--lock-all-tables) during business hours on a busy site.
• Restoring over a live database by accident.
• Treating replication as a backup.

Exercise

Write the commands (as you would type them in a shell) to: (1) back up only the orders and order_items tables of shop consistently, (2) create a compressed, dated dump of the whole shop database, and (3) restore that dump into a new database called shop_restored. Then say which SQL check you would run to compare the result.

mysqldump -u root -p --single-transaction shop orders order_items > orders-backup.sql
mysqldump -u root -p --single-transaction --routines --triggers shop | gzip > shop-$(date +%F).sql.gz
mysql -u root -p -e "CREATE DATABASE shop_restored"
gunzip < shop-2024-08-01.sql.gz | mysql -u root -p shop_restored