If you’re still running RDS 5.5 you have reached the end of the line, its time to upgrade… heck, if you’re running RDS 5.6 an upgrade is just as mandatory!

 

MySQL 5.7.x has been GA for 5 years and is the de-facto standard for many organisations while MySQL 8.x is doing very well to take the trophy of widespread adoption. We are seeing MySQL 8.0 out in the field more often than ever before, however MySQL 5.7 still seems to be the preferred version for the time being.

Specifically regarding RDS 5.5 (as per the official Amazon RDS forum):

“If you do not upgrade your databases February 9, 2021, RDS will upgrade your MySQL 5.5 databases to 5.7 during a scheduled maintenance window between Feb 9, 2021 00:00:01 UTC and March 9, 2021 00:00:01 UTC. On March 9, 2021 00:00:01 AM UTC, any Amazon RDS for MySQL 5.5 databases that remain will be upgraded to version 5.7 regardless of whether the instances are in a maintenance window or not”.

And as for RDS 5.6 (again as per the official Amazon RDS forum):

“If you do not upgrade your databases before August 3, 2021, RDS will upgrade your MySQL 5.6 databases to 5.7 during a scheduled maintenance window between August 3, 2021 00:00:01 UTC and September 1, 2021 00:00:01 UTC. On September 1, 2021 00:00:01 AM UTC, any Amazon RDS for MySQL 5.6 databases that remain will be upgraded to version 5.7 regardless of whether the instances are in a maintenance window or not.”

So what is trickery is typically needed to upgrade RDS? The RDS upgrade is quite simple to execute and can be performed in-place… indeed it does and this makes things easy in terms of performing the upgrade but not necessarily preparing for a new version.

The upgrade process can be performed from the AWS web console or via the AWS cli and incurs about the same amount of downtime either way while the process completes. This downtime is incurred regardless of whether a Multi-AZ deployment is in place or not. There are other ways to handle the upgrade process and we typically recommend the following approach:

  • Create a new 1st tier read-replica
  • Perform an in-place upgrade on the new read replica instance
  • Build out the needed number of read replicas for your environment as 2nd tier read replicas while running your current version (creating a Multi-AZ if that is was the preferred deployment choice in the previous cluster).
    • NOTE: This is to reduce downtime in repointing read replicas to the newly promoted primary. There are of course methods where GTID (when available) can be employed to repoint the pre-existing replicas after upgrading, this can lead to a more complicated cutover process and is typically only recommended in larger clusters.
  • Once the upgraded cluster is ready, then promote the replica and switch applications over while still replicating from the old version. Using ProxySQL allows seamless transition of any changes to the RDS endpoint during the promotion.
    • NOTE: You can’t switch the direction of replication in order to replicate from a newer version of RDS to an older version in case of the need to rollback.  However you can still salvage events from the new instance’s binary logs and replay them on your old MySQL RDS instance as regular SQL in the case you encounter unexpected behavior after the upgrade.

Speaking of which, there are several considerations that need to be taken into account to avoid unexpected behavior. Although RDS performs a few prechecks and Oracle’s documentation clearly states breaking or incompatible changes from one MySQL version to the next an audit is always required.

There are key activities such as reviewing new keywords and reserved words which may be introduced in a new MySQL version which are used by an application for some reason. Some are more dangerous than others – a few good examples are:

Changes like these are a common pitfall when upgrading as they are often names for tables in many popular database schemas and could completely cripple applications if overlooked.

Our tool of choice for upgrade preparation is of course ProxySQL. We use this extensively when helping our clients upgrade RDS (and of course regular MySQL!).

  • The first area where ProxySQL really helps is to understand the DB workload by analyzing the query digests and identifying issues with specific query patterns (or keywords) that could be affected. A plethora of information is available within ProxySQL, including insights into the “total time required to fully replay the recorded workload”. This indicates how long we need to test the DB workload in order to be sure all my queries have executed. This can be identified by reviewing the query digest “first_seen” / “last_seen” fields.
  • The query digest statistics also provide the information we need to search for possible new keywords used by an application prior to an upgrade.

Apart from reviewing live database traffic we also recommend replaying the application workload against the new version. In the absence of load testing tools that simulate real production traffic for your application, you can use ProxySQL’s Mirroring feature.

  • ProxySQL can mirror live traffic being sent to your 5.5 / 5.6 RDS cluster to another separate and isolated 5.7/8.x RDS cluster initially created from the same backup snapshot with replication synced up to the point where mirroring was enabled. This is a key validation step as it exposes the actual behavior of the new version of RDS (or MySQL) when running your application’s workload against it, without posing risks to production data.
  • This also provides an elaborate upgrade validation capability without the need for load testing tools. A careful review of the behavior of the new cluster vs. the old is still required, especially to verify data consistency and query response times as the same workload executes on the two datasets / versions in parallel.

There are several other approaches in upgrading RDS and in general if you need to guarantee uptime and perform upgrades transparently its going to require a bit more effort than just hitting the upgrade button in the AWS console…  but the extra effort and time spent however the effort is well worth it!

Don’t leave the fate of your RDS 5.5 & 5.6 instances in the hands of a forced automated upgrade process, take control and upgrade unadventurously today with ProxySQL!

If you need a hand in upgrading or any assitance with your MySQL scaling needs reach out to us here and learn more about our services.