Synchronize data from an ApsaraDB RDS instance to an AnalyticDB for MySQL cluster - Data Transmission Service

Prerequisites

Before you begin, make sure you have:

An ApsaraDB RDS for MySQL instance where all tables to synchronize have primary keys
An AnalyticDB for MySQL cluster with enough storage for the data you plan to synchronize. See Create an AnalyticDB for MySQL cluster
The required permissions on both databases (see Permissions required)

Billing

Synchronization type	Cost
Schema synchronization and full data synchronization	Free
Incremental data synchronization	Charged. See Billing overview

Supported SQL operations

DTS synchronizes the following operations from ApsaraDB RDS for MySQL to AnalyticDB for MySQL:

DDL operations: CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE, ADD COLUMN, DROP COLUMN, and MODIFY COLUMN
DML operations: INSERT, UPDATE, and DELETE

Note

If the data type of a field in the source table is changed during data synchronization, an error message is reported and the data synchronization task is interrupted. For more information about how to handle this error, see Troubleshoot a field type change error.

Operation type	Supported operations
DDL	`CREATE TABLE`, `DROP TABLE`, `RENAME TABLE`, `TRUNCATE TABLE`, `ADD COLUMN`, `DROP COLUMN`, `MODIFY COLUMN`
DML	`INSERT`, `UPDATE`, `DELETE`

If you change the data type of a field in the source table while synchronization is running, DTS reports an error and pauses the task. See Troubleshoot a field type change error.

Permissions required

Grant the following permissions before configuring the synchronization task.

ApsaraDB RDS for MySQL (source)

-- Grant SELECT on all objects to synchronize (replace db_name and dts_user)
GRANT SELECT ON db_name.* TO 'dts_user'@'%';

-- Grant replication permissions required for change data capture
GRANT REPLICATION CLIENT, REPLICATION SLAVE, SHOW VIEW ON *.* TO 'dts_user'@'%';

AnalyticDB for MySQL (destination)

Grant read and write permissions on the objects to synchronize:

GRANT SELECT, INSERT, UPDATE, DELETE ON db_name.* TO 'dts_user'@'%';

Limitations

Review these limitations before starting synchronization:

Prefix indexes are not supported. If your source tables use prefix indexes, the synchronization task fails. Remove or replace prefix indexes before starting the task.
Avoid gh-ost and pt-online-schema-change. Running these tools on source tables during synchronization can break the task. If you need to perform online DDL, pause the task first.
AnalyticDB cluster locks at 80% disk usage. If node disk usage exceeds 80%, the cluster locks and writes stop. Ensure the destination cluster has enough free space before starting.
DTS consumes read and write resources on both instances during full data synchronization. This can increase load and, in the worst case, affect database availability. Synchronize during off-peak hours, for example, when CPU utilization on both instances is below 30%.
Merging multiple source tables into one destination table disables DDL synchronization. When Merge Multi Tables is set to Yes, DTS adds a __dts_data_source column to track the origin of each row. In this mode, DDL changes from the source are not propagated.

Data type mappings

DTS maps source data types to destination data types during initial schema synchronization. For the full mapping table, see Data type mappings for initial schema synchronization.

Set up the synchronization task

Step 1: Purchase a DTS instance

Purchase a DTS instance. On the buy page, set:

Source Instance: MySQL
Destination Instance: AnalyticDB MySQL
Synchronization Topology: One-way Synchronization

Step 2: Configure the task

Log on to the DTS console.
In the left-side navigation pane, click Data Synchronization.
At the top of the Data Synchronization Tasks page, select the region where you created the task.
Find your task and click Configure Task in the Actions column.

Configure the source instance and destination cluster.

Section	Parameter	Description
N/A	Synchronization Task Name	Enter a name that helps you identify the task. Does not need to be unique.
Source instance details	Instance Type	Select RDS Instance.
	Instance Region	Pre-filled from the buy page. Cannot be changed.
	Instance ID	Select the source RDS instance.
	Database Account	The database account on the source instance. Note If the source instance runs MySQL 5.5 or MySQL 5.6, skip this field and Database Password.
	Database Password	The password for the database account.
	Encryption	Select Non-encrypted or SSL-encrypted. If you select SSL-encrypted, enable SSL encryption on the RDS instance first. See Configure the SSL encryption feature. Note The Encryption parameter is available only within the Chinese mainland and the China (Hong Kong) region.
Destination instance details	Instance Type	Pre-filled as AnalyticDB. Cannot be changed.
	Instance Region	Pre-filled from the buy page. Cannot be changed.
	Version	Select 3.0 .
	Database	Select the AnalyticDB for MySQL cluster.
	Database Account	The database account on the destination cluster.
	Database Password	The password for the database account.

源目实例信息配置

Click Set Whitelist and Next. DTS automatically adds its server CIDR blocks to the IP address whitelist of Alibaba Cloud database instances (such as ApsaraDB RDS). For self-managed databases, add the CIDR blocks manually. See Add the CIDR blocks of DTS servers.

Warning
Adding DTS CIDR blocks to your whitelist or security group rules creates security exposure. Before proceeding, restrict port access, use strong credentials, audit whitelist entries regularly, and consider connecting through Express Connect, VPN Gateway, or Smart Access Gateway instead of opening public IP access.

Select the synchronization policy and objects.

Setting	Description
Initial Synchronization	Select both Initial Schema Synchronization and Initial Full Data Synchronization. DTS synchronizes the schema and existing data to the destination before starting incremental synchronization.
Processing Mode In Existed Target Table	Pre-check and Intercept (default): the precheck fails if the destination already contains tables with the same names. Use this to prevent accidental overwrites. Ignore: skips the name-conflict check. If source and destination schemas match, DTS skips rows with duplicate primary keys. If schemas differ, the initial load may fail or synchronize only some columns.
Merge Multi Tables	No (default): DDL operations are synchronized normally. Yes: DTS merges all selected source tables into one destination table and adds a `__dts_data_source` column to identify the source. DDL synchronization is disabled. To merge only some tables, create a separate synchronization task for those tables.
Synchronization Type	Select the operation types to synchronize. All types are selected by default. See Supported SQL operations.
Objects to synchronize	Move tables or databases from Available to Selected. Selecting a database synchronizes all schema changes in that database. Selecting individual tables synchronizes only `ADD COLUMN` operations for those tables. To rename synchronized objects in the destination, use object name mapping.
Specify whether to copy temporary tables (DMS online DDL)	If you use Data Management (DMS) to run online DDL on the source, choose whether to synchronize the temporary tables DMS generates. Yes: synchronizes temporary table data. Large online DDL operations may increase synchronization lag. No: synchronizes only the final DDL result. The destination table may be temporarily locked during the operation.
Retry Time for Failed Connections	DTS retries for 720 minutes (12 hours) by default when it loses connection to the source or destination. You are charged during the retry period, so adjust this value based on your cost and availability requirements.

配置同步策略和对象

Click Next.
Specify the table type for each table to synchronize to the destination cluster. For each table, set the type, primary key column, and partition key column. These are required when Initial Schema Synchronization is selected. See CREATE TABLE in the AnalyticDB for MySQL documentation.
Click Precheck. Before you can start the data synchronization task, DTS performs a precheck. You can start the data synchronization task only after the task passes the precheck. If a check fails, click the icon next to the failed item for details. Fix the issue, then click Precheck again. To skip a non-critical failure, select Ignore for that item and rerun the precheck.
After the Precheck Passed message appears, close the dialog. The synchronization task starts automatically. Wait for the initial full synchronization to complete. When the task enters the Synchronizing state, incremental synchronization is active. Monitor the task status on the Synchronization Tasks page.

Troubleshoot a field type change error

If you modify the data type of a field on the source table while synchronization is running, DTS reports an error and pauses the task. To resume:

In the destination cluster, create a new table with the same schema as the failed table but a different name. For example, if the original table is customer, create customer_new with the schema that matches the current (post-change) source table.
Copy the existing data from the original table into the new table:
```
INSERT INTO customer_new SELECT * FROM customer;
```

Rename or drop the original table, then rename the new table to the original name:

RENAME TABLE customer TO customer_old, customer_new TO customer;
-- Drop customer_old after you confirm the data is correct

In the DTS console, restart the synchronization task.

What's next

Rename an object to be synchronized — change how objects appear in the destination cluster without modifying the source
Data type mappings for initial schema synchronization — verify how your source data types map to AnalyticDB for MySQL types
Billing overview — understand DTS pricing for incremental synchronization