Tutorial: Migrate RDS MySQL to Azure Database for MySQL online using DMS

You can use Azure Database Migration Service to migrate databases from an RDS MySQL instance to Azure Database for MySQL while the source database remains online during migration. In other words, migration can be achieved with minimal downtime to the application. In this tutorial, you migrate the Employees sample database from an instance of RDS MySQL to Azure Database for MySQL by using the online migration activity in Azure Database Migration Service.

In this tutorial, you learn how to:

  • Migrate the sample schema by using the mysqldump and mysql utilities.
  • Create an instance of Azure Database Migration Service.
  • Create a migration project by using Azure Database Migration Service.
  • Run the migration.
  • Monitor the migration.

Note

Using Azure Database Migration Service to perform an online migration requires creating an instance based on the Premium pricing tier. For more information, see the Azure Database Migration Service pricing page.

Important

For an optimal migration experience, Microsoft recommends creating an instance of the Azure Database Migration Service in the same Azure region as the target database. Moving data across regions or geographies can slow down the migration process and introduce errors.

Tip

When you migrate databases to Azure by using Azure Database Migration Service, you can do an offline or an online migration. With an offline migration, application downtime starts when the migration starts. With an online migration, downtime is limited to the time to cut over at the end of migration. We suggest that you test an offline migration to determine whether the downtime is acceptable; if not, do an online migration.

This article describes how to perform an online migration from an instance of RDS MySQL to Azure Database for MySQL.

Prerequisites

To complete this tutorial, you need to:

  • Ensure that the source MySQL server is running a supported MySQL community edition. To determine the version of your MySQL instance, in the mysql utility or MySQL Workbench, run the command:

    SELECT @@version;
    

    For more information, see the article Supported Azure Database for MySQL versions.

  • Download and install the MySQL Employees sample database.

  • Create an instance of Azure Database for MySQL.

  • Create a Microsoft Azure Virtual Network for Azure Database Migration Service by using the Azure Resource Manager deployment model, which provides site-to-site connectivity to your on-premises source servers by using either ExpressRoute or VPN. For more information about creating a virtual network, see the Virtual Network Documentation, and especially the quickstart articles with step-by-step details.

  • Ensure that your virtual network Network Security Group rules don't block the following inbound communication ports to Azure Database Migration Service: 443, 53, 9354, 445, and 12000. For more detail on virtual network NSG traffic filtering, see the article Filter network traffic with network security groups.

  • Configure your Windows Firewall (or your Linux firewall) to allow for database engine access. For MySQL server, allow port 3306 for connectivity.

Note

Azure Database for MySQL only supports InnoDB tables. To convert MyISAM tables to InnoDB, please see the article Converting Tables from MyISAM to InnoDB .

Set up AWS RDS MySQL for replication

  1. To create a new parameter group, follow the instructions provided by AWS in the article MySQL Database Log Files, in the Binary Logging Format section.
  2. Create a new parameter group with the following configuration:
    • log_bin = ON
    • binlog_format = row
    • binlog_checksum = NONE
  3. Save the new parameter group.
  4. Associate the new parameter group with the RDS MySQL instance. A reboot might be required.
  5. Once the parameter group is in place, connect to the MySQL instance and set binlog retention to at least 5 days.
call mysql.rds_set_configuration('binlog retention hours', 120);

Migrate the schema

  1. Extract the schema from the source database and apply to the target database to complete migration of all database objects such as table schemas, indexes, and stored procedures.

    The easiest way to migrate only the schema is to use mysqldump with the --no-data parameter. The command to migrate the schema is:

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

    For example, to dump a schema file for the Employees database, use the following command:

    mysqldump -h 10.10.123.123 -u root -p --databases employees --no-data > d:\employees.sql
    
  2. Import the schema to target service, which is Azure Database for MySQL. To restore the schema dump file, run the following command:

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

    For example, to import the schema for the Employees database:

    mysql.exe -h shausample.mysql.database.azure.com -u dms@shausample -p employees < d:\employees.sql
    
  3. If you have foreign keys in your schema, the initial load and continuous sync of the migration will fail. To extract the drop foreign key script and add foreign key script at the destination (Azure Database for MySQL), run the following script in MySQL Workbench:

    SET group_concat_max_len = 8192;
        SELECT SchemaName, GROUP_CONCAT(DropQuery SEPARATOR ';\n') as DropQuery, GROUP_CONCAT(AddQuery SEPARATOR ';\n') as AddQuery
        FROM
        (SELECT
        KCU.REFERENCED_TABLE_SCHEMA as SchemaName,
                    KCU.TABLE_NAME,
                    KCU.COLUMN_NAME,
                    CONCAT('ALTER TABLE ', KCU.TABLE_NAME, ' DROP FOREIGN KEY ', KCU.CONSTRAINT_NAME) AS DropQuery,
        CONCAT('ALTER TABLE ', KCU.TABLE_NAME, ' ADD CONSTRAINT ', KCU.CONSTRAINT_NAME, ' FOREIGN KEY (`', KCU.COLUMN_NAME, '`) REFERENCES `', KCU.REFERENCED_TABLE_NAME, '` (`', KCU.REFERENCED_COLUMN_NAME, '`) ON UPDATE ',RC.UPDATE_RULE, ' ON DELETE ',RC.DELETE_RULE) AS AddQuery
                    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU, information_schema.REFERENTIAL_CONSTRAINTS RC
                    WHERE
                      KCU.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
                      AND KCU.REFERENCED_TABLE_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA
      AND KCU.REFERENCED_TABLE_SCHEMA = 'SchemaName') Queries
      GROUP BY SchemaName;
    
  4. Run the drop foreign key (which is the second column) in the query result to drop the foreign key.

Note

Azure DMS does not support the CASCADE referential action, which helps to automatically delete or update a matching row in the child table when a row is deleted or updated in the parent table. For more information, in the MySQL documentation, see the Referential Actions section of the article FOREIGN KEY Constraints. Azure DMS requires that you drop foreign key constraints in the target database server during the initial data load, and you cannot use referential actions. If your workload depends on updating a related child table via this referential action, we recommend that you perform a dump and restore instead.

  1. If you have triggers (insert or update trigger) in the data, it will enforce data integrity in the target before replicating data from the source. The recommendation is to disable triggers in all the tables at the target during migration, and then enable the triggers after migration is complete.

    To disable triggers in target database:

    select concat ('alter table ', event_object_table, ' disable trigger ', trigger_name)
    from information_schema.triggers;
    
  2. If there are instances of the ENUM data type in any tables, we recommend temporarily updating to the ‘character varying’ datatype in the target table. WHen data replication is complete, then revert the data type to ENUM.

Register the Microsoft.DataMigration resource provider

  1. Sign in to the Azure portal, select All services, and then select Subscriptions.

    Show portal subscriptions

  2. Select the subscription in which you want to create the instance of Azure Database Migration Service, and then select Resource providers.

    Show resource providers

  3. Search for migration, and then to the right of Microsoft.DataMigration, select Register.

    Register resource provider

Create an instance of Azure Database Migration Service

  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.

    Azure Marketplace

  2. On the Azure Database Migration Service screen, select Create.

    Create Azure Database Migration Service instance

  3. On the Create Migration Service screen, specify a name for the service, the subscription, and a new or existing resource group.

  4. Select the location in which you want to create the instance of Azure Database Migration Service.

  5. Select an existing virtual network or create a new one.

    The virtual network provides Azure Database Migration Service with access to the source MySQL instance and the target Azure Database for MySQL instance.

    For more information about how to create a virtual network in the Azure portal, see the article Create a virtual network using the Azure portal.

  6. Select a pricing tier; for this online migration, be sure to select the Premium: 4vCores pricing tier.

    Configure Azure Database Migration Service instance settings

  7. Select Create to create the service.

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.

    Locate all instances of the Azure Database Migration Service

  2. On the Azure Database Migration Services screen, search for the name of the Azure Database Migration Service instance that you created, and then select the instance.

    Locate your instance of the Azure Database Migration Service

  3. Select + New Migration Project.

  4. On the New migration project screen, specify a name for the project, in the Source server type text box, select MySQL, and then in the Target server type text box, select AzureDbForMySQL.

  5. In the Choose type of activity section, select Online data migration.

    Important

    Be sure to select Online data migration; offline migrations are not supported for this scenario.

    Create Database Migration Service Project

    Note

    Alternately, you can choose Create project only to create the migration project now and execute the migration later.

  6. Select Save.

  7. Select Create and run activity to create the project and run the migration activity.

    Note

    Please make a note of the pre-requisites needed to set up online migration in the project creation blade.

Specify source details

  • On the Migration source detail screen, specify the connection details for the source MySQL instance.

    Source Details

Specify target details

  1. Select Save, and then on the Target details screen, specify the connection details for the target Azure Database for MySQL server, which is pre-provisioned and has the Employees schema deployed using MySQLDump.

    Select Target

  2. 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, Azure Database Migration Service selects the target database by default.

    Map to target databases

  3. 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.

    Migration Summary

Run the migration

  • Select Run migration.

    The migration activity window appears, and the Status of the activity is Initializing.

Monitor the migration

  1. On the migration activity screen, select Refresh to update the display until the Status of the migration shows as Running.

    Activity Status - running

  2. Under DATABASE NAME, select a specific database to get to the migration status for Full data load and Incremental data sync operations.

    Full data load shows the initial load migration status, while Incremental data sync shows change data capture (CDC) status.

    Inventory screen - full data load

    Inventory screen - incremental data sync

Perform migration cutover

After the initial Full load is completed, the databases are marked Ready to Cutover.

  1. When you're ready to complete the database migration, select Start Cutover.

    Start cut over

  2. Make sure to stop all the incoming transactions to the source database; wait until the Pending changes counter shows 0.

  3. Select Confirm, and the select Apply.

  4. When the database migration status shows Completed, connect your applications to the new target Azure Database for MySQL database.

Your online migration of an on-premises instance of MySQL to Azure Database for MySQL is now complete.

Next steps