Author- Kriti Bhardwaj(Data Engineer)
In this documentation you will learn how to migrate On-premises MySQL to Azure database for MySQL using DMS. Here I have taken demo sample database to migrate with minimal downtime.
- Create Azure database for MySQL.
- Create a virtual machine.
- Create DMS.
- Install MySQL workbench.
- Both Azure database for MySQL and on-premises MySQL should be of same version. (i.e., 5.7 or above).
- Add client IP address and VM IP address to the Azure Database for MySQL.
- Add 443 outbound port and 3306 inbound port on virtual machine.
- Configure the DNS name of the VM.
Steps to follow:
- Download MySQL workbench on Virtual machine.
- Create a database and a table on your local host and fill some entries in the table.
- Create Azure database for MySQL using portal.
- Connect azure database for MySQL with Workbench.
- Go to the following directory on cmd.
- Write the following command on command prompt to give all the privileges to your user.
- Use mysqldump command to do schema migration.
-h: localhost (You can get this IP from VM)
- To import schema to Azure Database for MySQL target, run the following command:
-p: Password of your MySQL Server
- If you get the above collation error, then follow the below step:
- Click on the above red mark option
- Select the .sql file created before and open it.
- Now where ever ‘utf8mb4_0900_ai_ci’ is written, replace it with ‘utf8mb4_unicode_ci’ and save it .
- Now again run the above command
- Now, the schema is migrated to Azure database for MySQL.
- Create database migration service instance and then create a migration project on Azure Portal.
- Select source as MySQL and destination as Azure database for MySQL.
- On the Add Source Details screen, specify the connection details for the source MySQL instance. You have to add details of your on-premises MySQL server. Select Save
- Source server name: DNS name of your VM
- Server Port: 3306
- Username: username
- Password: Password
- On the Target details screen, specify the connection details for the target Azure Database for MySQL server, which is the pre-provisioned instance of Azure Database for MySQL to which the Demo schema was deployed by using mysqldump.
- Target Server name: servername.mysql.database.azure.com
- Username: mysqladminuser@servername
- Password: Password of your MySQL server
- Select Save, and then on the Map to target databases screen, map the source and the target database for migration.
If the target database contains the same database name as the source database, the Azure Database Migration Service selects the target database by default.
- Select Save, on the Migration summary screen, in the Activity name text box, specify a name for the migration activity, and then review the summary to ensure that the source and target details match what you previously specified. Select Run migration.
- The migration activity window appears, and the Status of the activity is pending. Refresh the page in sometime.
- The migration process is complete.
- Now check Azure database for MySQL on workbench if the data is migrated or not.
- The data is successfully migrated.