How to Copy MySQL Database to Another Database?

This tutorial will show you how to replicate a MySQL database on the same server and from one server to another.

Alibaba Cloud Simple Application Server:  Anti COVID-19 SME Enablement Program
$300 coupon package for all new SMEs and a $500 coupon for paying customers.

To copy MySQL database on the same server, to copy MySQL database

To copy a MySQL database, you need to perform the following steps:

• First, use the CREATE DATABASE statement to create a new database.
• Second, use the mysqldump tool to export all database objects and data of the database to be replicated.
• Third, import the SQL dump file into the new database.

As a demonstration, suppose you want to copy the yiibaidb database to the yiibaidb_backup database. Please refer to the following steps-
Step 1. Create yiibaidb_backup database:

First, log in to the MySQL database server:

C:\Users\Administrator> mysql -u root -p
Enter password: **********

Then, use the CREATE DATABASE statement as follows:

CREATE DATABASE yiibaidb_backup;

Third, use the SHOW DATABASES command to verify:

SHOW DATABASES

The MySQL database server returns the following output:
As you can see, we have successfully created the yiibaidb_backup database.
Step 2
Use the mysqldump tool to dump database objects and data to SQL files.

Suppose you want to dump database objects and databases to SQL files located in the D:\database_bak folder, here is the following command:

C:\Users\Administrator> mysqldump -u root -p yiibaidb > d:\database_bak\yiibaidb.sql
Enter password: **********

Basically, this command instructs mysqldump to log in to the MySQL server using the root user account with a password, and export the database objects and the data of the yiibaidb database to d:\database_bak\yiibaidb.sql. Please note that the operator (>) means export.

Step 3

Import the d:\database_bak\yiibaidb.sql file to the yiibaidb_backup database.

C:\Users\Administrator> mysql -u root -p yiibaidb_backup < d:\database_bak\yiibaidb.sql
Enter password: **********

Please note that the operator (<) means import.

To verify the import, you can use the SHOW TABLES command to quickly check.

mysql> SHOW TABLES FROM yiibaidb_backup;

It returns the following output:

As you can see, we have copied all objects and data from the yiibaidb database to the yiibaidb_backup database on the same server.

Copy MySQL database from server to another server To copy MySQL database from server to another server, use the following steps:

• Export the database on the source server to a SQL dump file. 
• Copy the SQL dump file to the target server 
• Import the SQL dump file to the target server

Let's see how to copy the yiibaidb database from a server to another server.
First, export the yiibaidb database to the db.sql file.

C:\Users\Administrator> mysqldump -u root -p --databases yiibaidb > d:\database_bak\db.sql
Enter password: **********

Please note that the --database option allows mysqldump to include CREATE DATABASE and USE statements in the SQL dump file. These statements will create a yiibaidb database in the target server and use the new database as the default database for loading data.
In short, when using the --database option, the beginning of the SQL dump file contains the following statement.

CREATE DATABASE `yiibaidb2`.

USE `yiibaidb2`;

Third, import the db.sql file to the database server, assuming that the db.sql file is copied to the d:\database_bak\ folder.

C:\Users\Administrator> mysql -u root -p yiibaidb2 < d:\database_bak\db.sql

In this tutorial, you have learned step by step how to copy a MySQL database on the same server and copy the database from one server to another server.