This topic describes how to synchronize data from a user-created MySQL database connected over Express Connect, VPN Gateway, or Smart Access Gateway to an AnalyticDB for PostgreSQL instance by using Data Transmission Service (DTS). The data synchronization feature allows you to transfer and analyze data with ease.
Prerequisites
- A MySQL database of version 5.1, 5.5, 5.6, 5.7, or 8.0 is created.
- The tables to be synchronized from the source database contain primary keys.
- The binary logging feature is enabled for the source database. A database account
is created for the data synchronization task. For more information, see Create an account for a user-created MySQL database and configure binary logging.
Note The database account must have the SELECT permission on the required objects, the REPLICATION CLIENT permission, the REPLICATION SLAVE permission, and the SHOW VIEW permission.
- The on-premises network to which the user-created MySQL database belongs is connected
to Alibaba Cloud VPC over Express Connect, VPN Gateway, or Smart Access Gateway. DTS
is allowed to access the network to which Express Connect, VPN Gateway, or Smart Access
Gateway belongs. For more information, see Configure a route between DTS and Express Connect, VPN Gateway, or Smart Access Gateway.
Note For more information about how to connect an on-premises network to a VPC, see Connect to local IDCs.
- An AnalyticDB for PostgreSQL instance is created. For more information, see Create an instance.
Precautions
DTS uses read and write resources of the source and destination databases during initial full data synchronization. This may increase the load of the database server. 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%.
Limits
- You can select only tables as the objects to be synchronized.
- You cannot synchronize the following types of data: BIT, VARBIT, GEOMETRY, ARRAY, UUID, TSQUERY, TSVECTOR, and TXID_SNAPSHOT.
- Prefix indexes cannot be synchronized. If the source database contains prefix indexes, data may fail to be synchronized.
- We recommend that you do not use gh-ost or pt-online-schema-change to perform data definition language (DDL) operations on objects during data synchronization. Otherwise, data synchronization may fail.
SQL operations that can be synchronized
- Data manipulation language (DML) operations: INSERT, UPDATE, and DELETE
- DDL operations: ADD COLUMN and RENAME COLUMN
Note The CREATE TABLE operation is not supported. To synchronize data from a new table, you must add the table to the selected objects. For more information, see Add an object to a data synchronization task.
Supported synchronization topologies
- One-way one-to-one synchronization
- One-way one-to-many synchronization
- One-way many-to-one synchronization
Term mappings
MySQL | AnalyticDB for PostgreSQL |
---|---|
Database | Schema |
Table | Table |