Upgrading an RDS for MySQL Instance from 5.7 to 8.0

Scenarios

RDS for MySQL allows you to upgrade the major version of a DB instance in either of the following ways:

Upgrading a major version on the RDS console: For details about version functions, see RDS for MySQL Kernel Version Description.
To use this function, choose Service Tickets > Create Service Ticket in the upper right corner of the management console to apply for required permissions.
Upgrading a major version using DRS: You can migrate instance data from an earlier version to a later version.

Precautions

A pre-check is required for DB instances to be upgraded from MySQL 5.7 to 8.0. Note that:
- The time required for the pre-check depends on how many tables there are in your instance. To prevent service interruptions, perform the upgrade during off-peak hours.
- The check report is retained for 24 hours. Download it before it is deleted.
- If the Parameters item fails the check, rectify the fault based on the check items in the check details by referring to Table 1. Check items whose severity is Error must be rectified before the upgrade can be performed. Check items whose severity is Warning only requires you to learn about the kernel feature changes after the upgrade.
- After the fault is rectified, you need to click Retry to obtain the new check results.
Only the latest minor version of MySQL 5.7 can be upgraded to that of MySQL 8.0. Ensure that your instance uses the latest minor version of MySQL 5.7.
You are advised not to perform a major version upgrade during the backup time window.
You are advised to perform a full backup before upgrading a major version.
Upgrading a major version will cause a connection interruption for 10 to 120 seconds. Ensure that your applications support automatic reconnection. Perform this operation during off-peak hours because upgrading a major version during peak hours takes much more time.
When you upgrade a major version of a primary DB instance, major versions of its read replicas (if any) will also be upgraded. Major versions of read replicas cannot be upgraded separately.
A major version upgrade cannot be rolled back after the upgrade is complete.
Before the upgrade, compare the old and new versions carefully. To ensure that the syntax and features of the old version used by your applications are compatible with the new version, create a new RDS for MySQL 5.7 or 8.0 instance to test the syntax before the upgrade.
You are advised to restore data to a new instance and perform a test first. After confirming that all functions are normal, upgrade the original instance.
When your instance is being upgraded, storage autoscaling does not take effect, so sufficient storage must be reserved to ensure that data writes can continue during the upgrade.
Scheduled major version upgrades need to be prepared in advance and cannot be canceled.
After a major version upgrade is complete, the backups before the upgrade cannot be used for the instance of the new version, and the time points before the upgrade cannot be selected for point-in-time recovery (PITR).
DDL operations on events, such as CREATE EVENT, DROP EVENT, and ALTER EVENT, are not allowed during a major version upgrade.
After a major version upgrade, specification parameters are reset to the default values of the new version, including threadpool_size, innodb_buffer_pool_size, innodb_io_capacity, innodb_io_capacity_max, innodb_buffer_pool_instances, back_log, and max_connections.

Constraints

For details about kernel versions, see Kernel Version Description.
If the replication delay between primary and standby instances is longer than 300 seconds, the major version cannot be upgraded.
The major version cannot be upgraded for DB instances with abnormal nodes.
RDS for MySQL 5.7 and later versions no longer support Sequence Engine.
RDS for MySQL DB instances support a maximum of 500,000 tables (including system tables and data tables). If the number of tables is greater than 500,000, the major version upgrade may fail.
RDS for MySQL DB instances with event scheduler enabled do not support major version upgrades. If you want to perform a major version upgrade, disable event scheduler first. For details, see Enabling or Disabling Event Scheduler.
After your instance is upgraded to MySQL 8.0, read replicas (if any) share the SQL statement concurrency control rules of the primary instance. To prevent those rules of the primary instance from affecting workloads on the read replicas, review and adjust the rules of the primary instance before the upgrade.
If an instance meets one of the following conditions, it cannot be upgraded from MySQL 5.7 to 8.0:
- It has been associated with a DDM instance or database proxy has been enabled for it.
- It is a single-node instance.
- Its read replicas have SQL statement concurrency control rules. To upgrade the instance, delete such rules from the read replicas.

Upgrade Check Items and Handling Suggestions for Upgrade Failures

**Table 1** Check items and rectification
Check Item	Description	Rectification
utf8mb3Check	Check the character set utf8mb3.	In MySQL 5.7, the character set utf8 is equivalent to utf8mb3. In MySQL 8.0, the character set utf8 is equivalent to utf8mb4. After the upgrade, if you use utf8 to create tables, utf8mb4 is actually used. No action is required.
removedSysVars	Check the removed system variables.	Some system variables have been deleted from MySQL 8.0. This check item does not affect the upgrade. No action is required.
sysVarsNewDefaults	Check the default values of system variables.	Some system variables have new default values in MySQL 8.0. If you have changed the values of such variables before the upgrade, the new values are retained after the upgrade. If you have not changed the values, the new default values in MySQL 8.0 are used. This check item does not affect the upgrade. No action is required.
zeroDatesCheck	Check for zero dates, DATETIME, and TIMESTAMP values.	In MySQL 8.0.16 or later versions, when a zero-value date (for example, 2024-00-00) is used as a query condition, MySQL converts the character string to DATE. If the conversion fails, an error is reported. This check item does not affect the upgrade but affects the query logic after the upgrade. You do not need to handle the error reported for the global.sql_mode setting. You are advised to check for such values. If there is any, change it to a non-zero value.
enumSetElementLenghtCheck	Check the length of elements in ENUM and SET columns.	If any column contains more than 255 characters, modify it and ensure that its length does not exceed this limit.
reservedKeywordsCheck	Check reserved words.	If there are any objects with names conflicting with the reserved words of MySQL 8.0, change the names.
mysqlDollarSignNameCheck	Check the dollar sign ($).	If any database object name contains $, delete it from the name.
mysqlInvalid57NamesCheck	Check for invalid database names, table names, and column names.	Modify invalid names.
groupByAscCheck	Check the GROUP BY ASC and DESC syntax.	The GROUP BY ASC and DESC syntax has been removed from MySQL 8.0. Modify the database objects that contain the GROUP BY ASC or DESC syntax. You can remove the ASC and DESC keywords from the GROUP BY clause and place them in the ORDER BY clause.
checkTableOutput	Check tables using the check table x for upgrade command.	Rectify the fault based on the description in the check results.
engineMixupCheck	Check whether InnoDB recognizes tables that belong to other engines.	If there are tables recognized by the InnoDB engine, but the SQL layer considers that they belong to other engines, contact customer service.
foreignKeyLengthCheck	Check the length of the foreign key constraint name.	If the foreign key constraint name contains more than 64 characters, modify it.
nonNativePartitioningCheck	Check for partitioned tables of non-native partitioning engines.	MySQL 8.0 supports only InnoDB and NDB as partitioning engines. Convert the engine to InnoDB or delete the partitions.
routinesSyntaxCheck	Check for syntax incompatibility.	If the definitions of database objects such as stored procedures and functions contain incompatible syntax, for example, the syntax conflicts with the reserved words in MySQL 8.0, modify the syntax based on the description in the check results.
maxdbFlagCheck	Check for obsolete MAXDB sql_mode flags.	In MySQL 8.0, the MAXDB option has been deleted from sql_mode. Modify the sql_mode parameter to exclude MAXDB.
sqlModeFlagCheck	Check for obsolete sql_mode flags.	Some sql_mode flags have been deleted from MySQL 8.0. Modify the sql_mode parameter based on the description in the check results.
removedSysLogVars	Check the removed system variables for system logs.	Some system variables for system logs have been removed from MySQL 8.0. No action is required.
mysqlIndexTooLargeCheck	Check the index length.	Both MySQL 5.7 and MySQL 8.0 allow the maximum index length of 767 bytes. In MySQL 8.0, if utf8mb4 is used, an index cannot be longer than 191 characters. Change the index length to no more than 191 characters.
circularDirectoryCheck	Check whether any tablespace data file path uses a circular directory.	The target version does not allow tablespace data file paths to contain circular directory references (for example, /../). Contact customer service.
columnsWhichCannotHaveDefaultsCheck	Check default values of columns.	Default values are not allowed for columns of the BLOB, TEXT, GEOMETRY, or JSON type. Run the ALTER TABLE statement to delete the default values.
removedFunctionsCheck	Check the removed functions.	Some functions have been deleted from the target version. Modify the corresponding database objects based on the description in the check results.
mysqlOrphanedRoutinesCheck	Check for orphaned stored procedures or functions.	Orphaned stored procedures or functions cannot run because the database objects referenced by them do not exist. Delete such stored procedures or functions.
mysqlEmptyDotTableSyntaxCheck	Check for obsolete identifiers.	Change such identifiers in database objects based on the description in the check results.
mysqlSchemaCheck	Check for table name conflicts.	There are some tables added to MySQL 8.0. Run the RENAME TABLE statement to change the conflicting table names.
mysqlInvalidEngineForeignKeyCheck	Check for foreign keys pointing to tables from other engines.	Run the ALTER TABLE statement to change the table's engine or delete the foreign key reference.
lowerCaseNameCheck	Check whether any table name contains uppercase letters when lower_case_table_names is set to 1.	If any table name contains uppercase letters when lower_case_table_names is set to 1, the upgrade will fail. Change the value of lower_case_table_names to 0 first, run the RENAME TABLE statement to change the table name to lowercase, and then change the lower_case_table_names value back to 1.
specVarInConfigFileCheck	Check whether the values of sql_mode and loose_tls_version in the configuration file are outdated.	Some sql_mode flags have been removed from the target version. In MySQL 8.0.28 and later versions, the loose_tls_version parameter does not support TLSv1 or TLSv1.1. Change the values of sql_mode and loose_tls_version based on the check results.
reversedUserCheck	Check whether the mysql.infoschema@localhost account has been created.	MySQL 8.0 has a built-in mysql.infoschema@localhost account. If this account exists in MySQL 5.7, the upgrade will fail. Delete this account before the upgrade.
schemaInconsistencyCheck	Check for database structure inconsistency due to table file removal or corruption.	If the .frm table file is missing, contact customer service.
geometryIndexCheck	Check whether any spatial coordinate is a spatial index when it is used as an index.	In the target version, the spatial coordinate index must be a spatial index. Recreate or delete the index.
danglingIndexCheck	Check for dangling indexes.	If any table has dangling FTS_DOC_ID due to deletion of the full-text index column, run the OPTIMIZE TABLE statement.
viewColumnCheck	Check the length of the view column name.	In the target version, a view column name can contain a maximum of 64 characters. If this limit is exceeded, run the ALTER VIEW statement to modify the column name.
partitionedTablesInSharedTablespaceCheck	Check whether there are any partitioned tables in shared tablespaces.	The target version does not support shared tablespaces. Run the ALTER TABLE statement to move partitioned tables to independent tablespaces.
partitionsReferencedCheck	Check whether any partitioned table is referenced by an ordinary table using a foreign key.	Partitioned tables cannot be referenced by ordinary tables. Delete the foreign key reference.
partitionsRangeDateCheck	Check for tables partitioned by time.	In MySQL 8.0, if tables are partitioned by time, the content in the time column must be in the standard format (for example, YYYY-MM-DD hh:mm:ss or YY-MM-DD hh:mm:ss). Change the time values in the table to the standard format.

**Table 2** Handling Suggestions for Upgrade Failures
Symptom	Impact	Handling Suggestion
After RDS for MySQL 5.7 is upgraded to 8.0, indexes become invalid when some SQL statements are executed because the default character set is changed. By default, MySQL 5.7 uses utf8mb3, while MySQL 8.0 uses utf8mb4.	If you create a table using the default character set in MySQL 5.7, the upgrade will not change the character set to that of MySQL 8.0. After the upgrade, if you create another table and join the two tables with different character sets by running JOIN, the SQL statement execution will be prolonged due to index selection.	Modify the character sets of databases, tables, and fields and the default character set in MySQL 8.0 to be the same as those in MySQL 5.7.
The full-text index is deleted.	The upgrade may fail.	Run the OPTIMIZE TABLE statement to recreate the table and check for dangling FTS_DOC_ID. For details, see Table 1.
There are foreign key constraints (specified by the foreign_key_check parameter) on partitioned tables.	The upgrade fails.	Remove the foreign key constraint.
RDS for MySQL 5.7 contains the mysql.events table whose DEFINER column is blank or null.	The upgrade fails.	Set the DEFINER column to a non-null value.
The case formats of column names at the server and InnoDB layers do not match.	The upgrade fails.	Run the OPTIMIZE TABLE statement to recreate the table.
The case formats of the fields contained in the indexes at the server and InnoDB layers do not match.	The upgrade fails.	Run the OPTIMIZE TABLE statement to recreate the table.
The BTREE SPATIAL INDEX index, which is not supported by RDS for MySQL 8.0, is used.	The upgrade fails.	Delete the BTREE SPATIAL INDEX index.

Upgrading a Major Version on the RDS Console

Log in to the management console.
Click in the upper left corner and select a region and a project.
Click in the upper left corner of the page and choose Databases > Relational Database Service.
On the Instances page, click the instance name to go to the Overview page.
Under DB Engine Version, click Upgrade Engine Version.

Figure 1 Upgrading a major version
In the displayed dialog box, confirm instance details and click Next.

Figure 2 Confirming instance details
- If the instance is to be upgraded from MySQL 5.7 to 8.0, go to 7.
- In other scenarios, go to 8.
Perform a pre-check.

Figure 3 Performing a pre-check

After the pre-check is complete, rectify the fault (if any) based on the pre-check results by referring to Table 1.

After the fault is rectified, click Retry to perform a check again until the values of both Instance Statuses and Parameters are Check completed. Then, click Next.

Figure 4 Check completed
Select a scheduled time and click OK.
- Upon submission: The system upgrades your instance to the latest version of 5.7 or 8.0 immediately after you submit the upgrade request.
- In maintenance window: The system will upgrade your instance to the latest version of 5.7 or 8.0 during the maintenance window you specified. For details about how to configure a maintenance window, see Changing the Maintenance Window.
Figure 5 Selecting a scheduled time

If the upgrade fails, rectify the fault by referring to Changing the Maintenance Window.

Upgrading a Major Version Using DRS

You can migrate data from an RDS for MySQL 5.6 instance to an RDS for MySQL 5.7 instance using Data Replication Service (DRS). Before the migration, create a DB instance of the target version.

On the Instances page, click the instance you want to migrate. On the displayed Overview page, click Migrate Database in the upper right corner.

For more information, see Creating a Migration Task in the Data Replication Service User Guide.