AnalyticDB for MySQL is a real-time online analytical processing (RT-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 data-driven insights into your business. This topic describes how to synchronize data from an ApsaraDB RDS for MySQL instance 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 report systems.
Prerequisites
- The tables that you want to synchronize from the ApsaraDB RDS for MySQL instance contain primary keys.
- 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.
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%.
- We recommend that you do not use gh-ost or pt-online-schema-change to perform data definition language (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 that you want to synchronize. You must ensure 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 Pricing. |
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 permissions |
---|---|
ApsaraDB RDS for MySQL | The SELECT permission on the objects to be synchronized, the REPLICATION CLIENT permission, the REPLICATION SLAVE permission, and the SHOW VIEW permission |
AnalyticDB for MySQL | The read and write permissions on the objects to be synchronized |
Data type mappings
The data types of ApsaraDB RDS for MySQL and AnalyticDB for MySQL do not have one-to-one correspondence. During initial schema synchronization, DTS converts the data types of the source database into those of the destination database. For more information, see Data type mappings for schema synchronization.
Procedure
Troubleshoot the synchronization failure that occurs due to field type changes
If the data type of a field in the source table is changed during data synchronization, an error message is generated and the data synchronization task is stopped. You can submit a ticket or perform the following steps to troubleshoot the issue.
- Create a table in the destination cluster based on the schema of source table that fails to be synchronized. For example, if a table named customer (Table A) fails to be synchronized, you can create a table named customer_new (Table B) in the destination cluster. Make sure that Table B has the same schema as Table A.
- Run the INSERT INTO SELECT command to copy the data of Table A and insert the data into Table B. This ensures that the data of the two tables is consistent.
- Rename or delete Table A. Then, change the name of Table B to customer.
- Restart the data synchronization task in the DTS console.