This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Note
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.
Using a physical backup file, you can seamlessly migrate your MySQL on-premises or Virtual Machine (VM) workload on Azure or other cloud services to Azure Database for MySQL. With physical backup files, you can quickly restore your source server to the target flexible server instance with minimal downtime. In this tutorial, we show you how to use Azure DMS to migrate MySQL workloads from on-premises or VMs to Azure Database for MySQL with minimal downtime using Percona XtraBackup.
Note
DMS physical online data migration is now in public preview. DMS supports migration to MySQL versions 5.7 and 8.0 and migration from lower version MySQL servers (v5.6 and higher) to a higher version MySQL server. In addition, DMS supports cross-region, cross-resource group, and cross-subscription migrations.
In this tutorial, you learn how to:
To complete this tutorial, you need to:
Create or use an existing MySQL instance (the source server, whether on-premises or on VMs in Azure or other clouds).
To complete the online migration successfully, ensure that the following prerequisites are in place on the source MySQL server:
Take a physical backup of your MySQL workload using Percona XtraBackup. The following are the steps for using Percona XtraBackup to take a complete backup:
Install Percona XtraBackup on the on-premises or VM workload. For MySQL engine version v5.7, install Percona XtraBackup version 2.4, see Installing Percona XtraBackup 2.4. For MySQL engine version v8.0, install Percona XtraBackup version 8.0, see Installing Percona XtraBackup 8.0.
For instructions for taking a Full backup with Percona XtraBackup 2.4, see Full backup. For instructions for taking a Full backup with Percona XtraBackup 8.0, see Full backup
While taking full backup, run the below commands in order:
Considerations while taking the Percona XtraBackup:
Important
Attempting to access corrupted tables imported from a source server can cause the target flexible server to crash. As a result, before taking a backup using the Percona XtraBackup utility, performing a "mysqlcheck / Optimize Table" operation on the source server is recommended.
Create the target flexible server. For guided steps, see the quickstart Quickstart: Create an instance of Azure Database for MySQL with the Azure portal.
Create an Azure Blob container and get the Shared Access Signature (SAS) Token (Azure portal or Azure CLI) for the container. Ensure you grant Add, Create, and Write in the Permissions dropdown list.
Important
Save the Blob SAS token and URL values in a secure location. They're only displayed once and can't be retrieved once the window is closed.
Upload the full backup file from Percona Xtrabackup at {backup_dir_path} to your Azure Blob storage. Follow these steps to upload a file.
DMS uses the binlog positions captured when taking the full backup from the xtrabackup_binlog_info file to automatically initiate the replication process for a minimal downtime migration.
The Azure storage account should be publicly accessible using the SAS token. Azure storage account with virtual network configuration isn't supported.
An App registration needs to be created, and an app key using client secret needs to be generated to be used in the physical migration workflow. This app can be used with the storage account and the target flexible server for SAS key creation and server update.
Assign the Role-based access control (RBAC) role assignment with the app registration for storage account with the following roles.
Assign the Contributor role to the app registration on the target MySQL flexible server.
As you prepare for the migration, consider the following limitations.
The source server configuration hasn't been migrated. You must configure the target Flexible server appropriately before initiating the migration.
b as SELECT * FROM `a';'. The replication of this DDL results in the following error: "Only BINLOG INSERT, COMMIT, and ROLLBACK statements are allowed after CREATE TABLE with START TRANSACTION statement."DMS supports cross-region, cross-resource group, and cross-subscription migrations, so you can select the 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.
For an optimal migration experience, Select the compute size and compute tier for the target flexible server based on the source MySQL server configuration.
The MySQL version of the target flexible server must be greater than or equal to that of the source MySQL 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'.
Consider deploying both the Azure blob storage and the target flexible server in the same region for better performance during import operations.
With your target flexible server deployed and configured, you next need to set up DMS to migrate your MySQL server to a flexible server.
To register the Microsoft. The dataMigration resource provider performs 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 for which you want to create the DMS instance, then select Resource providers.
Search for "Migration" and then for Microsoft.DataMigration, select Register.
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.
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 verify that Azure is selected as the service mode.
To the right of Pricing tier, select Configure tier.
Select the Premium pricing tier with 4 vCores for your DMS instance on the Configure page and then select Apply.
DMS Premium 4-vCore is free for six months (183 days) from the date the DMS service was created before charges are incurred. For more information on DMS costs and pricing tiers, see the pricing page.
Next, we need to specify the virtual network (virtual network) that provides the DMS instance access to the source MySQL server and the target flexible server.
On the Create Migration Service page, select Next : Networking >>.
On the Networking tab, select an existing virtual network from the list or provide the name of the new virtual network you want to create, then select Review + Create.
For more information, see the article Create a virtual network using the Azure portal..
Your virtual network must be configured with access to both the source MySQL server and the target flexible server, so be sure to:
Note
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 select Create.
Your DMS instance's deployment begins now. The message "Deployment is in progress" appears for a few minutes, and then it changes to "Your deployment is complete."
Select Go to resource.
Identify the DMS instance's IP address from the resource overview page, create a firewall rule for your source MySQL server, and target a flexible server, allow-listing the IP address of the DMS instance.
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 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. In the Migration activity type selection box, select [Preview] Physical online data migration. Then, select Create and run activity.
Selecting Create project only as the migration activity type that only creates the migration project; you can then run it later.
To configure your DMS migration project, perform the following steps.
On the Select source screen, we must ensure that DMS is in the virtual network, which has connectivity to the source server. Here you input source server name, server port, user name, and password to your source MySQL server and then select Next: Select target >>
On the Select target screen, under automated Server selection, choose the Subscription, Location, Resource group, Azure Database for MySQL server name, user name, password for your target Azure Database for MySQL server and select Next: Select backup >>
On the Select backup screen, input the application ID of the app registration, client secret from the app registration, tenant ID from the app registration, subscription, storage account name, blob container name and the backup directory name where the percona xtrabackup files are stored and select Next: Configure migration settings >>
There's now a Migrate user accounts and privileges option. When selected, this option migrates all sign-in migrations. Additionally, you can replicate any DDL statements from the source MySQL server to the target flexible server.
On the Configure migration settings screen, if you want to customize the migration settings, select the check box or else advance to the summary page by selecting the Next: Summary >>
On the Summary screen, in the Activity Name text box, specify a name for the migration activity. Ensure all the migration-related details are correct, then select "Start Migration."
Once the migration starts, the migration activity window appears. Under the Initial Load tab, the status changes to Running.
As the migration is in flight, you can review the status of the migration and notice states such as Importing and Estimated time remaining for the physical backup files' data ingestion into the target MySQL flexible server.
After the Initial Load activity is completed, you've automatically navigated to the Replicate Data Changes tab. You can monitor the migration progress as the screen is autorefreshed every 30 seconds or select the Refresh button.
Once the initial data ingestion completes, monitor the Seconds behind source field under the Replicate Data Changes tab. As soon as it's 0, proceed to start the cutover by navigating to the Start Cutover button at the top of the migration activity screen. Select Refresh to update the display and view the seconds behind the source when needed.
Before you're ready to perform a cutover, follow steps 1 through 3 in the cutover window.
After completing all steps, select Confirm, and then select Apply.
When the migration finishes, be sure to complete the following post-migration activities.
Perform validation and data integration against the target database to certify the migration completion using one of the mentioned approaches.
You can validate data by comparing row count or checksum between source and target flexible servers.
You can additionally go to the target flexible server, under settings, select Databases page, and verify that the databases intended for migration have successfully migrated to the target.
Update the connection string to point to the new flexible server.
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 instance's name and then select Delete.
Create any read replicas for the flexible server for scalability and recovery.
When performing a migration, be sure to consider the following best practices.
As part of discovery and assessment, consider the server SKU, CPU usage, storage, database sizes, and extension usage as some of the critical data to help with migrations.
Perform test migrations before migrating for production:
Test migrations are important to ensure you cover all aspects of 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 to test the application and ensure expected performance and results. Test for application compatibility if you're migrating to a higher MySQL version.
After testing is completed, you can migrate the production databases. At this point, you need to finalize the day and time of the 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 determine whether performance tuning is required.
Training
Module
Choose a tool to migrate data to Azure Database for MySQL - Training
Choose a tool to migrate data to Azure Database for MySQL
Certification
Microsoft Certified: Azure Database Administrator Associate - Certifications
Administer an SQL Server database infrastructure for cloud, on-premises and hybrid relational databases using the Microsoft PaaS relational database offerings.