MySQL Backup and Recovery

Introduction

In this lab, you will learn how to back up and restore MySQL databases using the mysqldump command-line tool. mysqldump is a crucial utility for creating logical backups, which are essential for data protection, disaster recovery, and migration.

You will practice backing up an entire database, backing up a single table, and then restoring both from the backup files. The lab will guide you through simulating data loss scenarios and using your backups to recover the data, ensuring you understand the complete backup and recovery lifecycle.

Back Up an Entire Database

The first step in data protection is creating a complete backup. In this step, you will use mysqldump to create a full backup of the mydatabase database, which has been pre-configured for you.

First, open the terminal. Let's inspect the database to see what it contains. Connect to the MySQL server using the root user.

sudo mysql -u root

Once you see the mysql> prompt, switch to the mydatabase database and view its tables.

USE mydatabase;
SHOW TABLES;

You should see two tables, products and users.

+--------------------+
| Tables_in_mydatabase |
+--------------------+
| products           |
| users              |
+--------------------+
2 rows in set (0.00 sec)

Now, exit the MySQL shell to return to the terminal.

exit

It's time to back up the entire database. The mysqldump command reads the database and outputs the SQL statements required to recreate it. We will redirect this output to a .sql file.

Run the following command to back up mydatabase and save it to a file named mydatabase_backup.sql inside the /home/labex/backup directory.

sudo mysqldump -u root mydatabase > /home/labex/backup/mydatabase_backup.sql

To confirm the backup file was created, list the contents of the /home/labex/backup directory.

ls /home/labex/backup

You should see your backup file listed.

mydatabase_backup.sql

You have successfully created a full backup of the database.

Back Up a Single Table

Sometimes, you only need to back up a specific table rather than the entire database. This is useful for smaller, targeted operations. In this step, you will back up only the products table.

The syntax is similar to a full database backup, but you specify the table name after the database name.

From your terminal, run the following command to back up the products table from mydatabase. The backup will be saved as products_backup.sql.

sudo mysqldump -u root mydatabase products > /home/labex/backup/products_backup.sql

Let's verify that the new backup file was created. List the contents of the backup directory again.

ls /home/labex/backup

You should now see both the full database backup and the single-table backup.

mydatabase_backup.sql  products_backup.sql

You can inspect the contents of the single-table backup file to see that it only contains information for the products table.

cat /home/labex/backup/products_backup.sql

The output will show the SQL statements to create and populate only the products table. This confirms you have successfully created a targeted backup.

Restore an Entire Database

Now that you have backups, let's practice restoring them. In this step, you will simulate a disaster by deleting the entire mydatabase and then restoring it from your full backup file.

First, let's drop the database. The -e flag allows you to execute a single SQL command directly from your terminal.

sudo mysql -u root -e "DROP DATABASE mydatabase;"

Verify that the database is gone.

sudo mysql -u root -e "SHOW DATABASES;"

You will see that mydatabase is no longer in the list.

Before you can restore the data, you must create an empty database with the same name.

sudo mysql -u root -e "CREATE DATABASE mydatabase;"

Now, restore the database by redirecting the mydatabase_backup.sql file as input to the mysql client.

sudo mysql -u root mydatabase < /home/labex/backup/mydatabase_backup.sql

The command will execute all the SQL statements in the backup file, recreating the tables and inserting the data.

To verify the restoration, connect to MySQL and check the tables and their content.

sudo mysql -u root

At the mysql> prompt, run these commands:

USE mydatabase;
SHOW TABLES;
SELECT * FROM users;

You should see the products and users tables again, and the users table should contain its original data.

+--------------------+
| Tables_in_mydatabase |
+--------------------+
| products           |
| users              |
+--------------------+
2 rows in set (0.00 sec)

+----+------------+--------------------------+
| id | name       | email                    |
+----+------------+--------------------------+
|  1 | John Doe   | john.doe@example.com     |
|  2 | Jane Smith | jane.smith@example.com   |
+----+------------+--------------------------+
2 rows in set (0.00 sec)

Exit the MySQL shell.

exit

You have successfully recovered the entire database from a catastrophic failure.

Summary

In this lab, you have learned the essential skills for backing up and restoring MySQL databases using the mysqldump utility. You practiced creating a full backup of a database and a targeted backup of a single table.

You also gained hands-on experience with recovery procedures by simulating both a full database loss and the accidental deletion of a single table. By successfully restoring the data in both scenarios, you have demonstrated a fundamental understanding of the complete data protection lifecycle, a critical skill for any database administrator or developer.