ApsaraDB RDS:Synchronize MySQL instance data to a DuckDB analytic primary instance

Prerequisites

Before you begin, make sure you have:

• A DuckDB-based analytical primary instance. See Create a DuckDB-based analytical primary instance

• Storage space on the destination instance that exceeds the used storage space of the source database

Supported source instance types

DTS supports the following MySQL source types:

• RDS for MySQL

• A self-managed MySQL database on an ECS instance

• An on-premises self-managed MySQL database

This topic demonstrates the configuration using an RDS for MySQL instance. The procedure is similar for self-managed MySQL databases.

If your source is a self-managed MySQL database, complete the additional preparation steps first. See Overview of preparations.

Supported SQL operations

DDL: CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE, ADD COLUMN, DROP COLUMN, MODIFY COLUMN

DML: INSERT, UPDATE, DELETE

UPDATE and DELETE statements cannot include ORDER BY or LIMIT clauses.

If you change a source table's field type during synchronization, the task reports an error and stops. See Fix a synchronization failure caused by a field type change to resolve this manually.

Limitations

• All source tables must have a primary key. Tables without a primary key cannot be synchronized.

• Do not perform online DDL changes on source tables using gh-ost or pt-online-schema-change during synchronization—the task will fail. If you use DMS or gh-ost, configure the Replicate Temporary Tables Generated by Online DDL Tools setting in the advanced settings step.

• Prefix indexes are not supported. If a source table uses prefix indexes, the task may fail.

• A DuckDB-based analytical primary instance cluster is locked when disk usage on any node exceeds 80%. Estimate the required space for your sync objects in advance.

• If a DTS task is running while the destination instance is being backed up, the task fails.

• A DuckDB-based analytical primary instance cannot serve as the source in a synchronization task.

• DML statements that contain a LIMIT clause, a non-deterministic function such as UUID(), or an XA transaction are flagged as Unsafe replication operations. These may cause data inconsistency between the source and destination.

• Schedule synchronization during off-peak hours when CPU utilization on both databases is below 30%. Full data synchronization increases read and write load on both databases.

Billing

Required database account permissions

Set up the synchronization task

Step 1: Configure source and destination databases

1. Log on to the DTS console. In the left-side navigation pane, choose Data Synchronization. In the top navigation bar, select the region where your instance resides.

2. On the Data Synchronization Tasks page, click Create Task.

3. On the Create Synchronization Task > Configure Source and Destination Databases page, configure the following parameters.

Source instance

Parameter	Description
Database Type	Relational Database > MySQL
Access Mode	Cloud Instance
Instance Region	The region of the source instance. Selected when you purchased the task and cannot be changed.
Cross-account Synchronization	No
Instance ID	The ID of the source RDS for MySQL instance.
Database Account	The database account for the source instance. See Required database account permissions.
Database Password	The password for the database account.
Connection Method	Non-encrypted Connection or SSL-encrypted Connection. To use SSL, enable SSL encryption on the RDS instance first. See Use a cloud-native certificate to enable SSL encryptionUse a cloud-native certificate to enable SSL encryption Important Currently, the Encryption can be set only in the Chinese mainland and China (Hong Kong) regions. .

Important

SSL encryption can only be configured in the Chinese mainland and China (Hong Kong) regions.

Destination instance

Parameter	Description
Database Type	Data Warehouse > DuckDB
Access Mode	Cloud Instance
Instance Region	The region of the destination instance. Selected when you purchased the task and cannot be changed.
Cross-account Synchronization	No
RDS Instance ID	The ID of the destination DuckDB-based analytical primary instance.
Database Account	The database account for the destination instance. See Required database account permissions.
Database Password	The password for the database account.
Connection Method	Non-encrypted Connection or SSL-encrypted Connection Important Currently, the Encryption can be set only in the Chinese mainland and China (Hong Kong) regions. .

DTS supports cross-region synchronization. This topic uses instances in the same region and VPC as an example. For other scenarios, see Overview of data synchronization scenarios.

1. Click Test Connection and Proceed.

2. In the DTS Server Access Authorization dialog box, click Test Connection.

Step 2: Configure sync objects and advanced settings

1. On the Configure Synchronization Objects page, configure the following parameters.

Parameter	Description
Synchronization Types	Select the sync types for your use case: <br>• Full sync only: select Schema Synchronization and Full Data Synchronization. <br>• Continuous sync without service interruption: select Schema Synchronization, Full Data Synchronization, and Incremental Data Synchronization (charged). <br><br> Note If you skip Schema Synchronization, make sure the destination database already contains the target tables. If you skip Incremental Data Synchronization, do not write to the source during synchronization to maintain data consistency.
Trigger Migration Method	Select how to handle triggers. Only available when Schema Synchronization is selected. If no triggers are involved, skip this parameter. See Configure a method to synchronize or migrate triggers.
Enable Migration Evaluation	Evaluates whether source and destination schemas (index length, stored procedures, dependent tables) meet requirements. Only available when Schema Synchronization is selected. If set to Yes, the precheck takes longer, but you can view evaluation results during the precheck. Evaluation results do not affect the precheck outcome.
Synchronization Topology	Select One-way Synchronization.
Processing Mode for Existing Tables	• Precheck and Report Error: Reports an error during precheck if a table with the same name already exists in the destination. To rename conflicting tables, see Map table and column names. <br>• Ignore and Continue: Skips the existence check. <br><br> Warning With Ignore and Continue, conflicts are handled differently depending on sync type: during full sync, DTS retains the destination record and skips the source record; during incremental sync, the source record overwrites the destination record. Inconsistent schemas may cause partial sync or failure.
Migrate Event	Select whether to migrate events. If you select Yes, follow the requirements in Synchronize or migrate events.
Case Sensitivity of Object Names	Set the case-sensitivity policy for database, table, and column names in the destination. Defaults to DTS Default Policy. See Case-sensitivity policy for names of objects in the destination database.
Source Objects	In the Source Objects box, select objects to sync and click to move them to the Selected Objects box. Select at the database, table, or column level. Selecting only tables or columns excludes views, triggers, and stored procedures.
Selected Objects	Right-click an object to rename it or filter rows using a WHERE clause. Click Batch Edit to rename multiple objects. See Map table and column names and Filter data by using a WHERE clause.

1. Click Next: Advanced Settings and configure the following parameters as needed. The default settings work for most scenarios.

Parameter	Description
Dedicated Cluster	By default, DTS runs tasks on a shared cluster. To improve task stability, purchase a dedicated cluster. See DTS dedicated clusters.
Replicate Temporary Tables Generated by Online DDL Tools	Controls how DTS handles temporary tables from online DDL tools. pt-online-schema-change is not supported. <br>• Yes: Syncs temporary table data. Note that large temporary tables may increase latency. <br>• No, Adapt to DMS Online DDL: Skips temporary table data and syncs only the original DDL from DMS. Tables in the destination are locked during this process. <br>• No, Adapt to gh-ost: Skips temporary table data and syncs only the original DDL from gh-ost. Configure regular expressions for shadow and trash tables if needed. Tables in the destination are locked during this process.
Migrate Accounts	Select whether to sync source database account information. If Yes, select the accounts and confirm their permissions. See Migrate database accounts.
Retry Duration upon Connection Failure	How long DTS retries before failing the task after a connection error. Default: 720 minutes. Range: 10–1,440 minutes. Set 30 minutes or more. If DTS reconnects within this window, the task resumes automatically. <br><br> Note If multiple DTS instances share a source or destination, the shortest retry duration among them applies to all. DTS charges during retry periods.
Retry Duration for Other Errors	How long DTS retries non-connection errors (such as DDL or DML execution failures). Default: 10 minutes. Range: 1–1,440 minutes. Set 10 minutes or more. Must be less than Retry Duration upon Connection Failure.
Data Rate Limit for Full Synchronization	Limit the data rate for full sync to reduce load on the destination. Set QPS Limit for Source Database, RPS Limit for Full Synchronization, and BPS Limit for Full Synchronization (MB/s). Only available when Full Data Synchronization is selected.
Data Rate Limit for Incremental Synchronization	Limit the data rate for incremental sync. Set RPS Limit for Incremental Synchronization and BPS Limit for Incremental Synchronization (MB/s).
Remove Heartbeat Table SQL	Controls whether DTS writes heartbeat SQL to the source database. <br>• Yes: Does not write heartbeat SQL. The DTS instance may show a delay. <br>• No: Writes heartbeat SQL. This may affect physical backup and cloning of the source database.
Environment Tag	Tag to identify the environment of the instance.
Scenario Tag	Tag to identify the use case of the instance.
Configure ETL	Select whether to configure the extract, transform, and load (ETL) feature. See Configure ETL in a DTS migration or synchronization task.
Sensitive Data Scan and Desensitization	Enable to identify and mask sensitive data during transmission.

1. Click Next: Configure Data Verification and configure data verification settings.

Schema verification may report false inconsistency errors due to historical compatibility issues. These do not affect the actual sync result. To avoid false positives, do not enable schema verification.

Parameter	Description
Full Verification Mode	• Full-field verification on sampled rows: Set a sampling percentage (10–100%) for full-field verification. Charged based on the amount of data verified. <br>• Row count verification: Verifies only row counts, not data content. Free of charge.
Full Verification Schedule	Only Start Now is supported.
Full Verification Timeout	• Not set: The verification task runs until complete. <br>• Set: Forcibly ends the task after the specified duration. The value must be an integer from 1 to 72.
Full Verification Baseline	• Default: Uses the union of source and destination as the baseline. <br>• Source: Verifies that destination data matches the source; ignores data that exists only in the destination. <br>• Destination: Verifies that source data matches the destination; ignores data that exists only in the source.
Maximum RPS for Full Verification	Limits rows read per second during full verification to reduce database load. A value of 0 means no limit. If both this and Maximum BPS for Full Verification (byte/s) are set to 0, no rate limit applies.
Maximum BPS for Full Verification (byte/s)	Limits bytes read per second during full verification.
Incremental Verification Baseline	Filter the DML operations to verify.
Verification Objects	By default, all sync objects are included. Remove objects you do not want to verify by selecting them in the Selected Objects box and clicking .
Full Verification Alert	Set alerts for verification failures or when inconsistent data exceeds a threshold.
Incremental Verification Alert	Set alerts for verification failures, inconsistent record counts, or latency exceeding a threshold.

1. Click Next: Save Task and Precheck.

Step 3: Run the precheck and purchase

1. After the Precheck Pass Rate reaches 100%, click Next: Purchase.

   The task cannot start until it passes the precheck. If any check item fails, click the icon next to the failed item to see the cause, fix the issue, and rerun the precheck. To skip warning items, click Acknowledge and then Ignore and Rerun Precheck.

2. On the Purchase page, select a billing method and link specification.

Parameter	Description
Billing Method	• Subscription: Pay upfront. More cost-effective for long-term use—longer durations get higher discounts. <br>• Pay-as-you-go: Charged hourly. Better for short-term use; release the instance at any time to stop charges.
Resource Group	The resource group for the instance. Defaults to Default Resource Group. See What is Resource Management?.
Link Specification	Determines synchronization throughput. Select based on your data volume and latency requirements. See Specifications of data synchronization links.
Subscription Duration	Available only for the Subscription billing method. Select 1–9 months or 1, 2, 3, or 5 years.

1. Read and agree to the Data Transmission Service (Pay-As-You-Go) Terms of Service, click Purchase and Start, and then click OK.

2. After the purchase is complete, you are redirected to the Data Synchronization Tasks page. Click the task ID to open its details page, then click Task Management to monitor synchronization progress.

Fix a synchronization failure caused by a field type change

If a field type in a source table is changed during synchronization, the task stops. Follow these steps to resolve the issue.

1. In the destination instance, create a new table (for example, customer_new) based on the failed table (for example, customer). Adjust the field types to match the updated schema.

2. Run INSERT INTO SELECT to copy data from the original table to the new table. Verify that both tables contain identical data.

3. Rename or delete the original table, then rename the new table to the original name (for example, customer).

4. In the DTS console, restart the synchronization task.

What's next

• Overview of DuckDB-based analytical primary instances

• Create and connect to a DuckDB-based analytical primary instance

• Change the configurations of a DuckDB-based analytical primary instance

• Add a DuckDB-based analytical read-only instance to an RDS for MySQL high-availability instance