Backup and restore drills you would trust

Backup and restore drills you would trust. Backups you have never restored are wishful thinking. Run a restore at least once before you need one for real.

What to back up

• Logical backup – SQL dump (portable, human-readable, slower restore on huge DBs)
• Physical backup – copy of data files ( faster restore at scale; tool-dependent – Percona XtraBackup, etc.)
• Binlog – point-in-time recovery between backups (enable binary logging in production)

For learning and small projects, mysqldump is enough. For production, automate dumps or physical backups and store off-server.

mysqldump basics

-- From shell, not inside mysql client:
-- mysqldump -u root -p --single-transaction --routines --triggers shop > shop_backup.sql

--single-transaction gives a consistent InnoDB snapshot without locking tables (for mixed engines, research flags). Include routines and triggers if you use them.

-- One table only
-- mysqldump -u root -p shop orders > orders_backup.sql

-- Schema without data
-- mysqldump -u root -p --no-data shop > shop_schema.sql

Restore drill

Practice on a throwaway database – never overwrite production while learning:

CREATE DATABASE shop_restore;
-- From shell:
-- mysql -u root -p shop_restore < shop_backup.sql

USE shop_restore;
SELECT COUNT(*) FROM orders;
SELECT COUNT(*) FROM customers;

Compare row counts and spot-check critical tables. Run a few known queries from your app. Note how long restore took – that is your recovery time estimate.

Partial restore

Sometimes you only need one table back. Restore dump to a temp database, then copy data:

INSERT INTO shop.orders
SELECT * FROM shop_restore.orders
WHERE id NOT IN (SELECT id FROM shop.orders);

Messy if IDs overlap – often easier to restore whole DB to staging and extract what you need. Plan before panic.

Automation and storage

• Schedule nightly dumps via cron or your host’s backup feature
• Store off-machine (S3, another server, encrypted)
• Retention policy – daily for a week, weekly for a month, etc.
• Test restore quarterly – calendar it

Before destructive migrations

Take a fresh dump before risky ALTER or mass DELETE. Cheap insurance. Combined with transactions in app code, you layer safety – backups catch operator error and hardware failure, not just app bugs.

What a good drill proves

• Backup file is not empty or corrupted
• You know the commands without googling under stress
• Restore time fits your downtime tolerance
• Credentials and paths are documented for whoever is on call

The next tutorials pull these tutorials together: design a schema for a blog, shop, or booking domain, then write reporting queries against it.