Data Transmission Service:Migrate data from an ApsaraDB RDS for MySQL instance to an ApsaraDB for ClickHouse cluster

Destination cluster

• The destination ApsaraDB for ClickHouse cluster runs version 20.8 or later. See Create a cluster.

• Storage space on the destination cluster exceeds the storage used by the source RDS MySQL instance.

Source database account permissions

The source database account must have read permissions on the objects to migrate. If the account was not created through the RDS console, grant it the following permissions manually:

GRANT REPLICATION CLIENT, REPLICATION SLAVE, SHOW VIEW, SELECT ON *.* TO '<account>'@'%';

See Create an account and Modify account permissions.

Destination cluster account permissions

Binary log requirements (incremental migration only)

If you plan to include incremental data migration, configure the following on the source MySQL instance.

Enable binary log retention

The minimum retention period depends on the source type:

• ApsaraDB RDS for MySQL: Binary logs must be retained for at least 3 days (7 days recommended).

• Self-managed MySQL: Binary logs must be retained for at least 7 days.

Logs retained for less than the required minimum may cause the migration task to fail or result in data loss. To set the retention period for an RDS MySQL instance, see Configure parameters based on which the system automatically deletes the binary log files of an RDS instance.

Configure binary log format

Set the following parameters on the source MySQL instance:

These settings are required for DTS to read row-level change data. If they are not set correctly, the precheck fails and the migration task cannot start.

If the source is a self-managed MySQL database deployed in a dual-primary cluster, also set log_slave_updates = ON so DTS can obtain all binary logs.

Source database restrictions

• Table-level migration limit: A single migration task supports a maximum of 1,000 tables when object name mapping is used. If you exceed this limit, split the tables across multiple tasks or migrate the entire database.

• Invisible columns (MySQL 8.0.23 and later): DTS cannot read invisible columns, which causes data loss. To make a column visible, run ALTER TABLE <table_name> ALTER COLUMN <column_name> SET VISIBLE;. Tables without explicit primary keys automatically generate invisible primary keys — make those visible too. See Invisible Columns and Generated Invisible Primary Keys.

• Read-only RDS MySQL V5.6: This instance type does not record transaction logs and cannot be used as the source for incremental migration.

• EncDB-enabled instances: Full data migration is not supported. Transparent Data Encryption (TDE)-enabled instances support schema migration, full data migration, and incremental data migration.

• DDL restrictions during migration: Do not execute DDL statements while schema migration or full data migration is running. For full-only migration, do not write to the source database during migration.

• Physical backup restores and cascade operations: Change data generated by physical backup restores or cascade operations on the source is not recorded or migrated to the destination while the migration instance is running. If this occurs, you can perform full data migration again, provided your business is not affected.

Other restrictions

• Migration of INDEX, PARTITION, VIEW, PROCEDURE, FUNCTION, TRIGGER, and foreign keys is not supported.

• The RENAME TABLE operation cannot be migrated.

• If the source database has online DDL change operations in temporary table mode, such as multi-table merge scenarios, data loss in the destination database or failure of the migration instance may occur.

• If the source contains online DDL changes performed with pt-online-schema-change, data loss or migration failure may occur. If you migrate one or more tables instead of an entire database, do not use pt-online-schema-change to perform online DDL operations on the migration objects in the source database — otherwise the migration fails. Online DDL changes performed with DMS or gh-ost are supported — DTS migrates the original DDL statement without copying temporary table data, but this may cause locked tables in the destination. You can use Data Management (DMS) to perform online DDL operations.

• If DDL statements in the source do not follow standard MySQL syntax, the migration task may fail or data may be lost.

• The number of databases to migrate must not exceed 256 (the ClickHouse limit).

• Database, table, and column names must comply with ApsaraDB for ClickHouse naming conventions.

• During schema migration, DTS adds _sign, _is_deleted, and _version fields to destination tables. If you skip schema migration, create the destination tables manually and include these fields. See Appendix: table and field information.

• Data from sources other than DTS must not be written to the destination database while migration is running.

• If a DTS task fails to run, DTS technical support will try to restore the task within 8 hours. During the restoration, the task may be restarted and the parameters of the DTS task may be modified. Note: only the parameters of the DTS task may be modified — the parameters of databases are not modified. The parameters that may be modified include but are not limited to those in the Modify instance parameters section.

ClickHouse-specific constraints

• Time-type data range: ClickHouse time types have range limits. Source data outside these ranges is written incorrectly. See Appendix: time information.

• Partition key: Cannot be a nullable field. Supported types: BIGINT, INT, TIMESTAMP, DATETIME, DATE.

• Maximum databases: 256.

Special cases

Self-managed MySQL sources

• If you perform a primary/secondary switchover on the source database while the data migration task is running, the task fails.

• DTS calculates migration latency based on the timestamp of the latest migrated data in the destination and the current timestamp in the source. If no DML operation is performed on the source for a long time, the migration latency may be inaccurate. To update the latency, perform a DML operation on the source. If you select an entire database as the migration object, you can create a heartbeat table that is updated every second.

• DTS executes CREATE DATABASE IF NOT EXISTS \test\`` in the source database on a scheduled basis to advance the binary log file position.

ApsaraDB RDS for MySQL sources

• In incremental data migration, a read-only ApsaraDB RDS for MySQL V5.6 instance cannot be used as the source database.

• DTS executes CREATE DATABASE IF NOT EXISTS \test\`` in the source database on a scheduled basis to advance the binary log file position.

Step 1: Open the Data Migration page

Use one of the following methods to navigate to the Data Migration page.

Step 2: Configure the task

Click Create Task to open the task configuration page. Configure the parameters described in the following table.

Step 3: Test connectivity

Click Test Connectivity and Proceed at the bottom of the page.

DTS server CIDR blocks must be added to the security settings of both the source and destination databases. See Add the CIDR blocks of DTS servers. If the source or destination is a self-managed database not using the Alibaba Cloud Instance access method, click Test Connectivity in the CIDR Blocks of DTS Servers dialog box.

Step 4: Select objects to migrate

On the Configure Objects page, configure the following settings.

Step 5: Configure advanced settings

Click Next: Advanced Settings and configure the following parameters.

Step 6: Configure ClickHouse table fields

Click Next: Configure Database and Table Fields to set the Type, Primary Key Column, Sort Key, Distribution Key, and Partition Key for each destination table in ClickHouse.

• DTS provides a default configuration. To review or modify all tables, set Definition Status to All.

• Primary Key Column and Sort Key support composite keys — select multiple fields from the drop-down list.

• Partition Key must be one or more columns from Primary Key Column. Only BIGINT, INT, TIMESTAMP, DATETIME, and DATE types are supported. Nullable fields cannot be used as the partition key. Leaving the partition key blank is allowed.

• Distribution Key accepts a single field only.

For details on primary keys, sort keys, and partition keys in ClickHouse, see CREATE TABLE.

Step 7: Run a precheck

Click Next: Save Task Settings and Precheck.

To view the API parameters that correspond to this task configuration, hover over Next: Save Task Settings and Precheck and click Preview OpenAPI parameters.

DTS runs a precheck before starting the migration. If the precheck fails:

1. Click View Details next to the failed item.

2. Fix the issue based on the check results.

3. Click Precheck Again.

If an alert item can be ignored: click Confirm Alert Details > Ignore > OK > Precheck Again. Ignoring alerts may cause data inconsistency.

Step 8: Purchase the instance and start migration

1. Wait for Success Rate to reach 100%, then click Next: Purchase Instance.

2. On the Purchase Instance page, configure the following:

   Section	Parameter	Description
   New Instance Class	Resource Group	The resource group for the migration instance. Default: default resource group. See What is Resource Management?
   	Instance Class	The instance class determines migration speed. See Instance classes of data migration instances.

3. Read and accept Data Transmission Service (Pay-as-you-go) Service Terms.

4. Click Buy and Start > OK.

Query migrated data correctly

DTS uses the ReplicatedReplacingMergeTree engine and adds _sign, _is_deleted, and _version fields to destination tables. Without the FINAL keyword, queries may return duplicate or deleted rows. Always use FINAL and filter on _sign:

SELECT * FROM <table_name> FINAL WHERE _sign > 0;

The WHERE _sign > 0 condition filters out rows marked as deleted.

Table requirements for manual creation

If you skip schema migration, create the destination tables before starting the task. The tables must meet these requirements.

• Community Edition: Create one local table and one distributed table. The distributed table name must match the source table name. The local table name must be <distributed_table_name>_local.

• Enterprise Edition: Create a table with the same name as the source table.

DTS-managed fields

DTS automatically adds the following fields to destination tables during schema migration.

Partition key calculation logic