Practitioner Level

Backup and Restore Databases in MySQL

Pricing
45 minutes
  • 5 Learning Objectives

About this Hands-on Lab

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.

Learning Objectives

Successfully complete this lab by achieving the following learning objectives:

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
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
Contact SalesSee Pricing

Additional Resources

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.

Additional Information:

  • Tasks in the MySQL database should be performed as the root user (unless otherwise specified).
  • All backup files should be saved to the /home/cloud_user directory (unless otherwise specified).
  • The MySQL server is running with the --secure-file-priv option so delimited-text dump files must be copied to the /var/lib/mysql-files directory.
  • Use the following password to login to the MySQL server as the root user: Linux4me!
  • Do not update the password for the MySQL root user as it is used for grading purposes.

What are Hands-on Labs

Hands-on Labs are real environments created by industry experts to help you learn. These environments help you gain knowledge and experience, practice without compromising your system, test without risk, destroy without fear, and let you learn from your mistakes. Hands-on Labs: practice your skills before delivering in the real world.

Sign In
Welcome Back!

Psst…this one if you’ve been moved to ACG!

Don't have an account?

Get Started
Who’s going to be learning?
MyselfMy Organization