Perform MySQL major version upgrades in Flexible Server with minimal downtime
Published Oct 27 2022 02:08 PM 5,228 Views
Microsoft

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.

 

Introduction

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.

 

Demo video

 

 

Key features introduced in MySQL 8.0

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.

 

Atomic DDL

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.

 

Common Table Expressions

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

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.

 

Roles

MySQL 8.0 supports roles, which are named collections of privileges. Roles can be:

  • Created and dropped.
  • Have privileges granted to and revoked from them.
  • Be granted to and revoked from user accounts.

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.

 

Internal temporary tables

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.

 

EXPLAIN ANALYZE statement

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)

    -> Hash

        -> Table scan on t1  (cost=0.85 rows=6)

(actual time=0.018..0.022 rows=6 loops=1)

 

Upgrade prerequisites

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.

 

The MySQL upgrade checker utility

Included with the MySQL Shell utilities, the upgrade checker utility can assist with identifying potential incompatibilities, such as any:

  • Tables that use obsolete data types or functions.
  • Keyword or reserved word violations.
  • Tables in MySQL 5.7 mysql system database that use the same name as a table used by the MySQL 8.0 data dictionary.
  • Table partitions that reside in shared InnoDB tablespaces.
  • Foreign Key constraint names longer than 64 characters.

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.

 

The mysqlcheck –checkupgrade command

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.

 

Considerations

  • Checking databases for incompatibilities (in test or development environments) is mandatory to ensure that later major version upgrades in production environments will occur smoothly.
  • Running the MySQL update checker utility without identifying incompatibilities is not a guarantee that a major version upgrade will be 100% successful on an Azure Database for MySQL flexible server.
  • Flexible Server is currently running version 8.0.28, and as we upgrade to later versions, it’s possible that the utility will encounter incompatibilities with the service.
  • Using the –databases or –all-databases flag to process all tables in one or more databases might require a long time to invoke the mysqlcheck command because the utility acquires lock on the tables. This is also true for the MySQL upgrade procedure should it determine that table checking is required to process tables the same way.

 

Upgrade with minimal downtime

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:

 

Teaser - Sai.png

 

 

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:

  1. In the Azure portal, select an existing Azure Database for MySQL flexible server running version 5.7.
  2. Under Settings, select Replication, and then select Add replica.

1readreplica-create.jpg

 

  1. On the Add Replica server to Azure Database page, on the Basics tab, specify the Server name and Availability zone, and then select Review + Create.
  2. Review the configuration information, and then select Create.

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:

  1. In the Azure portal, select an existing Azure Database for MySQL flexible server read replica that is running version 5.7.
  2. On the Overview page, select Upgrade.

2ugprade-replica.jpg

 

Notice that the Current MySQL version is 5.7 and the MySQL version to upgrade is 8.0.

 

  1. Select Upgrade.

After the upgrade is completed, a notification will appear. confirming that the read replica is running major version 8.0.

 

  1. On the Overview page of the read replica server, confirm that MySQL major version 8.0 is now running.

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

 

Pre-cutover validation checks

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.

  1. Stop your application from connecting to the primary server.
  2. Ensure that the read replica has completely caught up with any delta changes from the primary server and the data is in sync by viewing the Replication Lag in Seconds metric, which should show as 0.

 

3replica-lag.jpg

 

  1. To view the replication status on the replica server, run the following command:

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.

 

  1. When the value of Seconds_Behind_Master reaches 0, stop replication.
  2. To promote the read replica, under Settings, select Replication, select Promote to the right of the listed read replica.
  3. In the Promote dialog box, select the Note that stopping… check box, and then select Promote.

 

4promote-replica.jpg

 

 

Post-cutover validation checks

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.

 

Summary

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!

Co-Authors
Version history
Last update:
‎Oct 27 2022 09:15 PM
Updated by: