Data Transmission Service (DTS) supports two-way data synchronization between two PolarDB for MySQL clusters. Each synchronization instance runs a forward task and a reverse task, keeping both clusters in sync while preventing circular data loops.
Prerequisites
Before you begin, make sure that:
Both PolarDB for MySQL clusters are created. For more information, see Purchase an Enterprise Edition cluster and Purchase a subscription cluster.
Both clusters must have the same storage capacity.
Binary logging is enabled on both clusters. For more information, see Enable binary logging.
Limitations
During schema synchronization, DTS synchronizes foreign keys from the source database to the destination database.
During full data synchronization and incremental data synchronization, DTS temporarily disables the constraint check and cascade operations on foreign keys at the session level. If you perform the cascade update and delete operations on the source database during data synchronization, data inconsistency may occur.
Source database requirements
Tables to be synchronized must have PRIMARY KEY or UNIQUE constraints, with all fields unique. Without these constraints, the destination database may contain duplicate records.
For tables without primary keys or UNIQUE constraints, enable the Exactly-Once write feature during configuration. For more information, see Synchronize tables without primary keys or UNIQUE constraints.
If you select tables as synchronization objects and need to rename tables or columns in the destination, a single task supports up to 1,000 tables. For more than 1,000 tables, configure multiple tasks or synchronize the entire database instead.
For incremental data synchronization, binary logging must be enabled and the
loose_polar_log_binparameter must be set toon. Without these settings, the precheck fails and the task cannot start. For more information, see Enable binary logging and Modify parameters.- Enabling binary logging incurs storage charges for the space occupied by binary logs. - For incremental-only sync tasks, retain binary logs for at least 24 hours. For full and incremental sync tasks, retain binary logs for at least 7 days. Shorter retention periods may cause DTS to fail fetching binary logs, leading to task failure or data inconsistency. After full data synchronization is complete, you can set the retention period to more than 24 hours.
During schema synchronization and full data synchronization, do not execute DDL statements that change database or table schemas. Doing so causes the task to fail.
DDL synchronization direction
DDL operations can only be synchronized in the forward direction. DTS ignores DDL operations in the reverse direction.
This is a key constraint in two-way synchronization. To avoid data inconsistency, execute DDL operations only on the source cluster in the forward direction, not on the destination cluster.
Two-way synchronization constraints
DTS supports two-way synchronization only between two PolarDB for MySQL clusters. Synchronization among more than two clusters is not supported.
To prevent circular synchronization, DTS creates a database named
dtsin the destination. Do not modify this database while the task is running.DTS executes
CREATE DATABASE IF NOT EXISTS \test\`` on the source database at scheduled intervals to advance the binary log file position.When an object is included in both the forward and reverse tasks:
Only one task can synchronize full data and incremental data. The other task synchronizes incremental data only.
Data synchronized by one task is not used as the source data for the other task.
Other limitations
DTS does not synchronize read-only nodes of the source PolarDB for MySQL cluster.
DTS does not synchronize Object Storage Service (OSS) external tables from the source cluster.
If the data to synchronize contains 4-byte characters such as rare characters or emojis, the destination database and tables must use the utf8mb4 character set. If you use schema synchronization, set the
character_set_serverparameter in the destination database to utf8mb4.Evaluate the performance impact on both clusters before starting synchronization. Run synchronization during off-peak hours when possible. During full data synchronization, DTS uses read and write resources on both clusters, which increases server load.
During full data synchronization, concurrent INSERT operations cause table fragmentation in the destination. After full sync completes, the destination tablespace is larger than the source.
Do not use pt-online-schema-change to perform DDL operations on source tables during synchronization. Doing so causes the task to fail.
If no other data sources write to the destination during synchronization, you can use Data Management (DMS) for online DDL operations on source tables. For more information, see Perform lock-free DDL operations.
If other data sources write to the destination while DMS executes online DDL statements, data loss may occur in the destination.
If a DDL statement fails in the destination, the synchronization task continues running. View failed DDL statements in the task logs. For more information, see View task logs.
To synchronize accounts from the source to the destination, review the prerequisites and requirements. For more information, see Migrate database accounts.
If a DTS task fails, DTS technical support will attempt to restore it within 8 hours. During restoration, the task may restart and task parameters may be modified. Database parameters are not modified.
Parameters that may be modified include but are not limited to those in the Modify the parameters of a DTS instance topic.
Billing
| Synchronization type | Fee |
|---|---|
| Schema synchronization and full data synchronization | Free |
| Incremental data synchronization | Charged. For more information, see Billing overview. |
Conflict detection
To maintain data consistency, update records with the same primary key, business primary key, or unique key on only one synchronization node. If both nodes update the same record, DTS resolves the conflict based on the conflict resolution policy you configure.
DTS detects the following types of conflicts:
INSERT conflicts (uniqueness violation): If a record with the same primary key is inserted into both nodes at nearly the same time, one insert fails because the primary key already exists on the other node.
UPDATE conflicts (inconsistent records):
If the record to update does not exist in the destination, DTS converts the UPDATE to an INSERT. This INSERT may then trigger a uniqueness conflict.
The primary key or unique key of the inserted record may conflict with an existing record in the destination.
DELETE conflicts (non-existent record): If the record to delete does not exist in the destination, DTS ignores the DELETE operation regardless of the configured conflict resolution policy.
System time differences and synchronization latency between instances mean conflict detection cannot prevent all conflicts. Always design your application so that records with the same primary key are written from only one node.
DTS provides conflict resolution policies to handle conflicts that do occur. Select a policy when you configure the two-way synchronization task.
Conflict resolution policies
Global conflict resolution policy (applies task-wide):
| Policy | Behavior |
|---|---|
| TaskFailed | The task reports an error and stops. Manually resolve the conflict to resume. |
| Ignore | The task skips the conflicting statement and continues. The existing record in the destination is retained. |
| Overwrite | The conflicting record in the destination is overwritten with the source data. |
If synchronization latency occurs when you pause or restart a task, the conflict resolution policy does not apply during the latency window. By default, destination data is overwritten by data synchronized during that window.
Independent conflict resolution policy (applies to specific tables or databases):
In addition to the three global policies, independent policies support two additional options:
| Policy | Behavior |
|---|---|
| UseMax | Compares the conflicting values and writes the larger value to the destination. If the record does not exist or the field type does not meet requirements, behaves like Overwrite. |
| UseMin | Compares the conflicting values and writes the smaller value to the destination. If the record does not exist or the field type does not meet requirements, behaves like Ignore. |
Independent conflict resolution policies apply only to incremental data synchronization.
SQL operations that can be synchronized
| Operation type | SQL statements |
|---|---|
| DML | INSERT, UPDATE, DELETE |
| DDL (forward direction only) | ALTER TABLE, ALTER VIEW; CREATE FUNCTION, CREATE INDEX, CREATE PROCEDURE, CREATE TABLE, CREATE VIEW; DROP INDEX, DROP TABLE; RENAME TABLE; TRUNCATE TABLE |
DDL operations are synchronized only in the forward direction. DTS ignores DDL operations in the reverse direction.
The RENAME TABLE operation may cause data inconsistency. For example, if you select a table as the synchronization object and then rename it, data from that table stops being synchronized to the destination. To avoid this, select the database (not the table) as the synchronization object, and make sure both the pre-rename and post-rename database names are in the selected objects.
Required database account permissions
| Database | Required permissions | References |
|---|---|---|
| Source PolarDB for MySQL cluster | Privileged account permissions | Create and manage a database account and Manage the password of a database account |
| Destination PolarDB for MySQL cluster | Privileged account permissions | Same as above |
Configure two-way data synchronization
Step 1: Open the Data Synchronization page
Use either the DTS console or the DMS console.
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 synchronization instance resides.
DMS console
The exact steps may vary based on the DMS console mode and layout. For more information, 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 your pointer over Data + AI and choose DTS (DTS) > Data Synchronization.
From the drop-down list next to Data Synchronization Tasks, select the region where the instance resides.
Step 2: Create a task
Click Create Task.
(Optional) 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 the Back to Previous Version button is shown instead. - Use the new configuration page when available, as some parameters differ between versions.
Step 3: Configure source and destination databases
Configure the parameters described in the following table.
| Section | Parameter | Description |
|---|---|---|
| N/A | Task Name | The task name. DTS auto-generates a name. Specify a descriptive name for easy identification. Unique names are not required. |
| Source Database | Select a DMS database instance. | Select an existing registered database, or configure the connection manually. If you select an existing database, DTS auto-populates the connection parameters. To register a new database: in the DMS console, click Add DMS Database Instance; in the DTS console, use the Database Connections page. For more information, see Register an Alibaba Cloud database instance, Register a database hosted on a third-party cloud service or a self-managed database, and Manage database connections. |
| Database Type | Select PolarDB for MySQL. | |
| Access Method | Select Alibaba Cloud Instance. | |
| Instance Region | The region where the source PolarDB for MySQL cluster resides. | |
| Replicate Data Across Alibaba Cloud Accounts | Select No to use a database under the current Alibaba Cloud account. | |
| PolarDB Cluster ID | The ID of the source PolarDB for MySQL cluster. | |
| Database Account | The database account. See Required database account permissions. | |
| Database Password | The password for the database account. | |
| Encryption | Configure SSL encryption based on your requirements. For more information, see Configure SSL encryption. | |
| Destination Database | Select a DMS database instance. | Same as the source database. Select an existing registered database or configure manually. |
| Database Type | Select PolarDB for MySQL. | |
| Access Method | Select Alibaba Cloud Instance. | |
| Instance Region | The region where the destination PolarDB for MySQL cluster resides. | |
| Replicate Data Across Alibaba Cloud Accounts | Select No. | |
| PolarDB Cluster ID | The ID of the destination PolarDB for MySQL cluster. | |
| Database Account | The database account. See Required database account permissions. | |
| Database Password | The password for the database account. | |
| Encryption | Configure SSL encryption based on your requirements. |
Step 4: Test connectivity
Click Test Connectivity and Proceed.
DTS automatically adds its server CIDR blocks to the whitelist of Alibaba Cloud database instances (such as ApsaraDB RDS for MySQL or ApsaraDB for MongoDB) and to the security group rules of Elastic Compute Service (ECS)-hosted 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 clouds, manually add the DTS server CIDR blocks to the database whitelist. For more information, see CIDR blocks of DTS servers.
Adding DTS server CIDR blocks to whitelists or security groups introduces security risks. Before using DTS, take precautions such as: using strong credentials, limiting exposed ports, authenticating API calls, auditing whitelist and security group rules regularly, and connecting to DTS through Express Connect, VPN Gateway, or Smart Access Gateway.
Step 5: Configure synchronization objects
In the Configure Objects step, configure the following parameters.
| Parameter | Description |
|---|---|
| Synchronization Types | Select Schema Synchronization, Full Data Synchronization, and Incremental Data Synchronization. DTS synchronizes historical data first, then continues with incremental sync. By default, Incremental Data Synchronization is pre-selected. |
| Method to Migrate Triggers in Source Database | Select how to handle triggers. Configure only if triggers need to be synchronized. Available only when Schema Synchronization is selected. For more information, see Synchronize or migrate triggers from the source database. |
| Synchronization Topology | Select Two-way Synchronization. |
| Enable Exactly-Once Write | Select Yesalert notification settings if any tables to synchronize lack primary keys or UNIQUE constraints. For more information, see Synchronize tables without primary keys or UNIQUE constraints. |
| Exclude DDL Operations | Select Yes to exclude DDL operations, or No to include them. This parameter appears only for the forward direction task. DDL operations are always ignored in the reverse direction. |
| Global Conflict Resolution Policy | Select based on your requirements. For conflict type details, see Conflict detection. Options: TaskFailed, Ignore, Overwrite. |
| Processing Mode of Conflicting Tables | Precheck and Report Errors: fails the precheck if source and destination have tables with identical names. Use object name mapping to resolve conflicts. Ignore Errors and Proceed: skips the identical-name check. During full sync, existing destination records with matching primary or unique keys are retained. During incremental sync, those records are overwritten. > Warning Selecting Ignore Errors and Proceed may cause data inconsistency. |
| Source Objects | Select objects from the Source Objects section and click the arrow icon to add them to Selected Objects. Select columns, tables, or databases. If you select tables or columns, DTS does not synchronize views, triggers, or stored procedures. |
| Selected Objects | To rename a single object, right-click it in Selected Objects. To rename multiple objects at once, click Batch Edit. For more information, see Map object names. Note: renaming an object may cause dependent objects to fail. To configure SQL operation filters, additional columns, or an independent conflict resolution policy, right-click an object in Selected Objects and configure the options in the dialog box. |
Step 6: Configure advanced settings
Click Next: Advanced Settings and configure the following parameters.
| Parameter | Description |
|---|---|
| Dedicated Cluster for Task Scheduling | By default, DTS uses a shared cluster. For higher stability, purchase a dedicated cluster. For more information, see What is a DTS dedicated cluster. |
| Select the engine type of the destination database | Select the storage engine: InnoDB (default) or X-Engine (an online transaction processing (OLTP) storage engine). |
| Copy the temporary table of the Online DDL tool that is generated in the source table to the destination database. | Applies when using DMS or gh-ost for online DDL operations on the source. Yes: synchronizes temporary tables from online DDL. Note: large temporary tables extend sync duration. No, Adapt to DMS Online DDL: skips temporary tables; synchronizes only the final DDL from DMS. Note: destination tables may be locked. No, Adapt to gh-ost: skips temporary tables; synchronizes only the final DDL from gh-ost. Note: destination tables may be locked. > Important Do not use pt-online-schema-change for online DDL. It causes the DTS task to fail. |
| Whether to Migrate Accounts | Select Yes to synchronize database accounts. Specify the accounts, allowed hosts, and permissions. For more information, see Migrate database accounts. |
| Retry Time for Failed Connections | The retry window if the database connection drops after the task starts. Range: 10–1440 minutes. Default: 720. Set to at least 30 minutes. If DTS reconnects within this window, the task resumes. Otherwise, the task fails. Note: the shortest retry time among tasks sharing the same database takes precedence. DTS charges apply during retries. |
| Retry Time for Other Issues | The retry window for DDL or DML failures. Range: 1–1440 minutes. Default: 10. Set to at least 10 minutes. Must be less than Retry Time for Failed Connections. |
| Enable Throttling for Full Data Migration | Limit QPS to the source database, rows per second (RPS), and data transfer speed (MB/s) during full data synchronization to reduce load. Available only when Full Data Synchronization is selected. |
| Enable Throttling for Incremental Data Synchronization | Limit RPS and data transfer speed during incremental sync to reduce load. |
| Whether to delete SQL operations on heartbeat tables of forward and reverse tasks | Yes: does not write heartbeat table SQL operations to the source. A latency may be displayed for the DTS instance. No: writes heartbeat table SQL operations to the source. Physical backup and cloning of the source may be affected. |
| Environment Tag | Tag the DTS instance by environment type. |
| Configure ETL | Select Yes to enable extract, transform, and load (ETL) and enter processing statements. For more information, see Configure ETL in a data migration or data synchronization task. |
| Monitoring and Alerting | Select Yes to receive alerts when the task fails or synchronization latency exceeds a threshold. Configure the alert threshold and notification settings. For more information, see Configure monitoring and alerting. |
Step 7: Configure data verification (optional)
Click Next Step: Data Verification. For more information, see Configure a data verification task.
Step 8: Run the precheck
Click Next: Save Task Settings and Precheck.
The task cannot start until the precheck passes.
If the precheck fails, click View Details next to each failed item, troubleshoot the issue, and run the precheck again.
If an alert appears during the precheck: for items that cannot be ignored, troubleshoot and rerun; for items that can be ignored, click Confirm Alert Details, then Ignore, then OK, then Precheck Again. Ignoring alerts may lead to data inconsistency.
To preview the API parameters for this configuration, move your pointer over Next: Save Task Settings and Precheck and click Preview OpenAPI parameters.
Step 9: Purchase the synchronization instance
Wait until Success Rate reaches 100%, then click Next: Purchase Instance.
On the purchase page, configure the following parameters.
| Section | Parameter | Description |
|---|---|---|
| New Instance Class | Billing Method | Subscription: pay upfront for a fixed term; 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 more information, see What is Resource Management? | |
| Instance Class | Select the instance class based on synchronization speed requirements. For more information, see Instance classes of data synchronization instances. | |
| Subscription Duration | Available for the subscription billing method only. Select 1–9 months, 1 year, 2 years, 3 years, or 5 years. |
Read and select Data Transmission Service (Pay-as-you-go) Service Terms, then click Buy and Start. In the dialog box, click OK.
The task appears in the task list. You can monitor its progress there.
Step 10: Configure the reverse synchronization task
Wait until the forward synchronization task Status changes to Running.
Find the reverse synchronization task in the task list and click Configure Task.
Repeat steps 3 through 8 to configure the reverse task.
Important- In the reverse task, the source and destination are swapped: the source is the forward task's destination, and the destination is the forward task's source. Verify that the database names, accounts, and passwords are correct. - The Instance Region parameter cannot be modified for the reverse task. Configure only the parameters shown in the console. - For Processing Mode of Conflicting Tables, configure the reverse task to ignore tables already synchronized in the forward direction. - Objects selected in the forward task cannot be selected again for the reverse task. - Do not use the object name mapping feature in the reverse task. Doing so may cause data inconsistency.
Wait until Success Rate reaches 100%, then click Back.
Wait until the Status of both the forward and reverse tasks changes to Running. Two-way data synchronization is now active.