Aurora Mysql Upgrade from 5.6 to 8 using RDS Blue/Green Deployment

Introduction

Aurora MySQL 5.6 is a relational database engine fully managed and compatible with MySQL. It is developed by Amazon Web Services (AWS) and offers its users a range of features and benefits. It offers the speed and availability of high-end commercial databases while also being cost-effective and easy to use, like open-source databases.

In addition to its compatibility with MySQL, Aurora MySQL 5.6 provides a variety of supplementary functionalities, such as database replication, encryption, and backup and restore capabilities. These additional features make it a comprehensive and versatile solution for managing relational databases. It also offers several additional features, including database replication, encryption, backup, and restore capabilities.

However, it should be noted that Aurora MySQL 5.6 is an older version, and AWS has stopped supporting it since February 2023. MySQL’s most recent version is version 8, which is entirely compatible with its predecessor, MySQL 5.7. This compatibility ensures that users can seamlessly upgrade to the latest version without encountering compatibility issues. MySQL 8 offers advanced features like automatic software patching, crash-resistant storage, and high performance.

Problem Statement/Objective/Scenario

It is not possible to upgrade MySQL 5.6 version to 8 directly. Firstly we have to upgrade 5.6 to 5.7 and then 5.7 to 8. We can upgrade MySQL 5.6 version to 8 through various methods/approaches i.e. blue/green deployment and MySQL dump.

We need to find out the best possible solution with minimum downtime. Blue/Green deployment is the best way that we can go with minimum downtime without affecting the older data.

Solution Approach

MySQL Blue/Green Deployment is a deployment strategy used to upgrade MySQL versions with minimal downtime. The upgrading process includes setting up two identical environments – one referred to as the “blue” environment and the other as the “green” environment. These environments are created to facilitate a seamless transition and minimize the possibility of disruptions during the upgrade process.

The blue environment is the primary environment that is currently live and being used by all applications, while the green environment is the new environment that contains the new version of the MySQL database. After the green environment is set up and thoroughly tested, incoming traffic is rerouted to the green environment, and the blue environment is switched to maintenance mode. This step ensures that users are directed to the updated environment while the previous environment undergoes necessary upgrades without causing any interruptions.

To set up the blue/green deployment, RDS duplicates the entire topology and setup of the Aurora DB cluster to create the green environment. The copied DB cluster and DB instances are assigned a unique identifier by adding random characters to their name. Using this deployment method, users can specify a higher DB engine version and a different DB cluster parameter group for the DB cluster in the green environment. A different DB parameter group can also be specified for the DB instances within the DB cluster. These options provide greater flexibility for users to tailor the upgraded environment to their specific requirements. This process helps to ensure that the upgraded database is compatible with existing applications and that there is minimal disruption to the end users.

Once the green environment is tested, switch over to the blue/green environment. Following the switchover, the Aurora DB cluster in the green environment is promoted as the new production DB cluster. The endpoint names in the current production environment are then assigned to the newly upgraded production environment, requiring no modifications to the application. This process ensures the updated environment is ready to handle production traffic seamlessly. Meanwhile, the DB cluster and instances in the blue environment are renamed, with “-oldn” appended to their current names, where n represents a number.

Prerequisites

The database should be a provisioned type if it is serverless, then we need to create a provisioned database from serverless.

Steps to create a provisioned database from serverless:

• Firstly create a snapshot from a serverless database.
• Create a provisioned type database from a newly created snapshot.

Solution/Steps-by-Step Procedure

• Create a custom cluster parameter and parameter group for the green environment having binlog enabled and binlog_format mixed for better data replication between blue and green databases for MySQL versions 5.7 and 8.
• Create a blue/green environment from the database you want to upgrade and select MySQL version 5.7.
• Once the blue/green environment is created, check whether the blue and green databases are in sync by running the show slave status; command in the green database.
• If blue and green databases are not in sync, make sure they are in sync.
• Check whether bin_log is enabled by running SHOW VARIABLES LIKE ‘log_bin’;. It should be On If it should be OFF. Then SET GLOBAL binlog_format = ‘MIXED’;.
• Check the binlog retention period by running called MySQL.rds_show_configuration();, If it is 0 or less than 24 hours, Then run call MySQL.rds_set_configuration(‘binlog retention hours’, 24);
• The blue database needs to restart after executing the above commands.
• Now change the charset to utf8mb4 as it is the default charset of MySQL version 8 by running ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
• Once all the changes are done, upgrade the green environment from 5.7 to 8 and select the custom cluster parameter and parameter group for version 8.
• Once the upgrade is done, check whether the blue and green databases are in sync or not.
• Test and verify a green environment.
• Once testing is done, switch over to the blue/green environment.

Debugging

While upgrading Aurora MySQL 5.6 to 8, we faced many issues as some features do not work in Aurora MySQL 8 version, So we need to modify the indexes to make our database compatible with MySQL 8 version. If our tables or databases are not compatible with MySQL 8 version, it will throw an error when we try to upgrade from 5.7 to 8. So we need to make sure by running the below command to make our tables compatible with version 8:

Optimize table table-name

We must continuously check the data replication status as our data must replicate from blue to green environments so that our whole data will be there when we switch over. If in case data sync is broken, then we need to troubleshoot by running the below command:

• CALL mysql.rds_stop_replication;

This command stops the replication from the blue to a green environment.

• CALL mysql.rds_skip_repl_error;

This command skips the error which causes the replication issue.

After following the aforementioned steps, when attempting to perform the switchover, the process is interrupted with the error message: “Switchover was canceled due to external replication. Please stop replication from the external database before proceeding.” To resolve this issue, execute the following command:

CALL mysql.rds_reset_external_master;

This command removes the external replication, allowing for a successful database switchover. Please comment down your questions in the comment section below.