ApsaraDB RDS MySQL has terminated its support for the TokuDB engine since August 1, 2019. This topic describes how to switch the storage engine from TokuDB to InnoDB.

Background information

Percona no longer provides support for TokuDB, leading to bugs that cannot be fixed and can cause business losses in extreme cases. Because of this, ApsaraDB RDS MySQL has terminated its support for the TokuDB engine since August 1, 2019. Direct engine switching will block DML operations and affect concurrency. Therefore, we recommend that you evaluate your business as soon as possible and use one of the following solutions to switch your storage engine:

TokuDB go-offline date

August 1, 2019

Application scope

Instances that use the TokuDB storage engine

Note You can use the show engines; command to view the current default engine of an instance, or use the Show create table <table name>; command to view the storage engine of a table.

Precautions

  • After the storage engine is switched, space usage increases. The space to be reserved is approximately twice the capacity of TokuDB tables during parallel operations. Pay attention to the space usage during operations.
  • After the storage engine is switched, CPU usage decreases but IOPS increases when the same data volume is read. This is because data pages are not compressed.
  • During full database migration, the endpoint needs to be switched. Perform this operation during off-peak hours.
  • If the database version is changed during full database migration, we recommend that you test the compatibility in advance.

Solution recommendations

  • If the table size in an instance is less than 100 MB, and short-term blocking is acceptable, you can use solution 1 to lock tables for a short period of time and avoid various tool configuration processes.
  • If the table size in an instance is larger than 5 GB, we recommend that you use solution 2 or 3.
  • If all tables in an instance need to be migrated to the InnoDB engine, we recommend that you use solution 3 or 4.
  • After all tables are migrated to the InnoDB engine, set the default_storage_engine parameter to InnoDB.

Solution 1

This solution migrates tables to InnoDB in the most straightforward way. However, DML operations may be blocked during the entire process and it takes a long time to migrate large tables.

Procedure

  1. Log on to an RDS MySQL instance through DMS.
  2. In the top navigation bar, choose SQL Operations > SQL Window.
  3. Run the following command: 
    Alter table test.testfs engine innodb
    Direct modification

Solution 2

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

Principle

The principle of how gh-ost migrates a table is as follows: gh-ost creates a temporary table that has the same schema as the original table and incrementally copies data from the original table to the temporary table. After all data is copied to the temporary table, gh-ost reads binary logs through a simulated slave process and synchronizes table changes from the original table to the temporary table in real time. Finally, gh-ost renames the tables to complete the cutover during off-peak hours. This solution puts a heavy load on the I/O performance during initial full data synchronization. However, you can modify parameters to limit I/O.
  • Advantages: gh-ost gives you greater control over the synchronization process.
  • Disadvantages: Each table needs to be synchronized using commands. If a large number of tables exist, the operations are cumbersome.

Parameters

Parameter Description
--initially-drop-old-table Checks and deletes an existing table.
--initially-drop-ghost-table Checks and deletes an existing ghost table.
--aliyun-rds Executes table migration on ApsaraDB for RDS.
--assume-rbr Sets gh-ost to read binary logs in Row Based Replication (RBR) format.
--allow-on-master Runs gh-ost on the primary database.
--assume-master-host Specifies the endpoint of the primary database.
--user Specifies the name of the database account.
--password Specifies the database password.
--host Specifies the endpoint of the database, which must be the same as that of the primary database.
--database Specifies the name of the database.
--table Specifies the name of the ghost table.
--alter Alters the ghost table.
--chunk-size Specifies the number of rows submitted by batch.
--postpone-cut-over-flag-file Specifies the file used to postpone the cutover process. If you delete the file at a specified time, tables will be swapped immediately.
--panic-flag-file Specifies the file used to stop the ghost process. After this file is generated, the ghost process stops immediately.
--serve-socket-file Receives interactive commands.
--execute Directly executes table migration and swapping.

Prerequisites

  • gh-ost is installed on your local host or ECS instance.
  • The IP address of the local host or ECS instance is added to the IP address whitelist of your ApsaraDB for RDS instance.

Procedure

  1. Run the following command on the local host or ECS instance to perform a cutover and wait until the cutover is completed: 
    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.
  3. In the left side, temporary tables ending with _ gho and _ ghc are displayed.Generate a temporary table
  4. Run the rm/tmp/ghostpost.postpone command to start table swapping. The results are as follows:Start table swapping
    Note Ignore the displayed error. The cutover has been completed.
  5. Check the tables and verify the data.
    Note Verify that the data is correct and then delete the _ del table.
    Cutover succeeded

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. This solution can migrate a large number of tables simultaneously.

Procedure

  1. Log on to an RDS MySQL instance through DMS.
  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 DTS instances.
    Note DTS instances are charged. 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 DTS instance and click Configure Synchronization Channel in the Actions column.
  7. Configure the following parameters.
    Category Parameter Description
    Source Instance Details Instance Type Select RDS Instance.
    Instance ID Select the RDS instance for which you want to switch the storage engine.
    Encryption Select Non-encrypted or SSL-encrypted. To select SSL-encrypted, you must enable SSL Encryption. Enabling SSL Encryption will significantly increase CPU consumption.
    Destination Instance Details Instance Type Select RDS Instance.
    Instance ID Select the RDS instance for which you want to switch the storage engine.
    Encryption Select Non-encrypted or SSL-encrypted. To select SSL-encrypted, you must enable SSL Encryption. Enabling SSL Encryption will significantly increase CPU consumption.
  8. Click Set Whitelist and Next.
  9. Wait until synchronization accounts are created. Then, click Next.
  10. Move the testfs table on the left to the right and click Edit.
  11. Set 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 completed, and click Close.
  15. The wait time for data synchronization is 0 ms.
  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 cutover, DTS will report an synchronization error, which can be ignored.
    • To avoid additional charges, release the DTS instance as soon as you have verified 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.

Procedure

  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.
  3. Use DTS to migrate data from the source instance to a new instance. For more information, see Create a real-time synchronization task between RDS instances.
    Note During synchronization initialization, select only Initial Full Data Synchronization.
  4. After confirming that there are no synchronization delays, switch the application connection address to the endpoint of the new instance.