Data Transmission Service:Synchronize data from an RDS for MySQL instance to a DataHub project

What DTS does not synchronize

DTS does not synchronize foreign keys, so cascade and delete operations on the source are not replicated to the destination. The following object types are also excluded: indexes, partitions, views, procedures, functions, triggers, and foreign keys.

Source database requirements

Destination DataHub limits

• A single string field cannot exceed 2 MB.

• If a source table has a column named record_id, use the object name mapping feature to rename it in the destination. Otherwise, an error occurs.

Encryption and EncDB

• TDE (Transparent Data Encryption): Fully supported — schema synchronization, full data synchronization, and incremental data synchronization all work.

• EncDB: Full data synchronization is not supported. No workaround is available; use a non-EncDB source if full data synchronization is required.

Data consistency

Writing data to the destination from sources other than DTS while a task is running causes data inconsistency. This includes running online DDL statements via DMS while other writes occur.

Task recovery

If a DTS task fails, DTS technical support restores it within 8 hours. During recovery, the task may be restarted and task-level parameters may be adjusted. Source and destination database parameters are not modified.

ApsaraDB RDS for MySQL instances

Binary logging is enabled by default. Verify and set the following parameters:

• To set the retention period, see Delete binary log files.

• To set binlog_row_image, see Modify instance parameters.

Read-only RDS for MySQL 5.6 instances do not record transaction logs and cannot be used as a source.

DTS executes the CREATE DATABASE IF NOT EXISTS 'test' statement in the source database as scheduled to move forward the binary log file position.

Self-managed MySQL databases

Enable binary logging and add the following to my.cnf:

binlog_format     = ROW
binlog_row_image  = FULL

For dual-primary clusters, also set:

log_slave_updates = ON

Without log_slave_updates, DTS may miss binary logs that were applied on the secondary primary node before the logs reached the first primary.

Retain binary logs for at least 7 days.

For a complete setup guide, see Create an account for a self-managed MySQL database and configure binary logging.

If you perform a primary/secondary switchover on a self-managed source while a task is running, the task fails.

Synchronization latency on self-managed sources

DTS calculates synchronization latency based on the timestamp of the latest synchronized record versus the current source timestamp. If no DML operations occur on the source for an extended period, the displayed latency may be inaccurate. To refresh the latency metric, run any DML operation on the source.

If you synchronize an entire database, create a heartbeat table that receives a write every second. DTS also executes CREATE DATABASE IF NOT EXISTS 'test' periodically to advance the binary log position.

Step 1: Go to the Data Synchronization page

1. Use either console to access the Data Synchronization page.

   DTS console

   1. Log on to the DTS console.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 will reside.

   DMS console

   Note

   Steps may vary based on your 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.DMS console

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

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

Step 2: Configure source and destination databases

Click Create Task, then configure the parameters below.

Set the Task Name field. DTS generates a name automatically — replace it with a descriptive name that makes the task easy to identify.

Source Database

Destination Database

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 source and destination databases. See Add the CIDR blocks of DTS servers. For self-managed databases not using Alibaba Cloud Instance access, click Test Connectivity in the CIDR Blocks of DTS Servers dialog.

Step 4: Configure objects to synchronize

In the Configure Objects step, set the following options.

Synchronization Type

Select Schema Synchronization, Full Data Synchronization, and Incremental Data Synchronization. Full data synchronization copies historical data to the destination first, establishing the baseline for incremental synchronization.

Naming rules for additional columns

DTS adds metadata columns to each DataHub topic. If these column names conflict with existing columns in the destination, the task fails. Select New Rule or Old Rule based on your destination schema.

Processing mode of conflicting tables

Case Policy for Destination Object Names

By default, DTS default policy is used. Adjust this to match your source or destination casing requirements. See Specify the capitalization of object names in the destination instance.

Select objects

In the Source Objects section, select the tables to synchronize and click the arrow icon to move them to Selected Objects.

You can select tables as the objects to be synchronized.

• To rename objects, right-click a table in Selected Objects. For bulk renaming, click Batch Edit. See Map object names.

• To filter rows using SQL conditions, right-click a table in Selected Objects and configure WHERE conditions. See Set filter conditions.

• To set a Shardkey for partitioning, right-click a table in Selected Objects, clear Synchronize All Tables, then set the Shardkey.

Step 5: Configure advanced settings

Click Next: Advanced Settings and configure the following options.

Step 6: Run the precheck

Click Next: Save Task Settings and Precheck.

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

DTS runs a precheck before starting the task. Address the results as follows:

• Failed items: Click View Details next to the failed item, fix the underlying issue, then click Precheck Again.

• Alert items that cannot be ignored: Fix the issue and rerun the precheck.

• Alert items that can be ignored: Click Confirm Alert Details > Ignore > OK, then click Precheck Again. Ignoring alerts may lead to data inconsistency.

Step 7: Purchase and start the instance

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

2. On the purchase page, configure the billing and instance settings.

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

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

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

How DTS represents change events

DTS uses dts_before_flag and dts_after_flag to indicate whether each record holds the state before or after a change.

INSERT

An INSERT produces one record. All column values are the newly inserted values (post-insert state).

UPDATE

DTS represents an UPDATE as two separate records with the same dts_record_id, dts_operation_flag, and dts_utc_timestamp. The first record holds the pre-update values; the second holds the post-update values.

To reconstruct the full change, join the two records on dts_record_id. For example, if a row changes name from "Alice" to "Alice Smith":

Record 1: dts_record_id=1001, dts_operation_flag=U, dts_before_flag=Y, dts_after_flag=N, id=42, name="Alice", address="..."
Record 2: dts_record_id=1001, dts_operation_flag=U, dts_before_flag=N, dts_after_flag=Y, id=42, name="Alice Smith", address="..."

DELETE

A DELETE produces one record. All column values are from the deleted row (pre-delete state).