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, check whether the Upgrade Kernel Version button exists. If the button exists, you can click the button to view and update the minor engine version. 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 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. Visit the RDS instance list, select a region above, and click the target instance ID.
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 instance.
5. In the top navigation bar, choose SQL Operations > SQL Window.
   
6. Run the following command to convert a table:

   alter table <The name of the database where 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. Perform the following steps to export all the schemas of your RDS instance:
   1. Log on to your RDS instance by using DMS. For more information, see Use DMS to log on to an ApsaraDB RDS instance.
   2. In the top navigation bar, choose Data Operation > Export.
      
   3. Choose New > Export Database.
   4. Configure the parameters and click OK. In the message that appears, click Yes.
      Note The message appears because Extended Options is selected in the Advanced Options dialog box. You can ignore the message.

      
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. Perform the following steps to import the schemas into the new RDS instance.
   1. Log on to the new RDS instance by using DMS. For more information, see Use DMS to log on to an ApsaraDB RDS instance.
   2. In the top navigation bar, choose Data Operation > Import.
      
   3. On the page that appears, click New Task.
   4. In the dialog box that appears, configure the parameters and click Start.
      

      Note After the schemas are imported, you can run the following command to verify that a table uses X-Engine:show create table <The name of the table>;.
5. Synchronize data from your RDS instance to the new RDS instance. For more information, see Configure two-way data synchronization between ApsaraDB RDS for MySQL instances.
   Warning In the Advanced Settings step, do not select Initial Schema Synchronization.