MySQL COPY Database

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:

mysql> CREATE DATABASE testdb_copy;

Next, use the SHOW DATABASES statement for verification:mysql> CREATE DATABASE testdb_copy;Next, use the SHOW DATABASES statement for verification:

mysql> SHOW DATABASES;

 

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.

C:\Users\javatpoint> CD C:\Program Files\MySQL\MySQL Server 8.0\bin

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:

mysqldump -u root -p testdb > D:\Database_backup\testdb.sql  

Enter password: **********

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:

mysql -u root -p testdb_copy < D:\Database_backup\testdb.sql  

Enter password: **********

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:

mysql> SHOW TABLES; 

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