# Migrate MySQL to Azure Database for MySQL online by using Azure DMS
## Introduction
[**Azure DMS (Database Migration Service)**](https://docs.microsoft.com/en-us/azure/dms/dms-overview) is a fully managed service designed to enable seamless migrations from multiple database sources to Azure data platforms with minimal downtime.
Azure DMS provides database **Online data migration** feature that DMS will create a connection make source MySQL database and target [**Azure database for MySQL**](https://docs.microsoft.com/en-us/azure/mysql/overview) keep in sync. When the source data are all migrated to the target database. Users can **Cutover** the workload to Azure Database for MySQL anytime.
## Scenario
In this lab, we will use Azure Virtual Machine and Virtual Network to simulate an on-premise environment. Next, we will use the MySQL function to insert some data into a new table. Then we will use Azure DMS to migrate the databases from an on-premises MySQL instance to Azure Database for MySQL with Online Migration. Finally, we can test the replication and cutover anytime.
> Note: Before cutover the database workload, you should confirm two things
> 1. Stop writing new data into the source database
> 2. Make sure all source data are already migrated into the target database
## Preparation
* An Azure account with Azure Subscription
## Step by step
### Step 1: Create and Connect to VM
1. Create a virtual machine
* Sign in to the [Azure portal](https://portal.azure.com/) if you haven't already.
* Type **virtual machines** in the search.
* Under **Services**, select **Virtual machines**.
* In the **Virtual machines** page, select **Add**. The **Create a virtual machine** page opens.
* In the **Basics** tab, under **Project details**, make sure the correct subscription is selected and then choose to **Create new** resource group. Type *myResourceGroup* for the name.
* Under **Instance details**, type *Test* for the **Virtual machine name**, choose *Korea South* for your **Region**, and choose Ubuntu 18.04 LTS for your **Image**. Leave the other defaults.
* Under **Administrator account**, select **Password**.
* Under **Inbound port rules** > **Public inbound ports**, choose **Allow selected ports** and then select **SSH (22)** and **HTTP (80)** and **SSH (443)** from the drop-down.
* Leave the remaining defaults and then select the **Review + create** button at the bottom of the page.
* On the **Create a virtual machine page**, you can see the details about the VM you are about to create. When you are ready, select **Create**.
* When the deployment is finished, select **Go to resource**.
* On the page for your new VM, select the public IP address and copy it to your clipboard.
2. Connect to the virtual machine
* Create an SSH connection with the VM.
### Step 2: Install MySQL in VM
1. Install MySQL
```
sudo apt update -y
sudo apt install mysql-server-5.7 -y
```
### Step 3: Register the Microsoft.DataMigration resource provider
1. Sign in to the Azure portal, select All services, and then select Subscriptions.
2. Select the subscription in which you want to create the instance of Azure Database Migration Service, and then select **Resource providers**.
3. Search for migration, and then to the right of **Microsoft.DataMigration**, select **Register**.
### Step 4: Create a Database Migration Service instance
1. In the Azure portal, select + **Create a resource**, search for Azure Database Migration Service, and then select **Azure Database Migration Service** from the drop-down list.
2. On the **Azure Database Migration Service** screen, select **Create**.
3. On the **Create Migration Service** screen, specify a name for the service, the subscription, and a new or existing resource group.
4. Select a pricing tier and move to the networking screen. Offline migration capability is available in both the Standard and Premium pricing tier.
5. Select an existing virtual network from the list or provide the name of the new virtual network to be created. Move to the review + create screen. Optionally you can add tags to the service using the tags screen.
> The virtual network provides Azure Database Migration Service with access to the source SQL Server and the target Azure SQL Database instance.
6. Review the configurations and select **Create** to create the service.
### Step 5: Create a migration project
After the service is created, locate it within the Azure portal, open it, and then create a new migration project.
1. In the Azure portal, select **All services**, search for Azure Database Migration Service, and then select **Azure Database Migration Services**.
2. Select your migration service instance from the search results and select + **New Migration Project**.
3. On the **New migration project** screen, specify a name for the project, in the **Source server type** selection box, select **MySQL**, in the **Target server type** selection box, select **Azure Database For MySQL** and in the **Migration activity type** selection box, select **Online data migration**. Select **Create and run activity**.
### Step 6: Create **Azure Database for MySQL servers**
1. In the Azure portal, select **All services**, search *Azure Database for MySQL Service*.
2. select + **New**.
3. In the **Basics** tab, under **Project details**, make sure the correct subscription is selected and then choose to **Create new** resource group.
4. Leave the remaining defaults and then select the **Review + create** button at the bottom of the page.
5. On the **Create MySQL Server** page, you can see the details about the MySQL Server you are about to create. When you are ready, select **Create**.
### Step 7: Login VM and finish configuration
1. MySQL configuration
```
sudo mysql_secure_installation
```
2. Firewall configuration
```
sudo ufw allow from remote_ip_address to any port 3306
```
3. Enable remote login by root
```
sudo mysql -u root -p
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '[yourpassword]';
FLUSH PRIVILEGES;
```
### Step 8: Create database and table, then insert some data into the table by MySQL function
1. Create database and table
* Type below SQL commands in the source MySQL database
```
CREATE DATABASE sample;
USE sample;
CREATE TABLE IF NOT EXISTS test ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, mail VARCHAR(255) NOT NULL, home VARCHAR(255) NOT NULL, messages VARCHAR(255) NOT NULL );
```
2. Create a mysql function to insert data
* Type below SQL commands in the source MySQL database
```
delimiter //
CREATE PROCEDURE pro1()
begin
declare v int;
set v=0;
repeat
insert into test(name,mail,home,messages) values ('eCloudture','info@ecloudture.com','www.ecloudture.com','Hi! I am eCloudture.');
set v=v+1;
until v=100
end repeat;
end
//
delimiter ;
CALL pro1();
SELECT * FROM test;
```
### Step 9: Enable mysql bin-log
1. Adjust MySQL configuration file
* Type below bash commands on the Linux VM
```
sudo vi /etc/mysql/my.cnf
```
* Add the following lines at the end of the file
```
[mysqld]
server_id=1
log-bin=binlog
max_binlog_size=100M
binlog_format=row
expire_logs_days=1
binlog_checksum=NONE
binlog_row_image=FULL
log_slave_updates=TRUE
```
2. Restart the MySQL service and log in to MySQL to test that binlog has been successfully enabled
```
sudo service mysql restart
sudo mysql -u root -p
show binary logs;
```
### Step 10: Migrate the database schema
1. Export the source database structure
```
mysqldump -u root -p[password] --databases sample --no-data > sample.sql
```
2. Import the database structure to the target database
```
mysql -h [SQL database for MySQL Endpoint] -u [username] -p[password] < sample.sql
```
### Step 11: Configure migration project
1. On the **Select source** screen, specify the connection details for the source MySQL instance, and select **Next: Select target>>**
2. On the **Select target** screen, specify the connection details for the target Azure Database for MySQL instance, and select **Next: Select databases>>**
3. On the **Select databases** screen, map the source and the target database for migration, and select **Next: Configure migration settings>>**.
4. On the **Configure migration settings** screen, select the tables to be part of the migration, and select **Next: Summary>>**.
5. On the Summary screen, in the Activity name text box, specify a name for the migration activity and review the summary to ensure that the source and target details match what you previously specified.
### Step 12: Monitor the migration
1. On the migration activity screen, select Refresh to update the display until the Status of the migration shows as Complete.
2. Under **Database Name**, select the specific database to get to the migration status for **Full data load** and **Incremental data sync operations**.
> Full data load will show the initial load migration status while Incremental data sync will show change data capture (CDC) status.
### Step 13: Perform migration cutover
* After the initial Full load is completed, the databases are marked **Ready to cutover**.
* When you're ready to complete the database migration, select **Start Cutover**.
* Make sure to stop all the incoming transactions to the source database; wait until the **Pending changes** counter shows **0**.
* Select **Confirm**, and then select **Apply**.
## Cleanup resources
* You can clean up the resources created in this quickstart by deleting the Azure **Resource Group**. To delete the resource group, navigate to the instance of the Azure Database Migration Service that you created. Select the Resource group name, and then select Delete resource group. This action deletes all assets in the resource group as well as the group itself.
## Conclusion
* Azure Database Migration Service integrates some of the functionality of our existing tools and services. It provides customers with a comprehensive, highly available solution.
* After this lab, you will learn:
* How to build a MySQL environment on Azure
* MySQL basic configurations and operations
* How to migrate MySQL schema by command **mysqldump**
* How to migrate the MySQL database by using Azure DMS
## Reference
* [Tutorial: Migrate MySQL to Azure Database for MySQL online using DMS
](https://docs.microsoft.com/en-us/azure/dms/tutorial-mysql-azure-mysql-online)
Sign in with Wallet
Connect another wallet