Chapter 19: Backup & Restore
This is the chapter that saves your life when something goes wrong! No matter how good your code or how careful you are, accidents happen:
- Server crashes
- Wrong DELETE without WHERE
- Hacker attack
- Disk failure
That’s why backups are non-negotiable in any real database. Today we’ll learn how to:
- Create full backups using mysqldump (the most common tool)
- Restore from backup
- Basics of point-in-time recovery (recover to any exact moment)
Let’s pretend our my_coaching database is our live coaching institute system — we never want to lose student enrollments, fees, or personal data!
1. mysqldump Utility – The King of MySQL Backups
mysqldump is a command-line tool that comes with MySQL. It creates a .sql file containing:
- CREATE DATABASE / CREATE TABLE statements
- All INSERT statements with your data
- Views, procedures, triggers, etc.
Important options you must know:
| Option | What it does | When to use |
|---|---|---|
| –databases dbname | Include CREATE DATABASE statement | Always (for full restore) |
| –all-databases | Backup every database on the server | For full server backup |
| –routines | Include stored procedures & functions | Almost always |
| –triggers | Include triggers | Almost always |
| –events | Include scheduled events | If you use events |
| –no-data | Only structure (no data) | For schema-only backup |
| –single-transaction | Consistent backup without locking tables | For InnoDB tables (recommended!) |
| –quick | Faster for large tables | Always with big data |
Basic full backup command (recommended):
|
0 1 2 3 4 5 6 7 8 |
mysqldump -u root -p --databases my_coaching \ --routines --triggers --events --single-transaction --quick \ > my_coaching_backup_2026-01-24.sql |
- Enter root password when prompted
- File saved as my_coaching_backup_2026-01-24.sql
- –single-transaction = no table locks, consistent snapshot
Backup only data (no CREATE statements):
|
0 1 2 3 4 5 6 |
mysqldump -u root -p my_coaching --no-create-info --skip-triggers > data_only.sql |
Backup specific tables:
|
0 1 2 3 4 5 6 |
mysqldump -u root -p my_coaching students enrollments > students_enrollments_backup.sql |
Best practice – Automate daily backups (cron job example – Linux/macOS):
|
0 1 2 3 4 5 6 7 |
# Add to crontab (crontab -e) 0 2 * * * /usr/bin/mysqldump -u root -p'YourRootPass' --single-transaction my_coaching > /backups/my_coaching_$(date +\%Y-\%m-\%d).sql |
2. Restoring from Backup – Bringing Data Back to Life
Two main ways to restore:
Method 1 – Restore entire backup file
|
0 1 2 3 4 5 6 |
mysql -u root -p < my_coaching_backup_2026-01-24.sql |
- This runs all CREATE + INSERT statements
- If database already exists → error! (unless you drop it first)
Safe way – Drop & recreate database first:
|
0 1 2 3 4 5 6 |
mysql -u root -p |
Inside mysql:
|
0 1 2 3 4 5 6 7 8 |
DROP DATABASE IF EXISTS my_coaching; CREATE DATABASE my_coaching CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; EXIT; |
Then:
|
0 1 2 3 4 5 6 |
mysql -u root -p my_coaching < my_coaching_backup_2026-01-24.sql |
Method 2 – Restore only specific tables from backup If backup contains multiple tables:
|
0 1 2 3 4 5 6 |
mysql -u root -p my_coaching < students_enrollments_backup.sql |
Important:
- Always test restore on a test server first!
- Check file size & contents before restoring
- Use –force if you want to ignore errors:
|
0 1 2 3 4 5 6 |
mysql -u root -p --force my_coaching < backup.sql |
3. Point-in-Time Recovery (PITR) Basics
(Recover to any exact moment – advanced but very powerful)
Full backup + binary logs = PITR
Binary logs record every change (INSERT/UPDATE/DELETE) after the backup.
Step-by-step PITR:
- Enable binary logging (in my.cnf / my.ini):
|
0 1 2 3 4 5 6 7 8 9 |
[mysqld] log_bin = /var/log/mysql/mysql-bin.log binlog_format = ROW # or MIXED expire_logs_days = 7 # keep 7 days |
Restart MySQL.
- Take full backup with binlog position
|
0 1 2 3 4 5 6 |
mysqldump -u root -p --single-transaction --master-data=2 my_coaching > full_backup.sql |
The file will contain a line like:
|
0 1 2 3 4 5 6 |
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=154; |
- Later – to recover to a specific time:
- Restore full backup first
- Then apply binlogs from that point:
|
0 1 2 3 4 5 6 |
mysqlbinlog --start-position=154 mysql-bin.000003 mysql-bin.000004 | mysql -u root -p |
Or recover to exact time:
|
0 1 2 3 4 5 6 |
mysqlbinlog --stop-datetime="2026-01-24 15:30:00" mysql-bin.000003 | mysql -u root -p |
Best practice: Use tools like Percona XtraBackup or MySQL Enterprise Backup for hot backups + PITR in production.
That’s it for Chapter 19! 🎉 You now know how to protect your data and recover from disasters — one of the most important skills any database professional must have!
Homework for today (do it right now – very important!) Run these commands and paste the output of the final SELECT:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
-- 1. Create a backup of your my_coaching database -- (Run in terminal, not mysql prompt) mysqldump -u root -p --databases my_coaching --single-transaction --routines --triggers > my_coaching_backup.sql -- 2. (Optional) Check file size ls -lh my_coaching_backup.sql -- 3. Simulate disaster: Drop a table DROP TABLE students; -- 4. Restore from backup mysql -u root -p my_coaching < my_coaching_backup.sql -- 5. Verify data is back SELECT COUNT(*) AS student_count FROM students; |
Paste the student_count from the last SELECT — I’ll check if restore worked!
This concludes our full MySQL Tutorial series! 🎉 You’ve gone from zero to professional-level MySQL knowledge — congratulations!
If you want:
- Practice projects
- Advanced topics (partitioning, replication, performance tuning)
- Interview questions
Just tell me — I’m always here for you! 😊
What would you like to do next?
