I want to upgrade my Amazon Relational Database Service (Amazon RDS) for MySQL version. How do I perform the version upgrade? Also, how long does the upgrade take or why is the upgrade taking longer than expected?
Performing an Amazon RDS for MySQL version upgrade
To manually upgrade the engine version of a DB instance, use the AWS Management Console, AWS Command Line Interface (AWS CLI), or Amazon RDS API.
Note: If you receive errors when running an AWS CLI command, make sure that you’re using the most recent version of the AWS CLI.
Amazon RDS MySQL upgrade time
The duration of the upgrade process depends on the upgrade type, data, and resources available. It's a best practice to first test the upgrades on separate test instances (similar to your production environment) to understand the approximate upgrade duration. You can also use a snapshot restore or read replica to launch test instances for upgrades, either with major or minor version upgrades. For more information about major and minor version upgrades, see Upgrading the MySQL DB engine.
Factors that can impact upgrade time
For all version upgrades of Amazon RDS for MySQL, the following is true:
- 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. The size of your changes determines how long it takes for the upgrade to complete. However, your instance can still continue accepting traffic while the snapshot is taken.
- After a backup completes, the instance is shut down. MySQL then runs the new engine version on a disabled network, preventing any remote connections.
Note: If you're performing a major version upgrade, then the shutdown can take longer.
For major version upgrades of Amazon RDS for MySQL, the following is true:
- Amazon RDS employs a slow shutdown by setting the innodb_fast_shutdown parameter value to "0". This mode performs additional flushing, such as merging the change buffer and performing a full purge of deleted rows. The slow shutdown can take minutes or hours, depending on the amount of data being recovered. Slow shutdown of a database with a large change buffer or long history list length can take longer. For more information about the bottleneck caused by a slow shutdown, see Change buffer on the MySQL website. For more information about innodb_fast_shutdown mode, see innodb_fast_shutdown on the MySQL website.
- The mysql_upgrade program runs on the MySQL database to convert system and user tables to a new version for major upgrade versions. Depending on the state of your table, your table might need to be recreated to conform to a new version. In most cases, no changes are required and mysql_upgrade updates the metadata in the table to indicate that the database was upgraded. If you have a larger number of tables, Amazon RDS 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 version upgrades to version 5.7, Amazon RDS for MySQL rebuilds some of your tables. All date and time column types are converted to the new format, which can take several hours to days. The duration depends on the amount of data in your tables. For more information, see Upgrades to MySQL version 5.7 might be slow.
Upgrading a DB instance engine version
Best practices for upgrading Amazon RDS for MySQL and Amazon RDS for MariaDB