Data Transmission Service:Synchronize RDS for MySQL to AnalyticDB for MySQL

Limitations

• During initial full data synchronization, DTS consumes read and write resources from the source and destination databases, which increases the database load. If database performance is poor, instance specifications are low, or business traffic is heavy (for example, the source database has many slow SQL queries or tables without primary keys, or the destination database experiences deadlocks), the database load increases and may even cause the service to become unavailable. Before you synchronize data, evaluate the performance of your source and destination instances. We recommend performing data synchronization during off-peak hours, for example, when the CPU utilization of both instances is below 30%.

• Do not use gh-ost or pt-online-schema-change for DDL operations on source objects during synchronization. Otherwise, the task fails.

• In AnalyticDB for MySQL, a cluster is locked when any node's disk usage exceeds 80%. Ensure the destination cluster has sufficient capacity before starting.

• Tables with prefix indexes cannot be synchronized and may cause task failure.

• 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, MODIFY COLUMN

• DML operations: INSERT, UPDATE, DELETE

Required database account permissions

Data type mappings

MySQL and AnalyticDB for MySQL data types do not map one-to-one. DTS converts source data types to destination types during initial schema synchronization. Data type mappings for schema synchronization.

Procedure

1. Purchase a data synchronization task. Purchase procedure.

   Note

   On the purchase page, 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 (DTS) console.

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

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

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

6. Configure the source and destination instances.

   Category	Parameter	Description
   N/A	Synchronization task name	DTS auto-generates a task name. Specify a descriptive name for easy identification. The name does not need to be unique.
   Source instance details	Instance type	Select RDS Instance.
   	Instance region	The region selected during purchase. Cannot be changed.
   	Instance ID	Select the source RDS instance.
   	Database account	Enter the database account for the source RDS instance. Required permissions: Required database account permissions. Note If the source RDS instance runs MySQL 5.5 or MySQL 5.6, you do not need to configure Database Account or Database Password.
   	Database password	Enter the password for the database account.
   	Encryption	Select Non-encrypted or SSL-encrypted based on your requirements. If you select SSL-encrypted, you must first enable SSL encryption on the RDS instance. Enable SSL encryption by using a cloud certificate. Important Currently, the Encryption parameter is available only for regions in the Chinese mainland and the China (Hong Kong) region.
   Destination instance details	Instance type	Fixed as AnalyticDB. Cannot be changed.
   	Instance region	The region selected during purchase. Cannot be changed.
   	Version	Select 3.0.
   	Database	Select the destination AnalyticDB for MySQL cluster.
   	Database account	Enter the database account for the AnalyticDB for MySQL cluster. Required permissions: Required database account permissions.
   	Database password	Enter the password for 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	Both Initial Schema Synchronization and Initial Full Data Synchronization are selected by default. After precheck, DTS initializes schema and data in the destination cluster as a baseline for incremental synchronization.
   Processing Mode In Existed Target Table	Precheck and Report Errors: Checks whether a same-name table exists in the destination. If one exists, precheck fails and the task does not start. Note If you cannot delete or rename the conflicting table, map it to a different name. Map object names to the destination. Ignore Errors and Proceed: Skips same-name table checks. Warning Selecting Ignore Errors and Proceed can lead to data inconsistency. For example: If schemas match and the destination has a record with the same primary key, the destination record is kept and the source record is skipped. If schemas differ, initialization may fail, partially synchronize data, or fail entirely.
   Merge Multi Tables	If you select Yes, DTS adds a __dts_data_source column to each table to record the data source. DDL synchronization is no longer supported. No: The default option. DDL synchronization is supported. Note Table merging is configured at the task level, not the table level. To merge some tables but not others, create separate synchronization tasks.
   Synchronization Type	Select the operation types to synchronize. All types are selected by default. Supported SQL operations.
   Objects to synchronize	In the Source Objects list, select the objects that you want to synchronize and click the icon to move them to the Selected Objects list. Select objects at the database or table level. Note Selecting an entire database synchronizes all schema changes for objects in that database. Selecting a specific table synchronizes only ADD COLUMN operations for that table. Object names remain the same in the destination by default. To rename objects, use Map object names to the destination.
   Mapped name change	Change the names of synchronized objects in the destination instance. For more information, see Map databases, tables, and columns.
   Specify whether to copy temporary tables (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.
   Retry time for failed connections	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 completing the preceding configurations, click Next in the lower-right corner of the page.

10. Specify the table properties in the destination database.

    The configuration table also includes the ADB Table Group, ADB Table Name, Number of Partitions, and Definition Status columns. You can hover over a table or database name to view its original name in the source database.

    Note

    If you selected Initial Schema Synchronization, define the Type, primary key column, partition key column, and other properties for tables in AnalyticDB for MySQL. CREATE TABLE.

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

    Note

    • A precheck runs before the synchronization task starts, and you can only start the task after it passes.

    • If the precheck fails, click the icon next to the failed item to view the details.

      • You can fix the issues based on the cause and run the precheck again.

      • If you do not need to fix the items that triggered warnings, you can click Ignore or Ignore Warnings and Rerun Precheck to skip the warnings and run the precheck again.

12. After the Precheck dialog box shows Precheck Passed, close the Precheck dialog box. The synchronization task starts.

13. Wait for the task to complete link initialization and enter the Synchronizing state.

    You can view the status of the data synchronization task on the Data Synchronization page.

    The task list includes columns for Instance ID/Task Name, Status, Synchronization Overview (latency and speed), Billing Method, Synchronization Topology, and Actions. The Actions column provides options such as Suspend Synchronization, Change to Subscription, and Upgrade. At the top of the page, you can search for tasks by name and filter or sort them by status.

Troubleshoot column type change failures

If you change a column's data type in the source table during synchronization, the task fails. To resolve:

1. In the destination instance, create a new table (for example, customer_new) with a schema that reflects the updated column types from the source table.

2. Use an INSERT INTO SELECT statement to copy the data from the original table (for example, customer) to the new table.

3. Drop or rename the original table, and then rename the new table to the original table name.

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