PolarDB is a next-generation relational database service that is developed by Alibaba Cloud. PolarDB is compatible with the MySQL database engine and features high availability, ease of use, and reliability. This topic describes how to configure one-way data synchronization between PolarDB for MySQL clusters by using Data Transmission Service (DTS).
Prerequisites
- The source and destination PolarDB for MySQL clusters are created. For more information, see Purchase a pay-as-you-go cluster. For more information about the supported database engine versions, see Overview of data synchronization scenarios.
- The binary logging feature is enabled for the source PolarDB for MySQL cluster. For more information, see Enable binary logging.
Precautions
- DTS uses read and write resources of the source and destination databases during initial full data synchronization. This may increase the loads of the database servers. 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 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. For example, you can synchronize data when the CPU utilization 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 is complete, the size of used tablespace of the destination cluster is larger than that of the source database.
- The source database must have PRIMARY KEY or UNIQUE constraints and all fields must be unique. Otherwise, the destination cluster may contain duplicate data records.
- To ensure compatibility, the database engine version of the destination PolarDB for MySQL cluster must be the same as or later than that of the source PolarDB for MySQL cluster.
SQL operations that can be synchronized
Operation type | SQL statement |
---|---|
DML | INSERT, UPDATE, DELETE, and REPLACE |
DDL |
|
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 more information, see Synchronization topologies.
Limits
- Incompatibility with triggers
If you select a database as the object to be synchronized and the database contains a trigger that updates a 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 you select a table as the object and rename the table during data synchronization, the data of this table is not synchronized to the destination database. To prevent this situation, you can select the database to which this table belongs as the object when you configure the data synchronization task.