ApsaraDB RDS:Upgrade the major database version

Select an upgrade method

Both Method 1: Directly upgrade the database version in the console and Method 2: Upgrade the database version using DTS support upgrades for all major MySQL versions. This includes upgrades from MySQL 5.5 to 5.6, 5.6 to 5.7, and 5.7 to 8.0. Before you upgrade the database, select a suitable upgrade method based on the following information:

• If your instance belongs to one of the following four categories and its configuration meets the corresponding requirements, use Method 1: Directly upgrade the database version in the console.

  Note

  Serverless instances do not support direct upgrades from the console. You must use Method 2: Upgrade the database version using DTS.

  Cluster Edition (ESSD and premium performance disk)

  • Group replication restriction: You cannot upgrade Cluster Edition instances that use MySQL Group Replication (MGR).

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

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

  • Engine restriction: The database and all its tables must use the InnoDB storage engine.

  • The instance must not use a phased-out instance type.

  High-availability Edition (ESSD and premium performance disk)

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

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

  • Engine restriction: The database and all its tables must use the InnoDB storage engine.

  • The instance must not use a phased-out instance type.

  High-availability Edition (premium performance local disk)

  • Encryption restriction: The transparent data encryption (TDE) feature must be disabled. After TDE is enabled, it cannot be disabled. If TDE is enabled on your instance, you must use Method 2: Upgrade the database version using DTS.

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

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

  • Table quantity restriction: The number of tables cannot exceed 1 million.

  • Engine restriction: The database and all its tables must use the InnoDB storage engine.

  • Instance type restriction: The database version after the upgrade must support the original instance types of the primary and read-only instances. The instances must not use a phased-out instance type. For more information, see Primary ApsaraDB RDS for MySQL instance types.

  Basic Edition (ESSD and premium performance disk)

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

  • Engine restriction: The database and all its tables must use the InnoDB storage engine.

  • The instance must not use a phased-out instance type.

• If your instance does not belong to one of the preceding four categories or if TDE is enabled, use Method 2: Upgrade the database version using DTS.

• If your instance belongs to one of the preceding four categories but its configuration does not meet the requirements, you can modify the configuration as described in the following table. Then, you can use either Method 1: Directly upgrade the database version in the console or Method 2: Upgrade the database version using DTS.

  Problem	Solution
  The instance is in a state other than Running, such as Restarting.	Wait for the current task to complete before you upgrade the database version.
  The number of tables on a High-availability Edition instance with a premium performance local disk exceeds 1 million.	Delete redundant tables before the upgrade.
  Some databases or tables do not use the InnoDB engine.	Run the ALTER TABLE <table_name> engine=InnoDB; command to switch to the InnoDB engine.
  The instance uses a phased-out instance type.	Upgrade the instance type before you upgrade the database version. For more information, see Change instance specifications.
  The minor version of the database proxy 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 engine version of a database proxy.
  The storage type of the instance is standard SSD.	First, upgrade the standard SSD to an enterprise SSD (ESSD), and then upgrade the database version.

To upgrade the major database version of other database engines, see the following topics:

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

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

Method 1: Directly upgrade the database version in the console

Method 2: Upgrade the database version using DTS

For instances that do not support a direct upgrade from the console, you can create a new instance with a later database version. Then, use a DTS data migration task to migrate data from the original instance to the new one. This indirectly upgrades the database version. This process involves the following steps:

1. Create a new instance

2. Migrate data to the new instance

3. Release the original instance

Example: You have a MySQL 5.7 instance with TDE enabled, which cannot be directly upgraded from the console. In this case, you can create a new instance that runs MySQL 8.0, migrate the data from the original instance to the new one, and finally release the original instance. This indirectly upgrades the database version.

Appendix 1: Benefits of upgrading from MySQL 5.7 to MySQL 8.0

• Improves security and provides greater flexibility in account management.

• Supports the creation and management of resource groups.

• Enhances the features of the InnoDB storage engine.

• Adds support for new character sets, data types, syntax, new backup locks, and optimizer_switch flags.

• Enhances JSON and XML functionality.

• Enhances the features of the optimizer.

• Improves replication performance.

• Supports the creation of multi-valued indexes and derived condition pushdown optimization.

• Supports reading MySQL grant tables.

• Supports resource allocation control.

Appendix 2: Benefits of upgrading from MySQL 5.6 to MySQL 5.7

• Adds features such as password management, account locking, and encrypted connections to improve database security.

• Supports Online DDL operations, such as renaming an index using RENAME INDEX.

• Improves the scalability of the InnoDB engine and the performance of temporary tables for faster data loading.

• Supports JSON.

• Supports Index Condition Pushdown (ICP) for partitioned tables and new InnoDB spatial indexes.

• Optimizes most parsers, optimizers, and cost models to improve database maintainability, scalability, and performance.

• Expands the range of supported character sets, including the GB18030 character set specified by the Chinese national standard.

• Provides the ngram full-text parser plugin, which supports Chinese, Japanese, and Korean.

• Optimizes source dump threads to reduce lock contention and increase source throughput.

• Significantly reduces replication delay.

• Adds the sys system database, which provides multiple metrics and reduces storage usage, significantly improving database usability.

Appendix 3: Feature differences between MySQL 8.0 and MySQL 5.7

Appendix 4: Feature differences between MySQL 5.7 and MySQL 5.6

Appendix 5: Feature differences between MySQL 5.5 and MySQL 5.6

FAQ

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

  A: To ensure service stability, instances with premium performance local disks are upgraded by first upgrading the secondary node and then performing a switchover. Instances with ESSDs are upgraded by creating a new node and then performing a switchover. There are no other serious risks. For more information about the impact of a primary/secondary failover, see Impact of a primary/secondary failover.

• Q: Are the primary and secondary nodes upgraded at the same time?

  A: When you upgrade a high-performance local disk, the secondary instance is upgraded first, followed by the primary instance.

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

  A: You cannot directly upgrade this type of instance. To upgrade a Basic Edition instance that runs MySQL 5.7 with standard SSDs, you must first change the storage type from standard SSD to ESSD, and then upgrade the database version.

• Q: Is the parameter template retained after the database version is upgraded?

  A: It depends. If the instance uses a system parameter template before the upgrade, it is automatically switched to the corresponding system parameter template for the new version. For example, an instance using the MySQL_InnoDB_5.7_High-availability_Performance parameter template is switched to the MySQL_InnoDB_8.0_High-availability_Performance parameter template after an upgrade from MySQL 5.7 to 8.0. However, if the instance uses a custom parameter template, the parameter template is not retained after the upgrade.

• Q: Can I modify the instance during the database version upgrade?

  A: No, you cannot. You can perform other operations on the instance only after the upgrade is complete.

• Q: Does the database version support automatic upgrades?

  A: No, it does not. Automatic major version upgrades are not supported.

• Q: Can I downgrade the database version?

  A: No, you cannot directly downgrade the version from the console. To downgrade, you can purchase an instance with an earlier version and use DTS to migrate data from the instance with the later version to the new instance. After the migration is complete, you can release the instance with the later version. For more information, see Data migration between RDS instances.

• Q: When I upgrade an RDS for MySQL instance from 5.6 to 5.7 or from 5.7 to 8.0, the upgrade fails. It shows the message "The current instance has a full-text index and its minor version is earlier than 20221130. Please upgrade the minor version before deleting and rebuilding the full-text index" or "The current instance contains a full-text index built in the system tablespace. Please delete and rebuild the corresponding full-text index before proceeding with the upgrade." What is the cause and solution?

  A: The cause and solution are as follows:

  • Cause

    Due to a historical issue in MySQL, when you create a full-text index on an early version of MySQL 5.6, it is built in the system tablespace. When you upgrade to version 5.7 or 8.0, a full-text index in the system tablespace might cause tablespace corruption. Therefore, you must resolve this issue before the upgrade to prevent data corruption and inaccessibility.

    Note

    This issue was fixed in RDS for MySQL 5.6 version 20221130. Full-text indexes are now built in a separate tablespace.

  • Solution

    Important

    Full-text indexes on earlier versions of RDS for MySQL 5.6 are created in the system tablespace. Therefore, make sure that the version you are upgrading from is RDS for MySQL 5.6 20221130 or later before you upgrade to RDS for MySQL 5.7. If you are using an earlier version, upgrade to the latest version of RDS for MySQL 5.6 first.

    1. Based on the table name in the prompt, delete the full-text index that was built in the system tablespace.

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

    2. Re-create the full-text index.

       # Re-create the full-text index.
       ALTER TABLE $table_name ADD FULLTEXT INDEX $fts_name;

    3. After you create the index, you can run the following SQL statement to check the full-text indexes on the current instance. The statement returns any full-text indexes that are built in the system tablespace. If the query returns an empty result, the upgrade from RDS for MySQL 5.6 to RDS for MySQL 5.7 will not fail due to this issue.

       # Query for full-text indexes built 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 an RDS for MySQL instance from 5.7 to 8.0, the error 267 - Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '=' is reported. How do I handle this?

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

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

  If you create a table with the utf8mb4_general_ci collation in MySQL 5.7 and then upgrade to MySQL 8.0, the system uses utf8mb4_0900_ai_ci as the default collation. If you run a query that compares a column that uses utf8mb4_general_ci with a column that uses utf8mb4_0900_ai_ci, MySQL cannot process the two different collations, which results in an error.

• Q: Is the transient connection time during a major version upgrade always 30 to 60 seconds, regardless of whether there are read-only instances?

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

Related API operations