MySQL - Copy Database

---

---

In modern times, companies rely on databases to store crucial information like financial transactions, customer profiles, and employee records. It is very important to maintain regular copies of databases there can always be a chance of data loss from power surges and disk crashes. Therefore, regular backups of databases are crucial for effective data management.

Copy Database in MySQL

In MySQL, copying a database involves creating an exact duplicate of an existing database, including its schema and data. This is almost similar to having a backup of a database. It is important to ensure that any changes made to the original database after the copy is made are also reflected in the copied database, if necessary.

To create a copy of a database SQL Server provides the Copy Database statement. But, this is not available in MySQL. Therefore, to create copy of a database we need to dump the contents of one database to other manually.

The following are three steps that involve in copying a database −

• First of all, we need to create a new database.

• Then, we need to export the original database using mysqldump.

• Finally, importing the exported data into the new database.

Example

First of all, let us create a database in the MySQL server using the following query −

CREATE DATABASE testdb;

We can verify whether the database testdb is created or not using the SHOW DATABASES statement.

SHOW DATABASES;

As we can see the output below, the testdb database has been created successfully.

Database
information_schema
mysql
performance_schema
testdb

Once the database is created successfully, we need to change the current database to 'testdb', using the USE statement so that any operations we perform such as creating a table will be stored in this database.

USE testdb;

Now, let us create a table named CUSTOMERS using the CREATE query as follows −

CREATE TABLE CUSTOMERS (
   ID INT AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

The following query inserts 7 records into the above-created table −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES 
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );

Using the following query, we can verify whether the table CUSTOMERS is created in 'testdb' database or not −

SHOW TABLES;

The table is successfully created in the testdb database.

Tables_in_testdb
customers

Creating Copy database (Manually)

As discussed earlier in MySQL to create a copy of an existing database we need to create a separate database and dump the contents of it to the newly created database manually.

Following statement creates a database named testdb_copy (to which we need to copy the contents of the testdb database created above).

CREATE DATABASE testdb_copy;

Once both our source (testdb) and destination (testdb_copy) databases are ready, we need to follow the steps given below to manually copy the data from one to another.

Step1 − Open the command prompt, browse through the bin folder of the MySQL server. For instance, we have installed MySQL in the C\ Program Files directory so, the following command will take us to the bin folder −

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

Step 2 − Using the mysqldump tool, we can copy the database objects and data into a .sql file. Here, we are exporting the contents of the testdb database to a file named "testdb.sql" located at "D:\Database_backup".

Note − The (>) operator is used for exporting the database from one location to another.

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

Step 3 − Import the contents of the "testdb.sql" file into the destination database (in our case "testdb_copy").

Note − The (<) operator is used for importing the database from one location to another.

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

Verification

To verify whether the data and database object is imported into the testdb_copy database, first, we need to use the current database using the following query in the MySQL Command Line Client −

USE testdb_copy;

If the contents of the testdb are copied in to the testdb_copy successfully, we should be able to find the customers table in the list of tables (which is created earlier).

Therefore, let us verify whether the data from the "testdb" database have been copied to the "testdb_copy" database or not using the following query −

SHOW TABLES;

As we can see in the list below, all the database objects and data have been successfully copied.

Tables_in_testdb
customers

Copy Database Without MySQLdump

If we want to copy a database without using the mysqldump tool, we must manually create each table in the destination database and copy all the data from the tables present in the current database. This is a repitetive process that should be done for each table that needs to be copied.

Example

Let us create a new database in the MySQL server using the following query −

CREATE DATABASE Tutorials;

We can verify whether the database Tutorials is created or not using the following query −

SHOW DATABASES;

As we can see the output below, the 'Tutorials' database has been created successfully.

Database
information_schema
mysql
performance_schema
tutorials

Now, we are switching the current database to Tutorials, so that any operations we perform such as creating a table will be stored in this database.

USE Tutorials;

Once we have switched, create a table named CUSTOMERS using the following query −

CREATE TABLE CUSTOMERS (
   ID INT AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Here, we are inserting some records into the table using the INSERT INTO statement below −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES 
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );

Using the below query, let us create another database Tutorials_copy, where we want to copy all the data objects and data of the Tutorials database.

CREATE DATABASE Tutorials_copy;

We can verify whether the database Tutorials_copy is created or not, using the following query −

SHOW DATABASES;

The database has been created.

Database
information_schema
mysql
performance_schema
tutorials
tutorials_copy

Now, switch the current database to 'tutorials_copy' using the following query −

USE Tutorials_copy;

Here, we are creating an empty table named "CUSTOMERS" in the 'Tutorials_copy' database with the same schema as the original 'CUSTOMERS' table in the 'Tutorials' −

CREATE TABLE Tutorials_copy.customers LIKE Tutorials.customers;

This query inserts all the data from the original 'customers' table in the 'Tutorials' database into the new 'customers' table in the 'tutorials_copy' database.

INSERT Tutorials_copy.customers SELECT * FROM Tutorials.customers;

We can verify whether the database objects and data from the 'Tutorials' database have been copied to the 'Tutorials_copy' database or not.

SHOW TABLES;

Output

As we can see in the list below, the table has been successfully copied −

Tables_in_tutorials_copy
customers

Let us also retrieve the records of CUSTOMERS table in 'Tutorials_copy' database to verify whether the records have been copied or not −

Select * from CUSTOMERS;

As we can see the CUSTOMERS table in 'Tutorials_copy' database below, the records have been successfully copied −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00