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

Prerequisites

Before you begin, make sure you have:

A PolarDB-X 1.0 instance with ApsaraDB RDS for MySQL as the storage type (custom or purchased). PolarDB for MySQL is not supportedPolarDB-X 1.0
An AnalyticDB for MySQL V3.0 cluster with available storage space larger than the total size of data in the source instance
Binary logging enabled on the ApsaraDB RDS for MySQL instances attached to PolarDB-X 1.0, with binlog_row_image set to full. Otherwise, error messages are returned during the precheck and the task cannot start. Binary logs must be retained for at least 7 days (for a full and incremental sync) or at least 24 hours (for incremental-only). If this requirement is not met, DTS may fail to obtain the binary logs and the task may fail. After full data synchronization is complete, you can set the retention period to more than 24 hours. Make sure the retention period meets these requirements; otherwise, the service reliability and performance stated in the DTS Service Level Agreement (SLA) cannot be achieved
The character set of the data to be synchronized is not utf8mb3. If it is, incremental synchronization fails

For setup instructions, see Create a PolarDB-X 1.0 instance, Create a database, and Create a database account.

Limitations

Source database requirements

Limitation	Details
Table constraints	Tables must have a PRIMARY KEY or UNIQUE constraint, and all fields must be unique. Tables with only UNIQUE constraints do not support schema synchronization — use tables with PRIMARY KEY constraints instead. Tables with secondary indexes cannot be synchronized.
Table count limit	If you select tables (not entire databases) as sync objects and need to rename tables or columns in the destination, a single task supports up to 5,000 tables. Exceeding this limit causes a request error. Split the work into multiple tasks, or sync the entire database instead.
Splitting method	PolarDB-X 1.0 supports horizontal splitting only (databases and tables). Vertical splitting is not supported.
Read-only instances	Read-only instances at the PolarDB-X 1.0 compute layer are not supported as the source.

Operations to avoid during synchronization

The following operations on the source cause the task to fail or data to become inconsistent:

Scaling the source instance or the attached ApsaraDB RDS for MySQL instances
Changing the distribution of physical databases for logical databases or tables configured in ApsaraDB RDS for MySQL
Migrating hot tables, changing shard keys, or performing online DDL operations
Performing DDL operations using gh-ost or pt-online-schema-change
Changing the network type of the PolarDB-X 1.0 instance — if you must do this, update the network connection information in the DTS task afterwardPolarDB-X 1.0

Data write recommendation

Write data to the destination cluster only through DTS during synchronization. Using other tools may cause data loss when Data Management (DMS) performs online DDL operations.

Foreign key behavior

DTS does not synchronize foreign keys during schema synchronization. During full 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 this time, data inconsistency may occur.

Performance impact and timing

Before you synchronize data, evaluate the impact of data synchronization on the performance of the source and destination databases. We recommend that you synchronize data during off-peak hours. During initial full data synchronization, DTS uses the read and write resources of the source and destination databases, which may increase the load on the database servers.

Destination tablespace size

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

Note

When AnalyticDB for MySQL is the destination, DTS supports writing the basic data types and complex data types (ARRAY, MAP, and JSON) of AnalyticDB for MySQL. If the destination table contains columns of data types that are not supported by DTS, such as MULTIVALUE, the data synchronization or migration task may fail. For more information, see Basic data types.

SQL operations that can be synchronized

Operation type	SQL statements
DML	INSERT, UPDATE, DELETE

When data is written to the destination AnalyticDB for MySQL V3.0 cluster, UPDATE statements are automatically converted to REPLACE INTO. If the UPDATE statement targets the primary key, it is converted to DELETE followed by INSERT.

Supported synchronization topologies

One-way one-to-one synchronization
One-way one-to-many synchronization
One-way cascade synchronization
One-way many-to-one synchronization

For details, see Synchronization topologies.

Required database account permissions

Database	Required permission	Reference
Source PolarDB-X 1.0 instance	Read permissions on the objects to be synchronized	Manage accounts
Destination AnalyticDB for MySQL V3.0 cluster	Read and write permissions on the target database	Create a database account

Create a data synchronization task

DTS runs a subtask for each ApsaraDB RDS for MySQL instance attached to the source PolarDB-X 1.0 instance. Monitor the state of each subtask in PolarDB-X 1.0Task Topology after the task starts.

Step 1: Open the Data Synchronization page

Use one of the following methods to access the Data Synchronization page.

DTS console

Log on to the DTS console .
In the left-side navigation pane, click Data Synchronization.
In the upper-left corner, select the region where the data synchronization instance resides.

DMS console

Note

The steps may vary depending on the mode and layout of the DMS console. For details, see Simple mode and Customize the layout and style of the DMS console.

Log on to the DMS console .
In the top navigation bar, move the pointer over Data + AI and choose DTS (DTS) > Data Synchronization.
From the drop-down list to the right of Data Synchronization Tasks, select the region where the data synchronization instance resides.

Step 2: Configure source and destination databases

Click Create Task.

Configure the following parameters.

Source database

Parameter	Description
Task Name	A name for the DTS task. DTS generates a name automatically. Specify a descriptive name to make the task easy to identify. The name does not need to be unique.
Database Type	Select PolarDB-X 1.0.
Connection Type	Select Alibaba Cloud Instance.
Instance Region	The region where the source PolarDB-X 1.0 instance resides.
Replicate Data Across Alibaba Cloud Accounts	Select No if the source and destination are under the same Alibaba Cloud account.
Instance ID	The ID of the source PolarDB-X 1.0 instance.
Database Account	The database account for the source instance. For required permissions, see Required database account permissions.
Database Password	The password for the database account.

Destination database

Parameter	Description
Database Type	Select AnalyticDB for MySQL 3.0.
Connection Type	Select Alibaba Cloud Instance.
Instance Region	The region where the destination AnalyticDB for MySQL V3.0 cluster resides.
Instance ID	The ID of the destination AnalyticDB for MySQL V3.0 cluster.
Database Account	The database account for the destination cluster. For required permissions, see Required database account permissions.
Database Password	The password for the database account.

Click Test Connectivity and Proceed.

Make sure DTS server CIDR blocks are added to the security settings of both the source and destination databases. For details, see Add the CIDR blocks of DTS servers.

Step 3: Configure objects to be synchronized

In the Configure Objects step, configure the following parameters.

Parameter	Description
Synchronization Types	By default, Incremental Data Synchronization is selected. Also select Schema Synchronization and Full Data Synchronization. After the precheck, DTS synchronizes historical data from the source to the destination as the baseline for incremental sync. If Full Data Synchronization is selected, DTS also synchronizes the schema and data of tables created using CREATE TABLE.
DDL and DML Operations to Be Synchronized	The SQL operations to synchronize. For supported operations, see SQL operations that can be synchronized. To select operations for a specific table, right-click the object in Selected Objects and choose the operations to synchronize.
Merge Table	Yesalert notification settings: Merges multiple source tables with the same schema into a single destination table. Useful when OLTP sharding is implemented for performance and you want to consolidate data for online analytical processing (OLAP) queries. For details, see Enable the multi-table merging feature. When enabled: rename the selected source tables to the destination table name using the object name mapping feature (see Database, table, and column name mapping), and DTS adds a `__dts_data_source` column (TEXT type) to the destination table to track the source of each record, in the format `<sync instance ID>:<source database>:<source schema>:<source table>` — for example, `dts******:dtstestdata:testschema:customer1`. All selected source tables are merged; create a separate task for tables that should not be merged. Warning** Do not perform DDL schema changes on source databases or tables when merge is enabled. No (default): Do not merge tables.
Processing Mode of Conflicting Tables	Precheck and Report Errors (default): The precheck fails if the destination contains tables with the same names as source tables. Rename conflicting tables using the object name mapping feature before proceeding. Ignore Errors and Proceed: Skips the name conflict check. Warning This may cause data inconsistency. During full sync, existing records in the destination with matching primary or unique keys are kept (source records are skipped). During incremental sync, matching records in the destination are overwritten. If schemas differ, the task may fail or only partial columns are synchronized.
Capitalization of Object Names in Destination Instance	The capitalization policy for database, table, and column names in the destination. Default: DTS default policy. For details, see Specify the capitalization of object names in the destination instance.
Source Objects	Select objects and click the icon to add them to Selected Objects. You can select tables as sync objects. If you select an entire database, DTS does not sync CREATE TABLE or DROP TABLE changes.
Selected Objects	To rename a single object in the destination, right-click it and follow the steps in Individual database, table, and column name mapping. To rename multiple objects at once, click Batch Edit in the upper-right corner (see Batch database, table, and column name mapping). To filter rows using WHERE conditions, right-click an object and specify conditions (see Specify filter conditions).

Click Next: Advanced Settings and configure the following parameters.

Parameter	Description
Dedicated Cluster for Task Scheduling	By default, DTS uses the shared cluster. To improve task stability, purchase a dedicated cluster. For details, see What is a DTS dedicated cluster.
Retry Time for Failed Connections	How long DTS retries if the source or destination database becomes unreachable. Valid values: 10–1,440 minutes. Default: 720 minutes. Set this to more than 30 minutes. If DTS reconnects within this window, the task resumes. If the shortest retry time across tasks sharing the same source or destination is shorter, that value takes precedence. DTS charges continue during the retry period.
Retry Time for Other Issues	How long DTS retries when DDL or DML operations fail. Valid values: 1–1,440 minutes. Default: 10 minutes. Set this to more than 10 minutes. This value must be smaller than Retry Time for Failed Connections.
Enable Throttling for Full Data Synchronization	Limits read/write load on the source and destination during full data sync. Configure Queries per second (QPS) to the source database, RPS of Full Data Migration, and Data migration speed for full migration (MB/s). Available only when Full Data Synchronization is selected.
Enable Throttling for Incremental Data Synchronization	Limits load on the destination during incremental sync. Configure RPS of Incremental Data Synchronization and Data synchronization speed for incremental synchronization (MB/s).
Environment Tag	An optional tag to categorize the DTS instance by environment (for example, production or staging).
Configure ETL	Whether to enable extract, transform, and load (ETL) to process data during synchronization. Yes: Enter data processing statements in the code editor (see Configure ETL in a data migration or data synchronization task). No (default): Skip ETL configuration. For an overview of ETL, see What is ETL?
Monitoring and Alerting	Whether to send alerts when the task fails or synchronization latency exceeds a threshold. No (default). Yes: Configure the alert threshold and notification settings (see Configure monitoring and alerting when you create a DTS task).

(Optional) Click Next: Configure Database and Table Fields to configure the Type, Primary Key Column, Distribution Key, Partition Key, Partitioning Rules, and Partition Lifecycle for the tables in the destination database.

This step is available only when Schema Synchronization is selected in Synchronization Types. To modify these settings, set Definition Status to All. For composite primary keys, specify one or more columns as the Distribution Key and Partition Key. For details, see .

Step 4: Run the precheck

Click Next: Save Task Settings and Precheck.

To preview the API parameters for this task configuration, move the pointer over Next: Save Task Settings and Precheck and click Preview OpenAPI parameters before proceeding.
Wait for the precheck to complete. DTS must pass the precheck before the task can start.
- If an item fails, click View Details to see the cause, resolve the issue, and click Precheck Again.
- If an item triggers an alert:
  - If the alert cannot be dismissed, resolve the underlying issue and rerun the precheck.
  - If the alert can be safely dismissed, click Confirm Alert Details, then click Ignore in the View Details dialog, confirm with OK, and click Precheck Again.
Dismissing an alert may cause data inconsistency.

Step 5: Purchase and start the instance

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

On the purchase page, configure the following parameters.

Parameter	Description
Billing Method	Subscription: Pay upfront for a fixed duration. More cost-effective for long-term use. Pay-as-you-go: Billed hourly. Suitable for short-term use. Release the instance when no longer needed to stop charges.
Resource Group Settings	The resource group for the instance. Default: default resource group. For details, see What is Resource Management?
Instance Class	The instance class determines synchronization speed. Select based on your data volume and latency requirements. For details, see Instance classes of data synchronization instances.
Subscription Duration	Available only for Subscription billing. Options: 1–9 months, 1 year, 2 years, 3 years, or 5 years.

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

The task appears in the task list. DTS runs a subtask for each ApsaraDB RDS for MySQL instance attached to the source PolarDB-X 1.0 instance. Monitor the state of each subtask in Task Topology.PolarDB-X 1.0