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:
Backup the Data of Old DBaaS
Restore the Dump on New DBaaS
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
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.