I. Overview

Version & Tools

A version has format MAJOR.MINOR.PATCH, cause we upgrade from MySQL 5.7 to 8.0, so it’s a MAJOR upgrade.

Major version upgrades can contain database changes that are not backward-compatible with existing applications. As a result, you must manually perform major version upgrades of your DB instances. [1]

=> We must upgrade manually.

For major version upgrades, you must manually modify the DB engine version through the AWS Management Console, AWS CLI, or RDS API. [2]

=> We can use AWS Console, AWS CLI or RDS API. When I try to upgrade MySQL engine in AWS RDS, I use AWS CLI for reuseable and reduce human errors.

Approach

There are 2 ways to upgrade MySQL engine version in AWS RDS:

1. Upgrade Directly: Upgrade current DB instance from MySQL v5.7 to v8.0
2. Use a Read Replica to reduce downtime when upgrading a MySQL DB:
   • Create a read replica from master DB instance.
   • Upgrade MySQL engine version in this read replica instance.
   • Promote this instance to a standalone DB instance => new master DB instance.
   • Change DNS endpoint, point RDS endpoint to new master DB instance’s endpoint.

No matter which way we choose, we need to prepare carefully before upgrading.

II. Before Upgrading

Prechecks for upgrades from MySQL 5.7 to 8.0

MySQL 8.0 includes a number of incompatibilities with MySQL 5.7. These incompatibilities can cause problems during an upgrade from MySQL 5.7 to MySQL 8.0. So, some preparation might be required on your database for the upgrade to be successful. [1]

We need to ensure that there must be no tables that use obsolete data types, no orphan *.frm files, no keyword violations, … (detail here).

When we start an upgrade, AWS RDS run prechecks automatically to detect these incompatibilities. If there are incompatibilities, RDS prevents the upgrade and provides a log for us to learn about them.

I think it’s better if we create a test instance from staging db snapshot, try to upgrade, get log file which show all incompatiblities and fix them locally.

MySQL provides nice guide to help us:

• Upgrading MySQL
• Upgrading to MySQL 8.0? Here is what you need to know

Backup data

Please ensure that Auto backup feature is enabled (or backup retention period value is greater than 0).

Amazon RDS takes two DB snapshots during the upgrade process. The first DB snapshot is of the DB instance before any upgrade changes have been made. If the upgrade doesn’t work for your databases, you can restore this snapshot to create a DB instance running the old version. The second DB snapshot is taken when the upgrade completes.

Amazon RDS only takes DB snapshots if you have set the backup retention period for your DB instance to a number greater than 0. To change your backup retention period, see Modifying an Amazon RDS DB instance. [1]

If this option is not enabled, we need to create snapshot manually.

A snapshot is taken (if backups are enabled) while the instance is still running on the previous version. If Amazon RDS doesn’t find any recent backups, a full snapshot is taken during the upgrade process, which can impact the overall upgrade time. [3]

=> Create snapshot before we upgrade. It will reduce upgrade time.

aws rds create-db-snapshot \
    --db-instance-identifier $db_instance_name \
    --db-snapshot-identifier $db_snapshot_name

Others

1. Upgrade DB instance OS (Optional)

   To perform a major version upgrade for a MySQL version 5.6 DB instance on Amazon RDS to MySQL version 5.7 or later, first perform any available OS updates. After OS updates are complete, upgrade to each major version: 5.6 to 5.7 and then 5.7 to 8.0. MySQL DB instances created before April 24, 2014, show an available OS update until the update has been applied. For more information on OS updates, see Applying updates for a DB instance. [1]

   => It’s better if DB instance OS is up to date before we upgrade.

2. Recheck targets which are needed to be upgraded > If your MySQL DB instance is using read replicas, you must upgrade all of the read replicas before upgrading the source instance. > > If your DB instance is in a Multi-AZ deployment, both the primary and standby replicas are upgraded. Your DB instance will not be available until the upgrade is complete.

   => We need to upgrade Read Replicas instances, and DB instances in other AZ.

III. Upgrading Approach

Highly Recommended: testing an upgrade before make it real

1. Upgrade Directly

Upgrade

Tested with this command aws-rds-upgrade#upgrade-directly.

aws rds modify-db-instance \
    --db-instance-identifier $db_instance_name \
    --engine-version $new_mysql_version \
    --allow-major-version-upgrade \
    --apply-immediately

Downtime Duration

Database engine upgrades require downtime. The duration of the downtime varies based on the size of your DB instance.

MySQL major version upgrades typically complete in about 10 minutes. Some upgrades might take longer because of the DB instance class size or because the instance doesn’t follow certain operational guidelines in Best practices for Amazon RDS. [1]

Tested with free-tier db instance db.t3.micro, it takes ~12 minutes to fully upgrade.

However, in upgrading process, the instance shuts down in a special mode called a slow shutdown to ensure data consistency, so first 5 minutes of upgrading process, our Rails app can still connect to DB.

2. Using a read replica to reduce downtime

Upgrade

This approach requires DB instance is enable Auto Backup.

Tested commands: aws-rds-upgrade#use-read-replica.

Steps to upgrade (detail):

• Create a read replica of your MySQL 5.7 instance.
• (Optional) When the read replica has been created and Status shows Available, convert the read replica into a Multi-AZ deployment and enable backups.
• When the read replica Status shows Available, upgrade the read replica to MySQL 8.0. (Use AWS Console or AWS CLI).
• When the upgrade is complete and Status shows Available, verify that the upgraded read replica is up-to-date with the source MySQL 5.7 DB instance.
• (Optional) Create a read replica of your read replica.
• (Optional) Configure a custom DB parameter group for the read replica.

• Make your MySQL 8.0 read replica a standalone DB instance.

  When you promote your MySQL 8.0 read replica to a standalone DB instance, it no longer is a replica of your MySQL 5.7 DB instance. We recommend that you promote your MySQL 8.0 read replica during a maintenance window when your source MySQL 5.7 DB instance is in read-only mode and all write operations are suspended. When the promotion is completed, you can direct your write operations to the upgraded MySQL 8.0 DB instance to ensure that no write operations are lost.

  In addition, we recommend that, before promoting your MySQL 8.0 read replica, you perform all necessary data definition language (DDL) operations on your MySQL 8.0 read replica. An example is creating indexes. This approach avoids negative effects on the performance of the MySQL 8.0 read replica after it has been promoted. [1]

• Direct your applications to the new MySQL 8.0 DB instance. (Update DNS endpoint or DB setting in app)

Downtime Duration

The advantages of this method:

• Short downtime: When read replica instance is upgraded, our app can still access to primary db instance. After upgrading, all new data will be synchorized from primary to read replica instance.

  => Downtime = Time to promote read replica to standalone instance + Time to change DNS endpoint/DB setting app.

  I tested with free-tier db instance, it takes ~5 minutes to promote read replica instance.

• Easy to rollback: If there are any problems with upgrading process, we can easy rollback by changing DNS endpoint/DB endpoint to v5.7 DB instance.

More articles about this approach

• Upgrading to RDS MySQL 8.0 with minimum downtime
• Upgrading a MySQL DB on AWS RDS
• Our MySQL RDS Upgrade Journey: Cutting Down Downtime by 11,200% and Lessons Learned
• Zero Downtime Maintenances on MySQL RDS

IV. After upgrading

Recheck if upgrading process is done

We can use this command to check if status of upgraded DB instance is Available

aws rds wait db-instance-available --db-instance-identifier $db_instance_name

Retest the system

Connect Rails app with new DB instance, and retest behaviors.

Rollback incase upgrade failed

1. In case we upgrade DB instance directly

After the 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 the first DB snapshot taken to create a new DB instance. [1]

• We can not rollback current engine (v8.0) to old engine (v5.7). We must restore a latest snapshot which is created before upgrade into a new db instance:

  new_db_instance_name="new-rds-upgrade-testing"
  
  aws rds restore-db-instance-from-db-snapshot \
  --db-instance-identifier $new_db_instance_name \
  --db-snapshot-identifier $db_snapshot_name

• Update the end points in Route 53 to point to the new RDS. If there is no DNS endpoint, update the new RDS endpoints in your application configuration so that it starts fetching data from new MySQL RDS instance.

2. In case we upgrade by using external replication

• We just need to upgrade DNS endpoint to old DB endpoint (v5.7) cause this DB instance is dependent with new RDS master instance (v8.0).

Conclusion

• We recommend use second approach: “Upgrade using a Read Replica” cause this approach has short downtime duration and easy to rollback.
• We should prepare carefully before executing upgrade:
  • Prechecks for upgrades.
  • Backup data to snapshot.
  • Testing an upgrade with test DB instance.
• We should use script to: backup, upgrade and rollback. So that, we can reduce risks.

References

• [1] AWS Official User Guide - Upgrading the MySQL DB engine
• [2] AWS Official User Guide - Upgrading a DB instance engine version
• [3] AWS Premium Support - I want to upgrade my Amazon RDS MySQL version
• [4] Best Practices for Upgrading Amazon RDS for MySQL and Amazon RDS for MariaDB