AWS RDS Upgrade

Upgrading the MySQL DB Engine

When Amazon Relational Database Service (Amazon RDS) supports a new version of a database engine, you can upgrade your DB instances to the new version. There are two kinds of upgrades: major version upgrades and minor version upgrades.

Major Version Upgrades for MySQL

Amazon RDS supports the following in-place upgrades for major versions of the MySQL database engine:

• MySQL 5.5 to MySQL 5.6
• MySQL 5.6 to MySQL 5.7

Note

You can only create MySQL version 5.7 DB instances with current generation DB instance classes and the M3 previous generation DB instance class. If you want to upgrade a MySQL version 5.6 DB instance running on a previous generation DB instance class (other than M3) to a MySQL version 5.7 DB instance, you must first modify the DB instance to use a current generation DB instance class. After the DB instance has been modified to use a current generation DB instance class, you can then modify the DB instance to use the MySQL version 5.7 database engine. For information on Amazon RDS DB instance classes, see DB Instance Class.

Major version upgrades can contain database changes that are not backward-compatible with existing applications. As a result, Amazon Relational Database Service (Amazon RDS) doesn't apply major version upgrades automatically; you must manually modify your DB instance. You should thoroughly test any upgrade before applying it to your production instances.

To perform a major version upgrade for a MySQL version 5.5 DB instance on Amazon RDS to MySQL version 5.6 or later, you should first perform any available OS updates. After OS updates are complete, you must upgrade to each major version: 5.5 to 5.6, and then 5.6 to 5.7. 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 Updating the Operating System for a DB Instance or DB Cluster.

During a major version upgrade of MySQL, Amazon RDS runs the MySQL binary mysql_upgrade to upgrade tables, if required. Also, Amazon RDS empties the slow_log and general_log tables during a major version upgrade. To preserve log information, save the log contents before the major version upgrade.

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. If you upgrade a DB instance from the Amazon RDS console, the status of the DB instance indicates when the upgrade is complete. If you upgrade using the AWS Command Line Interface (AWS CLI), use the describe-db-instances command and check the Status value.

Upgrades to MySQL Version 5.7 Might Be Slow

MySQL version 5.6.4 introduced a new date and time format for the datetime, time, and timestamp columns that allows fractional components in date and time values. When upgrading a DB instance to MySQL version 5.7, MySQL will force the conversion of all date and time column types to the new format. Because this conversion rebuilds your tables, it might take a considerable amount of time to complete the DB instance upgrade. The forced conversion will occur for any DB instances that are running a version prior to MySQL version 5.6.4, and also any DB instances that were upgraded from a version prior to MySQL version 5.6.4 to a version other than 5.7.

If your DB instance is running a version prior to MySQL version 5.6.4, or was upgraded from a version prior to MySQL version 5.6.4, then we recommend that you convert the datetime, time, and timestamp columns in your database before upgrading your DB instance to MySQL version 5.7. This conversion can significantly reduce the amount of time required to upgrade the DB instance to MySQL version 5.7. To upgrade your date and time columns to the new format, issue the ALTER TABLE <table_name> FORCE; command for each table that contains date or time columns. Because altering a table locks the table as read-only, we recommend that you perform this update during a maintenance window.

Upgrading a MySQL Database with Reduced Downtime

If your MySQL DB instance is currently in use with a production application, you can use the following procedure to upgrade the database version for your DB instance and reduce the amount of downtime for your application. This procedure shows an example of upgrading from MySQL version 5.5 to MySQL version 5.6.

To upgrade an MySQL database while a DB instance is in use

1. Sign in to the AWS Management Console and open the Amazon RDS console athttps://console.aws.amazon.com/rds/.
2. Create a Read Replica of your MySQL 5.5 DB instance. This process creates an upgradable copy of your database.
   1. On the console, choose Instances, and then choose the DB instance that you want to upgrade.
   2. Choose Instance Actions, and then choose Create Read Replica.
   3. Provide a value for DB Instance Identifier for your Read Replica and ensure that the DB instance Class and other settings match your MySQL 5.5 DB instance.
   4. Choose Yes, Create Read Replica.
3. When the Read Replica has been created and Status shows available, upgrade the Read Replica to MySQL 5.6.
   1. On the console, choose Instances, and then choose the Read Replica that you just created.
   2. Choose Instance Actions, and then choose Modify.
   3. For DB Engine Version, choose the MySQL 5.6 version to upgrade to, and then choose Apply Immediately. Choose Continue.
   4. Choose Modify DB Instance to start the upgrade.
4. When the upgrade is complete and Status shows available, verify that the upgraded Read Replica is up to date with the master MySQL 5.5 DB instance. You can do this by connecting to the Read Replica and issuing the SHOW SLAVE STATUS command. If the Seconds_Behind_Master field is 0, then replication is up to date.
5. Make your MySQL 5.6 Read Replica a master DB instance.
   Important

   When you promote your MySQL 5.6 Read Replica to a standalone, single-AZ DB instance, it will no longer be a replication slave to your MySQL 5.5 DB instance. We recommend that you promote your MySQL 5.6 Read Replica during a maintenance window when your source MySQL 5.5 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 5.6 DB instance to ensure that no write operations are lost.

   In addition, we recommend that before promoting your MySQL 5.6 Read Replica you perform all necessary data definition language (DDL) operations, such as creating indexes, on the MySQL 5.6 Read Replica. This approach avoids negative effects on the performance of the MySQL 5.6 Read Replica after it has been promoted. To promote a Read Replica, use this procedure:
   1. On the console, choose Instances, and then choose the Read Replica that you just upgraded.
   2. Choose Instance Actions, and then choose Promote Read Replica.
   3. Enable automated backups for the Read Replica instance. For more information, see Working With Backups.
      Choose Continue.
   4. Choose Yes, Promote Read Replica.
6. You now have an upgraded version of your MySQL database. At this point, you can direct your applications to the new MySQL 5.6 DB instance, add Read Replicas, set up Multi-AZ support, and so on.

AWS Management Console

To upgrade the engine version of a DB instance by using the AWS Management Console

1. Sign in to the AWS Management Console and open the Amazon RDS console athttps://console.aws.amazon.com/rds/.
2. In the navigation pane, choose Instances.
3. Choose the check box for the DB instance that you want to upgrade.
4. Choose Instance Actions, and then choose Modify.
5. For DB Engine Version, choose the new version.
6. To upgrade immediately, select Apply Immediately. To delay the upgrade to the next maintenance window, clear Apply Immediately.
7. Choose Continue.
8. Review the modification summary information. To proceed with the upgrade, choose Modify DB Instance. To cancel the upgrade, choose Cancel or Back.

CLI

To upgrade the engine version of a DB instance, use the AWS CLI modify-db-instance command. Specify the following parameters:

• --db-instance-identifier – the name of the db instance.
• --engine-version – the version number of the database engine to upgrade to.
• --allow-major-version-upgrade – to to upgrade major version.
• --no-apply-immediately – apply changes during the next maintenance window. To apply changes immediately, use --apply-immediately.

Example

For Linux, OS X, or Unix:

Copy

aws rds modify-db-instance \
    --db-instance-identifier <mydbinstance> \
    --engine-version <new_version> \
    --allow-major-version-upgrade \
    --apply-immediately