Data Transmission Service:Synchronize data from RDS for MySQL to ApsaraDB for SelectDB

Additional columns for the Duplicate engine

DTS automatically adds the following columns to destination tables that use the Duplicate engine. Use these columns to filter duplicate rows in queries.

Source database

• All tables to be synchronized must have a primary key or a UNIQUE constraint. For tables with neither, select Schema Synchronization for Synchronization Types and set Engine to duplicate for those tables during configuration. Otherwise, the task may fail or data may be lost.

  DTS adds extra columns to destination tables that use the Duplicate engine. See Additional columns for the Duplicate engine.

• During initial schema synchronization and initial full data synchronization, do not run DDL operations that change database or table schemas. DTS queries the source during full sync, which creates metadata locks that may block DDL operations on the source.

• Data changes that are not recorded in binary logs — such as data restored from a physical backup or data generated by cascade operations — are not synchronized.

• If the source database is MySQL 8.0.23 or later and the data to be synchronized contains invisible columns, those columns cannot be read and data loss may occur. To make invisible columns visible, run:

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

  See Invisible Columns for details.

• If you need to map table or column names during table-level sync, the task supports a maximum of 1,000 tables. Exceeding this limit causes a post-submission error. Split the tables across multiple tasks, or configure the task to synchronize the entire database instead.

• If the always-encrypted (EncDB) feature is enabled for the source RDS for MySQL instance, initial full data synchronization is not supported. For RDS for MySQL instances with Transparent Data Encryption (TDE) enabled, schema sync, full sync, and incremental sync are all supported.

• RDS for MySQL instances that do not record transaction logs, such as read-only instances of RDS for MySQL 5.6, are not supported as a source.

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

• For self-managed MySQL sources:

  • Enable binary logging, set binlog_format to row, and set binlog_row_image to full.

  • For a primary/primary cluster (both nodes acting as primary and secondary to each other), enable log_slave_updates to make sure DTS can read all binary logs.

  • Retain binary logs for at least 7 days.

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

  • If the source is an Amazon Aurora MySQL instance or another cluster-mode MySQL instance, the domain name or IP address configured for the task must always resolve to the read/write (RW) node address.

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

  • DTS latency is calculated by comparing the timestamp of the last synchronized data record in the destination with the current timestamp. If no DML operations are performed on the source database for a long time, the displayed latency may be inaccurate. To update the latency reading, perform a DML operation on the source. If you synchronize the entire database, you can also create a heartbeat table — DTS updates the heartbeat table every second.

Destination

• Only Unique or Duplicate engine tables are supported in the SelectDB destination. Synchronization of INDEX, PARTITION, VIEW, PROCEDURE, FUNCTION, TRIGGER, and FK objects is not supported.

• Do not create a new cluster in the destination SelectDB instance during synchronization. If the task fails, restart the synchronization instance to resume.

• Do not add backend (BE) nodes to the SelectDB instance during synchronization. If the task fails, restart the synchronization instance to resume.

• Database and table names must start with a letter. Use the mapping feature to rename objects that do not comply.

• Object names (databases, tables, or columns) that contain Chinese characters must be mapped to compliant names. Otherwise, the task may fail.

• DDL operations that modify multiple columns at once, and consecutive DDL operations on the same table, are not supported.

• In a multi-table merge scenario (multiple source tables synchronizing to a single destination table), all source tables must have the same schema to avoid data inconsistency or task failure.

• Online DDL changes made with pt-online-schema-change on the source are not supported. Data loss or task failure may occur.

• Online DDL changes made with DMS or the gh-ost tool are synchronized as the original DDL. No temporary table data is replicated, but the destination table may be locked temporarily.

• Do not write data to the destination from other sources during synchronization. This causes data inconsistency between source and destination.

• Before you synchronize data, evaluate the performance of the source and destination databases. We recommend that you perform data synchronization during off-peak hours to reduce the impact on source and destination database load.

• During incremental synchronization, DTS uses a batch synchronization policy. By default, DTS writes to a single sync object at most once every 5 seconds, which may result in a normal synchronization latency of less than 10 seconds. To reduce latency, adjust the selectdb.reservoir.timeout.milliseconds parameter in the DTS console. The valid range is 1,000–10,000 milliseconds.

  A shorter batching interval increases write frequency, which may increase load and write response time on the destination — and in turn increase DTS synchronization latency. Adjust based on actual destination load.

• MySQL VARCHAR(M) uses character length. SelectDB VARCHAR(N) uses byte length. If you are not using schema synchronization, set the VARCHAR field length in SelectDB to four times the MySQL length. The maximum VARCHAR length in SelectDB is 65,533 bytes; fields that exceed this limit are converted to STRING.

• Only the bucket_count parameter can be set in the Selected Objects box. The value must be a positive integer. The default is auto.

• Initial full data synchronization uses concurrent INSERT operations, which causes fragmentation in destination tables. After full sync completes, the tablespace of the destination instance will be larger than that of the source.

Step 1: Go to the Data Synchronization page

Use one of the following consoles:

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 data synchronization instance resides.

DMS console

The actual operations may vary based on the mode and layout of the DMS console. 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 to the right of Data Synchronization Tasks, select the region.

Step 2: Configure source and destination connections

Click Create Task and configure the following parameters.

Click Test Connectivity and Proceed.

Make sure the CIDR blocks of DTS servers are added to the security settings of both the source and destination databases. See Add DTS server IP addresses to a whitelist.

Step 3: Configure sync objects

In the Configure Objects step, set the following parameters.

Using the object name mapping feature may cause other objects that depend on the mapped object to fail to synchronize.

Click Next: Advanced Settings and configure the following parameters.

Optional: Click Next: Configure Database and Table Fields to set the Primary Key Column, Distribution Key, and Engine for destination tables. This step is available only when Schema Synchronization is selected.

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

For Primary Key Column, you can select multiple columns to form a composite primary key. Select one or more primary key columns as the Distribution Key.

For tables with neither a primary key nor a unique key, set Engine to duplicate.

Step 4: Run the precheck and purchase the instance

1. Click Next: Save Task Settings and Precheck.

   To preview the API parameters for this task, move the pointer over Next: Save Task Settings and Precheck and click Preview OpenAPI parameters.

2. Wait for the precheck to complete. If the precheck fails:

   • Click View Details next to each failed item, address the issue, and click Precheck Again.

   • For alert items that can be ignored, click Confirm Alert Details, then Ignore, then OK, then Precheck Again. Ignoring alerts may result in data inconsistency.

3. After the precheck reaches a Success Rate of 100%, click Next: Purchase Instance.

4. On the buy page, configure the following parameters:

   Parameter	Description
   Billing Method	Subscription: pay upfront; more cost-effective for long-term use. Subscription durations: 1–9 months, or 1, 2, 3, or 5 years. Pay-as-you-go: billed per hour; suitable for short-term use
   Resource Group Settings	The resource group for the instance. Default: default resource group
   Instance Class	The sync speed varies by instance class. See Instance classes of data synchronization instances

5. Read and select Data Transmission Service (Pay-as-you-go) Service Terms, then click Buy and Start. In the dialog box, click OK.

The task appears in the task list. DTS first runs schema synchronization and full data synchronization, then switches to incremental data synchronization.

If a DTS instance fails, DTS support will attempt to recover it within 8 hours. Recovery may involve restarting the instance or adjusting DTS instance parameters (not database parameters). For the parameters that may be modified, see Modify instance parameters.