Data Transmission Service:Synchronize data from an ApsaraDB RDS instance to an AnalyticDB for MySQL cluster

Limitations

• During full data initialization, DTS consumes read and write resources of the source and destination databases, which may increase the database load. When the databases have low performance, low specifications, or heavy workloads (for example, a large number of slow SQL statements, tables without primary keys, or deadlocks on the destination database), the database pressure may be further increased and services may become unavailable. Therefore, you must evaluate the performance of the source and destination databases before starting data synchronization, and it is recommended that you perform data synchronization during off-peak hours (for example, when the CPU load of both the source and destination databases is below 30%).

• Do not use tools such as gh-ost or pt-online-schema-change to perform online DDL operations on the objects to be synchronized in the source database during data synchronization. Otherwise, the synchronization task fails.

• Due to the AnalyticDB for MySQLusage limitation, if the disk usage of any node in the cluster exceeds 80%, the cluster is locked. Before you start, estimate the storage space required for the objects to be synchronized and make sure that the destination cluster has sufficient capacity.

• Prefix indexes cannot be synchronized. If the source database contains prefix indexes, data synchronization may fail.

• If the destination AnalyticDB for MySQL 3.0 cluster is backing up while the DTS task runs, the task fails.

Billing

Supported SQL operations

• DDL operations: CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE, ADD COLUMN, DROP COLUMN, and MODIFY COLUMN

• DML operations: INSERT, UPDATE, and DELETE

Required database account permissions

Data type mappings

The data types of MySQL and AnalyticDB for MySQL do not have a one-to-one mapping. During schema initialization, DTS maps data types based on the data type definitions. For more information, see Schema initialization: Data type mappings.

Procedure

1. Purchase a data synchronization task. For more information, see Purchase a DTS instance.

   Note

   When you purchase the task, set the source instance to MySQL, the destination instance to AnalyticDB MySQL, and the synchronization topology to One-way Synchronization.

2. Log on to the Data Transmission Service console.

3. In the left-side navigation pane, click Data Synchronization.

4. At the top of the Data Synchronization page, select the region of the data synchronization instance.

5. Find the purchased data synchronization instance, and click Configure Synchronization Channel.

6. Configure the source and destination instances for the synchronization channel.

   Section	Parameter	Description
   N/A	Synchronization Task Name	DTS automatically generates a task name. We recommend that you specify a descriptive name for easy identification. The name does not have to be unique.
   Source instance information	Instance Type	Select RDS Instance.
   	Instance Region	The region of the source instance that you selected when you purchased the data synchronization instance. This parameter cannot be changed.
   	Instance ID	Select the ID of the source RDS instance.
   	Database Account	Enter the database account of the source RDS instance. For more information about the required permissions, see Required database account permissions. Note If the database type of the source RDS instance is MySQL 5.5 or MySQL 5.6, you do not need to configure Database Account and Database Password.
   	Database Password	Enter the password of the database account.
   	Connection Method	Select Non-encrypted or SSL-encrypted as needed. If you select SSL-encrypted, you must enable SSL encryption for the RDS instance in advance. For more information, see Use a cloud certificate to enable SSL encryption. Important Currently, the Encryption can be set only in the Chinese mainland and China (Hong Kong) regions.
   Destination instance information	Instance Type	Fixed as ADS. This parameter cannot be changed.
   	Instance Region	The region of the destination instance that you selected when you purchased the data synchronization instance. This parameter cannot be changed.
   	Version	Select 3.0.
   	Database	Select the ID of the destination AnalyticDB for MySQL cluster.
   	Database Account	Enter the database account of the destination AnalyticDB for MySQL cluster. For more information about the required permissions, see Required database account permissions.
   	Database Password	Enter the password of the database account.

7. In the lower-right corner of the page, click Set Whitelist and Next.

   If the source or destination database is an Alibaba Cloud database instance, such as an ApsaraDB RDS for MySQL or ApsaraDB for MongoDB instance, DTS automatically adds the CIDR blocks of DTS servers to the IP address whitelist of the instance. If the source or destination database is a self-managed database hosted on an Elastic Compute Service (ECS) instance, DTS automatically adds the CIDR blocks of DTS servers to the security group rules of the ECS instance, and you must make sure that the ECS instance can access the database. If the self-managed database is hosted on multiple ECS instances, you must manually add the CIDR blocks of DTS servers to the security group rules of each ECS instance. If the source or destination database is a self-managed database that is deployed in a data center or provided by a third-party cloud service provider, you must manually add the CIDR blocks of DTS servers to the IP address whitelist of the database to allow DTS to access the database. For more information, see Whitelist DTS server IP addresses.

   Warning

   Adding the public IP address blocks of the DTS service, either automatically or manually, may pose security risks. Using this product, you acknowledge that you understand and accept the potential security risks and that you must implement basic security measures. These measures include, but are not limited to, strengthening password security, limiting the ports open to each CIDR block, using authentication for internal API calls, and regularly checking and restricting unnecessary CIDR blocks. Alternatively, you can connect through a private network using a leased line, VPN Gateway, or Smart Access Gateway.

8. Configure the synchronization policy and objects.

   Parameter	Description
   Initial Synchronization	By default, you must select both Initial Schema Synchronization and Initial Full Data Synchronization. After the precheck is complete, DTS initializes the schema and data of the objects to be synchronized from the source instance to the destination cluster as the baseline for subsequent incremental synchronization.
   Processing Mode for Existing Target Tables	Precheck and Report Errors: checks whether the destination database contains tables with the same names. If no tables with the same names exist, the check passes. If tables with the same names exist, an error is reported during the precheck and the synchronization task does not start. Note If tables with the same names in the destination database are inconvenient to delete or rename, you can change the names of those tables in the destination database. For more information, see Rename synchronized objects in the destination instance. Ignore Errors and Proceed: skips the check for tables with the same names in the destination database. Warning If you select Ignore Errors and Proceed, data inconsistency may occur, which poses risks to your business. For example: If the table schemas are the same and a record in the destination database has the same primary key value as a record in the source database, the record in the destination database is retained. In other words, the record in the source database is not synchronized to the destination database. If the table schemas are different, data may fail to be initialized, only data in some columns may be synchronized, or synchronization may fail.
   Merge Multi Tables	If you select Yes: DTS adds a __dts_data_source column to each table to store the data source, and DDL synchronization is no longer supported. If you select No: the default option. DDL synchronization is supported. Note The table merging feature works at the task level rather than the table level. If you want to merge only some tables, create two separate data synchronization tasks.
   Synchronization Type	Select the operation types to be synchronized based on your business requirements. For more information about the supported synchronization operations, see Supported SQL operations. All operation types are selected by default.
   Objects to Synchronize	In the Source Objects box, select the objects to be synchronized, and then click the icon to move them to the Selected Objects box. You can select databases or tables as the objects to be synchronized. Note If you select an entire database as the synchronization object, all schema changes in the database are synchronized to the destination database. If you select a table as the synchronization object, only the ADD COLUMN operations on the table are synchronized to the destination database. By default, the names of synchronized objects remain unchanged. If you want synchronized objects to have different names in the destination cluster, use the object name mapping feature. For more information, see Rename synchronized objects in the destination instance.
   Library, table, and column mapping	Change the names of synchronized objects in the destination instance. For more information, see Map databases, tables, and columns.
   Replicate temporary tables during DMS online DDL	If you use Data Management (DMS) to perform online DDL changes on the source database, you can choose whether to synchronize the temporary tables generated by the DDL changes. Yes: Synchronizes the temporary tables generated by online DDL changes. Note If a large amount of temporary table data is generated by online DDL changes, the data synchronization task may be delayed. No: Does not synchronize the temporary tables generated by online DDL changes. Only the original DDL operations from the source database are synchronized. Note This option causes tables in the destination database to be locked.
   Connection retry duration	If DTS cannot connect to the source or destination instance, it retries for 720 minutes (12 hours) by default. You can also specify a custom retry duration. If DTS reconnects to the source or destination instance within the specified duration, the synchronization task automatically resumes. Otherwise, the task fails. Note You are billed for task run time during connection retries. Customize the retry duration based on your business needs, or release the DTS instance as soon as the source and destination instances are released.

9. After you complete the preceding configurations, click Next in the lower-right corner of the page.

10. Set the types of the tables to be synchronized in the destination database.

    The configuration table also includes the ADB Table Group, ADB Table Name, Number of Partitions, and Definition Status columns. When you hover over a database or table name, DTS shows the source table name.

    Note

    If you select Initial Schema Synchronization, you must define the tables to be synchronized in AnalyticDB for MySQL, including the Type, primary key column, and partition column. For more information, see CREATE TABLE.

11. After you complete the preceding configurations, click Precheck and Start in the lower-right corner of the page.

    Note

    • Before the synchronization task is officially started, a precheck is performed. The synchronization task can only be started after the precheck passes.

    • If the precheck fails, click the icon next to a specific check item to view the failure details.

      • Follow the prompt to fix the issue and run the precheck again.

      • If you do not need to fix the warning check items, you can also select Ignore, Ignore Warning Items and Rerun Precheck to skip the warning check items and run the precheck again.

12. When Precheck Passed is displayed in the Precheck dialog box, close the Precheck dialog box. The synchronization task starts.

13. Wait until link initialization is complete and the task enters the Synchronizing state.

    You can go to the Data Synchronization page to view the status of the synchronization task.

    The synchronization task list on this page includes columns for Instance ID/Task Name, Status, Sync Overview (latency and speed), Billing Method, Synchronization Topology, and Actions. From the Actions column, you can perform operations such as Pause Synchronization, Switch to Subscription, and Upgrade. The page header supports searching by task name and filtering or sorting by status.

Fix synchronization failures caused by field type changes

If you change the data type of a field in the source table during data synchronization, the synchronization task reports an error and is interrupted. You can manually fix the issue by following these steps.

1. In the destination instance, create a new table B (for example, customer_new) based on the failed table A (for example, customer), and adjust the field types of table B to the required types.

2. Run the INSERT INTO SELECT statement to copy the data of table A to the new table B. Make sure that the data in both tables is consistent.

3. Rename or delete the failed table A, and then rename table B to customer.

4. In the DTS console, restart the data synchronization task.