How do I perform an Amazon RDS for MySQL version upgrade with minimal downtime?

Resolution

Note: If you receive errors when you run AWS Command Line Interface (AWS CLI) commands, then see Troubleshoot AWS CLI errors. Also, make sure that you're using the most recent AWS CLI version.

Manually upgrade an Amazon RDS for MySQL version

To manually upgrade the engine version of an Amazon RDS DB instance, use the AWS Management Console, AWS CLI, or Amazon RDS API. For more information, see Upgrading the MySQL DB engine.

Amazon RDS for MySQL upgrade time

The upgrade time depends on the upgrade type, data, and available resources. To estimate your upgrade time, test the upgrades on separate test instances. To launch test instances for the major or minor upgrade, use a snapshot restore or read replica. For more information, see Major version upgrades for MySQL and Automatic minor version upgrades for MySQL. To minimize the upgrade downtime, use blue/green deployment for database updates.

When you turn on backups, Amazon RDS takes a snapshot of the instance's previous version for all version upgrades. If Amazon RDS doesn't find any recent backups, then Amazon RDS takes a full snapshot during the upgrade process. The size of your changes affects your upgrade time. Your instance can continue to accept traffic when Amazon RDS takes the snapshot. After a backup completes, the instance shuts down. Then, MySQL runs the new engine version on a deactivated network to prevent any remote connections.

To employ a slow shutdown, Amazon RDS sets the innodb_fast_shutdown parameter value to 0. In a slow shutdown, Amazon RDS merges the change buffer and performs a full purge of deleted rows. A slow shutdown might take a few minutes to multiple hours based on the amount of data that's recovered. A slow shutdown of a database with a large change buffer or long history list might take a longer time period. For more information, see slow shutdown, innodb_fast_shutdown, change buffer, and purge on the MySQL website.

To convert system and user tables to a new version, the mysql_upgrade program runs on the MySQL database. Based on the state of your table, you might need to create your table to conform to a new version. To show that the database is upgraded, the mysql_upgrade program updates the metadata in the table. If you have a larger number of tables, then Amazon RDS for MySQL might take longer to complete the major version upgrade. For more information, see mysql_upgrade: Check and upgrade MySQL tables on the MySQL website.

During major upgrades to version 5.7, Amazon RDS for MySQL rebuilds some of your tables. All date and time column types convert to the new format. Based on the amount of data in your tables, the conversion can take several hours to days.

Incompatibilities between MySQL 5.7 to MySQL 8.0 might cause issues when you upgrade. For a successful upgrade, it's a best practice to prepare your database. When you start an upgrade from MySQL 5.7 to 8.0, Amazon RDS automatically runs prechecks to detect incompatibilities. For information, see Chapter 3 Upgrading MySQL on the MySQL website.

For an upgrade from MySQL 5.6 to MySQL 5.7, Amazon RDS runs prechecks to confirm that there's enough storage space to rebuild tables. Amazon RDS also confirms that there are no orphan tables. Amazon RDS doesn't run prechecks for upgrades to releases earlier than MySQL 5.7. It's a best practice to have a rollback plan for potential major version upgrade failures.

To check for issues in your tables, it's a best practice to perform a mysqlcheck. However, table maintenance operations might use significant time and resources. For more information, see mysqlcheck: A table maintenance program on the MySQL website.

Related information

Upgrading a DB instance engine version

Best practices for upgrading Amazon RDS for MySQL and Amazon RDS for MariaDB

MySQL on Amazon RDS versions