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. The lab covers essential techniques for creating logical backups and restoring them to ensure data integrity and availability.

The lab guides you through exporting a database, including creating a sample database and table, and then backing it up using mysqldump. You'll also learn how to back up a single table, restore a database from a dump file, and finally, check the accuracy of the restored data to verify the success of the restoration process.

Skills Graph

Export a Database with mysqldump

In this step, you will learn how to export a database using the mysqldump command-line tool. mysqldump is a utility that allows you to create a logical backup of your MySQL database. This backup can then be used to restore the database to its original state or to migrate it to a different server.

Before we begin, let's create a sample database and table to work with. Open your terminal and execute the following commands to connect to the MySQL server as the root user. You might be prompted for the root password, which is usually empty by default in a development environment.

mysql -u root -p

Now, create a database named mydatabase:

CREATE DATABASE mydatabase;

Next, switch to the mydatabase database:

USE mydatabase;

Create a simple table named users:

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE
);

Insert some sample data into the users table:

INSERT INTO users (name, email) VALUES
('John Doe', '[email protected]'),
('Jane Smith', '[email protected]');

Exit the MySQL shell:

exit

Now that we have a database with some data, let's export it using mysqldump. The basic syntax for exporting a database is:

mysqldump -u [username] -p[password] [database_name] > [output_file.sql]

Replace [username] with your MySQL username (usually root), [password] with your MySQL password (if any), [database_name] with the name of the database you want to export (mydatabase), and [output_file.sql] with the name of the file where you want to save the backup (e.g., mydatabase_backup.sql).

In our case, the command would be:

mysqldump -u root -p mydatabase > ~/project/mydatabase_backup.sql

You will be prompted for the MySQL root password. Enter the password (if any).

This command will create a file named mydatabase_backup.sql in your ~/project directory, which contains the SQL statements needed to recreate the mydatabase database and its contents.

You can verify that the file has been created by listing the contents of your ~/project directory:

ls ~/project

You should see mydatabase_backup.sql in the output.

You can also view the contents of the backup file using nano:

nano ~/project/mydatabase_backup.sql

This will show you the SQL statements that mysqldump generated. Press Ctrl+X to exit nano.

Back Up a Single Table

In this step, you will learn how to back up a single table from a MySQL database using the mysqldump command. This is useful when you only need to back up a specific table and not the entire database.

We will continue using the mydatabase database and the users table that we created in the previous step.

To back up a single table, you use the same mysqldump command, but you specify the table name after the database name:

mysqldump -u [username] -p[password] [database_name] [table_name] > [output_file.sql]

Replace [username] with your MySQL username (usually root), [password] with your MySQL password (if any), [database_name] with the name of the database (mydatabase), [table_name] with the name of the table you want to export (users), and [output_file.sql] with the name of the file where you want to save the backup (e.g., users_backup.sql).

In our case, the command would be:

mysqldump -u root -p mydatabase users > ~/project/users_backup.sql

You will be prompted for the MySQL root password. Enter the password (if any).

This command will create a file named users_backup.sql in your ~/project directory, which contains the SQL statements needed to recreate the users table and its contents.

You can verify that the file has been created by listing the contents of your ~/project directory:

ls ~/project

You should see both mydatabase_backup.sql (from the previous step) and users_backup.sql in the output.

You can also view the contents of the backup file using nano:

nano ~/project/users_backup.sql

This will show you the SQL statements that mysqldump generated specifically for the users table. Press Ctrl+X to exit nano.

Now, let's add another table to our database to further illustrate the single table backup process. Connect to the MySQL server:

mysql -u root -p

Switch to the mydatabase database:

USE mydatabase;

Create a new table named products:

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2)
);

Insert some sample data into the products table:

INSERT INTO products (name, price) VALUES
('Laptop', 1200.00),
('Mouse', 25.00);

Exit the MySQL shell:

exit

Now, if you only want to back up the products table, you would use the following command:

mysqldump -u root -p mydatabase products > ~/project/products_backup.sql

This will create a file named products_backup.sql in your ~/project directory containing only the products table data.

Restore a Database from a Dump File

In this step, you will learn how to restore a database from a dump file created by mysqldump. This is the process of recreating the database and its contents from the SQL statements stored in the backup file.

First, let's drop the mydatabase database to simulate a scenario where the database needs to be restored. Connect to the MySQL server:

mysql -u root -p

Drop the mydatabase database:

DROP DATABASE mydatabase;

Exit the MySQL shell:

exit

Now, we will restore the mydatabase database from the mydatabase_backup.sql file that we created in the first step. The basic syntax for restoring a database is:

mysql -u [username] -p[password] [database_name] < [input_file.sql]

Replace [username] with your MySQL username (usually root), [password] with your MySQL password (if any), [database_name] with the name of the database you want to restore to (mydatabase), and [input_file.sql] with the name of the backup file (mydatabase_backup.sql).

Before restoring, we need to create the mydatabase database again, as we just dropped it. Connect to the MySQL server:

mysql -u root -p

Create the mydatabase database:

CREATE DATABASE mydatabase;

Exit the MySQL shell:

exit

Now, restore the database:

mysql -u root -p mydatabase < ~/project/mydatabase_backup.sql

You will be prompted for the MySQL root password. Enter the password (if any).

This command will execute the SQL statements in mydatabase_backup.sql and recreate the mydatabase database and its tables and data.

To verify that the database has been restored, connect to the MySQL server:

mysql -u root -p

Switch to the mydatabase database:

USE mydatabase;

Show the tables in the database:

SHOW TABLES;

You should see the users and products tables.

Select all data from the users table:

SELECT * FROM users;

You should see the data that we inserted in the first step.

Select all data from the products table:

SELECT * FROM products;

You should see the data that we inserted in the second step.

Exit the MySQL shell:

exit

You have now successfully restored the mydatabase database from the mydatabase_backup.sql file.

Check Restored Data Accuracy

In this step, you will verify the accuracy of the restored data by comparing it to the original data. This ensures that the backup and restore process was successful and that no data was lost or corrupted.

We will connect to the restored mydatabase database and query the users and products tables to check if the data matches what we initially inserted.

Connect to the MySQL server:

mysql -u root -p

Switch to the mydatabase database:

USE mydatabase;

Select all data from the users table:

SELECT * FROM users;

You should see the following output:

+----+----------+-----------------------+
| id | name     | email                 |
+----+----------+-----------------------+
|  1 | John Doe | [email protected]  |
|  2 | Jane Smith | [email protected] |
+----+----------+-----------------------+
2 rows in set (0.00 sec)

This confirms that the data in the users table has been successfully restored.

Now, select all data from the products table:

SELECT * FROM products;

You should see the following output:

+----+---------+---------+
| id | name    | price   |
+----+---------+---------+
|  1 | Laptop  | 1200.00 |
|  2 | Mouse   |   25.00 |
+----+---------+---------+
2 rows in set (0.00 sec)

This confirms that the data in the products table has also been successfully restored.

If the output matches the data you initially inserted, then the restore process was successful. If the output is different or if you encounter any errors, then there may have been an issue with the backup or restore process.

To further ensure data integrity, you can perform more complex queries to check for specific data values or relationships between tables. However, for this lab, we have verified the basic data accuracy by checking the contents of the users and products tables.

Exit the MySQL shell:

exit

You have now successfully verified the accuracy of the restored data. This completes the lab on exporting, backing up, and restoring MySQL databases.

Summary

In this lab, you learned how to use the mysqldump command-line tool to create a logical backup of a MySQL database. This involved creating a sample database named mydatabase with a users table and inserting sample data. You then used mysqldump to export the entire database to a SQL file, mydatabase_backup.sql.

The process demonstrated the basic syntax of the mysqldump command, including specifying the username, password, database name, and output file. This backup file can be used to restore the database to its original state or migrate it to another server.