Data Transmission Service:Synchronize data from an ApsaraDB RDS for MySQL instance to a PolarDB-X 2.0 instance

Source database requirements

• Tables must have PRIMARY KEY or UNIQUE constraints with all fields unique. Without these constraints, the destination database may contain duplicate records.

• When synchronizing at the table level with object renaming (such as renaming tables or columns), a single task supports up to 1,000 tables. Tasks exceeding this limit return a request error. To synchronize more than 1,000 tables, split the work across multiple tasks or synchronize the entire database instead.

• Binary log requirements:

  • For ApsaraDB RDS for MySQL: Binary logging is enabled by default. Verify that binlog_row_image is set to full—DTS returns an error during precheck if this setting is missing. See Modify instance parameters. Retain binary logs for at least 3 days (7 days recommended).

  • For self-managed MySQL: Enable binary logging, set binlog_format to row, and set binlog_row_image to full. Retain binary logs for at least 7 days. For dual-primary clusters, also set log_slave_updates to ON so DTS can capture all binary logs. See Create an account for a self-managed MySQL database and configure binary logging.

  Important

  If binary logs are purged before DTS reads them, the synchronization task fails. In exceptional cases, data inconsistency or loss may occur. Insufficient binary log retention is not covered by the DTS Service Level Agreement (SLA).

• Do not execute DDL statements that change database or table schemas during schema synchronization or full data synchronization. Doing so causes the task to fail.

• Data generated by binary log change operations—such as data restored from a physical backup or data from cascade operations—is not synchronized to the destination. If needed, remove and re-add the affected tables from the synchronization objects without impacting your business. See Modify the objects to be synchronized.

• If the source is MySQL 8.0.23 or later and the data includes invisible columns, DTS cannot read those columns and data loss occurs.

  • To make a column visible, run: ALTER TABLE <table_name> ALTER COLUMN <column_name> SET VISIBLE;

  • Tables without explicit primary keys auto-generate invisible primary keys. Make these visible before synchronization. See Generated Invisible Primary Keys.

Other limitations

• DTS does not synchronize the following data types: BIT, VARBIT, GEOMETRY, ARRAY, UUID, TSQUERY, TSVECTOR, and TXID_SNAPSHOT.

• Prefix indexes cannot be synchronized. If the source database contains prefix indexes, the task may fail.

• Run the synchronization task during off-peak hours. Initial full data synchronization uses read and write resources on both the source and destination databases, which increases load.

• After initial full data synchronization, the destination tablespace is larger than the source because concurrent INSERTs cause table fragmentation.

• When synchronizing at the table level, do not use pt-online-schema-change for online DDL operations. Use Data Management (DMS) instead. See Perform lock-free DDL operations.

• Writing data from other sources to the destination during synchronization causes data inconsistency.

• If the EncDB feature is enabled on the source RDS MySQL instance, full data synchronization cannot be performed.

  Note RDS MySQL instances with Transparent Data Encryption (TDE) enabled support schema synchronization, full data synchronization, and incremental data synchronization.

• During schema synchronization, DTS synchronizes foreign keys from the source to the destination. During full data synchronization and incremental data synchronization, DTS temporarily disables foreign key constraint checks and cascade operations at the session level. If you perform cascade update or delete operations on the source during synchronization, data inconsistency may occur.

• If a DTS task fails, DTS technical support will restore it within 8 hours. During restoration, the task may be restarted and task parameters (not database parameters) may be modified.

Special cases

• Self-managed MySQL sources: A primary/secondary switchover during an active synchronization task causes the task to fail. If no DML operations occur on the source for a long time, synchronization latency readings may be inaccurate—run a DML operation to update the latency. If you synchronize an entire database, create a heartbeat table that receives writes every second to maintain accurate latency tracking.

• ApsaraDB RDS for MySQL sources: Read-only RDS MySQL 5.6 instances that do not record transaction logs cannot be used as the source.

• For both source types, DTS periodically executes CREATE DATABASE IF NOT EXISTS 'test' on the source database to advance the binary log file position.

Step 1: Open the DTS console

Go to the Data Synchronization page of the DTS console.

Step 2: Select the region

In the upper-left corner, select the region where the data synchronization instance will reside.

Step 3: Configure source and destination databases

Click Create Task. In the Create Data Synchronization Task wizard, configure the source and destination databases using the parameters below.

Source database

Destination database

Step 4: Test connectivity

Click Test Connectivity and Proceed.

DTS automatically adds its server CIDR blocks to the whitelist of Alibaba Cloud database instances and to the security group rules of ECS-hosted databases. For self-managed databases in a data center or provided by a third-party cloud service provider, manually add the DTS server CIDR blocks to your database whitelist. See CIDR blocks of DTS servers.

Step 5: Configure objects and advanced settings

Basic settings

Advanced settings

Step 6: Run the precheck

Click Next: Save Task Settings and Precheck.

DTS performs a precheck before the task can start. If the precheck fails:

• Click View Details next to each failed item, resolve the issue, and run the precheck again.

• If an alert item can be ignored, click Confirm Alert Details, then Ignore, click OK, and click Precheck Again. Ignoring alerts may lead to data inconsistency.

Step 7: Purchase a synchronization instance

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

On the buy page, configure the following:

Step 8: Start the task

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

The task appears in the task list. DTS synchronizes historical data from the source to the destination, then transitions to incremental synchronization automatically.