MySQL Copy Database

Last updated on September 11th, 2024 at 05:56 am

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

Table of Contents

How to Copy Database in MySQL

Here are the steps to copy MySQL database.

Create a new empty database using CREATE DATABASE statement
Export all database objects & data to new database using mysqldump command
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

MySQL Copy Database Schema

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

You can also use MySQLDump to copy specific tables in a database, instead of the entire database.

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.

Export the source database to an SQL file
Copy the SQL file to destination server
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!

Insert data from one table into another in MySQL

How to Calculate Total Sales Per Month in MySQL?

How To Create User in MySQL