Important: Remember to stop any virtual machines used in these labs when you no longer need them – this will minimize the Azure credit incurred for these services. When you have completed all of the labs, consider deleting the resource group you created if you don’t plan to experiment with it any further.

Step by Step guide MySQL to Azure DB for MySQL

Step 1 (Create/Setup Local Machine With MySQL Database )

Create a VM from Azure Portal
Connect with RDP
Download Chrome
Download and Install fom https://www.microsoft.com/en-sg/download/details.aspx?id=40784
Download and Install from https://support.microsoft.com/en-us/help/2977003/the-latest-supported-visual-c-downloads
Download and Install MySQL 5.7 Community Server (https://dev.mysql.com/downloads/windows/installer/5.7.html) and Workbench
Setup Environment Variable; so that mysql command can execute from CLI
Download Azure Lab File (https://github.com/MicrosoftLearning/DP-070-Migrate-Open-Source-Workloads-to-Azure)
Go to migration_samples/setup/mysql/adventureworks

MySQL Tweaks (If Needed)

SELECT user,authentication_string,plugin,host FROM mysql.user;

ALTER USER ‘root’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘password’;

FLUSH PRIVILEGES;

SELECT user,authentication_string,plugin,host FROM mysql.user;

exit

mysql -u root -p CREATE USER ‘azureuser’@’localhost’ IDENTIFIED BY ‘Pa55w.rd’;
GRANT ALL PRIVILEGES ON *.* TO ‘azureuser’@’localhost’ WITH GRANT OPTION;

Populate/Create On-prem Databases and Tables

GOTO /migration_samples/setup/mysql/adventureworks
mysql –user=azureuser –password=Pa55w.rd < adventureworks.sql

Review the on-premises database

In the MySQLWorkbench window, Expand adventureworks, and then expand Tables.
Right-click the contact table, click Select Rows – Limit 1000, and then, in the contact query window, click Limit to 1000 rows and click Don’t Limit.
Click Execute to run the query. It should return 19972 rows.
In the list of tables, right-click the Employee table, and click Select rows.
Click Execute to run the query. It should return 290 rows.

STEP 2

Dump the DB for Offline Migration

mysqldump -u azureuser -pPa55w.rd adventureworks > aw_mysql_backup.sql

STEP 3

Migrate the on-premises database to an Azure SQL Database for MySQL

Create Azure Database for MySQL (Azure Portal)
Connection Security > Add Firewall Rules > Add Local Machine IP
Allow Access to Azure Services’ > Yes

STEP 4

Open Cloud Shell prompt from Azure Portal, run the following command to create a new database in your Azure Database for MySQL service.

az MySQL db create \
–name adventureworksoffline \

–server-name adventureworks[nnn] \

–resource-group [resource group]

STEP 5

Back to Local Machine (Azure VM where MySQL has been install and Populated)

Execute this command from Lacal Machine:

mysql DB_NAME -h AZURE_HOST -u AZURE_UNAME -p < DUMP_FILE.SQL

Example :

mysql adventureworks_offline -h demomysql001.mysql.database.azure.com -u mysql@demomysql001 -p < aw_mysql_backup.sql

Review Azure database

Connect to Azure DB for MySQL (From Local Machine)

mysql -h AZURE_HOST -u AZURE_UNAME -pAZURE_PASS adventureworks_offline

Example:

mysql -h demomysqlmigration.mysql.database.azure.com -u arifmysql@demomysqlmigration -p adventureworks_offline
SELECT COUNT(*) FROM specialoffer;
Verify that this query returns 16 rows.
SELECT COUNT(*) FROM vendor;
This table should contain 104 rows.

This is how we can do Offline Migration

STEP 6

Perform an online migration to Azure Database for MySQL

Switch back to the Azure portal.
Click All services, click Subscriptions, and then click your subscription.
On your subscription page, under Settings, click Resource providers.
In the Filter by name box, type DataMigration, and then click Microsoft.DataMigration.
If the Microsoft.DataMigration isn’t registered, click Register, and wait for the Status to change to Registered. It might be necessary to click Refresh to see the status change.
Click Create a resource, in the Search the Marketplace box type Azure Database Migration Service, and then press Enter.
On the Azure Database Migration Service page, click Create.
Create Premium Tier

Migrate Sample Schema & Upload to a New Database on Azure DB for MySQL

mysqldump -u azureuser -pPa55w.rd adventureworks –no-data > aw_mysql_no_data.sql
mysql -h AZURE_HOST -u AZURE_UNAME -p AZURE_NEWDBNAME < aw_mysql_no_data.sql

Example

mysql -h demomysqlmigration.mysql.database.azure.com -u arifmysql@demomysqlmigration -p azureadventureworks_temp < aw_mysql_no_data.sql

STEP 7

Use Azure Database Migration Service and Create New Project
New migration project page, set the source server type to MySQL, set the target server type to Azure Database for MySQL, and select Online data migration.
Click Create and Run Activity
On the Add Source Details page, add the address of the source server, the source database, and an account that can connect to this database to retrieve the data. The account must have SUPERUSER
privileges to perform migration.
On the Target details page, specify the address of your Azure Database for MySQL service, the database into which you want to migrate the data, and the details of an account that has administrative rights
On the Map to target databases page, select the source database and target database. You can migrate a single database or multiple databases
On the Migration settings page, specify any additional settings that you want to configure, such as the maximum number of tables to load in parallel.
On the Migration summary page, enter a name for the activity, and then select Run migration.
If you’re doing an online migration, the status changes to Ready to cutover when the existing data has been transferred. However, the activity continues running, to transfer any additional changes that appear while applications are still actively using the original database.

This is how we can do Online Migration

DP-070T00-A

Migrate Open Source Data Workloads to Azure

Course Outline

Module 1: Introduction to open source database migration on Azure

Module 2: Migrate on-premises MySQL to Azure Database for MySQL

Module 3: Migrate on-premises PostgreSQL to Azure Database for PostgreSQL

Module 4: Protecting monitoring and tuning

Azure Services

Azure Database Services for OSS

Azure Database for MySQL

Azure Database for PostgreSQL

Azure Database Documentation

Official Guide Book

Lab Files