Copy a database in MySQL with mysqldump command

Clone a database in MySQL

Clone the database World to world_bkp in MySQL database Server

  1. Check the database present in MySQL
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |

| sys                |
| world              |
+--------------------+
5 rows in set (0.01 sec)

2. Backup the World database

C:\Program Files\MySQL\bin>mysqldump -u root -p  world > c:\world.sql
Enter password: *********

3. Create a World_BKP database in MySQL server

C:\Program Files\MySQL\bin>mysqladmin -u root -p create world_bkp
Enter password: *********

4. Restore the world_bkp database in MySQL Server.


C:\Program Files\MySQL\bin>mysql -u root -p world_bkp < c:\d\world.sql
Enter password: *********
Table   Op      Msg_type        Msg_text
world_bkp.city  histogram       status  Histogram statistics created for column 'Name'.
Table   Op      Msg_type        Msg_text
world_bkp.city  histogram       status  Histogram statistics created for column 'District'.

5. Verify the backup restore.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+

| information_schema |
| mysql              |
| performance_schema |

| sys                |
| world              |
| world_bkp          |
+--------------------+
6 rows in set (0.00 sec)

mysql> use world
Database changed
mysql> select count(*) from city;
+----------+
| count(*) |
+----------+
|     4079 |
+----------+
1 row in set (0.00 sec)

mysql> use world_bkp
Database changed
mysql> select count(*) from city;
+----------+
| count(*) |
+----------+
|     4079 |
+----------+
1 row in set (0.00 sec)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.