This topic describes how to synchronize data from an ApsaraDB RDS for MySQL instance to a user-created MySQL database connected over Express Connect, VPN Gateway, or Smart Access Gateway by using Data Transmission Service (DTS).
Prerequisites
- The version of the user-created MySQL database is 5.1, 5.5, 5.6, 5.7, or 8.0.
Note We recommend that you make sure the version of the source and destination MySQL databases is the same.
- The user-created MySQL database is connected to Alibaba Cloud VPC over Express Connect,
VPN Gateway, or Smart Access Gateway. For more information, see Connect an on-premises database to Alibaba Cloud.
Note DTS is allowed to access the VPC to which the user-created MySQL database belongs. For more information, see Configure a route between DTS and Express Connect, VPN Gateway, or Smart Access Gateway.
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 instance. After initial full data synchronization, the tablespace of the destination instance is larger than that of the source instance.
Limits
- 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.
- The tables to be migrated in the source database must have PRIMARY KEY or UNIQUE constraints and all fields must be unique. Otherwise, the destination database may contain duplicate data records.
Supported synchronization typologies
- One-way one-to-one synchronization
- One-way one-to-many synchronization
- One-way cascade synchronization
- One-way many-to-one synchronization
- Two-way one-to-one synchronization
For more information about synchronization topologies, see Synchronization topologies.
SQL operations that can be synchronized
Operation type | SQL statements |
---|---|
DML | INSERT, UPDATE, DELETE, and REPLACE |
DDL |
|
Limits
- Incompatibility with triggers
If you select a database as the object 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 during synchronization. For example, if only Table A needs to be synchronized and it is renamed Table B, Table B cannot be synchronized to the destination database. To prevent this situation, you can select the entire database where Table A is located as the object when you configure the data synchronization task.