Upgrade to Amazon Aurora MySQL version 3 (with MySQL 8.0 compatibility)

Amazon Aurora MySQL-Compatible Edition version 3 (with MySQL 8.0 compatibility) is the most current major version supported on Amazon Aurora MySQL. You can use Amazon Aurora MySQL version 3 to get the latest MySQL-compatible features and performance improvements. MySQL 8.0 introduces several new features, including JSON functions, window functions, common table expressions (CTEs), and role-based privileges. Amazon Aurora MySQL 3 also includes support for new features such as Amazon Aurora Serverless v2, Amazon Aurora zero-ETL, AWS Graviton3 support, enhanced binary log, and Amazon Aurora I/O-Optimized. For a complete list of features, refer to Aurora MySQL version 3 compatible with MySQL 8.0.

When a new major version is released for Amazon Aurora MySQL, you can choose how and when to upgrade your DB clusters. A major engine version upgrade can introduce changes that aren’t backward-compatible with existing applications; therefore, it’s critical to be aware of common challenges and best practices to upgrade your database version and maximize your benefits.

In this post, we discuss a framework to begin with as you prepare for the upgrade, review the end of standard support timelines, and then deep dive into the upgrade process. Starting with Aurora upgrade prechecks, overall steps, and various options you can use to perform the modification to your Aurora MySQL cluster. This post also describes best practices for performance testing before you upgrade your production database clusters, techniques to monitor as changes are being made, and other key considerations.

Preparing for the major version upgrade

While planning a major version upgrade, you can begin by defining a set of testing and validation steps to ensure that the database and application functionality remains as expected. When coming up with the requirements and success criteria for a major version upgrade, it can be helpful to break this topic down into smaller objectives. The following are some key focus areas to provide structure to your planning:

• Compatibility – Verify the correct operation of client applications with the upgrade. Identify the platform, database, and query-level features that must be present or behave in a certain way in order for your applications to continue operating as expected. Test the upgrade process before upgrading in production to identify any compatibility issues. For methods to test, see Testing your DB cluster with a new Aurora version before upgrading.
• Performance – Performance tests prior to upgrading your production database include maintaining adequate application performance and verifying improvements where expected. In this post, we discuss recommendations and tools to test query performance as there can be differences introduced due to changes between MySQL 5.7 and MySQL 8.0.
• Availability – There are two key aspects to availability. The first is to keep the application downtime to a minimum, and the second is to have a fallback option in case of any issues. Depending on the acceptable downtime and degree of control you’d like to have over the upgrade process, you can choose from the multiple upgrade options discussed in this post.
• Effort – While preparing for a major version upgrade, you may also have to gauge the engineering effort required to plan and test the upgrade in non-production environments before making the change in production. Whenever you assess the cost and effort of performing the preparation steps, consider whether that work can be reused elsewhere. As you invest in creating good change management procedures, you can likely reuse that work in other situations.

Upgrade timelines

Amazon Aurora MySQL version 2 (with MySQL 5.7 compatibility) will reach end of Standard Support on October 31, 2024. For detailed instructions, refer to Preparing for Amazon Aurora MySQL-Compatible Edition version 2 end of Standard Support.

End of Standard Support timeline

Note the following key dates in the end of Standard Support timeline:

• Now through October 31, 2024 – You can upgrade clusters from Amazon Aurora MySQL version 2 (with MySQL 5.7 compatibility) to Amazon Aurora MySQL version 3 (with MySQL 8.0 compatibility).
• October 31, 2024 – At this date, Aurora MySQL version 2 reaches the end of Standard Support. To enable you to continue using your existing version for up to 3 years after Aurora or RDS end of standard support date, you can opt in to Amazon RDS Extended Support.

Amazon RDS Extended Support

In September 2023, AWS announced Amazon RDS Extended Support, which is a paid offering where Amazon Relational Database Service (Amazon RDS) provides critical security and bug fixes for an Amazon Aurora MySQL or Amazon RDS for MySQL major version for up to 3 years after Aurora or RDS end of standard support date. For more information, refer to Introducing Amazon RDS Extended Support for MySQL databases on Amazon Aurora and Amazon RDS and Amazon RDS Extended Support costs in Aurora pricing.

For more information regarding updated timelines for release dates and end of support dates for Aurora versions, refer to Amazon Aurora major versions and Amazon Aurora minor versions.

Choosing a target version

As you decide to upgrade your existing Aurora MySQL 2 clusters to Amazon Aurora MySQL 3, you may notice there are multiple minor versions that you can choose as a target version for the upgrade. As of writing this post, the latest Amazon Aurora MySQL version is Amazon Aurora MySQL 3.05, compatible with community MySQL 8.0.32. Aurora MySQL 3 also supports a long-term support (LTS) version, which is Aurora MySQL 3.04 (compatible with MySQL 8.0.28) minor version. Database clusters that use LTS releases can stay on the same minor version for at least 3 years after that release becomes available, which allows for the cluster to undergo fewer upgrade cycles. While upgrading to Aurora MySQL 3, it is recommended to upgrade to the most current default minor version, instead of using the LTS release, to get access to the latest features and bug fixes. For Amazon Aurora MySQL 3 release notes discussing features and improvements in each version, see Database engine updates for Amazon Aurora MySQL version 3.

Prechecks for upgrades to Amazon Aurora MySQL 3

When upgrading any database engine’s major version, checking for compatibility of the new version and its features with your existing application 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 lead to changes in application behavior.

MySQL 8.0 includes a number of changes compared to MySQL 5.7. For example, some keywords might be reserved (for example, RANGE) in MySQL 8.0 that were not reserved previously, and some features may be removed (for example, query cache) in MySQL 8.0. These differences may need to be addressed during the upgrade. As a best practice, we recommend closely reviewing What Is New in MySQL 8.0 to reference all the changes and check if they apply to your workload. Specifically for Amazon Aurora MySQL, you can also review Comparison of Aurora MySQL version 2 and Aurora MySQL version 3 to learn about the changes when you upgrade.

When you initiate an upgrade from Aurora MySQL 2 to Aurora MySQL 3 from the AWS Management Console or AWS Command Line Interface (AWS CLI), Aurora runs prechecks automatically in the background to detect any incompatibilities. These prechecks are mandatory and can’t be skipped. They include some checks that are included with community MySQL and some that are introduced by Aurora. For more information, refer to Major version upgrade prechecks for Aurora MySQL. The prechecks run before the DB instance is stopped for the upgrade, meaning that they don’t cause any downtime for the duration when they run. If the prechecks find an incompatibility, Aurora automatically cancels the upgrade without causing database downtime and no changes are made to the original 5.7-compatible writer instance.

Aurora then generates an event for the incompatibility in the Logs and Events section on the Amazon RDS console and the incompatibilities are also reported in an upgrade-prechecks.log file. A non-zero errorCount indicates that the upgrade was not successful. In most cases, the log entry includes a link to the MySQL documentation for correcting the issue. A sample scenario that could prevent a successful upgrade and its resolution is discussed in Troubleshooting upgrade issues with Aurora MySQL version 3. You can locate the upgrade-prechecks.log in the Logs and Events section on the Amazon RDS console. You can also use AWS CLI by using aws rds describe-db-log-files followed by aws rds download-db-log-file-portion.

Before initiating the upgrade, you can also run ad hoc tests using the community edition MySQL prechecker tool to analyze your existing Aurora MySQL databases and identify the majority of potential upgrade issues.

As a best practice, test the upgrade process before upgrading in production. For methods to test, see Testing your DB cluster with a new Aurora version before upgrading. Performing these tests not only provides you with any upgrade incompatibilities (if any) using the Aurora prechecks log file, but also provides you with an estimate on how long it takes for the prechecks to run and the complete duration of the upgrade. The duration of the upgrade may vary depending on the workload and number of database objects.

Lastly, Aurora prechecks check for incompatibilities in the database objects, such as reserved words in a procedure definition. They do not validate any application-side logic; therefore, you should verify how any reserved keywords or unsupported syntax can impact your application. We strongly recommend thoroughly testing the application to make sure all functional aspects work correctly on the new version before upgrading.

Overall upgrade process

Amazon Aurora MySQL performs a major version upgrade as a multi-stage process as shown in the following flowchart.

As you initiate the upgrade on your Aurora MySQL cluster on version 2.x, Aurora first performs prechecks to locate any compatibility issues with the target version as discussed earlier. It then proceeds to create a pre-upgrade snapshot, which you can use to roll back in case there are any issues with the upgrade. The database is then restarted and if your database had any long-running transactions or a high history length, the undo logs are purged in this step. Because MySQL 8 introduces a new implementation of data dictionary, your database objects are then transformed as per the changes and the writer instance of your cluster is upgraded first, followed by the reader instances. For more information, see How the Data Dictionary is Upgraded and How the Aurora MySQL in-place major version upgrade works.

Perform a major version upgrade of Amazon Aurora MySQL

Now that we’ve reviewed the background material, let’s discuss the steps to perform a major version upgrade for your cluster to Amazon Aurora MySQL 3. Amazon Aurora MySQL lets you manually initiate a major version upgrade by modifying the DB cluster, either via the console, AWS CLI, or Amazon RDS API. The upgrade action requires downtime for applications while the upgrade is ongoing. Aurora upgrades the engine version of the entire cluster; therefore, the upgrade is performed on the writer and reader DB instances at the same time. As a best practice, you can create a manual snapshot before initiating the upgrade to have a rollback plan. In this section, we cover the following upgrade options in the order of simplicity:

• In-place upgrade
• Amazon RDS blue/green deployment
• Snapshot restore and Aurora clone

In-place upgrade

This is the most straightforward option, where you can run an upgrade process on the cluster itself. This does not create a new cluster. This technique keeps the same cluster endpoint and other characteristics of the cluster because it doesn’t require copying all your data to a new cluster volume. While Aurora is performing an in-place upgrade, the cluster observes downtime. One thing to be mindful of is that the upgrade process can’t be canceled mid-upgrade and will run until the upgrade either succeeds or fails. In case of any issues during the upgrade process, Aurora will attempt to roll back the changes. For more details, refer to How the Aurora MySQL in-place major version upgrade works.

This option can be used for upgrading your production environments but requires downtime for the duration of the upgrade. Because this option is straightforward to set up, you can also use it to test the upgrade process before performing it in production. For complete steps to perform an in-place upgrade, see How to perform an in-place upgrade. For troubleshooting tips, see Troubleshooting for Aurora MySQL in-place upgrade.

Amazon RDS blue/green deployment

If your top priority is to have minimal downtime of the database during the upgrade, you can use a managed process that runs the old and new upgraded clusters side by side. With Amazon RDS blue/green deployments, you replicate data from the old cluster to the new one until you are ready for the new cluster to take over. You can use this feature while upgrading your database clusters for minimal downtime and low risk upgrades. A blue/green deployment consists of two database environments: your current production environment, or blue environment, and a staging environment, or green environment. These are kept in sync using MySQL binary log replication. Therefore, before you create a blue/green deployment for an Aurora MySQL DB cluster, the cluster must be associated with a custom DB cluster parameter group with binary logging (binlog_format) turned on. If not already enabled, this change requires a restart of the blue cluster. For steps to create a blue/green deployment, see Creating a blue/green deployment.

You can make changes on the green environment, such as upgrading the major or minor DB engine version, without affecting the blue environment. After you have tested the upgrade on the green environment, you can perform a switchover to promote the green environment. You can specify a switchover timeout between 30–3,600 seconds (1 hour). After the switchover is successful, Amazon RDS renames the endpoints in the green environment to match the corresponding endpoints in the blue environment so that application changes aren’t required. To verify a successful switchover, see Best practices for blue/green deployments. To view a demo with detailed steps, refer to Upgrade to Amazon Aurora MySQL Version 3 with RDS Blue/Green Deployments.

Snapshot restore and Aurora clone

For use cases such as upgrading your dev/test environments, which can be more tolerant of downtime during upgrades, you can use a snapshot restore or Aurora clone. This is often helpful as you create test environments to test the major version upgrade in terms of database performance and application compatibility.

You begin by creating a manual snapshot of the cluster you want to upgrade. You may decide to stop the write workload on the current cluster before taking the snapshot. You can then restore from the snapshot and while restoring, choose the target engine version you wish to upgrade to. The upgrade will be performed as part of the restore process. When the upgrade finishes and the upgraded cluster becomes available, you redirect all client traffic to the newly upgraded cluster. Make sure that all the necessary configuration settings and other customizations have been applied on the new cluster before reenabling workloads. You may remove the original cluster when it’s no longer needed.

For larger datasets, restore times may increase as Aurora builds the distributed storage cluster volume spread across three Availability Zones. An Aurora clone is a faster and cost-effective option. You can stop the write workload and create an Aurora clone of your original cluster. When the clone is ready, you can perform the in-place upgrade (as discussed earlier) to perform the major version upgrade on the clone database. When the upgrade finishes and the cluster is available, you can redirect your application traffic to the upgrade cluster.

Both options result in a downtime because you stop writes before taking a snapshot or creating a clone. Additionally, it creates a new cluster. This means that the cluster will have a new endpoint and the application code needs to be updated to point to the new upgraded cluster.

Summary of major version upgrade methods

The following table summarizes the upgrade options.

Lastly, another option is to set up a manual blue/green deployment to include a rollback option.

Performance testing your test environment prior to upgrading your production environment

It is important to monitor and test the performance of the database after you have upgraded your test environments but before you have performed the upgrade in production. Although the query execution engine generally improves from version to version, in rare cases the queries may use less optimal execution plans in a newer version. You can observe query performance differences as a result of changes between MySQL 5.7 and MySQL 8.0 (for example, new data dictionary). Below are recommendations for diagnosing these performance discrepancies.

• We recommend that you store historical performance data and establish a baseline for your Aurora cluster. You can use this data to compare current performance against past trends. You can also distinguish normal performance patterns from anomalies, and devise techniques to address issues.
• Capture a sample workload from your Aurora MySQL 2 cluster and rerun on Aurora MySQL 3 to review the performance changes if any on version 3. You can use tools like mysqlslap to replay your workload for brute force tests. However, because it reruns identical queries with the same parameters, the results can vary and may require additional verification.
• You can assess Aurora performance using sysbench. For more information, see Amazon Aurora Performance Assessment Technical Guide. Although this guide uses sysbench to assess performance, you can adapt the instructions to use your preferred tool by adjusting the bash script.
• Use Amazon RDS Performance Insights to assess the load on your database, top SQL queries, users, and wait events. Monitor how the database load is impacting the Amazon Aurora MySQL wait events. This will be one of the most critical tools to identify any performance bottlenecks.
• Consider enabling Aurora MySQL slow query logs to find queries that take a long time to run and are therefore candidates for optimization.
• A major version upgrade may result in changes to the query execution plan of your queries. To compare for differences, you can gather sample execution plans from the older version and new version. In addition, review queries to see if you have used optimizer hints such as force index in earlier versions, which may perform differently after the major version upgrade. After you have identified the top SQL contributing to database waits using Performance Insights and the slow query log, you can optimize the query using some of these tools:
  • • EXPLAIN can show the individual steps involved in running a query.
    • Query profiling indicates resource usage for statements that are running during the current session.
    • ANALYZE TABLE updates the table and index statistics which helps the optimizer choose an appropriate plan to run the query.
• The query cache is removed from community MySQL 8.0 and also from Amazon Aurora MySQL 3. If you have been using query cache in your Aurora MySQL 2 clusters, verify how this change impacts the performance of your database in terms of query latency metrics such as SelectLatency.
• MySQL 8.0 Community Edition handles temporary tables differently from earlier MySQL versions and this change in behavior is followed in Amazon Aurora MySQL 3. To review the changes and its effects if your workloads create a lot of temporary tables, see New temporary table behavior in Aurora MySQL version 3.
• The default character set in MySQL 8.0 Community Edition is utf8mb4 and the same applies to Aurora MySQL 3. Verify if any changes need to be made to the character set prior to the upgrade, because some queries and stored procedures may perform poorly if they are unable to use table indexes due to conflicting collations.
• As you are comparing performance between two versions, check for any changes in the default of performance-related parameters, which can often be a good step to isolate and narrow down issues. For more information, see Changed Server Defaults.

Monitoring and alerting

Now that you have initiated the upgrade, let’s review some ways in which you can monitor the health of your DB instances and also check for any errors during the upgrade:

• You can check the current status of an upgrade by monitoring the Aurora events. You can subscribe to the RDS event IDs corresponding to upgrades in the DB instance events to be notified of the upgrade status.
• Use Amazon CloudWatch metrics like CPUUtilization and FreeableMemory to monitor the resource consumption after the upgrade and how it impacts query latency metrics such as SelectLatency, CommitLatency. For a complete list of metrics, see Amazon CloudWatch metrics for Amazon Aurora. You can also use CloudWatch alarms to get notified if a metric exceeds a specified threshold and be able to troubleshoot the issue.
• For an Aurora upgrade, the initial steps include Aurora performing a clean shutdown and completing outstanding operations such as transaction rollback and undo purge. Therefore, while preparing to initiate the upgrade in the production environment, verify the database doesn’t have long-running transactions, which can impact the duration of the upgrade. Similarly, a high rollback segment history length may cause the upgrade process to slow down as Aurora purges the undo logs before upgrading to the target version. To verify, you can use the following commands:

  • SHOW ENGINE INNODB STATUS

  • SHOW FULL PROCESSLIST

  • SELECT NAME,COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME="trx_rseg_history_len";

• Amazon Aurora MySQL writes the mysql-error.log file if any errors are encountered on cluster startup and shutdown. The log files also have a timestamp that helps you determine when the log entries were written. You can review the logs in case any issues occurred during upgrade. For more information, see Aurora MySQL error logs.
• If the upgrade fails for any reason, you can review the mysql-error.log file to troubleshoot the issue. If the error was encountered during the upgrade prechecks, review the error and steps to resolve in the upgrade-prechecks.log file.

Key considerations

When upgrading from Amazon Aurora MySQL 5.7 to 8.0, there are a few key considerations:

• Aurora creates a new parameter group for the target upgrade version automatically during a major version upgrade if one isn’t provided. For Amazon Aurora MySQL 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. Consult Parameter changes for Aurora MySQL version 3 to learn about parameter changes.
• If you are using a custom value for the lower_case_table_names parameter, you must set up the custom parameter group with this setting in advance. We recommend that you use the same setting for lower_case_table_names when you upgrade from Amazon Aurora MySQL version 2 to version 3. Unlike MySQL 5.7, MySQL 8.0 restricts changing the lower_case_table_names value after the upgrade. Review your application requirement and decide the desired value to be set. You cannot change this later.
• If you are using Amazon Aurora Global Database, review the steps in In-place major upgrades for global databases.
• If you are using Aurora Serverless v1, see Upgrade from Amazon Aurora Serverless v1 to v2 with minimal downtime.
• If your Aurora cluster contains a large number of database objects (tables, databases, events, triggers, stored procedures), consider using a larger instance class, which will provide more CPU capacity and memory resources for the upgrade to complete faster.

Conclusion

In this post, we reviewed the Amazon Aurora MySQL 3 upgrade procedures, different upgrade processes, and best practices. We encourage you to upgrade your Amazon Aurora MySQL 2.x clusters and perform the required testing to take advantage of newer features and optimizations available on the latest versions.

For more information about the upgrade, see Upgrading Amazon Aurora MySQL DB clusters.

About the Authors

Shagun Arora is a Database Specialist Solutions Architect at Amazon Web Services. She works with customers to design scalable, highly available and secure solutions in the AWS Cloud.

Dilip Simha is an Engineering Manager in Amazon Aurora MySQL team. He has two decades of experience in enterprise software and has spent the last 5 years with Amazon.

Aditi Sharma is a Technical Account Manager in AWS and started her journey as a Cloud Support Engineer in the RDS Databases team where she worked on various DB engines. She has since then held several roles in AWS before moving to the Technical Account Manager role. Prior to joining AWS, she was working as a Software Development Engineer in a Banking industry. Aditi holds a Master’s in Management Information systems and a Bachelor’s in Computer Science Engineering and also Certified Scrum Master, Product Owner, AWS Database specialist and Associate Solutions Architect.

Isael Pimentel is a Senior Technical Account Manager at AWS with over 15 years of experience in developing and managing complex infrastructures, and holds several certifications including AWS Solution Architect, AWS Network Specialty, AWS Security Speciality, MSCA, and CCNA.