Tutorial: Migrate from MySQL to Azure Database for MySQL - Flexible Server online using DMS via the Azure portal
This article contains references to the term slave, a term that Microsoft no longer uses. When the term is removed from the software, we'll remove it from this article.
You can migrate your on-premises or other cloud services MySQL 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’ll perform an online migration of a sample database from an on-premises MySQL server to an Azure Database for MySQL - Flexible Server (both running version 5.7) using a DMS migration activity.
DMS online migration is now generally available. DMS supports migration to MySQL versions 5.7 and 8.0 and also supports migration from lower version MySQL servers (v5.6 and above) to higher version servers. In addition, DMS supports cross-region, cross-resource group, and cross-subscription migrations, so you can select a region, resource group, and subscription for the target server that is different than what is specified for your source server.
In this tutorial, you'll learn how to:
- Implement best practices for creating a flexible server for faster data loads using DMS.
- Create and configure a target flexible server.
- Create a DMS instance.
- Create a MySQL migration project in DMS.
- Migrate a MySQL schema using DMS.
- Run the migration.
- Monitor the migration.
- Perform post-migration steps.
- Implement best practices for performing a migration.
To complete this tutorial, you need to:
- Create or use an existing MySQL instance (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, be sure to enable it before starting the migration.
- 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 an online migration, you will need to configure the binlog expiration 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. The parameter will depend on the version of your MySQL server. For MySQL 5.7 the parameter is expire_logs_days (by default it is set to 0, which is no auto purge). For MySQL 8.0 it is binlog_expire_logs_seconds (by default it is set to 30 days). 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:
- “READ” privilege on the source database.
- “SELECT” privilege for the ability to select objects from the database
- If migrating views, the user must have the “SHOW VIEW” privilege.
- If migrating triggers, the user must have the “TRIGGER” privilege.
- If migrating routines (procedures and/or functions), the user must be named in the definer clause of the routine. Alternatively, based on version, the user must have the following privilege:
- For 5.7, have “SELECT” access to the “mysql.proc” table.
- For 8.0, have “SHOW_ROUTINE” privilege or have the “CREATE ROUTINE,” “ALTER ROUTINE,” or “EXECUTE” privilege granted at a scope that includes the routine.
- If migrating events, the user must have the “EVENT” privilege for the database from which the events are to be shown.
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. Note that 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 Azure Database for MySQL Flexible Server target server. For target server engine version v5.x, DDL statement replication is not supported currently.
- 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 General Purpose Standard_D16ds_v4 Basic* 2 General Purpose Standard_D16ds_v4 General Purpose* 4 General Purpose Standard_D16ds_v4 General Purpose* 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 Create an Azure Database for MySQL flexible server.
- 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 may 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)
- Resource Health Alerts
- The user performing the migration requires the following permissions:
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.
Before creating your first DMS instance, sign in to the Azure portal, and then search for and select Subscriptions.
Select the subscription that you want to use to create the DMS instance, and then select Resource providers.
Search for the term “Migration”, and then, for Microsoft.DataMigration, select Register.
Create a Database Migration Service (DMS) instance
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 drop-down list.
On the Azure Database Migration Service screen, select Create.
On the Select migration scenario and Database Migration Service page, under Migration scenario, select MySQL as the source server type, and then select Azure Database for MySQL as target server type, and then select Select.
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.
Under Instance details, specify a name for the service, select a region, and then verify that Azure is selected as the service mode.
To the right of Pricing tier, select Configure tier.
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.
On the Create Migration Service page, select Next : Networking >>.
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..
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.
To add tags to the service, advance to the Tags tab by selecting Next : Tags. Adding tags to the service is optional.
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.
Select Go to resource.
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.
In the Azure portal, select All services, search for Azure Database Migration Service, and then select Azure Database Migration Services.
In the search results, select the DMS instance that you created, and then select + New Migration Project.
On the New migration project page, 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 - Flexible Server, in the Migration activity type selection box, select Online data migration, and then select Create and run activity.
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.
On the Select source screen, we have to ensure that DMS is in the VNet which has connectivity to the source server. Here you will input source server name, server port, username, and password to your source server.
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.
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.
There is now a Migrate all applicable databases option. When selected, this option will migrate all user created databases and tables. Note that because Azure Database for MySQL - Flexible Server does not support mixed case databases, mixed case databases on the source will not be included for an online migration.
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.
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.
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.
After configuring for schema migration, select Review and start migration.
You only need to navigate to the Configure migration settings tab if you are trying to troubleshoot failing migrations.
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.
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
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.
Select Refresh to update the display and view the seconds behind source when needed.
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.
Follow the steps in the cutover window before you're ready to perform a cutover.
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:
- In the Azure portal, select All services, search for Azure Database Migration Service, and then select Azure Database Migration Services.
- Select your migration service instance from the search results, and then select Delete service.
- 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.
- For information about Azure Database for MySQL - Flexible Server, see Overview - Azure Database for MySQL Flexible Server.
- For information about Azure Database Migration Service, see What is Azure Database Migration Service?.
- For information about known issues and limitations when migrating to Azure Database for MySQL - Flexible Server using DMS, see Known Issues With Migrations To Azure Database for MySQL - Flexible Server.
- For information about known issues and limitations when performing migrations using DMS, see Common issues - Azure Database Migration Service.
- For troubleshooting source database connectivity issues while using DMS, see article Issues connecting source databases.