Tutorial: Migrate Azure Database for MySQL - Single Server to Flexible Server online using DMS via the Azure portal

You can migrate an instance of Azure Database for MySQL – Single Server to Azure Database for MySQL – Flexible Server by using Azure Database Migration Service (DMS), a fully managed service designed to enable seamless migrations from multiple database sources to Azure data platforms. In this tutorial, we perform an online migration of a sample database from an Azure Database for MySQL single server to a MySQL flexible server (both running version 5.7) using a DMS migration activity.

In this tutorial, you learn how to:

Prerequisites

To complete this tutorial, you need to:

• Create or use an existing instance of Azure Database for MySQL – Single Server (the source server).

• To complete the online migration successfully, ensure that the following prerequisites are in place:

  • Use the MySQL command line tool of your choice to verify that log_bin is enabled on the source server by running the command: SHOW VARIABLES LIKE 'log_bin'. If log_bin isn't enabled, create a read replica for your Single Server instance and then delete it. This operation will set the parameter log_bin to ON and you can then trigger the migration operation.
  • Ensure that the user has "REPLICATION CLIENT" and "REPLICATION SLAVE" permissions on the source server for reading and applying the bin log.
  • If you're targeting a online migration, configure the binlog_expire_logs_seconds parameter on the source server to ensure that binlog files aren't purged before the replica commits the changes. We recommend at least two days to start. After a successful cutover, you can reset the value.

• 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. Also triggers are only migrated during cutover, you should eb able to see the triggers created post cutover completes successfully.
  • 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.

Limitations

As you prepare for the migration, be sure to consider the following limitations.

• When migrating non-table objects, DMS doesn't support renaming databases.

• When migrating to a target server with bin_log enabled, be sure to enable log_bin_trust_function_creators to allow for creation of routines and triggers.

• Currently, DMS doesn't support migrating the DEFINER clause for objects. All object types with definers on the source are dropped and after the migration, the default definer for all objects that support a definer clause and that are created during schema migration, will be set to the login used to run the migration.

• Currently, DMS only supports migrating a schema as part of data movement. If nothing is selected for data movement, the schema migration won't occur. Selecting a table for schema migration also selects it for data movement.

• Online migration support is limited to the ROW binlog format.

• Online migration now supports DDL statement replication when migrating to a v8.0 or v5.7 Azure Database for MySQL Flexible Server target server.

  • Statement replication is supported for databases, tables, and schema objects (views, routines, triggers) selected for schema migration when configuring an Azure DMS migration activity. Data definition and administration statements for databases, tables, and schema objects that aren't selected won't be replicated. Selecting an entire server for migration will replicate statements for any tables, databases, and schema objects that are created on the source server after the initial load has completed.
  • Azure DMS statement replication supports all of the Data Definition statements listed here, with the exception of the following commands:
    • LOGFILE GROUP statements
    • SERVER statements
    • SPATIAL REFERENCE SYSTEM statements
    • TABLESPACE statements
  • Azure DMS statement replication supports all of the Data Administration – Account Management statements listed here, with the exception of the following commands:
    • SET DEFAULT ROLE
    • SET PASSWORD
  • Azure DMS statement replication supports all of the Data Administration – Table Maintenance statements listed here, with the exception of the following commands:
    • REPAIR TABLE
    • ANALYZE TABLE
    • CHECKSUM TABLE

Best practices for creating a flexible server for faster data loads using DMS

DMS supports cross-region, cross-resource group, and cross-subscription migrations, so you're free to select appropriate region, resource group and subscription for your target flexible server. Before you create your target flexible server, consider the following configuration guidance to help ensure faster data loads using DMS.

• 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

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

• The MySQL version for the target flexible server must be greater than or equal to that of the source single server.

• Unless you need to deploy the target flexible server in a specific zone, set the value of the Availability Zone parameter to 'No preference'.

• For network connectivity, on the Networking tab, if the source single server has private endpoints or private links configured, select Private Access; otherwise, select Public Access.

• Copy all firewall rules from the source single server to the target flexible server.

• Copy all the name/value tags from the single to flex server during creation itself.

Create and configure the target flexible server

With these best practices in mind, create your target flexible server, and then configure it.

• Create the target flexible server. For guided steps, see the quickstart Quickstart: Create an instance of Azure Database for MySQL with the Azure portal.

• Configure the new target flexible server as follows:

  • The user performing the migration requires the following permissions:
    • Ensure that the user has "REPLICATION_APPLIER" or "BINLOG_ADMIN" permission on target server for applying the bin log.
    • Ensure that the user has "REPLICATION SLAVE" permission on target server.
    • Ensure that the user has "REPLICATION CLIENT" and "REPLICATION SLAVE" permission on source server for reading and applying the bin log.
    • To create tables on the target, the user must have the "CREATE" privilege.
    • If migrating a table with "DATA DIRECTORY" or "INDEX DIRECTORY" partition options, the user must have the "FILE" privilege.
    • If migrating to a table with a "UNION" option, the user must have the "SELECT," "UPDATE," and "DELETE" privileges for the tables you map to a MERGE table.
    • If migrating views, you must have the "CREATE VIEW" privilege. Keep in mind that some privileges might be necessary depending on the contents of the views. Refer to the MySQL docs specific to your version for "CREATE VIEW STATEMENT" for details.
    • If migrating events, the user must have the "EVENT" privilege.
    • If migrating triggers, the user must have the "TRIGGER" privilege.
    • If migrating routines, the user must have the "CREATE ROUTINE" privilege.
  • Configure the server parameters on the target flexible server as follows:
    • Set the TLS version and require_secure_transport server parameter to match the values on the source server.
    • Set the sql_mode server parameter to match the values on the source server.
    • Configure server parameters on the target server to match any non-default values used on the source server.
    • To ensure faster data loads when using DMS, configure the following server parameters as described.
      • max_allowed_packet – set to 1073741824 (i.e., 1 GB) 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.
      • 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 - Change to 4 from the Server parameters in Azure portal to improve the speed of migration.
  • Configure the replicas on the target server to match those on the source server.
  • Replicate the following server management features from the source single server to the target flexible server:
    • Role assignments, Roles, Deny Assignments, classic administrators, Access Control (IAM)
    • Locks (read-only and delete)
    • Alerts
    • Tasks
    • Resource Health Alerts

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.

   

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

   

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

   

Create a Database Migration Service (DMS) instance

1. In the Azure portal, select + Create a resource, search for the term "Azure Database Migration Service", and then select Azure Database Migration Service from the dropdown list.

   

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

   

3. On the Select migration scenario and Database Migration Service page, under Migration scenario, select Azure Database for MySQL-Single Server as the source server type, and then select Azure Database for MySQL as target server type, and then select Select.

   

4. On the Create Migration Service page, on the Basics tab, under Project details, select the appropriate subscription, and then select an existing resource group or create a new one.

5. Under Instance details, specify a name for the service, select a region, and then verify that Azure is selected as the service mode.

6. To the right of Pricing tier, select Configure tier.

   

7. On the Configure page, select the Premium pricing tier with 4 vCores for your DMS instance, and then select Apply.

   DMS Premium 4-vCore is free for 6 months (183 days) from the DMS service creation date before incurring any charges. For more information on DMS costs and pricing tiers, see the pricing page.

   

   Next, we need to specify the VNet that will provide the DMS instance with access to the source single server and the target flexible server.

8. On the Create Migration Service page, select Next : Networking >>.

9. On the Networking tab, select an existing VNet from the list or provide the name of new VNet to create, and then select Review + Create.

   For more information, see the article Create a virtual network using the Azure portal..

   

   [!IMPORTANT]
   Your VNet must be configured with access to both the source single server and the target flexible server, so be sure to:

   • Create a server-level firewall rule or configure VNET service endpoints for both the source and target Azure Database for MySQL servers to allow the VNet for Azure Database Migration Service access to the source and target databases.

   • Ensure that your VNet Network Security Group (NSG) rules don't block the outbound port 443 of ServiceTag for ServiceBus, Storage, and Azure Monitor. For more information about VNet NSG traffic filtering, see Filter network traffic with network security groups.

   Note

   To add tags to the service, advance to the Tags tab by selecting Next : Tags. Adding tags to the service is optional.

10. Navigate to the Review + create tab, review the configurations, view the terms, and then select Create.

    

    Deployment of your instance of DMS now begins. The message Deployment is in progress appears for a few minutes, and then the message changes to Your deployment is complete.

11. Select Go to resource.

    

12. Identify the IP address of the DMS instance from the resource overview page and create a firewall rule for your source single server and target flexible server allow-listing the IP address of the DMS instance.

Create a migration project

To create a migration project, perform the following steps.

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

   

2. In the search results, select the DMS instance that you created, and then select + New Migration Project.

   

3. On the New migration project page, specify a name for the project, in the Source server type selection box, select Azure Database For MySQL – Single Server, in the Target server type selection box, select Azure Database For MySQL - Flexible Server, in the Migration activity type selection box, select Online data migration, and then select Create and run activity.

   Note

   Selecting Create project only as the migration activity type will only create the migration project; you can then run the migration project at a later time.

   

Configure the migration project

To configure your DMS migration project, perform the following steps.

1. On the Select source screen, locate the server based on the subscription, location, and resource group. The user name is auto populated, then provide the password for the source server.

   

2. Select Next : Select target>>, and then, on the Select target screen, locate the server based on the subscription, location, and resource group. The user name is auto populated, then provide the password for the target flexible server.

   

3. Select Next : Select databases>>, and then, on the Select databases tab, under Server migration options, select Migrate all applicable databases or under Select databases select the server objects that you want to migrate.

   Note

   There's now a Migrate all applicable databases option when selected, this option will migrate all user created databases and tables. Because Azure Database for MySQL - Flexible Server doesn't support mixed case databases, mixed case databases on the source will not be included for an online migration.

   

4. In the Select databases section, under Source Database, select the database(s) to migrate.

   The non-table objects in the database(s) you specified will be migrated, while the items you didn't select will be skipped. You can only select the source and target databases whose names match that on the source and target server.

   If you select a database on the source server that doesn't exist on the target server, it will be created on the target server.

5. Select Next : Select tables>> to navigate to the Select tables tab.

   Before the tab populates, DMS fetches the tables from the selected database(s) on the source and target and then determines whether the table exists and contains data.

6. Select the tables that you want to migrate.

   If the selected source table doesn't exist on the target server, the online migration process will ensure that the table schema and data is migrated to the target server.

   

   DMS validates your inputs, and if the validation passes, you'll be able to start the migration.

7. After configuring for schema migration, select Review and start migration.

   Note

   You only need to navigate to the Configure migration settings tab if you're trying to troubleshoot failing migrations.

8. On the Summary tab, 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.

   

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

   

Monitor the migration

1. After the Initial Load activity is completed, navigate to the Initial Load tab to view the completion status and the number of tables completed.

   

   After the Initial Load activity is completed, you're navigated to the Replicate Data Changes tab automatically. You can monitor the migration progress as the screen is auto-refreshed every 30 seconds.

2. Select Refresh to update the display and view the seconds behind source as and when needed.

   

3. Monitor the Seconds behind source and as soon as it nears 0, proceed to start cutover by navigating to the Start Cutover menu tab at the top of the migration activity screen.

4. Follow the steps in the cutover window before you're ready to perform a cutover.

5. After completing all steps, select Confirm, and then select Apply.

   

Perform post-migration activities

When the migration has finished, be sure to complete the following post-migration activities.

• Perform sanity testing of the application against the target database to certify the migration.

• Update the connection string to point to the new flexible server.

• Delete the source single server after you have ensured application continuity.

• If you scaled-up the target flexible server for faster migration, scale it back by selecting the compute size and compute tier for the 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	Burstable	Standard_B1s
  Basic	2	Burstable	Standard_B2s
  General Purpose	4	General Purpose	Standard_D4ds_v4
  General Purpose	8	General Purpose	Standard_D8ds_v4

  • To clean up the DMS resources, perform the following steps:

    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 then select Delete service.

    3. In the confirmation dialog box, in the TYPE THE DATABASE MIGRATION SERVICE NAME textbox, specify the name of the instance, and then select Delete.

Migration best practices

When performing a migration, be sure to consider the following best practices.

• As part of discovery and assessment, take the server SKU, CPU usage, storage, database sizes, and extensions usage as some of the critical data to help with migrations.

• Perform test migrations before migrating for production:

  • Test migrations are important for ensuring that you cover all aspects of the database migration, including application testing. The best practice is to begin by running a migration entirely for testing purposes. After a newly started migration enters the Replicate Data Changes phase with minimal lag, only use your Flexible Server target for running test workloads. Use that target for testing the application to ensure expected performance and results. If you're migrating to a higher MySQL version, test for application compatibility.
  • After testing is completed, you can migrate the production databases. At this point, you need to finalize the day and time of production migration. Ideally, there's low application use at this time. All stakeholders who need to be involved should be available and ready. The production migration requires close monitoring. For an online migration, the replication must be completed before you perform the cutover, to prevent data loss.

• Redirect all dependent applications to access the new primary database and make the source server read-only. Then, open the applications for production usage.

• After the application starts running on the target flexible server, monitor the database performance closely to see if performance tuning is required.

Related content

• What is Azure Database for MySQL - Flexible Server?
• What is Azure Database Migration Service?
• Known Issues With Migrations To Azure Database for MySQL
• Troubleshoot common Azure Database Migration Service (classic) issues and errors
• Troubleshoot DMS errors when connecting to source databases