Convert tables from InnoDB, TokuDB, or MyRocks to X-Engine

Precautions

• If the tables that you want to convert uses InnoDB, make sure that the remaining disk space of your RDS instance is twice the data volume of the tables. After the conversion to X-Engine, the disk space that is occupied by the tables decreases to 10% to 50% of the original disk space that is occupied by the tables before the conversion.
• If you use Solution 1 in this topic to perform the conversion, you must reconfigure parameters and restart your RDS instance. Stop your database service before you perform the conversion.
• If you use Solution 2 in this topic to migrate all data from your RDS instance to a new RDS instance, you must update the endpoints on your application. We recommend that you perform the migration during off-peak hours.
• Before the conversion, make sure that X-Engine is compatible with SQL.
• After the conversion, change the value of the default_storage_engine parameter to xengine. This ensures that all of the tables that are created later use X-Engine.

Solution recommendations

• If your RDS instance runs MySQL 8.0 with a minor engine version of 20200229 or later, we recommend that you use Solution 1. This way, you do not need to configure various tools.
  Note If the minor engine version of your RDS instance does not meet your business requirements, you can update the minor engine version on the Basic Information page in the ApsaraDB RDS console. In the Configuration Information section of the Basic Information page, click the Upgrade Kernel Version button to update the minor engine version of your RDS instance. If the button does not exist, you are using the latest minor engine version. For more information, see Update the minor engine version of an ApsaraDB RDS for MySQL instance.
• If your RDS instance runs MySQL 5.6 or MySQL 5.7, we recommend that you use Solution 2.

Solution 1

This solution allows you to enable X-Engine by using a parameter template. Then, you can use data definition language (DDL) statements to convert tables to X-Engine. This solution is easy and fast, but requires a restart of your RDS instance. In addition, data manipulation language (DML) operations may be blocked, and the conversion of large-sized tables is time-consuming.

1. Access RDS Instances, select a region at the top, and then click the ID of the target RDS instance.
2. In the left-side navigation pane, click Parameters.
3. In the upper-left corner of the Editable Parameters tab, click Apply Template. In the dialog box that appears, select MySQL_8.0_X-Engine_High-availability_Default Parameter Template from the Apply Template drop-down list and click OK.
   Note This operation triggers a restart of your RDS instance. After the restart, 95% of the memory resources are allocated to X-Engine. Do not use X-Engine and InnoDB at the same time.
4. Log on to your RDS instance by using Data Management (DMS). For more information, see Use DMS to log on to an ApsaraDB RDS for MySQL instance.
5. Run the following command to convert a table on the SQL Console tab:

   alter table <The name of the database in which the table resides>.<The name of the table> engine xengine;

   Example:

   alter table test.sbtest1 engine xengine;

Solution 2

This solution allows you to synchronize the data of tables in real time from your RDS instance to a new RDS instance by using Data Transmission Service (DTS). After the data synchronization is complete, you can switch over your workloads to the new RDS instance.

1. Log on to your RDS instance by using DMS and export all the schemas of your RDS instance. For more information, see Use DMS to log on to an ApsaraDB RDS for MySQL instance and Export databases.
2. Decompress the schemas and change InnoDB or TokuDB to X-Engine.
   
3. Purchase a new RDS instance that runs MySQL 8.0 and has the same specifications as your RDS instance. Make sure that you select the X-Engine parameter template.
   Note When you create the new RDS instance, you can use the default X-Engine parameter template to specify X-Engine as the default storage engine. For more information, see Create an ApsaraDB RDS for MySQL instance.
4. Log on to the new RDS instance by using DMS and import the schemas into the new RDS instance. For more information, see Use DMS to log on to an ApsaraDB RDS for MySQL instance and Import data.
5. Synchronize data from your RDS instance to the new RDS instance. For more information, see Configure two-way data synchronization between MySQL instances.
   Warning In the Advanced Settings step, do not select Initial Schema Synchronization.