How To Copy a MySQL Database

Summary: This tutorial shows you how to copy a MySQL database on the same MySQL server and from one server to another.

Copy a MySQL database on the same server

Assuming that you have the mysqldump and mysql client tools already installed on your computer or the database server, you can proceed with executing them.

If these tools are installed on your local machine, you can run the commands directly. Otherwise, if the tools are located on the database server, you’ll need to connect to the server to execute the commands.

To copy a MySQL database, you need to follow these steps:

• First, create a new database using CREATE DATABASE statement.
• Second, export all a database from which you want to copy using mysqldump tool to a SQL dump file.
• Third, import the SQL dump file into the new database using the mysql tool.

The following picture illustrates the steps:

For the demonstration, we will copy the classicmodels sample database to classicmodels_backup database.

Step 1. Create the classmodels_backup database:

First, log in to the MySQL database server:

mysql -u root -pCode language: SQL (Structured Query Language) (sql)

Then, use CREATE DATABASE statement to create a new database:

CREATE DATABASE classicmodels_backup;Code language: SQL (Structured Query Language) (sql)

Third, use the SHOW DATABASES command to verify:

SHOW DATABASES;Code language: SQL (Structured Query Language) (sql)

MySQL database server returns the following output:

+----------------------+
| Database             |
+----------------------+
| classicmodels        |
| classicmodels_backup |
| information_schema   |
| mysql                |
| performance_schema   |
| sys                  |
+----------------------+
6 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

The output shows that the classicmodels_backup database has been created successfully.

Step 2. Dump the database into SQL files using the mysqldump tool.

The following command dumps the classicmodels database into an SQL file located at D:\db directory:

mysqldump -u root -p classicmodels > D:\db\classicmodels.sqlCode language: SQL (Structured Query Language) (sql)

This command instructs mysqldump to log in to the MySQL server using the root user account with a password and export the database objects to d:\db\classicmodels.sql. Note that the operator (>) means exporting.

Step 3. Import the d:\db\classicmodels.sql file into classicmodels_backup database.

mysql -u root -p classicmodels_backup < d:\db\classicmodels.sqlCode language: SQL (Structured Query Language) (sql)

Note that the operator (<) means importing.

To verify the import, you can perform a quick check by using the SHOW TABLES command.

SHOW TABLES FROM classicmodels_backup;Code language: SQL (Structured Query Language) (sql)

It returned the following output:

+--------------------------------+
| Tables_in_classicmodels_backup |
+--------------------------------+
| customers                      |
| employees                      |
| offices                        |
| orderdetails                   |
| orders                         |
| payments                       |
| productlines                   |
| products                       |
+--------------------------------+
8 rows in set (0.01 sec)Code language: SQL (Structured Query Language) (sql)

The output indicates that we have successfully copied all the objects and data from the classicmodels database to classicmodels_backup database.

Copy a MySQL database from one server to another

To copy a MySQL database from one server to another, you use the following steps:

• First, export the database on the source server to an SQL dump file using the mysqldump tool.
• Second, import the SQL dump file to the destination server using the mysql tool.

The following picture illustrates the steps:

First, export the classicmodels database to db.sql file using the mysqldump tool:

mysqldump -h source_server -u root -p --databases classicmodels > d:\db\db.sqlCode language: SQL (Structured Query Language) (sql)

In this command, you need to replace the source_server with your actual server.

Note that --database option allows mysqldump to include both CREATE DATABASE and USE statements in the SQL dump file.

These statements will create the classicmodels database in the destination server and make the new database the default database for loading the data.

In other words, the mysqldump generates the following statements at the beginning of the SQL dump file when we use --database option:

CREATE DATABASE `classicmodels`.

USE `classicmodels`;Code language: SQL (Structured Query Language) (sql)

Second, import the db.sql file into the destination database server using the mysql tool:

mysql -h destination_server -u root -p classicmodels < c:\tmp\db.sqlCode language: SQL (Structured Query Language) (sql)

In this command, you need to replace the destination_server with your actual server.

Summary

• Export a database to a dump file using the mysqldump tool and import the dump file using the mysql tool to copy a MySQL database within one server or from one server to another.