This topic describes how to synchronize data from a user-created MySQL database hosted on ECS to an AnalyticDB for PostgreSQL instance by using Data Transmission Service (DTS).
Prerequisites
- A MySQL database of version 5.1, 5.5, 5.6, 5.7, or 8.0 is created. The database is hosted on an ECS instance.
- 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 tables to be synchronized from the source database contain primary keys.
- 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 |