ApsaraDB RDS:Change the storage engine from InnoDB, TokuDB, or MyRocks to X-Engine

Usage notes

• If the existing table uses InnoDB, make sure that the remaining disk space of your RDS instance is twice the data volume of the table. After the storage engine is changed to X-Engine, the disk space that is occupied by the table decreases to 10% to 50% of the original disk space that is occupied by the table before the change.

• If you use Solution 1 in this topic, you must reconfigure parameters and restart your RDS instance. We recommend that you stop your database service before you change the storage engine.

• 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 operation during off-peak hours.

• Before you change the storage engine, make sure that X-Engine is compatible with SQL.

• We recommend that you change the value of the default_storage_engine parameter to xengine after the change. This ensures that all newly created tables 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 perform the following operations to update the minor engine version: Go to the Basic Information page of your RDS instance and click Update Minor Engine Version in the Configuration Information section. If Update Minor Engine Version is not displayed, you are using the latest minor engine version. For more information, see Update the minor engine version.

• If your RDS instance runs a different major engine version, such as 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 execute DDL statements to change the storage engine to X-Engine. This solution is easy and fast. However, this solution triggers an instance restart. The engine change process temporarily blocks write operations but does not affect your queries.

1. Go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the ID of the instance.

2. In the left-side navigation pane of the page that appears, click Parameters.

3. In the upper-left corner of the Modifiable Parameters tab, click Apply Template. In the dialog box that appears, select MySQL_8.0_X-Engine_High-availability_Default Parameter Template and click OK.

4. Use Data Management (DMS) to log on to your RDS instance. For more information, see Use DMS to log on to an ApsaraDB RDS for MySQL instance.

5. Execute the following statement to change the storage engine of 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;

   Examples

   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 your workloads over to the new RDS instance.

1. Use DMS to log on to your RDS instance 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 the storage engine from InnoDB or TokuDB to X-Engine. The following figure shows how to change the storage engine from InnoDB to X-Engine. You can use the same method to change the storage engine from TokuDB to X-Engine.

   

3. Create an RDS instance that runs MySQL 8.0 and has the same specifications as your original RDS instance. Make sure that you select the X-Engine parameter template.

   Note

   When you create the 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. Use DMS to log on to the new RDS instance 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 synchronization between MySQL databases.

   Important

   In the Advanced Settings step, do not select Initial Schema Synchronization.