Data Transmission Service:Synchronize data from an ApsaraDB RDS for MySQL instance to a self-managed Doris database

Source database requirements

Table constraints

• Tables without PRIMARY KEY or UNIQUE constraints: select Schema Synchronization for Synchronization Types and duplicate for Engine in the Configurations for Databases, Tables, and Columns step. DTS adds extra columns to destination tables during schema synchronization. See Additional columns.

Scale limits

• When synchronizing at the table level with object name editing (such as table or column name mapping), a single task supports a maximum of 1,000 tables. If you exceed this limit, split the objects across multiple tasks or synchronize the entire database.

Binary log requirements

Binary logging is enabled by default for ApsaraDB RDS for MySQL. Check and configure the following parameters before starting the task:

DDL restrictions

• Do not perform DDL schema changes during initial schema synchronization or initial full data synchronization. This causes the task to fail.

• Data changes from operations not recorded in binary logs (such as physical backup recovery and cascade operations) are not synchronized. If this occurs, remove the affected object from the synchronization scope and add it back. See Modify synchronization objects.

MySQL 8.0.23+ invisible columns

If the source runs MySQL 8.0.23 or later, invisible columns cause data loss because DTS cannot read their values. Run the following command to make invisible columns visible before starting the task:

ALTER TABLE <table_name> ALTER COLUMN <column_name> SET VISIBLE;

Tables without a primary key automatically generate an invisible primary key — make that visible as well. See Invisible Columns and Generated Invisible Primary Keys.

Destination database requirements

Supported Doris storage models

DTS can only write to tables that use the Unique Key model or Duplicate Key model in Doris.

When the destination table uses the Duplicate Key model, DTS converts UPDATE and DELETE statements to INSERT statements. Duplicate data may appear in the following situations:

• A retry occurred in the synchronization instance.

• The synchronization instance restarted.

• Two or more DML operations were performed on the same row after the task started.

To deduplicate, use the additional columns _is_deleted, _version, and _record_id. See Additional columns.

Naming requirements

• Doris only accepts database and table names that start with a letter. Use the object name mapping feature to rename any object whose name starts with a non-letter character.

• If a database name, table name, or column name contains Chinese characters, use object name mapping to rename it (for example, translate it to English). Otherwise, the task may fail.

Operational restrictions

• Do not create clusters in the destination Doris database during synchronization. This causes the task to fail. Restart the synchronization instance to resume.

• Do not add backend (BE) nodes to the Doris database during synchronization. This causes the task to fail. Restart the synchronization instance to resume.

• Do not modify DDL operations on multiple columns at once, or run consecutive DDL changes on the same table.

Multi-table merge

When merging data from multiple source tables into a single destination table, all source tables must have the same schema. Schema mismatches cause data inconsistency or task failures.

VARCHAR length mapping

MySQL VARCHAR(M) measures character length; Doris VARCHAR(N) measures byte length. If you are not using DTS schema synchronization, set the Doris VARCHAR field length to 4 times the corresponding MySQL VARCHAR length.

Online DDL restrictions

Performance and latency

• During initial full data synchronization, DTS consumes read and write resources on both source and destination databases. Run the synchronization during off-peak hours when the CPU load is below 30%.

• Concurrent INSERT operations during full data synchronization create table fragmentation in the destination. The destination storage usage will be larger than the source after full sync completes.

• During incremental synchronization, DTS uses a batch policy: by default, it writes to each synchronization object at most once every 5 seconds, resulting in a normal latency of under 10 seconds. To reduce latency, adjust the selectdb.reservoir.timeout.milliseconds parameter (range: 1,000–10,000 milliseconds) in the DTS console.

  A shorter batch interval increases write frequency, which may raise the load and response time (RT) of the destination database and increase latency. Adjust based on destination database load.

• Write to the destination database only through DTS during synchronization. Writing from other sources causes data inconsistency.

Encryption support

Instance recovery

If a DTS instance fails, the DTS team attempts recovery within 8 hours. Recovery may involve restarting the instance or adjusting DTS instance parameters. Database parameters are not modified. For parameters that may be adjusted, see Modify instance parameters.

Special cases

Self-managed MySQL source

• If a primary/secondary failover occurs during synchronization, the task fails.

• Latency is calculated as the difference between the timestamp of the last synchronized record and the current time. If no DML operations occur on the source for an extended period, the displayed latency may be inaccurate. Perform a DML operation on the source to refresh the latency display. Alternatively, if you choose to synchronize the entire database, you can create a heartbeat table that is updated every second.

• DTS periodically runs CREATE DATABASE IF NOT EXISTS \test\`` on the source to advance the binary log offset.

• If the source is an Amazon Aurora MySQL instance or a similar clustered MySQL instance, make sure the domain name or IP address configured for the task always resolves to the read/write (RW) node.

ApsaraDB RDS for MySQL source

• ApsaraDB RDS for MySQL instances that do not record transaction logs — such as read-only instances of ApsaraDB RDS for MySQL 5.6 — cannot be used as a source.

• DTS periodically runs CREATE DATABASE IF NOT EXISTS \test\`` on the source to advance the binary log offset.

Step 1: Open the Data Synchronization page

Use one of the following methods to go to the Data Synchronization page:

DTS console

1. Log on to the DTS console.

2. In the left-side navigation pane, click Data Synchronization.

3. In the upper-left corner, select the region where the synchronization instance resides.

DMS console

The exact navigation path may vary based on DMS console mode and layout. See Simple mode and Customize the layout and style of the DMS console.

1. Log on to the DMS console.

2. In the top navigation bar, move the pointer over Data + AI and choose DTS (DTS) > Data Synchronization.

3. From the drop-down list next to Data Synchronization Tasks, select the region where the synchronization instance resides.

Step 2: Configure source and destination databases

Click Create Task, then configure the following parameters:

Step 3: Test connectivity

Click Test Connectivity and Proceed. In the CIDR Blocks of DTS Servers dialog box, click Test Connectivity.

DTS server CIDR blocks must be added to the security settings of both source and destination databases before the test passes. See Add DTS server IP addresses to a whitelist.

Step 4: Configure synchronization objects

In the Configure Objects step, configure the following settings:

Step 5: Configure advanced settings

Click Next: Advanced Settings and configure the following options:

(Optional) Click Next: Configure Database and Table Fields to specify Primary Key Column, Distribution Key, and Engine for each table. This step is available only when Schema Synchronization is selected.

Set Definition Status to All to view and edit all tables.

Primary Key Column supports multiple columns. Columns selected for Primary Key Column are also available for Distribution Key.

For tables without primary keys or UNIQUE constraints, select duplicate for Engine. Otherwise, the task may fail or data may be lost.

Step 6: Run the precheck

Click Next: Save Task Settings and Precheck.

To preview the API parameters for this task, hover over the button and click Preview OpenAPI parameters before proceeding.

DTS runs a precheck before the task starts. The task only starts after the precheck passes.

• If the precheck fails, click View Details next to each failed item, fix the underlying issues, and then click Precheck Again.

• If a precheck item triggers an alert:

  • Items that cannot be ignored: click View Details, fix the issue, and rerun the precheck.

  • Items that can be ignored: click Confirm Alert Details, click Ignore in the dialog box, click OK, and then click Precheck Again. Ignoring alerts may cause data inconsistency.

Step 7: Purchase the instance

1. Wait until Success Rate reaches 100%, then click Next: Purchase Instance.

2. On the purchase page, configure the following parameters:

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

2. Click Buy and Start, then click OK in the confirmation dialog box.

The task appears in the task list. Monitor its progress from there.