Upgrading from Amazon RDS for MySQL version 5.5

Amazon Relational Database Service (Amazon RDS) for MySQL 5.5 major version is reaching end of life, and it’s recommended to upgrade to newer supported major versions. Amazon Relational Database Service (Amazon RDS) provides newer versions of databases so you can keep your DB instances up to date. These versions include bug fixes, security enhancements, and other optimizations. When Amazon RDS for MySQL supports a new version, you can choose how and when to upgrade your DB instances. Amazon RDS for MySQL supports major version and minor version upgrades. A major engine version upgrade can introduce changes that aren’t backward-compatible with existing applications, whereas minor version upgrades include only changes that are backward-compatible with existing applications.

Although Amazon RDS for MySQL allows the option to automate some of these patching decisions, such as automatic minor version upgrades for performing minor version upgrades, major version upgrades must be requested explicitly. Therefore, it’s critical to be aware of common issues, steps involved, and best practices to upgrade your major version with the least impact on your applications.

In this post, we look at the following topics:

• Amazon RDS for MySQL 5.5 end of life timeline
• Upgrade choices available to you
• Best practices to follow during the upgrade process

Amazon RDS for MySQL 5.5 major version end of life timeline

The MySQL community releases new major versions with a defined end of life (EOL) policy for older major versions. This allows you to make version and upgrade decisions into the future. The community EOL policy is to support a major version for 8 years after its initial release. When a major version is marked for end of life, there are no more updates and most importantly no more security fixes after the EOL date. Community MySQL has already discontinued support for MySQL 5.5 as of December 3, 2018. Amazon RDS for MySQL 5.5 reaches end of life on February 9, 2021, and support for Amazon RDS for MySQL 5.6 is ending in early 2021.

To encourage the use of newer versions of MySQL, Amazon RDS for MySQL will stop supporting the creation of new MySQL 5.5 instances starting January 13, 2021, via both the AWS Management Console and AWS Command Line Interface (AWS CLI).

For more information about the EOL dates for Amazon RDS for MySQL 5.5, see Announcement: Amazon RDS for MySQL 5.5 End-of-Life date is approaching. 

Version upgrade choices in Amazon RDS for MySQL

The following table shows the major version upgrade paths that Amazon RDS for MySQL supports (as of this writing).

Major version upgrades in Amazon RDS for MySQL can go only from one major version to the next immediate major version. For example, to upgrade an Amazon RDS for MySQL 5.5 major version instance to Amazon RDS for MySQL 8.0 major version, the following upgrade steps are involved:

• MySQL 5.5 to MySQL 5.6
• MySQL 5.6 to MySQL 5.7
• MySQL 5.7 to MySQL 8.0

To find out if you have any MySQL 5.5 instances in a given Region, on the Amazon RDS console, choose Recommendations. On the Active tab, your instances are listed under Deprecated major versions.

You can also get a list of all valid upgrade target versions for a database version using the describe-db-engine-versions AWS CLI command in a given Region:

aws rds describe-db-engine-versions --output=table --engine mysql --engine-version 5.5

As part of deciding which version to upgrade to, you have the following options:

• MySQL 5.6 – Community end of life planned for February 5, 2021. Amazon RDS for MySQL 5.6 will reach end of life in early 2021.
• MySQL 5.7 – Community end of life planned until October, 2023. Amazon RDS for MySQL will continue to support until the community EOL date.
• MySQL 8.0 – Community end of life planned until April, 2026. Amazon RDS for MySQL will continue to support until the community EOL date.

If you have any MySQL 5.5 instances in your account, you can upgrade them before February 9, 2021. For instructions on upgrading a database instance, see Upgrading the MySQL DB engine.

Choosing a target engine version for upgrade

We recommend you upgrade any of your RDS for MySQL 5.5 DB instances to 5.7 or newer versions because they are the long-term support releases.

When upgrading any database engine’s major versions, checking for compatibility of the new version and its features plays a crucial role in the upgrade’s overall success. MySQL database versions and releases can differ in how they work and interact with applications, which may result in compatibility issues. Therefore, it’s crucial to upgrade your RDS for MySQL DB instances to a version that is compatible, certified, and supported by your application.

In certain cases, feature improvements and performance optimizations in newer releases also play a pivotal role in picking a target engine version for your upgrade.

For example, MySQL 5.6 introduced features such as multi-threaded replication, replication checksum, and persistent optimizer statistics.

MySQL 5.7 introduced several new features, such as native support for the JSON data type and built-in JSON functions, and query performance improvements via optimizer improvement, resulting in better parsing and explain plans. It also introduced dynamic buffer pool resizing and improved tablespace discovery for minimizing crash recovery duration.

Similarly, MySQL 8.0 offers better performance, reliability, and new features such as Window Functions and Common Table Expressions, better performance compared to MySQL 5.7 and optimizations around fast DDL operations and logging improvement for error logs, and introduction of the TempTable storage engine for internal temporary tables.

In addition to newer features and optimizations available in each major version, community MySQL also provides performance benefits version over version. The following simple benchmark visualization depicts the performance of each major version with default configurations and identical workloads across all the major versions. The performance increase is one of the crucial reasons to stay current with the latest releases. However, it’s always advised to test your applications and queries for performance when moving between major versions.

In addition, Amazon RDS for MySQL supports the latest instance classes using AWS Graviton2 processors such as r6g and m6g starting from Amazon RDS for MySQL 8.0.17 and later, which offers better cost-performance for your RDS for MySQL DB instances.

Downtime considerations for a major version upgrade

Amazon RDS for MySQL lets you manually initiate a major version upgrade by modifying the DB instance—either via the console, AWS CLI, or Amazon RDS API. This is an in-place upgrade and requires downtime for your applications during the upgrade process. The duration of the upgrade process depends on the engine version and the DB instance class. The biggest determinant of upgrade duration when upgrading to MySQL 5.7 major version is if there is a need to convert date and time format columns to the new format, which can take hours for large tables. You can determine the amount of time it takes to upgrade by performing trial upgrade exercises with a snapshot restore in a staging environment. Upgrading your production database instance is an irretrievable process and if any issue occurs during or after the upgrade process, the only fallback option is to restore from the latest snapshot. So as a best practice, always take a snapshot before initiating the upgrade.

If you’re running your DB instance in a Multi-AZ deployment, Amazon RDS for MySQL upgrades both the primary and Multi-AZ synchronous standby simultaneously.

If your RDS for MySQL DB instance is in use with a production application, you can reduce the amount of downtime for your application by using a read replica to perform most of your maintenance tasks ahead of time. This method involves setting up a logical read replica that has the same major and minor version as the primary instance, then upgrade the read replica to a newer version and ensure that all validation steps for the application pass through. When this is done, make sure the replication has caught up completely by stopping application writes to ensure data integrity and then promote the read replica to a standalone instance and point the application to the upgraded promoted instance that has the up-to-date data. For best practices on achieving minimum downtime with a read replica, see Best practices for using a MySQL read replica to upgrade an Amazon RDS for MySQL database.

How Amazon RDS for MySQL performs a major version upgrade

When a major version upgrade is invoked on the console or via the AWS CLI or Amazon RDS API, Amazon RDS automation completes the following steps:

1. Verifies if there are any read replicas that need to be upgraded.
2. Runs a mandatory pre-upgrade check when upgrading from MySQL 5.7 to 8.0. This makes sure that there are no incompatibilities when moving to 8.0 and blocks the upgrade process if the pre-upgrade checks detect incompatibilities to avoid unplanned downtime. To find the incompatibilities the pre-upgrade checks detected, you can check the PrePatchCompatibility log of the DB instance. For a list of incompatibilities checked, see Prechecks for upgrades from MySQL 5.7 to 8.0.
3. Takes a pre-upgrade snapshot (if backups are enabled on the RDS for MySQL DB instance). This snapshot can be used for rollback purposes if the upgrade runs into any issues.
4. Shuts down the instance and prepares the instance for upgrade.
5. Runs mysql_upgrade script to upgrade tables to the newer version.
6. Takes a post-upgrade snapshot.

Key considerations and best practices

When performing an upgrade, consider the following:

• Take a manual snapshot using the create-db-snapshot AWS CLI command or via the console prior to starting the upgrade phase.
• If your RDS for MySQL 5.5 instances are running on one of the deprecated t1, m1, or m2 instance types, you have to change to a newer instance type prior to initiating the major version upgrade. You can identify any RDS for MySQL DB instances running the older instance types via the console or the describe-db-instances AWS CLI command.
• Amazon RDS truncates the slow_log and general_log tables during a major version upgrade. To preserve log information, back up the log contents before the major version upgrade. Take a table-level backup of the slow_log and general_log tables using the following code:

  mysqldump -h <RDS-instance-endpoint> -u <username> -p --databases mysql --tables slow_log --skip-lock-tables > filename.sql

• By default, when upgrading from one major version to next higher major version, Amazon RDS for MySQL chooses the default option group if the corresponding custom option group for the target version isn’t chosen. Always choose to have a custom option group created in the target version that reflects the options that you have from the version you’re upgrading from.
• Amazon RDS creates a new parameter group for the target upgrade version automatically during a major version upgrade if one isn’t provided. For RDS for MySQL DB instances using custom parameter groups, when upgrading, always choose a parameter group in the target version that matches your current version parameter group with similar parameters carrying similar or same values. If the parameters don’t exist or have their default values changed between versions, carefully test out and validate the results and behavior prior to upgrading the instance.
• Disable any jobs prior to upgrading and ensure you enable them post-upgrade. Also test job behavior in a lower environment prior to performing the actual database upgrade process.
• Perform an overall benchmark for the application to understand if there are any dependencies and application behavior deviation in a lower environment prior to performing the major version upgrade in the production environment.
• Keep a list of clients and their drivers used along with the appropriate version numbers. Identify that these clients and their drivers work with newer versions of MySQL.
• Post-upgrade, we strongly recommend you test your automated backup restore process, disaster recovery, or production to non-production refresh procedures.

Conclusion

In this post, we reviewed the MySQL 5.5 end of life support timeline in Amazon RDS for MySQL, studied version upgrade choices and how to pick a target version for upgrade, and covered the best practices and what to expect during the upgrade process. With Amazon RDS for MySQL 5.5 reaching end of life on February 9, 2021, we encourage you to upgrade your RDS for MySQL 5.5 instances to Amazon RDS for MySQL 5.7 or Amazon RDS for MySQL 8.0 version at the earliest to test out the version upgrades, validate your applications, and take advantage of newer features and optimizations available for newer versions.

For more information about the upgrade, see Upgrading the MySQL DB Engine.

About the Authors

Sai Kondapalli is a Database Specialist Solutions Architect at Amazon Web Services.

Gaurav Sharma is a Senior Solutions Architect at AWS. He works with customers providing technical assistance and architectural guidance on various AWS services. Prior to AWS, Gaurav worked for over 23 years in database management, database engineering, and cloud architecture roles for various large-scale enterprises.

Nitin Aggarwal is a Solutions Architect at AWS. He works with digital native business customers providing architectural guidance on AWS services.

Vivek Kumar is a Solutions Architect at AWS. He works with digital native business customers providing architectural guidance on AWS services.