Skip to main content
MySQL Basics
CHAPTER 24 Beginner

Database Backup and Recovery

Updated: May 16, 2026
6 min read

# CHAPTER 24

Database Backup and Recovery

1. Introduction

A database is the most valuable asset a company owns. The PHP source code can be rewritten. The HTML frontend can be redesigned. But if you lose 10 years of customer orders and billing history, the company goes bankrupt immediately. Hardware fails, datacenters catch fire, and malicious hackers drop tables. Hope is not a strategy. In this chapter, we elevate your skills to Database Administrator (DBA) status by mastering automated Backup and Disaster Recovery strategies.

2. Learning Objectives

By the end of this chapter, you will be able to:
  • Explain the importance of Disaster Recovery.
  • Differentiate between Logical and Physical backups.
  • Automate scheduled backups using Cron Jobs.
  • Understand the concept of Point-in-Time Recovery.
  • Safely restore a corrupted database from a backup.

3. Logical vs. Physical Backups

There are two primary ways to back up a MySQL database:
  1. 1. Logical Backups (mysqldump): As learned in Chapter 23, this generates a massive .sql text file of INSERT commands.
  • *Pros:* Human-readable, highly portable across different MySQL versions.
  • *Cons:* Extremely slow to generate, and extremely slow to restore on large databases (e.g., executing 100 million INSERT queries takes hours).
  1. 2. Physical Backups: This involves literally copying the raw .ibd binary data files directly off the hard drive.
  • *Pros:* Blazing fast. To restore it, you just paste the files back.
  • *Cons:* You cannot read the files, and they must be restored to the exact same version of MySQL.

4. Automating Backups with Cron Jobs

A backup you have to trigger manually is a backup that will eventually be forgotten. Backups must be automated. On Linux servers, we use a task scheduler called Cron.

Let's write a simple bash script (backup.sh) that dumps the database, names it using the current date, and compresses it:

bash
123456 
#!/bin/bash
# Generate a filename like backup_2024-10-31.sql.gz
FILENAME="backup_$(date +%Y-%m-%d).sql.gz"

# Dump the DB and pipe it directly into gzip compression!
mysqldump -u root -p'YourPassword' company_db | gzip > /backup_folder/$FILENAME

You then add this script to the Linux crontab to tell the server to run it automatically every single night at 3:00 AM!

5. Point-in-Time Recovery (Advanced)

A nightly backup is great, but what if a junior developer accidentally types DELETE FROM users; at 2:00 PM? If you restore last night's backup, you lose 14 hours of customer orders!

To solve this, Enterprise databases use the Binary Log (BinLog). When enabled, MySQL silently writes down *every single query* that modifies the database into a secure log file. If the disaster happens at 2:00 PM:

  1. 1. You restore the 3:00 AM nightly backup.
  1. 2. You feed the BinLog back into MySQL, and tell it to replay all the queries from 3:01 AM up until exactly 1:59 PM.
  1. 3. The database is perfectly restored to the state it was in exactly 60 seconds before the disastrous DELETE command was executed!

6. Strategy: The 3-2-1 Backup Rule

The golden rule of IT disaster recovery:
  • Keep 3 total copies of your data (1 live production, 2 backups).
  • Keep them on at least 2 different types of storage media (e.g., local hard drive and cloud block storage).
  • Keep at least 1 copy completely offsite (e.g., in an AWS S3 bucket in a different geographic region, safe from local datacenter fires).

7. Common Mistakes

  • Storing backups on the same hard drive: If you automate a mysqldump every night, but you save the .sql files directly onto the same server that hosts the database, you have accomplished nothing. If the hard drive dies, the live data AND the backups are destroyed simultaneously. Backups MUST be sent to an external server.
  • Never testing the restore process: A backup is completely worthless if it is corrupted and cannot be restored. DBAs must perform routine "Fire Drills" where they actively attempt to import the backup into a staging server to verify its integrity.

8. Best Practices

  • Encrypt your backups: A database dump file contains millions of plain-text passwords, SSNs, and emails. If a hacker intercepts your .sql file while it is uploading to cloud storage, your company is breached. Always encrypt the dump file before transferring it offsite.

9. Exercises

  1. 1. According to the 3-2-1 Backup Rule, why is it mathematically insufficient to only keep a backup on an external hard drive sitting in the same office building as the server?
  1. 2. Explain why a Logical Backup (mysqldump) is not ideal for restoring a massive 500-Gigabyte database during a critical company outage.

10. MCQ Quiz with Answers

Question 1

What is the fundamental difference between a Logical Backup and a Physical Backup in MySQL?

Question 2

What MySQL feature must be enabled to achieve "Point-in-Time Recovery", allowing a DBA to restore a database to its exact state 5 minutes before a catastrophic human error occurred?

11. Interview Questions

  • Q: Explain the concept of Point-in-Time recovery using MySQL's Binary Log. How does it protect against data loss between scheduled nightly backups?
  • Q: Describe the architectural components required to automate a nightly, compressed, offsite database backup on a Linux server.

12. FAQs

Q: Does taking a backup slow down the live website? A: It can. Running a massive SELECT * via mysqldump can consume severe CPU and Disk IO. This is why DBAs schedule backups for 3:00 AM (when web traffic is lowest) and use locking strategies to prevent database freezing.

13. Summary

Disaster Recovery is the highest responsibility of a database engineer. By implementing the 3-2-1 rule, automating compressed dumps via Cron, and understanding the power of the Binary log, you ensure that hardware failures and catastrophic human errors are merely temporary setbacks, rather than company-ending events.

14. Next Chapter Recommendation

Your database is now backed up and safe from physical disasters. But what about digital disasters? What if a hacker types a malicious string into your website's login box and tricks MySQL into deleting itself? In Chapter 25: MySQL Security Best Practices, we will master user permissions and defend against the terrifying SQL Injection attack.

Finish this Chapter

Save your progress on your learning path and prepare for coding interview challenges.

Previous Chapter Importing and Exporting Databases
Next Chapter MySQL Security Best Practices

Discussion

Join the discussion

Log in or create a free account to participate.

Sort: ·

Explore More

📖 Related Tutorials 6

❓ Related Quizzes 6