AnalyticDB for MySQL is a real-time online analytical processing (OLAP) service that is developed by Alibaba Cloud for online data analysis with high concurrency. AnalyticDB for MySQL can analyze petabytes of data from multiple dimensions at millisecond-level timing to provide you with data-driven insights into your business. This topic describes how to synchronize data from a PolarDB for MySQL cluster to an AnalyticDB for MySQL cluster by using Data Transmission Service (DTS). After you synchronize data, you can use AnalyticDB for MySQL to build internal business intelligence (BI) systems, interactive query systems, and real-time reporting systems.
Prerequisites
- An AnalyticDB for MySQL cluster is created. For more information, see Create an AnalyticDB for MySQL cluster .
- The destination AnalyticDB for MySQL cluster has sufficient storage space.
- The binary logging feature is enabled for the 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 or specifications are unfavorable, 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%.
- We recommend that you do not use gh-ost or pt-online-schema-change to perform DDL operations on the required objects during data synchronization. Otherwise, data may fail to be synchronized.
- Due to the limits of AnalyticDB for MySQL, if the disk space usage of the nodes in an AnalyticDB for MySQL cluster reaches 80%, the cluster is locked. We recommend that you estimate the required disk space based on the objects to be synchronized. You must make sure that the destination cluster has sufficient storage space.
- Prefix indexes cannot be synchronized. If the source database contains prefix indexes, data may fail to be synchronized.
Billing
Synchronization type | Task configuration fee |
---|---|
Schema synchronization and full data synchronization | Free of charge. |
Incremental data synchronization | Charged. For more information, see Billing overview. |
SQL operations that can be synchronized
- DDL operations: CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE, ADD COLUMN, DROP COLUMN, and MODIFY COLUMN
- DML operations: INSERT, UPDATE, and DELETE
Permissions required for database accounts
Database | Required permission |
---|---|
PolarDB for MySQL cluster | Read permissions on the objects to be synchronized |
AnalyticDB for MySQL | Read and write permissions on the required objects |
For more information about how to create and authorize a database account, see Create a database account for a PolarDB for MySQL cluster and Create a database account for an AnalyticDB for MySQL cluster.
Data type mappings
For more information, see Data type mappings for schema synchronization.