How do i copy data from one mysql database to another?

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.

7.4.5.2 Copy a Database from one Server to Another

On Server 1:

$> mysqldump --databases db1 > dump.sql

Copy the dump file from Server 1 to Server 2.

On Server 2:

$> mysql < dump.sql

Use of --databases with the mysqldump command line causes the dump file to include CREATE DATABASE and USE statements that create the database if it does exist and make it the default database for the reloaded data.

Alternatively, you can omit --databases from the mysqldump command. Then you need to create the database on Server 2 (if necessary) and to specify it as the default database when you reload the dump file.

On Server 1:

$> mysqldump db1 > dump.sql

On Server 2:

$> mysqladmin create db1
$> mysql db1 < dump.sql

You can specify a different database name in this case, so omitting --databases from the mysqldump command enables you to dump data from one database and load it into another.

How do I transfer data from one database to another database?

How do I copy a MySQL database?

How do I copy data from one table of one database to another table of another database in SQL?

How do I copy a table from one MySQL server to another?