How to Restore a Single Database from a Full MySQL Database Dump

Introduction

MySQL Dump is a backup utility used to recover your databases and tables in case your database server crashes. A single MySQL Database Dump is a logical backup in a flat file with Standard Query Language (SQL) statements that restore a database to its original state before backup.

MySQL dump files are important when transferring databases from one server to another or when recovering the database server from a crash. This guide illustrates how you can restore a single database from a full MySQL database dump consisting of two or more databases.

Prerequisites

Before you start, make sure you:

• Have a running MySQL database server.
• SSH and Login to the MySQL database server.

Create a Full MySQL Dump

To create a Full MySQL database dump, make sure you have two or more databases available on your database server. For purposes of this guide, create three new databases on your server as below.

1. Log in to the MySQL console.

     $ mysql

2. Create the databases.

     mysql> CREATE DATABASE db1; CREATE DATABASE db2; CREATE DATABASE exampledb3;

3. Switch to the db1 database.

     mysql> use db1;

4. Add a new table to the database.

     mysql> CREATE TABLE Example (
                ID int,
                FirstName varchar(255),
                LastName varchar(255),
                Country varchar(255),
                City varchar(255)
             );

5. Create a new privileged user.

     mysql> CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'strong-password';

6. Grant the user full privileges to all databases.

     mysql> GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'localhost';

7. Reload MySQL privileges.

     mysql> FLUSH PRIVILEGES;

8. Exit the console.

     mysql> EXIT

9. Create a full MySQL dump of all databases.

     $ mysqldump -u myuser -p --all-databases > backup.dump

   A new backup.dump file is added to your working directory.

10. For restoration purposes, re-login to the MySQL console.

     $ mysql -u myuser -p

11. Delete the databases you created earlier.

     mysql> DROP DATABASE db1;
    
     mysql> DROP DATABASE db2;
    
     mysql> DROP DATABASE exampledb3;

12. Exit the MySQL console.

     mysql> EXIT

Restore a single Database from the Full MySQL Dump

To restore a single database from a full MySQL dump, you should create an empty database with the same name, then restore the single database to recover all its tables and table data. For this guide, restore the db1 database as below.

1. Verify that the full MySQL dump file exists in your working directory.

     $ ls -l

   Output:

     -rw-r--r-- 1 example example 264261856 Sep 21 14:06 backup.dump

2. Restore the db1 database from the full MySQL dump to a new file without importing it to the database server.

     $ sed -n '/^-- Current Database: `db1`/,/^-- Current Database: `/p' backup.dump > db1.sql

3. Import the single database file to the database server.

     $ mysql -u myuser -p < db1.sql

4. When the command completes, re-login to the MySQL console to verify changes.

     $ mysql -u myuser -p

5. Switch to the db1 database.

     mysql> use db1;

6. Show all tables to verify that your data is available.

     mysql> show tables;

   Output:

     +---------------+
     | Tables_in_db1 |
     +---------------+
     | Example       |
     +---------------+
     1 row in set (0.00 sec)

7. Exit the MySQL console, and restore any other databases.

     mysql> EXIT

Restore a single Table from a MySQL Database Dump

1. Back up the example table from the db1 database using the following command.

     $ mysqldump -u myuser -p db1 example > example-tbl.sql

2. Assuming you want to restore the example-tbl.sql file to a new database such as db2, run the following command.

     $ mysql -u myuser -p db2 < example-tbl.sql

3. Login to the MySQL console.

     $ mysql -u myuser -p

4. Switch to the db2 database.

     mysql> use db2;

5. Verify that the example table is available.

     mysql> show tables;

6. Exit the console.

     mysql> EXIT

You can back up database tables and restore them to any other databases without any restriction on the source database name.

Conclusion

You have restored a single database from a full MySQL Database dump on your database server. You can automate periodic MySQL backups and restore them to a new server or in case the database server crashes. For more information, please visit the following resources.

• Backup Multiple MySQL Or MariaDB Databases Automatically.
• How to Backup and Restore MySQL or MariaDB on Ubuntu 20.04.