Tutorial: Migrate MySQL to Azure Database for MySQL offline using DMS

  • Article

You can use Azure Database Migration Service to perform a seamless migration from your external MySQL instance to Azure Database for MySQL with high speed data migration capability. In this tutorial, we will migrate a sample database from an on-premises instance of MySQL 5.7 to Azure Database for MySQL (v5.7) by using an offline migration activity in Azure Database Migration Service. Although the articles assume the source to be a MySQL database instance and target to be Azure Database for MySQL, it can be used to migrate from one Azure Database for MySQL to another just by changing the source server name and credentials. Also, migration from lower version MySQL servers (v5.6 and above) to higher versions is also supported.

Note

For a PowerShell-based scriptable version of this migration experience, see scriptable offline migration to Azure Database for MySQL.

Amazon Relational Database Service (RDS) for MySQL and Amazon Aurora (MySQL-based) are also supported as sources for migration.

In this tutorial, you learn how to:

  • Create a DMS instance.
  • Create a MySQL migration project in DMS.
  • Migrate a MySQL schema using DMS.
  • Run the migration.
  • Monitor the migration.

Prerequisites

To complete this tutorial, you need to:

  • Have an Azure account with an active subscription. Create an account for trial.

  • Have an on-premises MySQL database with version 5.7. If not, then download and install MySQL community edition 5.7.

  • Create an Azure Virtual Network for Azure Database Migration Service by using 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.

    During virtual networkNet setup, if you use ExpressRoute with network peering to Azure, add the following service endpoints to the subnet in which the service will be provisioned:

    • Target database endpoint (for example, SQL endpoint, Azure Cosmos DB endpoint, and so on)
    • Storage endpoint
    • Service bus endpoint

    This configuration is necessary because Azure Database Migration Service lacks internet connectivity.

  • Ensure that your virtual network Network Security Group rules don't block the outbound port 443 of ServiceTag for ServiceBus, Storage and AzureMonitor. For more detail on virtual network NSG traffic filtering, see the article Filter network traffic with network security groups.

  • Open your Windows firewall to allow connections from Virtual Network for Azure Database Migration Service to access the source MySQL Server, which by default is TCP port 3306.

  • When using a firewall appliance in front of your source database(s), you might need to add firewall rules to allow connections from Virtual Network for Azure Database Migration Service to access the source database(s) for migration.

  • Create a server-level firewall rule or configure VNET service endpoints for target Azure Database for MySQL to allow Virtual Network for Azure Database Migration Service access to the target databases.

  • The source MySQL must be on supported MySQL community edition. To determine the version of MySQL instance, in the MySQL utility or MySQL Workbench, run the following command:

    SELECT @@VERSION;

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

  • The user must have the privileges to read data on the source database.

  • To complete a schema migration successfully, on the source server, the user performing the migration requires the following privileges:

    • "SELECT" privilege at the server level on the source.
    • If migrating views, user must have the "SHOW VIEW" privilege on the source server and the "CREATE VIEW" privilege on the target server.
    • If migrating triggers, user must have the "TRIGGER" privilege on the source and target server.
    • If migrating routines (procedures and/or functions), the user must have the "CREATE ROUTINE" and "ALTER ROUTINE" privileges granted at the server level on the target.
    • If migrating events, the user must have the "EVENT" privilege on the source and target server.
    • If migrating users/logins, the user must have the "CREATE USER" privilege on the target server.
    • "DROP" privilege at the server level on the target, in order to drop tables that might already exist. For example, when retrying a migration.
    • "REFERENCES" privilege at the server level on the target, in order to create tables with foreign keys.
    • If migrating to MySQL 8.0, the user must have the "SESSION_VARIABLES_ADMIN" privilege on the target server.
    • "CREATE" privilege at the server level on the target.
    • "INSERT" privilege at the server level on the target.
    • "UPDATE" privilege at the server level on the target.
    • "DELETE" privilege at the server level on the target.

Size the target Azure Database for MySQL instance

To prepare the target Azure Database for MySQL server for faster data loads using the Azure Database Migration Service, the following server parameters and configuration changes are recommended.

  • max_allowed_packet - set to 1073741824 (i.e. 1GB) to prevent any connection issues due to large rows.

  • slow_query_log - set to OFF to turn off the slow query log. This will eliminate the overhead caused by slow query logging during data loads.

  • query_store_capture_mode - set to NONE to turn off the Query Store. This will eliminate the overhead caused by sampling activities by Query Store.

  • innodb_buffer_pool_size - Innodb_buffer_pool_size can only be increased by scaling up compute for Azure Database for MySQL server. Scale up the server to 64 vCore General Purpose SKU from the Pricing tier of the portal during migration to increase the innodb_buffer_pool_size.

  • innodb_io_capacity & innodb_io_capacity_max - Change to 9000 from the Server parameters in Azure portal to improve the IO utilization to optimize for migration speed.

  • innodb_write_io_threads & innodb_write_io_threads - Change to 4 from the Server parameters in Azure portal to improve the speed of migration.

  • Scale up Storage tier - The IOPs for Azure Database for MySQL server increases progressively with the increase in storage tier.

    • In the Single Server deployment option, for faster loads, we recommend increasing the storage tier to increase the IOPs provisioned.
    • In the Flexible Server deployment option, we recommend you can scale (increase or decrease) IOPS irrespective of the storage size.
    • Storage size can only be scaled up, not down.

  • Select the compute size and compute tier for the target flexible server based on the source single server's pricing tier and VCores based on the detail in the following table.

    Single Server Pricing Tier Single Server VCores Flexible Server Compute Size Flexible Server Compute Tier
    Basic 1 1 General Purpose Standard_D16ds_v4
    Basic 1 2 General Purpose Standard_D16ds_v4
    General Purpose 1 4 General Purpose Standard_D16ds_v4
    General Purpose 1 8 General Purpose Standard_D16ds_v4
    General Purpose 16 General Purpose Standard_D16ds_v4
    General Purpose 32 General Purpose Standard_D32ds_v4
    General Purpose 64 General Purpose Standard_D64ds_v4
    Memory Optimized 4 Business Critical Standard_E4ds_v4
    Memory Optimized 8 Business Critical Standard_E8ds_v4
    Memory Optimized 16 Business Critical Standard_E16ds_v4
    Memory Optimized 32 Business Critical Standard_E32ds_v4

    1 For the migration, select General Purpose 16 vCores compute for the target flexible server for faster migrations. Scale back to the desired compute size for the target server after migration is complete by following the compute size recommendation in the Performing post-migration activities section later in this article.

Once the migration is complete, you can revert back the server parameters and configuration to values required by your workload.

Set up DMS

With your target flexible server deployed and configured, you next need to set up DMS to migrate your single server to a flexible server.

Register the resource provider

To register the Microsoft.DataMigration resource provider, perform the following steps.

  1. Before creating your first DMS instance, sign in to the Azure portal, and then search for and select Subscriptions.

    Screenshot of a Select subscriptions from Azure Marketplace.

  2. Select the subscription that you want to use to create the DMS instance, and then select Resource providers.

    Screenshot of a Select Resource Provider.

  3. Search for the term "Migration", and then, for Microsoft.DataMigration, select Register.

    Screenshot of a Register your resource provider.

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 dropdown list.

    Screenshot of Azure Marketplace.

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

    Screenshot of 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 a pricing tier and move to the networking screen. Offline migration capability is available only on the Premium pricing tier.

    For more information on costs and pricing tiers, see the pricing page.

    Screenshot of Configure Azure Database Migration Service basic settings.

  5. Select an existing virtual network from the list or provide the name of 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.

    Screenshot of Configure Azure Database Migration Service network settings.

    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. Review the configurations and select Create to create the service.

    Screenshot of Azure Database Migration Service create.

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.

    Screenshot of Locate all instances of Azure Database Migration Service.

  2. Select your migration service instance from the search results and select + New Migration Project.

    Screenshot of Create a 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 Data migration. Select Create and run activity.

    Screenshot of Create Database Migration Service Project.

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

Configure migration project

  1. On the Select source screen, specify the connection details for the source MySQL instance, and select Next : Select target>>

    Screenshot of Add source details screen.

  2. On the Select target screen, specify the connection details for the target Azure Database for MySQL instance, and select Next : Select databases>>

    Screenshot of Add target details screen.

  3. On the Select databases screen, map the source and the target database for migration, and select Next : Configure migration settings>>. You can select the Make Source Server Read Only option to make the source as read-only, but be cautious that this is a server level setting. If selected, it sets the entire server to read-only, not just the selected databases.

    If the target database contains the same database name as the source database, Azure Database Migration Service selects the target database by default.

    Screenshot of Select database details screen.

  4. On the Configure migration settings screen, select the tables to be part of migration, and select Next : Summary>>. If the target tables have any data, they aren't selected by default but you can explicitly select them and they will be truncated before starting the migration.

    Screenshot of Select tables screen.

  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.

    Screenshot of Migration project summary.

  6. Select Start migration. The migration activity window appears, and the Status of the activity is Initializing. The Status changes to Running when the table migrations start.

    Screenshot of Running migration.

Monitor the migration

  1. On the migration activity screen, select Refresh to update the display and see progress about number of tables completed.

  2. You can select the database name on the activity screen to see the status of each table as they are getting migrated. Select Refresh to update the display.

    Screenshot of Monitoring migration.

Complete the migration

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

    Screenshot of Complete migration.

Post migration activities

Migration cutover in an offline migration is an application dependent process which is out of scope for this document, but following post-migration activities are prescribed:

  1. Create logins, roles and permissions as per the application requirements.
  2. Recreate all the triggers on the target database as extracted during the pre-migration step.
  3. Perform sanity testing of the application against the target database to certify the migration.

Clean up resources

If you're not going to continue to use the Database Migration Service, then you can delete the service with the following steps:

  1. In the Azure portal, select All services, search for Azure Database Migration Service, and then select Azure Database Migration Services.

    Screenshot of Locate all instances of DMS.

  2. Select your migration service instance from the search results and select Delete Service.

    Screenshot of Delete the migration service.

  3. On the confirmation dialog, type in the name of the service in the TYPE THE DATABASE MIGRATION SERVICE NAME textbox and select Delete.

    Screenshot of Confirm migration service delete.

Related content