Data Transmission Service (DTS) supports two-way data synchronization between Apsara PolarDB for MySQL clusters. This feature is applicable to scenarios such as active geo-redundancy and geo-disaster recovery. This topic describes how to configure two-way data synchronization between Apsara PolarDB for MySQL clusters.
Prerequisites
- The source and destination Apsara PolarDB for MySQL clusters are created. For more information, see Create a PolarDB MySQL cluster.
- The binary logging feature is enabled for the source and destination Apsara PolarDB for MySQL clusters. For more information, see Enable binlogging.
Precautions
- DTS uses read and write resources of the source and destination databases during initial full data synchronization. This may increase the database load. If the database performance is unfavorable, the specification is low, or the data volume is large, database services may become unavailable. For example, DTS occupies a large amount of read and write resources in the following cases: a large number of slow SQL queries are performed on the source database, the tables have no primary keys, or a deadlock occurs in the destination database. Before synchronizing data, you must evaluate the performance of the source and destination databases. We recommend that you synchronize data during off-peak hours. For example, you can synchronize data when the CPU usage of the source and destination databases is less than 30%.
- During initial full data synchronization, concurrent INSERT operations cause fragmentation in the tables of the destination cluster. After initial full data synchronization, the tablespace of the destination cluster is larger than that of the source cluster.
- The source cluster must have PRIMARY KEY or UNIQUE constraints and all fields must be unique. Otherwise, the destination cluster may contain duplicate data records.
Limits
- DTS supports two-way data synchronization only between two PolarDB clusters. DTS does not support two-way data synchronization between multiple PolarDB clusters.
- If you have selected one or more tables (not a database) for synchronization, do not
use gh-ost or pt-online-schema-change to modify the tables during data synchronization.
Otherwise, data synchronization may fail.
Notice To avoid synchronization failure, you can use Data Management (DMS) to perform online DDL schema changes during data synchronization. For more information, see Change the table schema without locking.
- Incompatibility with triggers
If the object to be synchronized is a database and the database contains a trigger that updates a synchronized table, data inconsistency may occur. For more information about how to solve this issue, see Configure a data synchronization task for a source database that contains a trigger.
- Limits on RENAME TABLE operations
RENAME TABLE operations may cause data inconsistency between the source and destination databases. For example, if a table to be synchronized is renamed during data synchronization, the data of this table is not synchronized to the destination database. To avoid this situation, you can select the database to which this table belongs as the object when you configure the data synchronization task.
- Limits on DDL synchronization direction
To ensure the stability of a two-way synchronization channel, you can synchronize DDL updates of a single table only in one direction. If DDL synchronization in a direction is configured, DDL synchronization in the opposite direction is not supported. Only DML operations can be synchronized in the opposite direction.
SQL operations that can be synchronized
Operation type | SQL statements |
---|---|
DML | INSERT, UPDATE, DELETE, and REPLACE |
DDL |
|
Conflict detection
To ensure data consistency, make sure that data records with the same primary key, business primary key, or unique key are updated only on one PolarDB cluster. If data records are updated on both two PolarDB clusters, a synchronization conflict occurs.
DTS checks and fixes conflicts to maximize the stability of two-way synchronization instances. DTS can detect the following types of conflicts:
- Uniqueness conflicts caused by INSERT operations
INSERT operations that do not comply with the uniqueness constraint cannot be synchronized. For example, if data records with the same primary key are inserted into two synchronization nodes at almost the same time, one of the inserted records fails to be synchronized. The synchronization fails because a record with the same primary key already exists in the other node.
- Inconsistent records caused by UPDATE operations
- If the records to be updated do not exist in the destination cluster, DTS converts the UPDATE operation into an INSERT operation. However, uniqueness conflicts may occur.
- The primary keys or unique keys of the records to be inserted may conflict with those of existing records in the destination instance.
- Non-existent records to be deleted
The records to be deleted do not exist in the destination cluster. In this case, DTS ignores the DELETE operation regardless of the conflict resolution policy that you have specified.
- During two-way synchronization, the system time of the source and destination instances may be different. Synchronization latency may occur. For these reasons, DTS cannot guarantee that the conflict detection mechanism can prevent all data conflicts. To perform two-way synchronization, make sure that records with the same primary key, business primary key, or unique key are updated only on one of the synchronization nodes.
- DTS provides conflict resolution policies to prevent conflicts that may occur during data synchronization. You can select a conflict resolution policy when you configure two-way data synchronization.
Procedure
Result
