Planning for MySQL 5.7 End of Life with AWS Aurora

If you are one of the many MySQL 5.7 RDS Aurora customers, you will have received notification that MySQL 5.7 will reach its end of life in October 2024. This article covers prerequisites for upgrading to MySQL 8.0, which are specific to Aurora.

Versions

First off, we will cover the versions of Aurora. MySQL 5.7 is under the Aurora 2.X version series, with the current highest version being Aurora 2.12.3 (MySQL 5.7.44 compatible). MySQL 8.0 is part of the Aurora 3.X series, with the latest version of 3.7.0 (compatible with MySQL 8.0.36). 

In order to upgrade to MySQL 8.0 in Aurora, you need to be on at least version 2.11.1. If your instance is on an older version of 2.X, you will need to do a minor version upgrade to at least 2.11.1 before upgrading to 3.X.

The AWS CLI command below can be run to provide a list of Aurora MySQL clusters and their corresponding versions.

aws rds describe-db-clusters --output table --filters 
'Name=engine,Values=aurora-mysql' --query 'DBClusters[].{DBClusterIdentifier:DBClusterIdentifier,
EngineVersion:EngineVersion}'

--------------------------------------------------------------------------------
| DescribeDBClusters                                                           |
+-------------------------------------------------+----------------------------+
| DBClusterIdentifier                             |       EngineVersion        |
+-------------------------------------------------+----------------------------+
|  mycluster-east1                                |  5.7.mysql_aurora.2.11.2   |
|  mycluster-test2                                |  5.7.mysql_aurora.2.07.10  |
|  mycluster-west3                                |  8.0.mysql_aurora.3.05.2   |
+-------------------------------------------------+----------------------------+

Instance Class Size

AWS is deprecating several instance class sizes which were available using MySQL 5.7, but will not be available for version 8.0. It is a requirement to upgrade the instance class size if your Aurora instances are on older sizes, such as t.3.small, db.r4 series.

This will be done at the instance level, rather than the cluster level. To do the upgrade, it is recommended to change the readers first, and then the writer. The writer will automatically fail over and a reader will be promoted.

To check the instance class sizes for your MySQL Aurora servers, run the following cli command.

aws rds describe-db-instances --output table --filters
'Name=engine,Values=aurora-mysql' --query 'DBInstances[].{DBInstanceIdentifier:DBInstanceIdentifier,
DBInstanceClass:DBInstanceClass}'
-----------------------------------------------------
| DescribeDBInstances                               |
+-----------------+---------------------------------+
| DBInstanceClass |  DBInstanceIdentifier           |
+-----------------+---------------------------------+
|  db.r5.large    |  mycluster-east1-instance-1     |
|  db.r5.large    |  mycluster-east1-instance-2     |
|  db.t3.small    |  mycluster-test2-instance-1     |
|  db.t3.small    |  mycluster-test2-instance-1     |
|  db.t3.medium   |  mycluster-west3-instance-1     |
|  db.t3.medium   |  mycluster-west3-instance-2     |
+-----------------+---------------------------------+

To see a list of supported instance classes for Aurora 3.X, check this link. 

Custom Parameter Groups

If you are using custom parameter groups for your Aurora 5.7 clusters, new Aurora 8.0 parameter groups will need to be created before you upgrade to MySQL 8.0. In addition to creating the new parameter groups, the user modified parameters from your existing groups will need to be added to the new groups.

To check and see which parameters were modified in one of your 5.7 parameter groups, run the following cli commands to check for changes made in both cluster and instance parameter groups.

aws rds describe-db-cluster-parameters --db-cluster-parameter-group-name
aurora-test-cluster-mysql57 --source user --query 'Parameters[].{ParameterName:ParameterName,
ParameterValue:ParameterValue}' --output table

-------------------------------------------------------
|             DescribeDBClusterParameters             |
+----------------------------------+------------------+
|           ParameterName          | ParameterValue   |
+----------------------------------+------------------+
|  binlog_format                   |  ROW             |
|  connect_timeout                 |  120             |
|  interactive_timeout             |  300             |
|  log_bin_trust_function_creators |  1               |
|  max_allowed_packet              |  1073741824      |
|  net_read_timeout                |  300             |
|  net_write_timeout               |  300             |
|  performance_schema              |  1               |
|  server_audit_events             |  QUERY_DDL       |
|  server_audit_logging            |  1               |
|  server_audit_logs_upload        |  1               |
|  wait_timeout                    |  300             |
+----------------------------------+------------------+

aws rds describe-db-parameters --db-parameter-group-name aurora-test-mysql57 --source
user --query 'Parameters[].{ParameterName:ParameterName,ParameterValue:ParameterValue}'
 --output table
--------------------------------------------
|           DescribeDBParameters           |
+----------------------+-------------------+
|     ParameterName    |  ParameterValue   |
+----------------------+-------------------+
|  connect_timeout     |  120              |
|  interactive_timeout |  300              |
|  join_buffer_size    |  524288           |
|  long_query_time     |  2                |
|  max_allowed_packet  |  1073741824       |
|  max_connect_errors  |  1000000          |
|  max_heap_table_size |  33554432         |
|  net_read_timeout    |  300              |
|  net_write_timeout   |  300              |
|  performance_schema  |  1                |
|  slow_query_log      |  1                |
|  tmp_table_size      |  33554432         |
|  wait_timeout        |  300              |
+----------------------+-------------------+

Conclusion

Major version upgrades require a fair amount of time and planning. Implementing prerequisite changes before the upgrade can save time during the upgrade maintenance, as well as prevent unforeseen issues. Upgrading to MySQL 8.0 involves first ensuring your Aurora version is at least 2.11.1, followed by moving to the 3.X series. Deprecated instance class sizes must be updated to compatible ones for MySQL 8.0, starting with reader instances before the writer. Additionally, custom parameter groups from MySQL 5.7 need to be recreated for MySQL 8.0, incorporating user-modified parameters. Following these steps will ensure a smooth transition and continued support for your Aurora MySQL databases.