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

Binary log requirements

Verify the following binary log settings before you configure DTS.

For ApsaraDB RDS for MySQL (binary logging is enabled by default):

• Set binlog_row_image to full. See Modify instance parameters

• Retain binary logs for at least 3 days (7 days recommended). See Delete binary log files

For self-managed MySQL:

• Enable binary logging

• Set binlog_format to row

• Set binlog_row_image to full

• Retain binary logs for at least 7 days

• For dual-primary clusters, set log_slave_updates to ON. See Create an account for a self-managed MySQL database and configure binary logging

Source database

• Tables must have PRIMARY KEY or UNIQUE constraints with all-unique fields. Tables without these constraints may produce duplicate records in the destination.

• When synchronizing individual tables (not entire databases) and you want to edit the tables, such as renaming tables or columns, a single task supports up to 1,000 tables. If a task exceeds 1,000 tables, a request error occurs. For larger sets, configure multiple tasks or synchronize at the database level.

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

• Prefix indexes are not supported and may cause the task to fail.

• Only tables can be selected as synchronization objects. Indexes, partitions, views, stored procedures, functions, triggers, and foreign keys are excluded.

• Do not execute DDL statements that modify primary keys or add comments during synchronization. For example, ALTER TABLE table_name COMMENT='...' has no effect.

• Do not execute DDL statements that change database or table schemas during schema synchronization or full data synchronization phases.

• If the source is MySQL 8.0.23 or later and tables include invisible columns, DTS cannot read those columns and data loss occurs. Run ALTER TABLE <table_name> ALTER COLUMN <column_name> SET VISIBLE; to make columns visible before starting the task. Tables without explicit primary keys auto-generate invisible primary keys — make those visible too. See Invisible Columns and Generated Invisible Primary Keys.

• If the source is an ApsaraDB RDS for MySQL instance with EncDB enabled, full data synchronization is not supported. Instances with Transparent Data Encryption (TDE) enabled support all three synchronization types.

• DATETIME values of 0000-00-00 00:00:00 are converted to null in the destination. To avoid this, change the value to 0001-01-01 00:00:00 in the source or leave the destination field blank before starting the task.

• Data generated by binary log change operations — such as data restored from a physical backup or data from cascade operations — is not recorded or synchronized to the destination while the synchronization instance is running. If this occurs, you can remove the affected databases or tables from the synchronized objects and then re-add them, provided your business is not impacted. See Modify the objects to be synchronized.

Destination database

• The destination table cannot be an append-optimized (AO) table.

• The unique key (including primary key) of the destination table must include all columns of the distribution key.

• If the source table has a primary key, the destination primary key column matches the source. If the source table has no primary key, the destination primary key column matches the distribution key.

Other limitations

• Data written to the destination from other sources during synchronization may cause inconsistency. Avoid concurrent writes to destination tables while DTS is running.

• Online DDL tools such as pt-online-schema-change are not supported when individual tables are selected as objects. Use Data Management (DMS) for lock-free DDL instead. See Perform lock-free DDL operations.

• If column mapping is used or source and destination schemas differ, columns present in the source but absent in the destination are not synchronized.

• During schema synchronization, DTS synchronizes foreign keys from the source database to the destination database.

• DTS temporarily disables foreign key constraint checks and cascade operations at the session level during full and incremental data synchronization. Cascade updates or deletes on the source during this period may cause data inconsistency.

• During initial full data synchronization, concurrent INSERT operations cause table fragmentation in the destination. The destination tablespace will be larger than the source after full sync completes. Schedule initial synchronization during off-peak hours to reduce load.

• If a DTS task fails, DTS technical support attempts to restore it within 8 hours. The task may be restarted and its parameters adjusted during recovery.

Self-managed MySQL source

• A primary/secondary switchover while the task is running causes the task to fail.

• DTS calculates synchronization latency based on the latest synchronized timestamp versus the current source timestamp. If no DML operations run on the source for an extended period, the reported latency may be inaccurate. Run a DML operation on the source to refresh the latency metric. If you select an entire database as the synchronization object, you can create a heartbeat table that is updated every second to keep the latency metric accurate.

• DTS periodically executes CREATE DATABASE IF NOT EXISTS 'test' on the source to advance the binary log position.

ApsaraDB RDS for MySQL source

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

• DTS periodically executes CREATE DATABASE IF NOT EXISTS 'test' on the source to advance the binary log position.

Step 1: Open the Data Synchronization Tasks page

1. Log on to the Data Management (DMS) console.

2. In the top navigation bar, click Data + AI.

3. In the left-side navigation pane, choose DTS (DTS) > Data Synchronization.

Console navigation may vary based on your DMS layout. See Simple mode and Customize the layout and style of the DMS console. Alternatively, go directly to the Data Synchronization Tasks page.

Step 2: Select a region

On the right side of Data Synchronization Tasks, select the region where the data synchronization instance resides.

In the new DTS console, select the region from the top navigation bar.

Step 3: Configure the source and destination databases

Click Create Task. In the wizard, configure the following parameters.

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. If the database is hosted on ECS, you must also ensure that the ECS instance can access the database. If the database is deployed across multiple ECS instances, you must manually add the DTS CIDR blocks to the security group rules of each ECS instance. For self-managed databases in data centers or third-party clouds, manually add the DTS CIDR blocks to the database whitelist. See CIDR blocks of DTS servers.

Step 5: Configure synchronization objects and settings

Step 6: Configure advanced settings

Click Next: Advanced Settings.

Data verification

See Configure data verification.

Advanced settings

Step 7: Set primary key and distribution key columns

Click Next: Configure Database and Table Fields. Set the primary key and distribution key columns for each table being synchronized to the destination AnalyticDB for PostgreSQL instance.

Step 8: Run the precheck

Click Next: Save Task Settings and Precheck.

To preview the equivalent API parameters before saving, hover over the button and click Preview OpenAPI parameters.

DTS runs a precheck before starting synchronization. If any item fails:

• Click View Details next to the failed item, resolve the issue, then click Precheck Again.

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

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

Step 9: Purchase and start the instance

On the buy page, configure the following parameters.

Read and select 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. Monitor its progress there.