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 220.127.116.11. 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 18.104.22.168 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 firstname.lastname@example.org:/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.