ApsaraDB RDS:Upgrade the major engine version

Select an upgrade method

Before you upgrade the major engine version of your RDS instance, you must select an upgrade method based on your business requirements.

• If your RDS instance falls into one of the following categories and meets the configuration requirements for the category, we recommend that you upgrade the major engine version based on Method 1: Upgrade the major engine version in the ApsaraDB RDS console.

  RDS instance that runs RDS Cluster Edition with ESSDs

  • Group replication: Your RDS cluster does not use MySQL group replication (MGR). If the RDS cluster uses MGR, the major engine version cannot be upgraded. An RDS instance that runs RDS Cluster Edition is referred to as an RDS cluster. For more information, see Introduction to the MGR mode.

  • Database proxy: If the database proxy feature is enabled for the RDS cluster, the version of the database proxy must be 1.13.41 or later.

  • Instance status: The RDS cluster is in the Running state. The primary and secondary nodes in the RDS cluster run as expected and no replication latency exists between the nodes.

  • Database engine: The databases of the RDS cluster and all tables in the databases use InnoDB.

  • Instance type: The RDS cluster does not use a phased-out instance type. For more information, see Instance types for standard primary ApsaraDB RDS for MySQL instances (original x86 architecture).

  RDS instance that runs RDS High-availability Edition with ESSDs

  • Database proxy: If the database proxy feature is enabled for the RDS instance, the version of the database proxy must be 1.13.41 or later.

  • Instance status: The RDS instance is in the Running state. The primary and secondary RDS instances run as expected and no replication latency exists between the RDS instances.

  • Database engine: The databases of the RDS instance and all tables in the databases use InnoDB.

  • Instance type: The RDS instance does not use a phased-out instance type. For more information, see Instance types for standard primary ApsaraDB RDS for MySQL instances (original x86 architecture).

  RDS instance that runs RDS high-availability Edition with local SSDs

  • Encryption: The transparent data encryption (TDE) feature is disabled for the RDS instance. If the TDE feature is enabled, you cannot disable the TDE feature. In this case, we recommend that you upgrade the major engine version based on Method 2: Upgrade the major engine version in the DTS console.

  • Read-only instance: If read-only RDS instances are attached to the primary RDS instance, the instance types of the read-only RDS instances must be the same, and up to eight read-only RDS instances can be attached. The instance types of read-only RDS instances and the primary RDS instance can be different.

  • Database proxy: If the database proxy feature is enabled for the RDS instance, the version of the database proxy must be 1.13.41 or later.

  • Instance status: The RDS instance is in the Running state. The primary and secondary RDS instances run as expected and no replication latency exists between the RDS instances.

  • Maximum number of tables:: The RDS instance can contain up to 200,000 tables.

  • Database engine: The databases of the RDS instance and all tables in the databases use InnoDB.

  • Instance type: The RDS instance does not use a phased-out instance type. For more information, see Instance types for standard primary ApsaraDB RDS for MySQL instances (original x86 architecture).

  RDS instance that runs RDS Basic Edition with ESSDs

  • Instance status: The RDS instance is in the Running state.

  • Database engine: The databases of the RDS instance and all tables in the databases use InnoDB.

  • Instance type: The RDS instance does not use a phased-out instance type. For more information, see Instance types for standard primary ApsaraDB RDS for MySQL instances (original x86 architecture).

• If your RDS instance does not fall into one of the preceding categories or the TDE feature is enabled for your RDS instance, we recommend that you upgrade the major engine version based on Method 2: Upgrade the major engine version in the DTS console.

• If your RDS instance falls into one of the preceding categories but does not meet the configuration requirements for the category, you can modify the configuration and then upgrade the major engine version based on Method 1: Upgrade the major engine version in the ApsaraDB RDS console or Method 2: Upgrade the major engine version in the DTS console.

  Item	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 on the RDS instance that runs RDS High-availability Edition with local SSDs exceeds 200,000.	Delete redundant tables before you upgrade the major engine version.
  Specific databases and tables do not use InnoDB.	Execute the ALTER TABLE <Table name> engine=InnoDB; statement to change the engine to InnoDB.
  The RDS instance uses a phased-out instance type.	Upgrade the instance type before you upgrade the major engine version. For more information, see Change instance specifications.
  The database proxy version does not meet the requirements.	Upgrade the database proxy version to 1.13.41 or later. For more information, see Upgrade the database proxy 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

• Upgrade an ApsaraDB RDS for SQL Server instance with local disks from SQL Server 2008 R2 to SQL Server 2012 or SQL Server 2016

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

Preparations

1. Understand the differences and benefits between the original and required major engine versions

   • Upgrade from MySQL 5.6 to MySQL 5.7: For more information about the feature comparison, see Appendix 4: Differences between MySQL 5.6 and MySQL 5.7. For more information about the upgrade benefits, see Appendix 2: Advantages of MySQL 5.7 over MySQL 5.6.

   • Upgrade from MySQL 5.7 to MySQL 8.0: For more information about the feature comparison, see Appendix 3: Differences between MySQL 5.7 and MySQL 8.0. For more information about the upgrade benefits, see Appendix 1: Advantages of MySQL 8.0 over MySQL 5.7.

2. Understand the upgrade process and impacts

   • Version: You cannot upgrade the RDS instance across major engine versions in the ApsaraDB RDS console. After the major engine version is upgraded, the upgrade cannot be rolled back. For example, if you want to upgrade the major engine version from MySQL 5.6 to MySQL 8.0, you must upgrade the major engine version from MySQL 5.6 to MySQL 5.7 and then to MySQL 8.0.

   • Upgrade procedure for an RDS instance that uses local SSDs: The major engine version of the secondary RDS instance is first upgraded. After the upgrade is complete, a primary/secondary switchover is performed. Then, the major engine version of the primary RDS instance is upgraded. During the upgrade, your workloads are interrupted for 30 seconds to 1 minute. We recommend that you perform the upgrade during off-peak hours.

   • Upgrade procedure for an RDS instance that uses Enterprise SSDs (ESSDs): An instance is created, and the major engine version upgrade is performed on the new RDS instance. After the upgrade is complete, the data is migrated from the original RDS instance to the new RDS instance. During the upgrade, your workloads are interrupted for 30 seconds to 1 minute. We recommend that you perform the upgrade during off-peak hours.

3. Check instance and database configurations

   • Check reserved keywords: You must check user-defined functions to determine whether reserved keywords are used in the functions. For more information, see Reserved keywords of an ApsaraDB RDS for MySQL instance.

   • Check full backups: Before a major engine version upgrade, you must check whether a full backup is performed over the most recent seven days. If no full backup is performed over the most recent seven days, you must perform a full backup.

   • Check the automatic reconnection mechanism: During a major engine version upgrade, an instance switchover is triggered. We recommend that you perform the upgrade during off-peak hours or make sure that your application is configured to automatically reconnect to your RDS instance. For more information about the impacts of an instance switchover, see Impacts of an instance switchover.

   • Check available storage: Before a major engine version upgrade, make sure that sufficient storage is reserved for the upgrade. We recommend that you reserve more than 10 GB of storage.

   • Adjust the log cleanup policy: You must increase the retention period and maximum storage usage of log files. For more information, see Manage binary log files.

   • Backup instance parameters: After the major engine version upgrade, you can no longer view or modify the instance parameters that are used in the earlier version but are deprecated in the later version. This ensures the stability and performance of your RDS instance that runs the new MySQL version. Before you upgrade the major engine version of your RDS instance, we recommend that you back up the parameter modification records for subsequent operations and auditing.

   • If you upgrade the major engine version of your RDS instance from MySQL 5.6 to MySQL 5.7 or from MySQL 5.7 to MySQL 8.0, you must also check the following information.

     Upgrade from MySQL 5.6 to MySQL 5.7

     Check the full-text index and version information: When you create a full-text index for a database on your RDS instance that runs a major engine version of MySQL 5.6 and a minor engine version of 20221130 or earlier, the full-text index is created in the system tablespace. In this case, after the major engine version is upgraded to MySQL 5.7, the tablespace may be damaged. If the minor engine version is outdated, we recommend that you update the minor engine version to the latest version and then upgrade the major engine version. For more information, see Upgrade the major engine version.

     Upgrade from MySQL 5.7 to MySQL 8.0

     • Check feature compatibility: If the stored procedures, triggers, views, or functions in MySQL 5.7 involve features that are not supported by MySQL 8.0, you must modify them before the upgrade. Otherwise, the upgrade fails. For more information, see Changes in MySQL 8.0.

     • Check system table dependencies:: You must check whether your workloads depend on system tables in MySQL 5.7, such as sys, mysql, information_schema, and performance_schema. After the upgrade to MySQL 8.0, specific system tables in MySQL 5.7 may change. For example, tables are removed, table names are modified, or table schemas are changed. If the system tables on which your workloads depend are changed, errors may occur.

     • Check the data type compatibility: RDS instances that run MySQL 8.0 do not support specific data types. If the unsupported data types are used for specific tables in MySQL 8.0, you must execute the REPAIR TABLE statement or use the logical export and import methods to resolve the issue before the upgrade. For more information, see Preparing Your Installation for Upgrade.

     • Check comment values: The loose_upgrade_clear_invalid_comment parameter is introduced for RDS instances that run a major engine version of MySQL 8.0 and a minor engine version of 20221231 or later. If the parameter uses the default value ON, garbled comments for the tables, fields, and indexes are deleted during the upgrade to prevent upgrade failures. Before the upgrade, you must check whether garbled characters exist in comment values. If garbled characters exist, the comments are deleted.

4. Perform pre-upgrade testing and simulation

   • Test the syntax: Before the upgrade, we recommend that you create an RDS instance that runs the required major engine version and test the syntax on the RDS instance. This prevents syntax or feature incompatibility issues.

   • Simulate the upgrade: Before the upgrade, we recommend that you clone the original RDS instance and use the cloned RDS instance to test whether the required major engine version is compatible with your workloads. After you confirm that the required major engine version is compatible with your workloads, upgrade the original RDS instance.

5. Post-upgrade usage notes

   • Restore the RDS instance that runs the original major engine version: If the RDS instance uses cloud disks, you can use the backup sets of the RDS instance to restore the RDS instance. If the RDS instance uses local disks, you cannot use the backup sets of the RDS instance to restore the RDS instance.

   • Restore the RDS instance that runs the new major engine version: You cannot use the backup sets of the RDS instance that runs the original major engine version to restore the RDS instance that runs the new major engine version. To restore the RDS instance that runs the new major engine version, you must create backup sets after the upgrade.

Procedure

In the following scenarios, you must perform a pre-upgrade check and then upgrade the major engine version after the check is passed:

• Upgrade from MySQL 5.6 to MySQL 5.7 and then to MySQL 8.0 for RDS instances that run RDS High-availability Edition and use local SSDs

• Upgrade from MySQL 5.7 to MySQL 8.0 for RDS instances that run RDS High-availability Edition and use ESSDs

In other scenarios, you can directly upgrade the major engine version.

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 that runs the required major engine version. For more information, see Create an ApsaraDB RDS for MySQL instance.

2. Migrate the data of the original RDS instance to the new RDS instance. For more information, see Migrate data between ApsaraDB RDS for MySQL instances.

3. Release the original RDS instance. For more information, see Release or unsubscribe from an ApsaraDB RDS for MySQL instance.

Example: If your RDS instance runs MySQL 5.7 and the TDE feature is enabled for the RDS instance, you cannot upgrade the major engine version of the RDS instance 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 RENAME INDEX to rename an index.

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

• JSON expressions are 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. The character sets include the gb18030 character set that is defined in the Chinese National Standard GB 18030-2005: Information technology - Chinese coded character set.

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

• Dump threads are optimized to reduce lock contention and increase throughput.

• The replication latency is significantly reduced.

• The sys system database is added to support multiple metrics. These metrics help reduce storage usage and simplify database management.

Appendix 3: Differences between MySQL 5.7 and MySQL 8.0

Appendix 4: Differences between MySQL 5.6 and MySQL 5.7

Troubleshooting related to resharding

• Why does an instance switchover occur when I upgrade the major engine version of my RDS instance? Does the upgrade cause other serious risks?

  If your RDS instance uses local SSDs, the system upgrades the major engine version of the secondary RDS instance. Then, the system switches your workloads over to the secondary RDS instance before it upgrades the major engine version of the primary RDS instance. This ensures service stability. If your RDS instance uses ESSDs, the system create an RDS instance and upgrade 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 In this case, a primary/secondary switchover occurs. The major engine version upgrade does not cause other serious risks. For more information about the impacts of a primary/secondary switchover, see Impacts.

• Does the system upgrade the major engine version of the primary RDS instance and the secondary RDS instance at the same time?

  No. If your RDS instance uses local SSDs, the system first upgrades the major engine version of the secondary RDS instance, switches your workloads to the secondary RDS instance, and then upgrades the major engine versions of the primary RDS instance.

• How do I upgrade the major engine version of my RDS instance that runs MySQL 5.7 on RDS Basic Edition with standard SSDs?

  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 the major engine version of your RDS instance that runs MySQL 5.7 on RDS Basic Edition with standard SSDs, you must upgrade the RDS edition of the RDS instance from RDS Basic Edition to RDS High-availability Edition and change the storage type of the RDS instance.

• Is the parameter template that is applied to my RDS instance changed after I upgrade the major engine version of my RDS instance?

  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.

• Can I change the configuration items of my RDS instance when I upgrade the major engine version of the RDS instance?

  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.

• Does ApsaraDB RDS support the automatic upgrade of a major engine version?

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

• Can I downgrade the major engine version of an RDS instance?

  No, you cannot downgrade the major engine version of an RDS instance or roll back the major engine version upgrade.

• When I upgrade the minor engine version of my RDS instance from MySQL 5.6 to MySQL 5.7 or from MySQL 5.7 to MySQL 8.0, the upgrade fails and the error message "The current instance minor version is less than 20221130. Please upgrade the minor version before deleting or rebuilding the full-text index." or "The current instance contains full-text indexes created in the system tablespace. Delete and rebuild the corresponding full-text indexes before upgrading." is displayed. What are the cause and solution of the error?

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

  • Cause

    If you create a full-text index for your RDS instance that runs MySQL 5.6 and an earlier minor engine version, the full-text index is created in the system tablespace due to history reasons. After you upgrade the major engine version of your 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.

  • Solutions

    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. If your RDS instance runs MySQL 5.6 and an earlier minor engine version, update the RDS instance to the most recent minor engine version.

    1. Delete the full-text index created in the system tablespace based on the mentioned table name.

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

    2. Recreate the full-text index.

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

    3. After the recreation, execute the following SQL statement to check 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);

Related operations