Skip to main content

Backup and Restore Databases in MySQL

Hands-On Lab

 

Photo of Matthew Pearson

Matthew Pearson

Linux Training Architect II in Content

Length

00:45:00

Difficulty

Intermediate

In MySQL server administration, it is important to safeguard your data in case of human error or a system failure. To accomplish this, MySQL provides the ability to create backups of databases and tables. In this lab, you will be tasked with creating backups for individual databases and tables, as well as full backups of the MySQL server. You will also need to demonstrate the ability to restore the MySQL server from a complete backup.

What are Hands-On Labs?

Hands-On Labs are scenario-based learning environments where learners can practice without consequences. Don't compromise a system or waste money on expensive downloads. Practice real-world skills without the real-world risk, no assembly required.

Backup and Restore Databases in MySQL

Introduction

In MySQL server administration, it is important to safeguard data in case of human error or a system failure. To accomplish this, MySQL provides the ability to create backups of databases and tables. In this lab, we will create backups for individual databases and tables, as well as full backups of the MySQL server. We will also restore the MySQL server from a complete backup.

The Scenario

Our team has been charged with creating backups for the MySQL server, as part of our departments disaster recovery initiative. First we will need to create a partial backup of the prod database that only includes the products table. This backup file should be named prod_products.sql. Next, we need to create a full backup of the dev and test databases, which include all tables. This backup file should be named dev_test.sql. Then we have to create a full backup of all the databases within the MySQL server, and this file should be named full_backup.sql.

Lastly, we will need to create delimited-text dump files of the prod database. These dump files should be created in the /var/lib/mysql-files directory. Once all of the backup files have been created, we will need to perform a full restore of the MySQL server using the mysql_dump.sql file located in the /home/cloud_user directory.

Getting Logged In

Use the credentials and server IP in the hands-on lab overview page to log into our lab server.

Create a Partial Backup of the prod Database That Only Includes the products Table

Run following command as cloud_user to create a backup of just the products table in the prod database:

# mysqldump -u root -p prod products > prod_products.sql

Create Full Backups of the dev and test Databases

Run following command to create backups of the dev and test databases:

# mysqldump -u root -p --databases dev test > dev_test.sql

Create a Complete Backup of All the Databases on the MySQL Server

Use the following command to create a full backup of all the databases on the MySQL server:

# mysqldump -u root -p --all-databases > full_backup.sql

Create Delimited-Text Dump Files of the prod Database in the /var/lib/mysql-files Directory

Run the following command to create delimited-text dump files of the prod database in /var/lib/mysql-files (the use of sudo does not require a password in this lab, so the prompt is for the MySQL root user, using the password Linux4me!:

# sudo mysqldump -u root -p --tab=/var/lib/mysql-files/ prod

Show what got dumped out with this command:

sudo ls /var/lib/mysql-files/

Let's take a look at one:

sudo cat orders.txt

We'll see that it's just a raw dump of the data in that table.

Perform a Full Restore of the Databases on the MySQL Server by Reloading the msyql_dump.sql Dump File Located in the /home/cloud_user Directory

To perform a full restore of the MySQL server, run the following command from the /home/cloud_user directory:

# mysql -u root -p < mysql_dump.sql

And to check, let's get to a MySQL prompt:

mysql -u root -p

Once we enter the password, we'll run a query from the mysql> prompt:

SELECT * FROM prod.orders;

We'll see six entries there, and there were only five in the orders.txt file we looked at earlier, so we know that this is in fact different data (which came from msyql_dump.sql).

Conclusion

In the event of an emergency, we should be all set. We've managed to create dumps of both whole databases and just single tables. We've also managed to restore from these kinds of dumps. Congratulations!