IFI Techsolutions - Microsoft Gold Partner

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.

Pre-requisites:

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.

C Drive-> program files -> MySQL -> MySQL server 8.0 -> bin

Write the following command on command prompt to give all the privileges to your user.

mysql> CREATE USER 'monty'@'localhost' IDENTIFIED BY 'some_pass';

mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost'
->     WITH GRANT OPTION;
mysql> CREATE USER 'monty'@'%' IDENTIFIED BY 'some_pass';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%'
->     WITH GRANT OPTION;

Use mysqldump command to do schema migration.

-h: localhost (You can get this IP from VM)

-u: username

-p: Password

–databases: demo

mysqldump -h [localhost] -u [username] -p[password] --databases [db name] --no-data > [schema file path]

Example:

mysqldump -h localhost -u username -p --databases demo --no-data > C:\migration\sample.sql

To import schema to Azure Database for MySQL target, run the following command:

-h: servername.mysql.database.azure.com

-u: mysqladminuser@servername

-p: Password of your MySQL Server

–databases: demo

mysql -h [servername] -u [username] -p[password] [database]< [schema file path]

Example:

mysql -h mydemoservernm.mysql.database.azure.com -u myadmin@mydemoservernm -p demo < C:\migration\sample.sql

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.