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

Source database

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

• If you select tables as the objects to synchronize and need to rename tables or columns in the destination, a single task can synchronize up to 5,000 tables. To synchronize more than 5,000 tables, configure multiple tasks or synchronize at the database level.

• Binary log retention requirements:

  • Incremental data synchronization only: retain binary logs for at least 24 hours.

  • Full data synchronization + incremental data synchronization: retain binary logs for at least seven days during full synchronization. After full synchronization completes, you can reduce the retention period to more than 24 hours. Otherwise, DTS may fail to obtain the binary logs and the task may fail. In exceptional circumstances, data inconsistency or loss may occur. Make sure that you set the retention period of binary logs in accordance with the preceding requirements. Otherwise, the service reliability and performance stated in the Service Level Agreement (SLA) of DTS cannot be achieved.

• Table names containing uppercase letters support schema synchronization only.

• TABLEGROUP objects and databases or schemas with the Locality attribute are not supported.

• During schema synchronization and full data synchronization, do not execute DDL statements that alter database or table schemas. Doing so causes the task to fail.

Foreign key behavior

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

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

Other limitations

• Run synchronization during off-peak hours. Full data synchronization consumes read and write resources on both databases and increases load on the database servers.

• After full data synchronization, the destination tablespace is larger than the source because concurrent INSERT operations during full synchronization create fragmentation.

• Do not use pt-online-schema-change for DDL operations on synchronized objects. Doing so may cause synchronization to fail.

• Write data to the destination only through DTS during synchronization. Using other tools to write data to the destination, combined with DMS online DDL operations, may cause data loss.

• If a DDL statement fails to execute on the destination, DTS continues running the statement. Check failed DDL statements in task logs.

• MySQL column names are case-insensitive. If the source has columns whose names differ only in capitalization, their data is written to the same destination column, which produces unexpected results.

• If the data to synchronize contains four-byte characters (such as rare characters or emojis), the destination tables must use the UTF8mb4 character set. If you use schema synchronization, set the character_set_server parameter in the destination to UTF8mb4.

• After synchronization completes (status changes to Completed), run analyze table <Table name> to verify that data was written correctly. For example, a high-availability (HA) switchover in the source may leave data only in memory, causing data loss.

• If a DTS task fails, DTS technical support will attempt to restore it within 8 hours. During restoration, the task may be restarted and task parameters may be modified (database parameters are not modified). The parameters that may be modified include but are not limited to the parameters in the Modify instance parameters section of the Modify the parameters of a DTS instance topic.

• DTS updates the dts_health_check.ha_health_check table in the source database on a schedule to advance the binary log file position.

Step 1: Go to 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.

Step 2: Select a region

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

Step 3: Configure source and destination databases

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

Task Name

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 Elastic Compute Service (ECS) instances hosting self-managed databases. If the database is deployed on multiple ECS instances, you must manually add the CIDR blocks of DTS servers to the security group rules of each ECS instance. For self-managed databases in data centers or on third-party cloud providers, manually add the DTS server CIDR blocks to the database whitelist. For more information, see Add the CIDR blocks of DTS servers.

Step 5: Select objects and configure settings

Configure the following parameters.

Synchronization types

Conflicting table handling

Objects to synchronize

Select objects from the Source Objects section, then click the icon to move them to the Selected Objects section. You can select columns, tables, or databases. If you select tables or columns, DTS does not synchronize views, triggers, or stored procedures.

In the Selected Objects section:

• To rename a single object, right-click it. For more information, see Map the name of a single object.

• To rename multiple objects at once, click Batch Edit in the upper-right corner. For more information, see Map multiple object names at a time.

• To filter which SQL operations to synchronize for a specific object, right-click the object and select the operations. For supported operations, see Supported SQL operations.

• To filter rows by condition, right-click the object and specify a WHERE clause. For more information, see Specify filter conditions.

Step 6: Configure advanced settings

Click Next: Advanced Settings and configure the following parameters.

Step 7: Run the precheck

Click Next: Save Task Settings and Precheck.

DTS runs a precheck before the task starts. If the precheck fails:

• Click View Details next to each failed item, diagnose the issue, resolve it, and rerun the precheck.

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

Step 8: Purchase an instance

Wait for the Success Rate to reach 100%, then click Next: Purchase Instance.

On the buy page, configure the following parameters.

Step 9: Start the task

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

Track task progress in the task list.