As mentioned in the AWS discussion forum back in October, Amazon has started the end of life (EOL) process for RDS MySQL version 5.5. What this means is:
- AWS will upgrade RDS instances to MySQL 5.7 starting February 9 2021 00:00 UTC during your next defined maintenance window, provided you have one.
- If you don’t have a maintenance window defined, RDS will automatically upgrade you on March 9 00:00 UTC and there is no opt-out.
As any seasoned administrator knows, upgrades can be painful and things might go wrong.
I think we can safely assume that the upgrade will be performed in-place, as it would be too complex and time-consuming otherwise. Since a direct upgrade from 5.5 to 5.7 is not supported, we need to go through 5.6 first. This means instances need to be restarted twice; first to go from 5.5 to 5.6, and then from 5.6 to 5.7.
As per the RDS upgrade guide, any read replicas are upgraded first, while the primary instance is upgraded last.
If there are issues with any of the restarts, you will need to depend on AWS support to help you. Remember you don’t have access to the underlying instance.
Here is a list of other important issues you need to be aware of:
By far the most common issue, some queries will perform differently in 5.7 due to changes in the optimizer code. Some will be faster, some will be slower. There is even the possibility of some queries starting to do a full table scan, in spite of them using an index before.
It is of utmost importance to do regression testing in advance of the upgrade to avoid any unpleasant surprises.
Tables Using the Old Datetime format
Starting with MySQL 5.6, the datetime columns have microsecond precision. This means old 5.5 tables using such columns have to be rebuilt, which can be a lengthy process. This happens at upgrade time by default – more precisely while running mysql_upgrade script.
We can avoid this by converting tables on a read replica, and then promoting it to primary. For more information check out the article about Upgrading to MySQL 5.7 focusing on temporal types.
It is a good practice to check for new reserved words usage prior to doing a database upgrade. Any reserved keywords will cause syntax errors on the new version, potentially breaking your application.
Luckily, the MySQL manual provides a list of reserved words for each version.
It is not clear what will happen with snapshots from the old version. To be on the safe side, a new full backup should be taken right after the upgrade.
Since there is no mention of any rollback mechanism in the communication, you need to think about the strategy if things go wrong past the point of no return.
The replica could be useful as a temporary data source, while any unexpected issues with the new version are addressed.
MySQL 5.5 is ancient by now, and it definitely makes sense to upgrade. Even MySQL 5.6 is going out of support soon – in fact, Percona is offering post-MySQL 5.6 EOL support if you are not ready to upgrade yet.
In this case, the upgrade process is not straight-forward and is definitely not risk-free. Rather than wait and pray nothing breaks, a better strategy is to do some testing, prepare a proper upgrade plan and execute it well in advance of the EOL date.
This will help you minimize downtime, and give you time to deal with any problems beforehand, reducing the risk to your business.
Not ready to give up MySQL 5.6? Talk to our sales team about MySQL 5.6 Post EOL Support.