Upgrading an RDS for MySQL Instance from 5.7 to 8.0
Scenarios
You can upgrade your DB instance from 5.7 to 8.0 for more functions and better experience.
Upgrade Methods
During a major upgrade, the system first creates a target instance of the new version, synchronizes data from the source instance to the target instance, and then switches the virtual IP address of the source instance to the target instance.
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, submit a service ticket to request required permissions.
- Upgrading a major version using DRS: You can migrate instance data from an earlier version to a later version.
Upgrade Notes
Phase |
Notes |
Constraints |
---|---|---|
Before an upgrade |
|
|
During an upgrade |
|
|
After an upgrade |
|
|
Procedure
- Click
in the upper left corner and select a region.
- 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
- 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 3.
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 timeIf the upgrade fails, rectify the fault by referring to Table 4.
You can migrate data from an RDS for MySQL 5.7 instance to an RDS for MySQL 8.0 instance using Data Replication Service (DRS). Before the migration, prepare 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.
Source Database Version |
Destination Database Version |
Migration Type |
---|---|---|
RDS for MySQL/Self-managed MySQL/MySQL in other clouds
|
RDS for MySQL
|
Version upgrade |

DRS supports migration only from an earlier version to a later version.
Upgrade Pre-check and Handling Suggestions for Upgrade Failures
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, submit a service ticket. |
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, /../). Submit a service ticket for handling the problem. |
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, submit a service ticket. |
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. |
An upgrade starts after the pre-check is passed. If the upgrade fails, rectify the fault by following the instructions provided in Table 4.
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 3. |
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. |
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot