Migrating an Azure Database for MySQL single server (v5.6) to a flexible server (v5.7)

[Guest NeilDsouza]

Azure Database for MySQL is a relational database service in the Microsoft cloud based on the MySQL Community Edition database engine. The MySQL open-source community has retired version 5.6 as of Feb 2021, and so we have followed suit by deprecating the ability to create new servers running v5.6 starting September 2021.

 

 

 

Note: Azure Database for MySQL supports each major version of MySQL until retirement by the MySQL community, as provided in the versioning policy. For more information, see version support policy documentation.

 

 

 

While you can no longer create new servers running v5.6, we continue to support existing single servers running v5.6, so if you’re still hosting databases on MySQL v5.6, this post is for you! I’ll provide details about the migration strategy and a plan for moving your v5.6 instances to MySQL v5.7 and further, to our Azure Database for MySQL – Flexible Server offering, which provides more granular control and flexibility over database management functions and configuration settings.

 

 

 

Since we’ve already announced plans to deprecate Azure Database for MySQL - Single Server by October 2024, we’ve designed this guide to help you plan and complete the migration of your MySQL single servers to Azure Database for MySQL - Flexible Server well before deprecation.

 

 

 

Although it’s possible to migrate directly from MySQL 5.6 on Single Server to MySQL 5.7 on Flexible Server, it may not work for all databases and architectures, especially if you’re using a specific MySQL 5.6 configuration that is removed or changed in MySQL 5.7. We’d recommend following a step-by-step approach so that you can ensure that complex workloads and application architectures are compatible with the database engine and configuration, and then tune accordingly.

 

 

 

Now you may be wondering, “Why can’t I migrate from MySQL 5.6 to MySQL 8.0 directly?”. While it’s possible to migrate from MySQL 5.6 to MySQL 8.0, we recommend that you not skip a major version, instead approaching the process in phases, upgrading from one major version to the next. This does require a bit more effort, multiple steps, and downtimes, it will ultimately save you time and trouble. If you encounter problems during the process, the cause will be easier to identify.

 

 

 

In this blog post, I’m covering at a high level the plan for migrating from an Azure Database for MySQL single server running version 5.6 to a new Azure Database for MySQL flexible server running version 5.7. I’ll also be providing details for both online migrations with minimal downtime and as offline migrations.

 

 

 

The migration process

 

 

The process for migrating a MySQL single server to Flexible Server involves preparation and a couple of phases, regardless of whether you are performing an online or offline migration:

 

 

 

First, you’ll want to ensure application compatibility with MySQL 5.7, and then:

 

1. Upgrade in-place or migrate the Azure Database for MySQL single server (v5.6) to an Azure Database for MySQL single server (v5.7).
   
2. Migrate the Azure Database for MySQL single server (v5.7) to an Azure Database for MySQL flexible server (v5.7).
   

 

 

 

 

 

 

The following sections provide additional details about each phase.

 

 

 

Ensure application compatibility with MySQL 5.7

 

 

Again, regardless of whether you are performing an online or offline migration, the first phase involves ensuring application compatibility with MySQL 5.7. This requires:

 

• Thorough testing to ensure application and database architecture is compatible with MySQL 5.7.
  
• Performance testing to ensure there is no performance degradation with MySQL 5.7 or incompatibility.
  

 

It’s generally recommended to begin testing in lower (staging/test/dev) environments if possible. If such environments do not exist or are vastly different from production, use the CLI to create a new instance of Azure Database for MySQL – Single Server with MySQL version 5.7, and then dump and restore data from one of the production servers (read/write or read replica servers) that are running MySQL v5.6

 

If you encounter issues, make changes to the application to make it compatible with MySQL 5.7.

 

 

 

Note: Before upgrading, be sure to review the official MySQL community documentation here: MySQL :: MySQL 5.7 Reference Manual :: 2.10.1 Before You Begin.

 

 

 

If your MySQL installation contains a large amount of data that might take a long time to convert after an in-place upgrade, it may be useful to create a test instance for assessing the conversions that are required and the work involved to perform them. To create a test instance, make a copy of the MySQL instance that contains the MySQL database and other databases without the data. Run the upgrade procedure on the test instance to assess the work involved to perform the actual data conversion.

 

 

 

Important: For information about incompatibility between major versions, such as removed features or other changes that could break your application, be sure to have your application team consult the following document: MySQL :: MySQL 5.7 Reference Manual :: 2.10.3 Changes in MySQL 5.7.

 

 

 

Upgrade in-place or migrate the Azure Database for MySQL single server (v5.6) to an Azure Database for MySQL single server (v5.7)

 

 

 

 

In-place upgrade

 

You can perform in-place upgrades of your MySQL 5.6 servers to a MySQL single server running 5.7 with a click of button, without any data movement or the need for any application connection string changes. For detailed steps, refer to the article Major version upgrade in Azure Database for MySQL - Single Server.

 

 

 

Migration

 

When an in-place upgrade won’t work for you because of complexities in the database schema, architecture, usage of incompatible features, etc., migration is definitely the way to go.

 

 

 

Online (minimal downtime)

 

To perform an online migration from Azure Database for MySQL v5.6 – Single Server to Azure Database for MySQL v5.7 – Single Server, perform the following steps:

 

1. Create a new Azure Database for MySQL single server (v5.7).
   
2. Dump the database from the source Azure Database for MySQL v5.6 by using the mydumper tool.
   
3. Gather the binlog position from the dump and note it to be used later for Data-In Replication
   
4. Restore to the newly created Azure Database for MySQL single server (v5.7).
   
5. Set up and start Data-In Replication from the Azure Database for MySQL single server (v5.6) to the Azure Database for MySQL single server (v5.7).
   
6. [Optional] Create a Read Replica for the target Azure Database for MySQL v5.7 – Single Server (Alternatively you may also test the workload on the target server directly).
   
7. Monitor replication and wait for target server to catch up to the source.
   
8. In the meantime, you can test the read workload with the Azure Database for MySQL v5.7 – Single Server Read Replica to ensure it works without issues.
   
9. [Optional] Create an additional read replica and make one of the read replicas of the Azure Database for MySQL v5.7 – Single Server stand-alone by stopping replication and test write workload. (Recommended if you want to catch any issues with write workload before actual cutover, fix them, if not you will need to deal with the issues post cutover of production)
   
10. Take downtime and perform cutover. As part of cutover process, during downtime, stop and remove the Data-In Replication
    
11. [Optional] If you require rollback to v5.6 option to original source server for any reason post cutover, configure reverse Data-In Replication from Azure Database for MySQL v5.7 – Single Server to Azure Database for MySQL v5.6 – Single Server.
    

 

Important

: Data-In replication is MySQL community native logical, binlog-based replication. While the replication from v5.7 to v5.6 is possible, it needs to be thoroughly tested to ensure the application architecture and database schema design supports it and does not cause any issues. For support on issues related to the MySQL engine or database design contact the MySQL community.

 

 

1. Run the database in Azure Database for MySQL v5.7 – Single Server for some time to ensure there are no issues and eventually you can stop and remove replication and delete original source server and any additional replicas no longer needed.
    
   

 

Offline

 

To perform an offline migration from Azure Database for MySQL – Single Server (v5.6) to Azure Database for MySQL – Single Server (v5.7), perform in-place upgrade of Azure Database for MySQL – Single Server with a click of a button in the Azure portal. This offline method restarts the server incurring at least 120 seconds downtime and potentially more based on the workload on the server at the time, as well as the size and complexity of the database. Plan this activity during low workload hours to keep the downtime minimal.

 

 

 

Migrate the Azure Database for MySQL single server (v5.7) to an Azure Database for MySQL flexible server (v5.7).

 

 

For both online and offline migrations of Azure Database for MySQL – Single Server (v5.6) to Azure Database for MySQL – Flexible Server (v5.7), perform the following steps:

 

1. Create an Azure Database for MySQL flexible server (v5.7)
   
2. Use DMS, MyDumper/MyLoader, or MySQL Shell to migrate database(s) from your Azure Database for MySQL single server (v5.7) to you Azure Database for MySQL flexible server (v5.7).
    
   Note: If your database server storage size exceeds 700Gb and you face any challenges migrating, please reach out to us at AskAzureDBforMySQL@service.microsoft.com.
    
    
   
3. Test your application with the Azure Database for MySQL flexible server (v5.7) to ensure that there are no issues. If you do encounter issues, work around/fix those before moving to the next step.
   
4. When you are confident that your application has no issues with the Azure Database for MySQL flexible server (v5.7), perform the migration, via either of the following options.
    
   

 

Online (minimal downtime)

 

To perform an online migration of Azure Database for MySQL v5.7 – Single Server to Azure Database for MySQL v5.7 – Flexible Server, perform the following steps.

 

1. Use DMS, MyDumper/MyLoader or MySQL Shell to migrate database(s) from Azure Database for MySQL v5.7 – Single Server to Azure Database for MySQL v5.7 – Flexible Server
   
2. Configure Data-In Replication from Source Azure Database for MySQL v5.7 – Single Server to Azure Database for MySQL v5.7 – Flexible Server
   
3. Monitor the replication and wait for the target server to catch up to the source.
   
4. Once replication has caught up, obtain downtime that is needed to switch the application to point to Azure Database for MySQL v5.7 – Flexible Server and perform the cutover.
   
5. [Optional] As part of cutover process, during downtime, stop and remove the Data-In Replication and configure reverse Data-In Replication from Azure Database for MySQL v5.7 – Single Server to Azure Database for MySQL v5.7 – Flexible Server for having an option to roll back to Single Server.
   

 

Important

:

Data-in replication is MySQL community native logical, bin_log-based replication. This is currently a customer managed replication and not part of the Azure Database for MySQL service.

 

 

Offline

 

To perform an offline migration of an Azure Database for MySQL single server (v5.7) to an Azure Database for MySQL flexible server (v5.7), perform the following steps:

 

1. Create an Azure Database for MySQL flexible server (v5.7).
   
2. Take downtime and use DMS, MyDumper/MyLoader, or MySQL Shell to migrate your Azure Database for MySQL single server (v5.7) to your Azure Database for MySQL flexible server (v5.7).
   
3. Update the application connection string to point to your Azure Database for MySQL flexible server (v5.7).
   

Summary

 

 

In conclusion, if you’re running databases on the deprecated version MySQL v5.6, this guide is designed to help with your move to v5.7 running on Azure Database for MySQL – Flexible Server smoothly.

 

 

 

Note: The MySQL community has announced an intention to deprecate MySQL v5.7 later this year, so watch out in the near future for a similar guide for migrating from v5.7 to v8.0.

 

 

 

If you have any feedback or questions about the information provided above, please leave a comment below or email us at AskAzureDBforMySQL@service.microsoft.com. Thank you!

 

Continue reading...