How copy mysql database from command line?

Sometimes you may need to copy database or clone database in MySQL to create duplicate database. Here’s how to copy database in MySQL.

How to Copy Database in MySQL

Here are the steps to copy MySQL database.

1. Create a new empty database using CREATE DATABASE statement
2. Export all database objects & data to new database using mysqldump command
3. Import SQL dump file into new database

Bonus Read : MySQL Insert Into Select

Let us look at different examples to copy MySQL database.

MySQL Copy Database on Same Server

Let’s say you want to copy your database source_db to new database destination_db

Log into MySQL and create new database destination_db

mysql> create database destination_db;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| dashboard          |
| destination_db     |
| fedingo            |
| mysql              |
| performance_schema |
| sample             |
| source_db          |
| testdb             |
| wordpress          |
+--------------------+

Export objects & data of source_db to a file, say, D:\db.sql. You will be prompted for a password.

>mysqldump -u root -p source_db > d:\db.sql
Enter password: **********

Import the file d:\db.sql into destination_db

>mysqldump -u root -p destination_db < d:\db.sql
Enter password: **********

The above steps will copy database with data to same server.

Bonus Read : MySQL Select Top N Rows

If you only want to copy database schema, use -d option in MySQLdump command above. This will copy only the database structure and not the content.

mysql>mysqldump -u root -p -d source_db > d:\db.sql

Import the database structure into destination_db as before

mysql>mysql -u root -p -d destination_db < d:\db.sql

Bonus Read : MySQL Remove Duplicate Records

MySQL Copy Database on Another Server

If you want to copy database on another server, you need to follow similar steps but export the database content & objects to destination server.

1. Export the source database to an SQL file
2. Copy the SQL file to destination server
3. Import SQL file into destination database

First we export source database source_db to db.sql

>mysqldump -u root -p --databases source_db > d:\db.sql
Enter password: **********

The –database option will allow you to include CREATE DATABASE and USE statements in your SQL file.

Next, copy the SQL file to another server (e.g F:\db.sql)

Finally, import the SQL file to destination database.

>mysql -u root -p destination_db < f:\db.sql 
Enter password: **********

Bonus Read : How to Execute Stored Procedure in Workbench

MySQL Copy Database Without MySQLdump

If you want to copy database without MySQLdump, then you will have to manually copy each table in source database to destination database.

Here’s the SQL query to copy table sales from source_db database to destination_db database

CREATE TABLE destination_db.sales 
LIKE source_db.sales;

INSERT destination_db.sales 
SELECT *
FROM source_db.sales;

The first statement will duplicate table structure in MySQL from source database (e.g source_db) to another (e.g destination_db. The second statement will copy data from one table to another. You will have to execute the above statements for each database table, or write a script that generates and executes the above statement for all tables in your database.

Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!

Related posts:

• About Author

A database is an application used for storing the organized collection of records that can be accessed and manage by the user. It holds the data into tables, rows, columns, and indexes to quickly find the relevant information.

MySQL copy or clone database is a feature that allows us to create a duplicate copy of an existing database, including the table structure, indexes, constraints, default values, etc. Making a duplicate copy of an original database into a new database is very useful when accidentally our database is lost or failure. The most common use of making a duplicate copy of the database is for data backups. It is also useful when planning the major changes to the structure of the original database.

In MySQL, making the clone of an original database is a three-step process: First, the original database records are dumped (copied) to a temporary file that holds the SQL commands for reinserting the data into the new database. Second, it is required to create a new database. Finally, the SQL file is processed, and the data will be copied into the new database.

We need to follow these steps to copy a database to another database:

1. First, use the CREATE DATABASE statement to create a new database.
2. Second, store the data to an SQL file. We can give any name to this file, but it must end with a .sql extension.
3. Third, export all the database objects along with its data to copy using the mysqldump tool and then import this file into the new database.

For the demonstration, we will copy the testdb database to testdb_copy database using the following steps:

Open the MySQL console and write down the password, if we have set during installation. Now we are ready to create a duplicate database of testdb using the command below:

Next, use the SHOW DATABASES statement for verification:

This command will return all available database in the server where we can see the newly created database in red rectangle box:

Now, open a DOS or terminal window to access the MySQL server on the command line. For example, if we have installed the MySQL in the C folder, copy the following folder and paste it in our DOS command. Then, press the Enter key.

In the next step, we need to use the mysqldump tool to copy the database objects and data into the SQL file. Suppose we want to dump (copy) the database objects and data of the testdb into an SQL file located at D:\Database_backup folder. To do this, execute the below statement:

The above statement instructs mysqldump tool to log in to the MySQL database server using the username and password and then exports the database objects and data of the testdb database to D:\Database_backup\testdb.sql. It is to note that the operator (>) used for exporting the database from one location to another.

In the next step, we need to import the D:\Database_backup\testdb.sql file into testdb_copy database. To do this, execute the below statement:

It is to note that the operator (<) used for importing the database from one location to another.

Finally, we can verify whether the above operation is successful or not by using the SHOW TABLES command in the MySQL command-line tool:

In this output, we can see that all the objects and data from the testdb database to testdb_copy database have successfully copied.

How do I copy an entire MySQL database?

How do I copy and paste from MySQL command line?

How do I copy a database in Linux?

How do I transfer MySQL database to another computer?