ApsaraDB RDS:[EOS/Discontinuation] ApsaraDB RDS for MySQL no longer supports TokuDB

Background information

Percona no longer provides support for TokuDB, known bugs cannot be fixed, and business loss occur in extreme cases. As a result, ApsaraDB RDS for MySQL terminated its support for the TokuDB engine from August 01, 2019. If you directly change the storage engine, DML operations may be blocked and concurrency is affected. We recommend that you evaluate your workloads at the earliest opportunity and use one of the following solutions to change your storage engine.

Effective date

August 01, 2019

Application scope

RDS instances that use the TokuDB storage engine

Usage notes

• After the storage engine is changed, storage usage increases. The storage capacity that you need to reserve for the engine change operation is approximately twice the storage of TokuDB tables during parallel operations. Pay attention to the storage usage during operations.

• After the storage engine is changed, CPU utilization decreases but IOPS increases when the same volume of data is read. This is because data pages are not compressed.

• During full migration, the endpoint is switched. We recommend that you perform the migration during off-peak hours.

• If the database engine version is changed during full migration, we recommend that you test the compatibility in advance.

Solution recommendations

• If the table size in an RDS instance is less than 100 MB and short-term blocking is acceptable, you can use Solution 1. This solution locks tables for a short period of time and frees you from configuring various tools.

• If the table size in an RDS instance is larger than 5 GB, we recommend that you use Solution 2 or 3.

• If you need to change the storage engine for all tables in an RDS instance to the InnoDB engine, we recommend that you use Solution 3 or 4.

• After the storage engine is changed, you must change the value of the default_storage_engine parameter to InnoDB.

Solution 1

This solution directly changes the storage engine. However, DML operations may be blocked during the entire process, and a long period of time is required to migrate large tables.

1. Log on to an RDS MySQL instance through DMS Use Data Management (DMS) to log on to an RDS instance.

2. In the top navigation bar, choose SQL Operations > SQL Window.

3. Execute the following statement:

   Alter table test.testfs engine innodb

   

Solution 2

This solution uses a third-party tool to migrate tables. Various third-party tools, such as pt-osc developed by Percona and gh-ost developed by GitHub, support online DDL operations. In this example, gh-ost is used to describe the migration process. For more information, see gh-ost.

Principle

Parameters

Prerequisites

• gh-ost is installed on your on-premises host or Elastic Compute Service (ECS) instance.

• The IP address of the on-premises host or ECS instance is added to the IP address whitelist of your RDS instance.

Procedure

1. Run the following command on the on-premises host or ECS instance to perform a migration and wait until the migration is complete:

   gh-ost --user="test01" --password="Test123456" --host="rm-bpxxxxx.mysql.rds.aliyuncs.com"  --database="test" --table="testfs"  --alter="engine=innodb" --initially-drop-old-table --initially-drop-ghost-table --aliyun-rds --assume-rbr --allow-on-master --assume-master-host="rm-bpxxxxx.mysql.rds.aliyuncs.com" --chunk-size=500 --postpone-cut-over-flag-file="/tmp/ghostpost.postpone" --panic-flag-file="/tmp/stop.flag" --serve-socket-file="/tmp/ghost.sock" --execute

   

2. Log on to an RDS MySQL instance through DMS Use DMS to log on to an RDS instance.

3. In the left-side navigation pane, check the tables. Temporary tables ending with _ gho and _ ghc are displayed.

4. Run the rm /tmp/ghostpost.postpone command to start table migration. The following figure shows a sample output.

   Note

   You can ignore the displayed error in the output. The migration has been completed.

5. Check the tables and verify the data.

   Note

   Verify that the data is correct and then delete the _del table.

Solution 3

This solution uses Alibaba Cloud Data Transmission Service (DTS) to synchronize data from an original table to a temporary table in real time, and then locks the original table and renames the tables during off-peak hours. You can use this solution to simultaneously migrate a large number of tables.

1. Use DMS to log on to an RDS instance.

2. In the top navigation bar, choose SQL Operations > SQL Window.

3. Run the following command to create a temporary table:

   CREATE TABLE `testfs_tmp` (
     `id` int(11) NOT NULL AUTO_INCREMENT,
     `vc` varchar(8000) DEFAULT NULL,
     PRIMARY KEY (`id`)
   ) ENGINE=innodb  DEFAULT CHARSET=utf8
                       

4. Purchase a DTS instance.

   Note

   You are charged for the data synchronization instance. For more information, see Data Transmission Service Pricing.

5. In the left-side navigation pane of the DTS console, click Data Synchronization.

6. Find the purchased data synchronization instance and click Configure Synchronization Channel in the Actions column.

7. Configure the parameters. The following table describes the parameters.

   Section	Parameter	Description
   Source Instance Details	Instance Type	Select RDS Instance.
   	Instance ID	Select the RDS instance for which you want to change the storage engine.
   	Encryption	Select Non-encrypted or SSL-encrypted. If you want to select SSL-encrypted, you must enable SSL encryption. Enabling SSL encryption significantly increases CPU utilization. For more information, see Configure SSL encryption for an RDS instance.
   Destination Instance Details	Instance Type	Select RDS Instance.
   	Instance ID	Select the RDS instance for which you want to change the storage engine.
   	Encryption	Select Non-encrypted or SSL-encrypted. If you want to select SSL-encrypted, you must enable SSL encryption. Enabling SSL encryption significantly increases CPU utilization. For more information, see Configure SSL encryption for an RDS instance.

   

8. Click Set Whitelist and Next.

9. Wait until synchronization accounts are created. Then, click Next.

10. Move the testfs table from the left-side section to the right-side section and click Edit.

11. Set the database name to testfs_tmp and click OK.

12. Click Next.

13. Select Initial Full Data Synchronization and click Precheck.

14. Wait until the precheck is complete. Then, click Close.

15. Wait until data synchronization is complete. If 0 Milliseconds is displayed for the Delay parameter, data synchronization is complete.

16. Run the command to rename tables in the SQL window of DMS:

    rename table `testfs` to `testfs_del`,`testfs_tmp` to `testfs`;

    Note

    • After the migration, DTS reports an synchronization error, which can be ignored.

    • To prevent additional charges, we recommend that you release the data synchronization instance at the earliest opportunity after you verify the data.

Solution 4

This solution uses DTS to synchronize data from a database instance to a new instance. This solution applies to instances that require instance upgrade or can accept a relatively long service downtime.

1. Export all schema scripts from the source instance, and delete or modify the engine part in the scripts.

   Note

   For example, change create table t1(id int,name varchar(10)) engine=tokudb; to create table t1(id int,name varchar(10)) engine=innodb;.

2. Create an RDS instance and use the modified scripts to create databases and tables. For more information, see Create an ApsaraDB RDS for MySQL instance.

3. Use DTS to migrate data from the source instance to a new instance. For more information, see Configure one-way data synchronization between ApsaraDB RDS for MySQL instances.

   Note

   During synchronization initialization, select only Initial Full Data Synchronization.

   

4. After you confirm that no synchronization latencies occur, change the application connection address to the endpoint of the new instance.