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 within milliseconds to provide data-driven insights into your business. This topic describes how to migrate 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 of ApsaraDB RDS for MySQL to be synchronized from the source database 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 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 |
---|---|
ApsaraDB RDS for MySQL instance | The SELECT permission on the objects to be synchronized and the REPLICATION CLIENT, REPLICATION SLAVE, and SHOW VIEW permissions |
AnalyticDB for MySQL | Read and write permissions on the required objects |
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 reported and the data synchronization task is interrupted. You can troubleshoot the issue by using the following method.
- 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.