How to Copy/Transfer Data from One Database to Another in MySQL

MySQL is a popular database used by many organizations & developers. Sometimes, while working with MySQL, you may need to migrate a database from one server to another, for the sake of replication, or testing or cloning. You may need to copy/transfer database in order to create a separate database for the purpose of reporting. In this article, we will learn how to copy/transfer data from one database to another in MySQL.

How to Copy/Transfer Data from One Database to Another in MySQL

There are three steps to copy/transfer data from one database to another in MySQL.

1. Backup MySQL database

First step is to backup your MySQL database using mysqldump command. Let us say you want to copy database sales, then open terminal on the server where your database is located and run the following command to take a backup of the database. Replace sales with your database name and username with your database username.

$ sudo mysqldump -u username -p sales > sales_dump.sql

You will be prompted for your MySQL user’s password before processing begins.

If you want to take backup of remote database then add its host IP address with -h option.

$ sudo mysqldump -h host_ip_address_or_url -u username -p sales > sales_dump.sql

If you want to backup multiple databases, use –databases option. Here is the command to backup sales and orders databases.

$ sudo mysqldump -u username -p --databases sales orders > sales_dump.sql

If you want to backup all databases on your server, use –all-databases option.

$ sudo mysqldump -u username -p --all-databases > sales_dump.sql

If you want to backup only specific tables, mention it after the database name in MySQLdump command. Here is the command to backup tables users and products from sales database.

$ sudo mysqldump -u username -p sales users products > sales_dump.sql

You can even save query results as .sql files.

$ sudo mysqldump -u username -p sales users --where="WHERE CLAUSE" > dump.sql

2. Transfer backup to another database server

You can use any of the file transfer programs to copy .sql files between two servers. You need to run these commands from the source server where you have created the data dump file.

We will use scp command for this purpose.

$ Scp -P [port] [dump_file].sql [username]@[servername]:[path on destination]

Here is the command to transfer file sales_dump.sql to ip 54.43.32.21. But it is important that you have SSH access on this server, since you will need to enter username & password for access. Also, you need to have write access to the destination folder to be able to copy files to it. Replace 54.43.32.21 with IP address of destination server, root with your username on that server, and /home/ubuntu/sales with the folder location where you want the file to be saved.

$ scp sales_dump.sql root@54.43.32.21:/home/ubuntu/sales

3. Restore data dump

The last step is to create a new database from the data dump file. First, log into MySQL, create an empty database on the destination server, and exit it.

mysql> create database testdb;
mysql> exit

Run the following command to copy data from .sql dump file to the new database. Here is the syntax for it.

$ sudo mysql -u [username] -p [database] < [dump_file].sql

Here is the example.

$ sudo mysql -u root -p testdb < sales_dump.sql

If your dump file contains multiple databases, don’t specify the database name in the above command.

$ sudo mysql -u root -p < sales_dump.sql

Once the copy is complete, you can log into MySQL and access the new database as any other database.

mysql> use testdb;

In this article, we have learnt how to copy/transfer data from one database to another in MySQL.

How to Copy/Transfer Data from One Database to Another in MySQL