Skip to main content

Migrate/transfer data between MySQL DBaaS

let’s understand the steps to migrate MySQL databases. Below are the steps you can follow to migrate MySQL databases:

  1. Backup the Data of Old DBaaS
  2. Restore the Dump on New DBaaS

Step 1. Backup the Data

The first step to migrate MySQL database is to take a dump of the data that you want to transfer. This operation will help you move mysql database data from one DBaaS to another DBaaS. To do that, you will have to use mysqldump command. The basic syntax of the command is:

mysqldump -h [source host IP] -u [username] -p [database] > dump.sql

There are various options available for this command, let’s go through the major ones as per the use case.

a. Backing Up Specific Databases

This command dumps specified databases to the file.

mysqldump -u [username] -p [database] > dump.sql

You can specify multiple databases for the dump using the following command:

mysqldump -u [username] -p --databases [database1] [database2] > dump.sql

You can use the –all-databases option to backup all databases on the MySQL instance.

mysqldump -u [username] -p --all-databases > dump.sql

b. Backing Up Specific Tables

The above commands dump all the tables in the specified database, if you need to take backup of some specific tables, you can use the following command:

mysqldump -u [username] -p [database] [table1] [table2] > dump.sql

c. Custom Query

If you want to backup data using some custom query, you will need to use the where option provided by mysqldump.

mysqldump -u [username] -p [database] [table1] --where="WHERE CLAUSE" > dump.sql
Example: mysqldump -u root -p testdb table1 --where="mycolumn = myvalue" > dump.sql
Note

By default, mysqldump command includes DROP TABLE and CREATE TABLE statements in the created dump. Hence, if you are using incremental backups or you specifically want to restore data without deleting previous data, make sure you use the –no-create-info option while creating a dump.

mysqldump -u [username] -p [database] --no-create-info > dump.sql

If you need to just copy the schema but not the data, you can use –no-data option while creating the dump.

mysqldump -u [username] -p [database] --no-data > dump.sql

Step 2. Restore the Dump

The last step in MySQL migration is restoring the data on the destination DBaaS. MySQL command directly provides a way to restore to dump data to MySQL.

mysql -h [destination Host IP] -u [username] -p [database] < dump.sql

The data has been successfully migrated from Old DBaaS to New DBaaS.