Backup and Restore

Introduction

In system administration, data loss is not a matter of "if," but "when." Whether it is caused by hardware failure, software bugs, or human mistakes, losing critical data can be catastrophic. That is why backup and recovery are among the most important database skills.

In this lab, you will perform a complete "Disaster Recovery" simulation. You will start with a healthy database, back it up safely, destroy it (simulating a disaster), and then bring it back to life using your backup.

You will learn how to:

• Create a logical backup using the mysqldump utility.
• Simulate a catastrophic failure by deleting a database.
• Restore the database to its original state from your backup file.

A logical backup stores SQL statements that can recreate the schema and data. This is different from a physical backup, which copies the raw database files. Both approaches exist, but logical backups are easier for beginners to read and understand.

This distinction matters because each backup type solves slightly different problems. Logical backups are portable and human-readable, while physical backups are usually closer to the storage engine and recovery internals.

Create a Backup

First, let's verify that we have a database worth saving. We have pre-populated a database named app_db with some user data.

Check the current data:

sudo mysql -e "SELECT * FROM app_db.users;"

You should see a list of users (Alice, Bob, Charlie).

Now, we will create a logical backup using the mysqldump tool. A logical backup basically exports a text file containing all the SQL commands (like CREATE TABLE, INSERT) needed to rebuild your database from scratch.

So the backup file is not a mysterious binary blob. It is usually a SQL script that can be replayed later into a database server to reconstruct schema and data.

Run the following command to save the database to a file named backup.sql:

sudo mysqldump app_db > ~/project/backup.sql

Breaking down the command:

• mysqldump: The utility used to export database contents.
• app_db: The specific database we want to back up.
• >: The Linux redirection operator, which sends the output of the command into a file.
• ~/project/backup.sql: The destination file path.

Verify the backup file was created:

ls -l ~/project/backup.sql

You can even peek inside the file to see the SQL commands:

head -n 20 ~/project/backup.sql

Simulate Data Loss

Now comes the scary part. To test our recovery plan, we need a disaster. We will simulate an accidental deletion of the production database.

Warning: The command below deletes data permanently. In a real production environment, you would never run this unless you were absolutely certain you wanted to destroy the data.

This destructive step is included because backups are only meaningful if you can prove recovery works after actual loss. A backup strategy that is never tested is only an assumption.

Delete (drop) the app_db database:

sudo mysql -e "DROP DATABASE app_db;"

Confirm the disaster has occurred by trying to access the database:

sudo mysql -e "USE app_db;"

You should see an error: ERROR 1049 (42000): Unknown database 'app_db'.

This confirms the database is gone. If this were a real production server without a backup, this would be a very bad day.

Restore the Database

Fortunately, we have our backup.sql file. We can use it to restore the data.

However, mysqldump (by default) only exports the contents of the database, not the command to create the database itself. Since we completely deleted app_db, we first need to create an empty shell for our data to live in.

That detail helps beginners understand what exactly was captured. Backups are not magic snapshots of everything unless the chosen command and options explicitly include those parts.

Create the empty database:

sudo mysql -e "CREATE DATABASE app_db;"

Import the data from your backup file:

sudo mysql app_db < ~/project/backup.sql

Breaking down the restore command:

• sudo mysql: Connects to the database server.
• app_db: Tells MySQL which database to use as the target.
• <: The Linux input redirection operator. It reads the contents of the file and feeds it into the mysql command as if you were typing it.

If the command runs silently (no output), that usually means it succeeded!

The restore process is essentially feeding SQL statements back into the server. In other words, recovery is often just controlled replay of the instructions stored in the logical backup.

Verify Data Recovery

The final and most important step of any backup plan is verification. Just because the restore command didn't error doesn't mean the data is correct. We must check.

This is one of the most important operational habits in all of systems work: success is not "the command finished." Success is "the system state is correct afterward."

Query the users table again:

sudo mysql -e "SELECT * FROM app_db.users;"

Success Criteria:

You should see the original data:

+----+---------+---------------------+
| id | name    | email               |
+----+---------+---------------------+
|  1 | Alice   | alice@example.com   |
|  2 | Bob     | bob@example.com     |
|  3 | Charlie | charlie@example.com |
+----+---------+---------------------+

If you see Alice, Bob, and Charlie, you have successfully recovered from the disaster!

Summary

In this lab, you successfully completed a full disaster recovery cycle. You learned how to:

• Backup: Export a database to a file using mysqldump.
• Destroy: Simulate a data loss event using DROP DATABASE.
• Restore: Import a SQL dump file using input redirection <.
• Verify: Confirm the integrity of the recovered data.

Regular backups are your primary defense against data loss. In a real environment, you should automate this process and test your restores frequently.