How do I upgrade an Amazon RDS for MySQL database engine version and minimize 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.

Upgrade an Amazon RDS for MySQL database engine version

To upgrade an Amazon RDS database engine to a major version, you must manually use the AWS Management Console, AWS CLI, or RDS API. For more information, see Manually upgrading the engine version.

To automatically upgrade an Amazon RDS database engine to a minor version, see Automatically upgrading the minor engine version.

Best practices to minimize downtown for Amazon RDS for MySQL database engine upgrades

Estimate 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 database instances. To launch test database instances for the major or minor upgrade, use a snapshot restore or read replica. For more information, see Upgrades of the RDS for MySQL DB engine.

To minimize the upgrade downtime, use blue/green deployment for database updates. A blue/green deployment creates a staging environment that copies the production environment. You can upgrade your Amazon RDS database instances in the green environment without it affecting production workloads. Then, switch over the environments after you test them.

Database backup impact

When you have backups turned on, Amazon RDS takes a snapshot of the database 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 database instance can continue to accept traffic when Amazon RDS takes the snapshot. After a backup completes, the database instance shuts down. Then, MySQL runs the new engine version on a deactivated network to prevent any remote connections.

Impact of slow shutdown

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 longer time. For more information, see slow shutdown, innodb_fast_shutdown, change buffer, and purge on the MySQL website.

System and user table conversion to a new version

To convert system and user tables to a new version, the MySQL binary mysql_upgrade 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. The mysql_upgrade binary updates the metadata in the table and shows that the database upgraded. 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.

Considerations for MySQL upgrades

During major upgrades from version 8.0 to 8.4, Amazon RDS for MySQL rebuilds some of your tables. Based on the amount of data in your tables, the rebuild might take several hours or several days to complete.

Incompatibilities between MySQL major versions might cause issues when you upgrade. For a successful upgrade, it's a best practice to review the considerations for MySQL upgrades. When you start an upgrade from MySQL 5.7 to 8.0, Amazon RDS automatically runs pre-checks to detect incompatibilities. For more information, see Upgrading MySQL on the MySQL website.

Have a rollback plan

For an upgrade from MySQL 8.0, Amazon RDS runs pre-checks 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 pre-checks for upgrades to releases earlier than MySQL 5.7. It's a best practice to have a plan in case the upgrade fails and is rolled back for potential major version upgrade failures.

Check for tables issues

To check for issues in your tables, it's a best practice to perform a mysqlcheck. It's best practice to test your table maintenance operations on a restored snapshot. 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