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 you with 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 database load. 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 synchronizing data, you must evaluate 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 usage 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.
SQL operations that can be synchronized
- DDL operations: CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE, ADD COLUMN, DROP COLUMN, and MODIFY COLUMN
- Data manipulation language (DML) operations: INSERT, UPDATE, and DELETE
Permissions required for database accounts
Database | Required permissions |
---|---|
ApsaraDB RDS for MySQL | The database account must have 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 initial schema synchronization.
Procedure
Troubleshoot the synchronization failure that occurs due to field type changes
In this example, the data of a table named customer fails to be synchronized to the AnalyticDB for MySQL cluster.
- In the AnalyticDB for MySQL cluster, create a table named customer_new. The customer_new table has the same schema as the customer table.
- Run the INSERT INTO SELECT command to copy the data of the customer table and insert the data into the customer_new table. This ensures that the data of the two tables is consistent.
- Rename or delete the customer table. Then, change the name of the customer_new table to customer.
- Restart the data synchronization task in the DTS console.