ApsaraDB RDS:Upgrade the major engine version

Select an upgrade method

Method 1: Upgrade the major engine version in the ApsaraDB RDS console and Method 2: Upgrade the major engine version in the DTS console support all MySQL version upgrades, including upgrades from MySQL 5.5 to MySQL 5.6, from MySQL 5.6 to MySQL 5.7, and from MySQL 5.7 to MySQL 8.0. Before you upgrade the major engine version of your RDS instance, select an upgrade method based on your business requirements:

• If your RDS instance belongs to one of the following four types and meets the requirements of the type, we recommend that you use Method 1: Upgrade the major engine version in the ApsaraDB RDS console.

  Cluster Edition (enhanced SSDs or standard SSDs)

  • MySQL Group Replication (MGR) limitation: You cannot upgrade the major engine version of an RDS instance that uses MySQL Group Replication (MGR).

  • Database proxy limitation (if applicable): The minor version of the database proxy must be 1.13.41 or later.

  • Instance status limitation: The RDS instance must be in the Running state. The primary and secondary nodes must be healthy and have no replication latency.

  • Storage engine limitation: The database and all tables must use the InnoDB storage engine.

  • The RDS instance does not use phased-out instance types.

  High-availability Edition (enhanced SSDs or standard SSDs)

  • Database proxy limitation (if applicable): The minor version of the database proxy must be 1.13.41 or later.

  • Instance status limitation: The RDS instance must be in the Running state. The primary and secondary nodes must be healthy and have no replication latency.

  • Storage engine limitation: The database and all tables must use the InnoDB storage engine.

  • The RDS instance does not use phased-out instance types.

  High-availability Edition (local SSDs)

  • Encryption limitation: Transparent data encryption (TDE) is not enabled. TDE cannot be disabled after it is enabled. If TDE is enabled, you must use Method 2: Upgrade the major engine version in the DTS console.

  • Database proxy limitation (if applicable): The minor version of the database proxy must be 1.13.41 or later.

  • Instance status limitation: The RDS instance must be in the Running state. The primary and secondary nodes must be healthy and have no replication latency.

  • Table quantity limitation: The number of tables cannot exceed 1,000,000.

  • Storage engine limitation: The database and all tables must use the InnoDB storage engine.

  • Instance type limitation: The major engine version to which you want to upgrade must support the instance type of the original RDS instance (including the primary RDS instance and read-only RDS instances). The instance type must not be phased out. For more information, see Primary ApsaraDB RDS for MySQL instance types.

  Basic Edition (enhanced SSDs or standard SSDs)

  • Instance status limitation: The RDS instance must be in the Running state.

  • Storage engine limitation: The database and all tables must use the InnoDB storage engine.

  • The RDS instance does not use phased-out instance types.

• If your RDS instance does not belong to any of the preceding four types or TDE is enabled, use Method 2: Upgrade the major engine version in the DTS console.

• If your RDS instance belongs to one of the preceding four types but does not meet the requirements of the type, you can modify the configuration of the RDS instance based on the following table and then use Method 1: Upgrade the major engine version in the ApsaraDB RDS console or Method 2: Upgrade the major engine version in the DTS console.

  Issue	Solution
  The RDS instance is in another state, such as Restarting.	Upgrade the major engine version after the ongoing task is complete.
  The number of tables in the RDS instance that uses local SSDs and runs High-availability Edition exceeds 1,000,000.	Delete redundant tables before you upgrade the major engine version.
  Specific databases and tables do not use InnoDB.	Run the ALTER TABLE engine=InnoDB; command to convert the storage engine to InnoDB.
  The RDS instance uses a phased-out instance type.	Change the instance type before you upgrade the major engine version. For more information, see Change the specifications of an ApsaraDB RDS for MySQL instance.
  The database proxy version does not meet the requirements.	Upgrade the minor version of the database proxy to 1.13.41 or later. For more information, see Upgrade the minor version of the database proxy.
  The RDS instance uses standard SSDs.	Upgrade the standard SSDs to enhanced SSDs and then upgrade the major engine version.

For more information about how to upgrade the major engine version of an RDS instance that runs a different database engine, see the following topics:

• Upgrade the major engine version of an ApsaraDB RDS for PostgreSQL instance

• Upgrade the major engine version of an ApsaraDB RDS for SQL Server instance

Method 1: Upgrade the major engine version in the ApsaraDB RDS console

Method 2: Upgrade the major engine version in the DTS console

If the major engine version of your RDS instance cannot be upgraded in the ApsaraDB RDS console, you can create a RDS instance that runs the required major engine version and a DTS data migration task and then migrate the data of the original RDS instance to the new RDS instance to upgrade the major engine version.

1. Create an RDS instance

2. Migrate data to the new RDS instance

3. Release the original RDS instance

Example: You have an RDS instance that runs MySQL 5.7 and has TDE enabled. The major engine version of the RDS instance cannot be directly upgraded in the ApsaraDB RDS console. In this case, you can create an RDS instance that runs MySQL 8.0, migrate the data of the original RDS instance to the new RDS instance, and then release the original RDS instance.

Appendix 1: Advantages of MySQL 8.0 over MySQL 5.7

• The security of your database system is enhanced. You can manage accounts in a more flexible manner.

• You can create and manage resource groups.

• The features of the InnoDB storage engine are enhanced.

• New character sets, data types, and syntax are supported. Backup locks and optimizer_switch flags are introduced.

• JSON and XML expressions are enhanced.

• Optimizers are enhanced.

• Replication performance is enhanced.

• Multi-value indexes can be created. Derived condition pushdown is optimized.

• MySQL grant tables can be read.

• Resource allocation control is supported.

Appendix 2: Advantages of MySQL 5.7 over MySQL 5.6

• New features such as password management, account locking, and connection encryption are introduced. These new features help improve the security of your database system.

• Online DDL operations are supported. For example, you can use the RENAME INDEX clause to rename an index.

• The scalability of the InnoDB storage engine and the performance of temporary InnoDB tables are optimized to increase data loading speeds.

• The JSON format is supported.

• Index Condition Pushdown (ICP) is supported for partitioned tables, and spatial indexes are supported for InnoDB tables.

• Most of the used parsers, optimizers, and cost models are optimized to improve the maintainability, scalability, and performance of your database system.

• New character sets are supported. These include the gb18030 character set that is defined in the Chinese National Standard GB 18030-2005: Information technology - Chinese coded character set.

• A built-in ngram full-text parser plug-in that supports Chinese, Japanese, and Korean (CJK) is provided.

• Dump threads on primary RDS instances are optimized to reduce lock contention and increase throughput.

• The replication latency is significantly reduced.

• The sys system database is added to support multiple monitoring metrics. These metrics help you reduce the storage usage and improve the ease of use.

Appendix 3: Differences between MySQL 5.7 and MySQL 8.0

Appendix 4: Differences between MySQL 5.6 and MySQL 5.7

Appendix 5: Differences between MySQL 5.5 and MySQL 5.6

FAQ

• Q: Why does a switchover occur during the upgrade? Are there other serious risks?

  A: To ensure business stability, if your RDS instance uses local SSDs, the system first upgrades the secondary RDS instance. After the upgrade is complete, the system automatically switches your workloads over to the secondary RDS instance. If your RDS instance uses ESSDs, the system creates an RDS instance and upgrades the major engine version of the new RDS instance to the required version. Then, the system switches your workloads over from the original RDS instance to the new RDS instance. The major engine version upgrade does not cause other serious risks. For more information about the impacts of a primary/secondary switchover, see Impacts of a primary/secondary switchover.

• Q: Are the primary and secondary RDS instances upgraded together?

  A: If your RDS instance uses local SSDs, the system first upgrades the secondary RDS instance and then upgrades the primary RDS instance.

• Q: How do I upgrade an RDS instance that runs MySQL 5.7 on RDS Basic Edition with standard SSDs?

  A: You cannot directly upgrade the major engine versions of RDS instances that run MySQL 5.7 on RDS Basic Edition with standard SSDs. If you want to upgrade such an RDS instance, you must first change the storage type from standard SSDs to enhanced SSDs and then upgrade the major engine version.

• Q: Are parameter templates retained before and after the major engine version upgrade?

  A: The answer varies. Assume that you want to upgrade the major engine of your RDS instance from MySQL 5.7 to MySQL 8.0. If the RDS instance uses the system parameter template before the upgrade, the RDS instance will use the system parameter template that corresponds to the original template after the upgrade. For example, if the RDS instance uses the MySQL_InnoDB_5.7_RDS High-availability Edition_High-performance parameter template, the RDS instance will use the MySQL_InnoDB_8.0_RDS High-availability Edition_High-performance parameter template after the upgrade. If the RDS instance uses a custom parameter template before the upgrade, the RDS instance cannot use the template after the upgrade because the template is not retained.

• Q: Can I change the configuration of my RDS instance during the major engine version upgrade?

  A: You cannot change the configuration items of your RDS instance when you upgrade the major engine version of the RDS instance. You can perform other operations on your RDS instance only after the major engine version is upgraded.

• Q: Does the major engine version support automatic upgrade?

  A: No, the major engine version of your RDS instance does not support automatic upgrade.

• Q: Does the major engine version support downgrade?

  A: You cannot downgrade the major engine version in the ApsaraDB RDS console. You can create an RDS instance that runs the required major engine version and use DTS to migrate the data of the original RDS instance to the new RDS instance. After the migration is complete, you can release the original RDS instance. For more information, see Migrate data between ApsaraDB RDS for MySQL instances.

• Q: When I upgrade the major engine version from MySQL 5.6 to MySQL 5.7 or from MySQL 5.7 to MySQL 8.0, the upgrade fails and the system displays the error message "The current instance has a full-text index and a minor engine version earlier than 20221130. Upgrade the minor engine version first and then delete and recreate the full-text index" or "The current instance contains a full-text index created in the system tablespace. Delete and recreate the full-text index before you upgrade the major engine version." What are the cause and solution of the error?

  A: The following list describes the cause and solution of the error:

  • Cause

    If your RDS instance runs MySQL 5.6 and an earlier minor engine version, the full-text index is created in the system tablespace for the RDS instance due to history reasons. After you upgrade the major engine version of the RDS instance from MySQL 5.6 to MySQL 5.7 or MySQL 8.0, the full-text index that is created in the system tablespace may cause tablespace damage. Therefore, you must delete the full-text index before the upgrade to avoid issues such as data corruption and inaccessibility.

    Note

    The preceding issues are fixed for RDS instances that run MySQL 5.6 and a minor engine version of 20221130, and the full-text indexes are created in a separate tablespace for the RDS instances.

  • Solution

    Important

    If your RDS instance runs MySQL 5.6, upgrade the major engine version of the RDS instance to MySQL 5.7 or make sure that the RDS instance runs a minor engine of 20221130 or later. In this case, the full-text index is not created in the system tablespace for the RDS instance.

    1. Delete the FTS index created in the system tablespace based on the table name.

       # Delete the full-text index
       ALTER TABLE $table_name DROP INDEX $fts_name;

    2. Recreate the FTS index.

       # Recreate the full-text index
       ALTER TABLE $table_name ADD FULLTEXT INDEX $fts_name;

    3. Execute the following SQL statement to query the full-text index created for your RDS instance. If no full-text index created in the system tablespace is returned, the upgrade of the major engine version from MySQL 5.6 to MySQL 5.7 does not fail due to the full-text index created in the system tablespace.

       # Query the full-text index created in the system tablespace
       SELECT NAME FROM information_schema.INNODB_SYS_TABLES WHERE TABLE_ID IN ( SELECT CONV(SUBSTRING_INDEX(SUBSTRING_INDEX(NAME, '_', -4),'_', 1),16,10) FROM INNODB_SYS_TABLES WHERE NAME LIKE '%fts_00000000%' AND SPACE = 0);

• Q: When I upgrade the major engine version from MySQL 5.7 to MySQL 8.0, the system displays the error message 267 - Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='. How do I resolve this issue?

  A: Check the character set and collation in MySQL. If you are using utf8mb4_general_ci, we recommend that you change it to utf8mb4_0900_ai_ci by using the following SQL statements.

  # Modify the character set and collation of a database
  ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci;
  # Modify the character set and collation of a table
  ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
  # Modify the character set and collation of a field
  ALTER TABLE table_name CHANGE column_name column_name type CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

  In MySQL 5.7, tables created with the utf8mb4_general_ci collation will default to using utf8mb4_0900_ai_ci as the collation when upgraded to MySQL 8.0. If you compare a column using utf8mb4_general_ci with a column using utf8mb4_0900_ai_ci in a query, MySQL will not be able to handle these two different collations, resulting in a fault.

• Q: Is the interruption time 30 seconds to 1 minute regardless of whether read-only RDS instances exist?

  A: Yes, we recommend that you perform the upgrade during off-peak hours.

API references