AWS Database Blog

Upgrade Amazon Aurora MySQL-Compatible Edition version 1 (with MySQL 5.6 compatibility)

December 2022: This post was updated with support for blue/green deployments for Amazon RDS and Amazon Aurora.

November 2022: This post was updated with changes introduced with the launch of Amazon Aurora MySQL-Compatible Edition versions 2 and 3, and in-place upgrade from version 2 to 3.

Amazon Aurora MySQL-Compatible Edition version 1 (with MySQL 5.6 compatibility) is planned to reach end of life on February 28, 2023, and it’s recommended to upgrade to newer supported major versions before it reaches the end of its lifecycle. Amazon Aurora provides newer versions of database engines so you can keep your DB clusters and instances up to date. Newer DB versions include bug fixes, security enhancements, improved features (including support for latest instance types) and other optimizations.

When a new version is released for 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, steps involved, and best practices to upgrade your major version with the least impact on your applications.

In this post, we discuss the following topics:

  • Aurora MySQL version 1 end of life timeline
  • Available version upgrade choices
  • Upgrade best practices

Aurora with MySQL 5.6 compatibility end-of-life timeline

The following timeline is provided for upgrading Aurora MySQL version 1 clusters (provisioned and Aurora Serverless v1) that are reaching end of life. For each action, the start time is midnight Universal Coordinated Time (UTC).

  • Now through February 28, 2023 – At any time, you can start upgrades of Aurora MySQL version 1 (with MySQL 5.6 compatibility) clusters to Aurora MySQL version 2 (with MySQL 5.7 compatibility).
  • January 16, 2023 – After this time, you can’t create new Aurora MySQL version 1 clusters or instances from either the AWS Management Console or the AWS Command Line Interface (AWS CLI). You also can’t add new secondary Regions to an Aurora global database running Aurora MySQL version 1. You will also be unable to create a new cross-Region read replica running Aurora MySQL version 1. You can still do the following for existing Aurora MySQL version 1 clusters until February 28, 2023:
    • Restore a snapshot taken of an Aurora MySQL version 1 cluster.
    • Add read replicas (not applicable for Aurora Serverless DB clusters).
    • Change instance configuration.
    • Perform point-in-time restore.
    • Create clones of existing version 1 clusters.
    • Create a new cross-Region read replica running Aurora MySQL version 2 or higher.
  • February 28, 2023 – After this time, we plan to automatically upgrade Aurora MySQL version 1 clusters to the default version of Aurora MySQL version 2 within a scheduled maintenance window. Restoring Aurora MySQL version 1 DB snapshots results in an automatic upgrade of the restored cluster to the default version of Aurora MySQL version 2 at that time.

You can find upcoming end-of-life dates for Aurora major versions in Amazon Aurora versions. Amazon automatically upgrades any clusters that you don’t upgrade yourself before the end-of-life date. After the end-of-life date, these automatic upgrades to the subsequent major version occur during a scheduled maintenance window for clusters.

To find out if you have any Aurora MySQL version 1 clusters in a given AWS Region, refer to Finding clusters affected by this end-of-life process.

Available version upgrades in Aurora MySQL

The following table shows the major version upgrade paths that Aurora MySQL currently supports.

Current Version

Major Version Upgrade Path

Aurora MySQL version 1 (with MySQL 5.6 compatibility) Aurora MySQL version 2 (with MySQL 5.7 compatibility)
Aurora MySQL version 2 (with MySQL 5.7 compatibility) Aurora MySQL version 3 (with MySQL 8.0 compatibility)

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 aurora --engine-version 5.6

As part of deciding which major version to upgrade to, refer to How long Amazon Aurora major versions remain available.

Selecting a target engine version for upgrade

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 result in compatibility issues. Therefore, it’s crucial to upgrade your Aurora MySQL DB instances to a version that is compatible and supported by your application. If the application was written in-house, we strongly recommend thoroughly testing the application to make sure all functional aspects work correctly on the new version before upgrading.

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. To review the improvements and features added in MySQL 5.7 refer to What Is New in MySQL 5.7. Aurora MySQL version 2 (compatible with MySQL 5.7) also introduced features such as JSON support, spatial indexes, and generated columns. To understand various improvements and bug fixes, refer to Database engine updates for Amazon Aurora MySQL version 2. It’s recommended to upgrade to the default minor version of Aurora version 2, Aurora MySQL 2.10.2 or you may also choose the latest version available 2.11.1 (as of this writing).

Similarly, MySQL 8.0 offers better performance, reliability, and new features such as common table expressions, improvements to binary log replication and other enhancements. In addition, Aurora version 3 (compatible with MySQL 8.0) also added new parallel query features and support for Aurora Serverless v2. For more information on the feature improvements, refer to Aurora MySQL version 3 compatible with MySQL 8.0.

In addition, Aurora MySQL supports the latest instance classes using AWS Graviton2 processors such as x2g and r6g starting from Aurora version 2.09.2 and higher, which offer up to 35% better price/performance with Aurora, based on internal testing of workloads with varying characteristics of compute and memory requirements.

Downtime considerations for a major version upgrade

Aurora MySQL lets you manually initiate a major version upgrade by modifying the DB instance, either via the console, AWS CLI, or Amazon RDS API. The upgrade action requires downtime for your 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. The duration of the upgrade process depends on multiple factors, like properties of your schema and how busy the cluster is. You can determine the amount of time it takes to upgrade by performing trial upgrade exercises with an in-place upgrade or snapshot restore in a staging environment. For faster testing, you may also test by creating an Aurora clone. After the major version upgrade is complete, you can’t revert to the previous version of the database engine. If you want to return to the previous version, restore from the latest pre-upgrade DB snapshot. So as a best practice, take a snapshot or create an Aurora clone before initiating the upgrade as a fallback option.

If you have an Aurora MySQL version 1 cluster and want to upgrade it to version 2, you can do so by running an in-place upgrade. 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. Identify the Aurora version for your cluster—if your cluster is running a version that’s lower than 1.22.3, the upgrade might take longer because Aurora MySQL automatically performs an upgrade to 1.22.3 as a first step. To minimize downtime during the major version upgrade, you can do an initial minor version upgrade to Aurora MySQL 1.22.3 in advance. 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, we attempt to roll back the changes. Therefore, it’s important to create a manual snapshot or an Aurora clone before initiating the upgrade. For more details, refer to How the Aurora MySQL in-place major version upgrade works.

If you have an Aurora MySQL version 1 cluster and want to upgrade it to Aurora MySQL version 3, you have to follow a two-step process: upgrade to Aurora version 2, and then perform another major version upgrade to Aurora version 3. In-place upgrade functionality also exists while upgrading from Aurora version 2 to 3, however, the time needed for an in-place upgrade can vary depending on the properties of your schema and how busy the cluster is. Thus, depending on the needs for your cluster, you might want to choose among the multiple upgrade techniques. These include in-place upgrade and snapshot restore. For more details, refer to Upgrading from Aurora MySQL 2.x to 3.x. In addition, a direct snapshot restore isn’t available from Aurora MySQL version 1 clusters to Aurora MySQL version 3. Refer to Aurora MySQL major version upgrade paths to see if an in-place upgrade is supported with the target engine version of your choice.

For situations where the top priority is to reduce downtime, you can also use Amazon RDS Blue/Green Deployments for performing the major version upgrade in provisioned Amazon Aurora clusters. A blue/green deployment creates a staging environment that copies the production environment. You can make changes to the Aurora DB cluster in the green environment without affecting production workloads. The switchover typically takes under a minute with no data loss and no need for application changes. For details, refer to Using Amazon RDS Blue/Green Deployments for database updates.

Perform a major version upgrade in Aurora MySQL

Now that we’ve reviewed the background material, let’s discuss the steps to perform a major version upgrade for your cluster impacted by the end-of-life process of Aurora version 1.

Upgrade from Aurora MySQL version 1 to 2

To upgrade to Aurora version 2, you can use the in-place upgrade method as mentioned in the previous sections. The steps are as follows:

  1. On the Amazon RDS console, in the left navigation pane, choose Databases.
  2. On the list, choose the DB cluster that you want to modify and choose Modify.
  3. For Version, choose an Aurora MySQL 2.x version, default version is 2.10.2.
  4. Choose Continue.
  5. On the next page, specify when to perform the upgrade: During the next scheduled maintenance window or Apply Immediately.
  6. Choose Modify cluster.

To perform the upgrade using the AWS CLI, use the following modify-db-cluster command, which performs a major version upgrade to version 2.10.2. Replace cluster-name with the name of your Aurora cluster identifier. This action will cause downtime and the changes will be applied immediately.

aws rds modify-db-cluster \ 
--db-cluster-identifier cluster-name \ 
--engine aurora-mysql \ 
--engine-version 5.7.mysql_aurora.2.10.2 \ 
--allow-major-version-upgrade \ 
--apply-immediately

For the complete steps, refer to How to perform an in-place upgrade. In-place upgrade also works if your clusters are a part of an Aurora global database. At the time of upgrade, make sure to choose the global database cluster instead of one of the clusters it contains. In case of any errors during this upgrade phase, refer to Troubleshooting for Aurora MySQL in-place upgrade guide.

Note: This end of life only impacts clusters running Aurora version 1. After you upgrade your Aurora version 1 cluster to the latest version of Aurora version 2, no additional upgrades or changes are required at this time. However, if you want to upgrade your Aurora version 2 to Aurora version 3, you can review the following additional steps. To learn about changes across the major versions, refer to Comparison of Aurora MySQL version 2 and Aurora MySQL version 3.

Upgrading from Aurora MySQL version 2 to 3

Major version upgrades in Aurora MySQL can go only from one major version to the next immediate major version. For an Aurora MySQL version 1 cluster that you want to upgrade to Aurora MySQL version 3, you can use a two-stage upgrade process. The first stage requires an intermediate upgrade from Aurora MySQL version 1 to Aurora MySQL version 2 using the methods we discussed earlier. The second stage requires another major version upgrade from Aurora MySQL version 2 to Aurora MySQL version 3 using the in-place upgrade or snapshot restore method. The steps are as follows for an in-place upgrade:

  1. On the Amazon RDS console, in the left navigation pane, choose Databases.
  2. On the list, choose the DB cluster that you want to modify and choose Modify.
  3. For Version, choose the latest Aurora MySQL 3 version.
  4. Under Database Options, choose the correct DB cluster parameter group and DB parameter group compatible with the target version.
  5. Choose Continue.
  6. On the next page, specify when to perform the upgrade: During the next scheduled maintenance window or Apply Immediately.
  7. Choose Modify cluster.

If you are choosing to perform the upgrade using the snapshot restore method, the steps are as follows:

  1. On the Amazon RDS console, in the navigation pane, choose Databases.
  2. In the list, choose the writer instance for the Aurora MySQL version 2 DB cluster.
  3. Choose Actions, then choose Take snapshot.
  4. Enter a name for the DB cluster snapshot.
  5. Choose Take Snapshot.
  6. After snapshot is created, choose this DB cluster snapshot.
  7. Choose Actions, then choose Restore snapshot.
  8. For DB cluster identifier, enter a name for your restored DB cluster.
  9. For Version, choose the latest Aurora MySQL 3 version.
  10. Specify other settings and choose Restore DB cluster.

Refer to detailed examples with sample AWS CLI commands in Upgrading to Aurora MySQL version 3. To help you decide the right time and approach to upgrade, refer to Upgrade planning for Aurora MySQL version 3. In case of any errors reported during the upgrade, use the troubleshooting guide to diagnose the cause of the problem.

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 Amazon RDS console prior to starting the upgrade phase.
  • Use the command mysqlcheck --check-upgrade to analyze your existing Aurora MySQL databases and identify potential upgrade issues in advance.
  • Aurora creates a new parameter group for the target upgrade version automatically during a major version upgrade if one isn’t provided. For 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. 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.
  • Check the What’s new page for the target engine version to understand changes in the default parameter settings and review other changes that might be in use with the application.
  • Disable any scheduled database jobs prior to upgrading and make sure you enable them post-upgrade. Additionally, test job behavior in a test or dev environment prior to performing the actual database upgrade process.
  • We also recommend load testing your application with the new major version in a test environment prior to upgrading the production environment. Test important queries and run EXPLAIN on them to account for any query execution plan changes or optimizer enhancements that may be added with the new version, which can alter execution plans for your queries and may thereby impact database performance. Similarly, also check the statistics of the tables are updated.
  • For an Aurora in-place 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, ensure the database doesn’t have long-running transactions or a large number of transactions that would need to be rolled back that can impact the duration of the upgrade. For details, refer to Troubleshooting for Aurora MySQL in-place upgrade.
  • Post-upgrade, we strongly recommend testing your automated backup restore process, disaster recovery, or production to non-production refresh procedures.
  • Upgrading the major version from 1 to 2 changes the engine attribute of the cluster from aurora to aurora-mysql. Make sure to update any AWS CLI or API automation that you use with this cluster to account for the changed engine value.
  • If your Aurora MySQL provisioned cluster is in use with a production application, you can reduce the amount of downtime for your application by using Amazon RDS Blue/Green Deployments.

Conclusion

In this post, we reviewed the Aurora MySQL version 1 (with MySQL 5.6 compatibility) end-of-life support timeline, discussed version upgrade choices and how to pick a target version for the upgrade, covered best practices and explained what to expect during the upgrade process. With Aurora MySQL version 1 reaching end of life on February 28, 2023, we encourage you to upgrade your Aurora MySQL version 1 clusters to Aurora MySQL version 2 or Aurora MySQL version 3 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 Amazon Aurora MySQL DB clusters.


About the Author

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.