Data Transmission Service:Synchronize data from PolarDB for MySQL to PolarDB-X 1.0

Source database requirements

• Tables must have a primary key or UNIQUE constraint, with all fields unique. Without this, the destination may contain duplicate records.

• If you rename tables or columns at the destination, a single task supports up to 1,000 tables. For more than 1,000 tables, split into multiple tasks or sync at the database level instead.

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

• DTS does not sync prefix indexes. If the source has prefix indexes, the task may fail.

• DTS does not sync read-only nodes or Object Storage Service (OSS) external tables from the source cluster.

Requirements for incremental synchronization

Binary logging must be enabled on the source cluster and the loose_polar_log_bin parameter must be set to on. Without this, the precheck fails and the task cannot start. See Enable binary logging and Modify parameters.

Binary log retention requirements:

If binary logs are not retained for the required period, DTS may fail to obtain them and the task may fail — which can cause data inconsistency or loss. After full synchronization completes, you can set the retention period to more than 24 hours.

Incremental synchronization supports the following DML operations: INSERT, UPDATE, and DELETE.

DDL operation constraints

You can use Data Management (DMS) to perform lock-free DDL operations on source tables during data synchronization, but only if no other sources are writing to the destination database at the same time. If other sources are also writing to the destination while you run DMS online DDL statements, data loss may occur. See Perform lock-free DDL operations.

Operational constraints

• Sync during off-peak hours. Full data synchronization consumes read and write resources on both source and destination databases, which increases server load.

• After full data synchronization completes, the destination tablespace is larger than the source due to fragmentation from concurrent INSERT operations.

• If a DTS task fails, DTS support will try to restore it within 8 hours. The task may be restarted during restoration, and task parameters (not database parameters) may be modified.

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

Step 1: Open the Data Synchronization page

Use either the DTS console or the DMS console.

Step 2: Create a task

1. Click Create Task.

2. If the page shows a New Configuration Page button in the upper-right corner, click it to switch to the new configuration page. Skip this step if Back to Previous Version is shown instead.

Step 3: Configure source and destination databases

Configure the following parameters:

To register a database:

In the DMS console, click Add DMS Database Instance. See Register an Alibaba Cloud database instance and Register a database hosted on a third-party cloud service or a self-managed database.

In the DTS console, register on the Database Connections page. See Manage database connections.

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. For self-managed databases in data centers or on third-party clouds, manually add the CIDR blocks. See CIDR blocks of DTS servers.

Step 5: Configure objects to synchronize

In the Configure Objects step, set the following parameters.

Synchronization types

Schema synchronization is not supported and cannot be selected.

Processing mode for conflicting tables

Capitalization of object names in destination instance

Controls the casing of database names, table names, and column names in the destination. Default is DTS default policy. See Specify the capitalization of object names in the destination instance.

Selecting objects

In the Source Objects section, select the tables to synchronize and click to move them to Selected Objects. Only tables can be selected as synchronization objects.

To rename objects in the destination:

• Single object: right-click the object in Selected Objects. See Map the name of a single object.

• Multiple objects: click Batch Edit in the upper-right corner of Selected Objects. See Map multiple object names at a time.

To filter rows in a table, right-click the table in Selected Objects and configure filter conditions. See Set filter conditions.

Renaming an object with the object name mapping feature may cause other objects that depend on it to fail synchronization.

Step 6: Configure advanced settings

Click Next: Advanced Settings and configure the following:

While DTS retries a connection, you are still charged for the DTS instance. Release the instance promptly after both source and destination instances are released.

Step 7: Run the precheck

Click Next: Save Task Settings and Precheck.

To preview the OpenAPI parameters for this task, hover over the button and click Preview OpenAPI parameters before proceeding.

DTS runs a precheck before starting the task. The task only starts after passing.

• If any check item fails, click View Details next to the failed item, fix the issue, and click Precheck Again.

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

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

Step 8: Purchase the synchronization instance

On the buy page, configure the instance:

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

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