Online migration from Single Server to Flexible Server using MySQL Import and Data-In Replication

Posted on by — No Comments ↓

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Community Hub.

Azure Database for MySQL – Flexible Server is the flagship Platform as a Service running on Linux, offering of the popular open-source software relational database management system MySQL. It is a go-to platform for web applications such as WordPress, Magento, Moodle in the LAMP stack, e-commerce, OLTP, and gaming on the Microsoft Azure Cloud.

 

Since the announcement of deprecation of our legacy platform Single Server, we have been busy at work releasing multiple features each month in Flexible Server achieving feature parity with Single Server. Read more about them now and bookmark for future updates: What's new in Azure Database for MySQL - Flexible Server

 

While we have many tooling options for you to migrate your MySQL servers from the legacy platform Single Server to the latest platform Azure Database for MySQL – Flexible Server, we just announced GA of the latest tool MySQL Import CLI (Command Line Interface), the simple and fast way to migrate to Flexible Server with new capability to migrate online with minimal downtime.

 

Azure Database for MySQL Import CLI (General Availability)

Azure Database for MySQL Import command line interface (CLI) provisions the target Flexible Server and then uses snapshot backup and restore technology at the storage layer to achieve a simple and fast migration of the source - single server's physical data files to the target flexible server.

 

The MySQL Import process comprises of the following high-level phases:

  1. Provisioning the target flexible server using Smart Defaults logic.*
  2. Taking a snapshot backup of the source single server.
  3. Restoring the backup to the target flexible server.
  4. Copying the data files, server parameters, compatible firewall rules, and server properties to the target flexible server.

 

NeilDsouza_21-1713255411779.png

*Provisioning the target flexible server using Smart Defaults logic sets the properties as described in the following table.

 

Property

Smart Defaults

Post-migration options

Limitations

Tier
  • General Purpose --> General Purpose
  • Memory Optimized --> Business Critical
  • Switching the tier after migration with minimal downtime
  • The region must have the capacity for SKU and must not be a restricted region.

SKU
  • Equivalent Performing vCore and Memory
  • Scaling up or down with minimal downtime
  • Downtime depends on how busy the servers is.

Version
  • 5.7 / 8.0 major version
  • Performing an in-place upgrade from 5.7 to 8.0 with one click
  • Source and destination must be same major version

Storage Size
  • Same size as source server with auto-grow enabled
  • Scaling up
  • Storage size cannot be scaled down

Storage IOPS
  • Max IOPS possible for the storage tier
  • Tuning up or down within the Storage SKU/Tier limits.
  • Provisioned IOPS - billed whether used and throttled when max value is reached.
  • Auto-scale IOPS - billed by IOPS used and auto-scale within the min-max range for the SKU.
  • Max IOPS in each setting is the max possible IOPS for each SKU/Tier

Location
  • Same region / location as source server
  • Migration to any region with the same hardware availability using the feature Universal Cross-region Read Replica.
  • Migration requires connection string changes

Networking

 
  • Switching only by further performing PiTR

 

Backup type
  • Locally / Geo redundant as in the source server
  • Switching only by further performing PiTR
  • Cannot be switched during migration

Backup Retention
  • Same as the source
  • Scaling up or down, enabling LRT Backup
  • None

Admin Credentials
  • Same Admin-user and admin-password from the source Single Server instance
  • Adding users, modification post migration
  • Admin user from Single Server remains in Flexible Server through migration

 

For more information, see the following resources:

 

Syntax of the MySQL Import command

The syntax of the MySQL Import CLI command includes complexity and multiple sub-tasks using Smart Defaults and requiring minimal user input. The syntax of the command follows:

 

 

az mysql flexible-server import create --data-source-type --data-source --resource-group --name [--high-availability {Disabled, SameZone, ZoneRedundant}] [--identity] [--iops] [--key] [--private-dns-zone] [--public-access] [--standby-zone] [--subnet] [--subnet-prefixes] [--tags] [--vnet] [--zone]

 

 

Note: The arguments listed below are those useful in most common scenarios; it is not a comprehensive list. For details on all the arguments, see the article Migrate Azure Database for MySQL - Single Server to Flexible Server using Azure Database for MySQL Import CLI.

 

Required Arguments

Running the MySQL Import CLI requires at a bare minimum the arguments listed in the following table.

Argument

Sample value

Description

data-source-type

mysql_single

The type of data source that serves as the source destination for triggering Azure Database for MySQL Import. Accepted values: [mysql_single]. Description of accepted values- mysql_single: Azure Database for MySQL Single Server.

data-source

test-single-server

The name or resource ID of the source Azure Database for MySQL single server.

resource-group

test-rg

The name of the Azure resource group of the source Azure Database for MySQL single server.

name

test-flexible-server

A unique name for your target Azure Database for MySQL flexible server. The server name can contain only lowercase letters, numbers, and the hyphen (-) character, and must contain from 3 to 63 characters.

 

Note: You can deploy this server in the same subscription, resource group, and region as the source server, but only during the import process.

 

Optional Arguments

You can specify optional arguments for particular configuration requirements. You can also have MySQL Import Smart Defaults reduce your effort by selecting the best configuration (based on the source configuration) for the optional arguments listed in the following table.

 

Important: Certain scenarios, such as using Zone Redundant HA and Zone Redundant Backup, require that you specify during flexible server creation; you cannot set them later.

 

Argument

Sample value

Description

public-access

0.0.0.0

Determines the public access for the target Azure Database for MySQL flexible server. Enter a single IP address or a range of IP addresses to include in the list of allowed IP addresses. IP address ranges must be dash-separated without any spaces. Specifying 0.0.0.0 allows public access from any resources deployed within Azure to access your server. Selecting None sets the server in public access mode, but it doesn't create a firewall rule.

vnet

myVnet

Name or resource ID of a new or existing virtual network. To use a VNet from a different resource group or subscription, provide a resource ID. The name must be between 2 to 64 characters; begin with a letter or number; end with a letter, number or underscore; and may contain only letters, numbers, underscores, periods, and hyphens.

subnet

mySubnet

Name or resource ID of a new or existing subnet. To use a subnet from different resource group or subscription, provide a resource ID instead of a name. Note that the subnet will be delegated to Microsoft.DBforMySQL/. After delegation, you cannot use this subnet for any other type of Azure resource.

private-dns-zone

mysvr.priv.contoso.com

The name or ID of new or existing private DNS zone. You can use the private DNS zone from the same resource group, a different resource group, or a different subscription. To use a zone from different resource group or subscription, provide a resource ID. If you don’t specify a private DNS zone, then the CLI creates a new private DNS zone within the same resource group as the virtual network.

key

key identifier of testKey

The resource ID of the primary key for data encryption.

identity

testIdentity

The name or resource ID of the user assigned identity for data encryption.

tags

key=value

The name of the Azure resource group.

high-availability

ZoneRedundant

Enable (ZoneRedundant or SameZone) or disable the high availability feature for the target Azure Database for MySQL flexible server. Accepted values: Disabled, SameZone, ZoneRedundant; Default value: Disabled.

zone

1

The availability zone into which to provision the resource.

standby-zone

3

The availability zone information of the standby server (if High Availability is enabled).

iops

500

The number of IOPS to allocate for the target Azure Database for MySQL flexible server. You get a certain amount of free IOPS based on compute and storage provisioned. The default value for IOPS is free IOPS. To learn more about IOPS based on compute and storage, see the article Service tiers - Azure Database for MySQL - Flexible Server | IOPS.

 

For more information about all the arguments as well as how to plan for same-zone vs zone-redundant HA and Backup, and IOPS configuration please see the following resources:

 

Online Migration (Minimal Downtime)

You can upgrade your Azure Database for MySQL single server to the newer Flexible Server platform to take advantage of the latest features and functionality. To achieve a near-zero downtime migration, first perform an offline import operation and then set up data-in replication between the source server and target server to perform an online migration.

 

Importance of POC / Dry-Run

Be sure that you plan for and schedule the target time for application cutover with minimal downtime based on the time it takes for MySQL Import to provision a new flexible server, take a snapshot backup of the single server, and restore it to the flexible server using a multi-threaded parallelization mode. For an estimate, see the results of our benchmarking runs by the data size in the article How long does Azure Database for MySQL Import take to migrate my Single Server instance?

 

Note: Numbers may vary from server to server reasonably based on the complexity of the database architecture, the number of database objects, etc. For a more realistic estimate, use PiTR (Point in time Restore) to create a copy of the production server and then note the time it takes to run the MySQL Import command against it.

 

Prerequisites and Limitations

Some important prerequisites to meet and limitations to understand before starting the import process are listed in the following sections.

 

Note: For a detailed list Prerequisites and Limitations, see the article Migrate Azure Database for MySQL - Single Server to Flexible Server using Azure Database for MySQL Import CLI.

 

Version Compatibility

  • Supported major versions:
    • MySQL 5.7
    • MySQL 8.0

If you’re using a different major MySQL version on Single Server, make sure to upgrade your version on your Single Server instance before triggering the import command.

  • If your source Azure Database for MySQL Single Server has engine version v8.x, be sure to upgrade your source server's .NET client driver version to 8.0.32 to avoid any encoding incompatibilities after migration to Flexible Server.

 

Server Parameters

  • If your Single Server instance has Legacy Storage architecture (General Purpose storage V1), you must set the parameter log_bin=ON for your Single Server instance before initiating the import operation.
  • 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 begin with, which you can increase as necessary. After a successful cutover, you can reset the value.
  • If the Azure Database for MySQL - Single Server instance has server parameter lower_case_table_names set to 2 and your application uses partition tables, the import operation will result in corrupted partition tables. We recommend setting the lower_case_table_names parameter to 1 for your Azure Database for MySQL - Single Server instance to better ensure a corruption-free MySQL Import operation.

 

High Availability

If the target flexible server is provisioned as non-HA (High Availability disabled) when updating the CLI command parameters, you can later switch it to Same-Zone HA, but not to Zone-Redundant HA.

 

Security

  • For CMK-enabled Single Server instances, the Azure Database for MySQL Import command requires you to provide mandatory input parameters for enabling CMK on target Flexible Server.
  • If the Single Server instance has 'Infrastructure Double Encryption' enabled, we recommend enabling Customer Managed Key (CMK) on target Flexible Server instance to support similar functionality. You can choose to enable CMK on target server with Azure Database for MySQL Import CLI input parameters or after migration as well.

 

MySQL Import Limitations

  • Support includes instance-level import only. There is no option to import selected databases within an instance.
  • Importing to an existing Azure Database for MySQL - Flexible Server instance isn't supported. The CLI command creates a new Azure Database for MySQL flexible server.
  • Import across subscriptions, resource groups, regions, and versions isn't possible. The target Azure Database for MySQL - Flexible Server will be created in the same subscription, resource group, region, and on the same MySQL version as the source Azure Database for MySQL - Single Server.

 

Migration Workflow

In this section, I’ll walk you start to finish through the MySQL Import Online method for migrating a MySQL single server to a MySQL flexible server, including some crucial prerequisites such as configuring server parameters on the source MySQL single server.

  1. Configure server parameters for migration.
  • log_bin = On

If the value of the log_bin parameter is OFF (the default value for legacy General Purpose v1 storage architecture), to change the value to ON, you need to create a read replica for your Single Server instance and then delete the read replica. You can check this parameter value when logged in to the MySQL server and executing below SQL statement:

NeilDsouza_23-1713255411785.png

 

To configure the following server parameters, in the Azure portal, navigate to the Server parameters blade as shown below.NeilDsouza_24-1713255411869.png

 

  • binlog_expire_logs_seconds = 172800

To begin with, you can set this value to 172800 (2 days), and then you can increase if needed to 432000 (5 days) or 604800 (7 days), for example, based on how the replication goes and your cutover plan and downtime schedule.

 

  • lower_case_table_names = 1

To configure this server parameter, in the Azure portal, navigate to the Server Parameters blade.

 

  1. Determine whether the Azure account context is set to the subscription hosting your Azure Database for MySQL single server by running the following command:

 

 

az account show

 

 

Example output:

NeilDsouza_25-1713255411873.png

If the right subscription is not displayed in the output, set the context by running the following command:

 

 

az account set --subscription 00000000-0000-0000-0000-000000000000

 

 

NOTE - Enter the Subscription ID in place of the zeroes.

Then, to verify that the correct Subscription context, re-run the command ‘az account show’, as shown above.

  1. From the Azure CLI, run the following MySQL Import command:

Example command:

 

 

az mysql flexible-server import create --data-source-type "mysql_single" --data-source "neilmyss57" --resource-group "neild_auto_mig" --name "neilmyfs57"

 

 

Example output:NeilDsouza_26-1713255411880.png

  1. In the Azure Portal, in the same Resource Group, you should be able to locate the new flexible server with data imported from your Single Server instance. Be sure that there is network connectivity between the client to the source and the target. Also ensure that you have added the IP address of the client or the public IP address of the CLI to your flexible server firewall.
  1. Connect to it and then, to capture the BinLog position and file, run the following Stored Procedure:

NeilDsouza_27-1713255411886.png

NOTE – This step is unnecessary if you use GTID based replication. For more information about using GTID based replication, see the article Configure data-in replication - Azure Database for MySQL - Flexible Server | Set GTID in Replica Server.

 

  1. Configure Data-In Replication by using the Binlog file and position using the output from the previous step.

Important: Before completing this step, be sure that the MySQL flexible server can connect to the source MySQL single server successfully by whitelisting the servers in each other’s firewall or access to Azure Resources if on a Public Network or Virtual Network and Private Endpoint configuration for successful private network connectivity.

 

Example command:

 

 

CALL mysql.az_replication_change_master('neilmyss57.mysql.database.azure.com', 'azureuser@neilmyss57', 'Password’, 3306, 'mysql-bin.000010', 399, '');

 

 

Example command for GTID based Replication:

 

 

CALL mysql.az_replication_change_master_with_gtid ('neilmyss57.mysql.database.azure.com', 'azureuser@neilmyss57', 'Password’, 3306, '');

 

 

 

Example output:NeilDsouza_28-1713255411888.png

  1. To start the replication, run the following command:

Command syntax:

 

 

CALL mysql.az_replication_start;

 

 

Example output:

NeilDsouza_29-1713255411891.png

 

To check the replication status and monitor the replication lag, in the Azure portal, navigate to the Metrics blade of the Flexible server:

NeilDsouza_30-1713255411894.png

 

You can also check the replication status by connecting to the server and running the following command:
Command syntax:

 

 

show slave status\G;

 

 

Example output:

NeilDsouza_31-1713255411905.pngNeilDsouza_32-1713255411917.png

 
  1. After the replica catches up to the delta changes in the master and the replication lag is 0 seconds, you’re ready to cutover the application to the new MySQL flexible server. At this stage, you can start the downtime for the application, stop and remove the Data-in replication configuration:

  Command syntax:

 

 

CALL mysql.az_replication_stop;CALL mysql.az_replication_remove_master;

 

 

Example output:                      NeilDsouza_33-1713255411921.png

 
  1. Update the connection string to point to the MySQL flexible server that is up to date with the data from the source. This completes the migration from MySQL Single Server to Flexible Server with minimal downtime required to cutover the application.

Post-import Steps

After the Azure Database for MySQL Import, Data-In Replication, and application cutover stages have completed successfully, copy the following properties from the source single server to the target flexible server:

  • Read-Replicas
  • Monitoring page settings (Alerts, Metrics, and Diagnostic settings)
  • Any Terraform/CLI scripts you host to manage your Single Server instance should be updated with Flexible Server references.

Demo Video

For a demonstration of this technique, see our recent monthly webinar:

 

Conclusion

I hope this post has provided some insights into how to migrate from Azure Database for MySQL - Single Server to Flexible Server in the most efficient manner using MySQL Import CLI with minimal downtime.

 

If you have any questions or feedback, please feel free to leave a comment below or contact us at AskAzureDBforMySQL@service.microsoft.com. We’d love to hear from you and help you with your cloud journey. Thank you for reading, and happy coding!

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.