Note: This post 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 post.
MySQL database major version upgrades can contain database changes that may not be backwardly compatible with existing applications. As is common for open-source MySQL databases, you must manually perform a major version upgrade. Azure Database for MySQL - Flexible Server, a fully managed, production ready database service, now supports in-place major version upgrade from 5.7 to 8.0, which will help to keep your applications up-to-date with MySQL’s versioning policy.
The time required to perform a major version upgrade can vary depending on various factors such as how busy your database server is, how your schema is designed, the size of the database, and the number of tables in the database. For mission critical applications, it’s vital that any maintenance activities such as major version upgrades be as minimally disruptive as possible for business continuity.
In this post, I’ll highlight key features available in MySQL 8.0, mandatory prerequisites for a smooth upgrade, and details on how to perform major version upgrades on an Azure Database for MySQL flexible server with minimal downtime.
The latest major version, MySQL 8.0, includes several important new features and enhancements, which are described in the following sections.
Note: For a complete list of new features and enhancements added in MySQL 8.0, in the official MySQL documentation, see What Is New in MySQL 8.0.
In MySQL 5.7 and earlier major versions, DDL statements always involved a metadata lock in the system, and at times were complex, requiring rigorous planning by DBAs to ensure smooth DB object upgrades in production systems. MySQL 8.0 introduces Atomic DDL. Atomic DDL statements combine the data dictionary updates, storage engine operations, and binary log writes associated with a DDL operation into a single, atomic transaction. For more information about Atomic DDL operations, in the official MySQL documentation, see Atomic Data Definition Statement Support.
MySQL 8.0 supports recursive and non-recursive Common Table Expressions (CTEs), which are temporary result sets that can be referred to within another SELECT statement. For more information about CTEs, in the official MySQL documentation, see WITH (Common Table Expressions).
Window Functions perform a calculation for each row in the query using rows that are related to that row. With Window Functions, output results are calculated per row. For more information about Window Functions, in the official MySQL documentation, see Window Functions.
MySQL 8.0 supports roles, which are named collections of privileges. Roles can be:
The active applicable roles for an account can be selected from among those granted to the account, and they can be changed during sessions for that account. For more information about roles, in the official MySQL documentation, see Using Roles.
MySQL 8.0 introduces the TempTable storage engine as the default engine for in-memory internal temporary tables, replacing the MEMORY storage engine used in MySQL major versions 5.7 and earlier. The TempTable storage engine offers efficient storage for VARCHAR and VARBINARY datatype columns.
MySQL 8.0 extends the EXPLAIN statement with a new EXPLAIN ANALYZE statement, which provides additional information about the execution of SELECT statements in a TREE format.
mysql> EXPLAIN ANALYZE SELECT * FROM t1 JOIN t2 ON (t1.c1 = t2.c2)\G
*************************** 1. row ***************************
EXPLAIN: -> Inner hash join (t2.c2 = t1.c1) (cost=4.70 rows=6)
(actual time=0.032..0.035 rows=6 loops=1)
-> Table scan on t2 (cost=0.06 rows=6)
(actual time=0.003..0.005 rows=6 loops=1)
-> Table scan on t1 (cost=0.85 rows=6)
(actual time=0.018..0.022 rows=6 loops=1)
To help ensure that an Azure Database for MySQL flexible server upgrade from version 5.7 to 8.0 progresses smoothly, take some time to check existing databases for any potential incompatibilities.
Important: It’s strongly recommended to perform major version upgrade tests against databases in a development or test environment before attempting major version upgrades on production database servers.
Included with the MySQL Shell utilities, the upgrade checker utility can assist with identifying potential incompatibilities, such as any:
Run the upgrade checker utility against the databases in a development or test environment and address any potential incompatibilities before beginning the upgrade process.
Note: For more information about the MySQL upgrade checker utility, in the official MySQL documentation, see Upgrade Checker Utility.
After running the upgrade checker utility, use the “mysqlcheck –checkupgrade” command to ensure that there aren’t any incompatibility issues with the tables in your databases.
Sample output from the MySQL mysqlcheck –checkupgrade command is shown below.
azureuser@demo-vm:~$ mysqlcheck --check-upgrade -h testserver.mysql.database.azure.com -u adminuser -p --all-databases
Be sure to check any custom server parameters set for MySQL 5.7 database servers and ensure the values are still applicable in MySQL 8.0. For more information about the mysqlcheck command, in the official MySQL documentation, see mysqlcheck — A Table Maintenance Program.
Completing the major version upgrade process sometimes requires a significant amount of time and can impact application uptime. Consider using the approach outlined below, which will minimize the downtime required to perform a major version upgrade for mission critical applications that are backed by an Azure Database for MySQL flexible server.
At a high level, this approach involves leveraging Azure Database for MySQL read replicas, upgrading the read replicas, promoting them, and then pointing the application to the upgraded database servers, as depicted below:
If a primary production database server already has read replicas, simply leverage one of the read replicas to perform a major version upgrade.
For primary production database servers without read replicas, create a read replica using the following steps:
A notification will appear after successful creation of the read replica.
When you’re ready to perform an in-place upgrade of the Azure Database for MySQL 5.7 read replica, use the following steps:
Notice that the Current MySQL version is 5.7 and the MySQL version to upgrade is 8.0.
After the upgrade is completed, a notification will appear. confirming that the read replica is running major version 8.0.
Note: You can also perform a major version upgrade from the Azure CLI by running the following command:
az mysql server upgrade --name testsvr --resource-group testgroup --subscription MySubscription --version 8.0
After the read replica is upgraded to MySQL major version 8.0, you can begin the process of performing the cutover in preparation for redirecting your application to the upgraded server.
SHOW SLAVE STATUS\G
If the state of Slave_IO_Running and Slave_SQL_Running is "yes" and the value of Seconds_Behind_Master is "0", replication is working well. Seconds_Behind_Master indicates how late the replica is. If the value isn't "0", it means that the replica is still processing updates.
Next, point your application to the new primary server that is running MySQL major version 8.0.
Note: Each server has a unique connection string, so be sure to provide the correct connection string.
Use the upgraded server to create any necessary read replicas for read scalability, as well as business continuity and disaster recovery scenarios.
The information above provides details and best practices for upgrading your mission critical Azure Database for MySQL flexible servers running MySQL major version 5.7 to version 8.0 with minimal downtime. 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.