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 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:
The following sections provide additional details about each phase.
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:
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.
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:
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.
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.
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:
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.
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:
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!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.