Data Transmission Service:Synchronize self-managed TiDB 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, and DROP COLUMN

• DML operations: INSERT, UPDATE, and DELETE

Preparations

1. Deploy the Pump and Drainer components. For more information, see Deploy a TiDB Binlog Cluster.

2. Modify the Drainer component's configuration file to set the output to Kafka. For more information, see Develop a custom Kafka consumer.

3. Prepare a Kafka cluster by using one of the following methods:

   • Deploy a self-managed Kafka cluster. See the Apache Kafka official website.

     Warning

     To handle large TiDB binlogs, increase the message.max.bytes and replica.fetch.max.bytes broker parameters, and the fetch.message.max.bytes consumer parameter. See Kafka configuration reference.

   • Use Alibaba Cloud

     ApsaraMQ for Kafka

     (MQ for Apache Kafka). For more information, see Alibaba Cloud

     ApsaraMQ for Kafka

     Quick Start.

     Note

     Deploy the Message Queue for Apache Kafka instance in the same VPC as the source database server to minimize network latency.

4. Create a topic in the self-managed Kafka cluster or the ApsaraMQ for Kafka instance.

5. Add the DTS server CIDR blocks to your TiDB database's whitelist. For the specific CIDR blocks, see Add the CIDR blocks of DTS servers to a whitelist.

Procedure

1. Purchase a data synchronization task. Purchase procedure.

   Note

   When you purchase the task, set TiDB to TiDB and Destination Instance to AnalyticDB for MySQL.

2. Log on to the DTS console.

   Note

   If you are automatically redirected to the Data Management (DMS) console, you can click the icon in the lower-right corner and then click to return to the classic 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 for the synchronization channel.

   1. Configure the task name and source instance.

      

      Parameter	Description
      Synchronization task name	DTS auto-generates a task name. Specify a descriptive name for easy identification. The name does not need to be unique.
      Instance type	Select where the source database is deployed. This example uses User-Created Database in ECS Instance. Note If your self-managed database is of a different instance type, you may need to complete additional preparations. For more information, see Preparations for data migration.
      Instance region	The region selected when you purchased the DTS instance. Cannot be changed.
      Database type	Fixed as TiDB.
      Port number	Enter the service port of the TiDB database. Default: 4000.
      Database account	Enter the database account for the TiDB database. The account must have SHOW VIEW and SELECT permissions on the objects to synchronize. Privilege Management.
      Database password	Enter the password for the database account.
      Kafka cluster type	Select the deployment location of the Kafka cluster. This example uses User-Created Database in ECS Instance. For other instance types, additional preparations are required. Overview of preparations. Note Because DTS does not currently support directly selecting Alibaba Cloud ApsaraMQ for Kafka If you are using an Alibaba Cloud Message Queue for Kafka instance, configure it as a self-managed Kafka cluster. Select Self-managed database connected via Express Connect/VPN Gateway/Smart Access Gateway, then select the VPC where the instance is located.
      Instance region	Must be the same as the source instance region. Cannot be changed.
      ECS instance ID	Select the ID of the ECS instance where the self-managed Kafka cluster is located.
      Kafka port number	The service port of the self-managed Kafka cluster. Default: 9092.
      Kafka cluster account	Enter the username for the self-managed Kafka cluster. Leave blank if authentication is not enabled.
      Kafka cluster password	Enter the password for the username. Leave blank if authentication is not enabled.
      Topic	Click Get Topic List on the right and select a topic from the drop-down list.
      Kafka version	Select the version of your self-managed Kafka cluster.
      Kafka cluster connection method	Select Non-encrypted or SCRAM-SHA-256 based on your business and security requirements.

   2. Configure the destination instance.

      

      Parameter	Description
      Instance type	Fixed as AnalyticDB.
      Instance region	The region selected when you purchased the DTS instance. Cannot be changed.
      Version	Fixed as 3.0.
      Database	Select the cluster ID of the destination AnalyticDB for MySQL cluster.
      Database account	Enter the database account for the AnalyticDB for MySQL cluster. The account must have read and write permissions on the destination database. Create a database account.
      Database password	Enter the password that corresponds to 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
   Select the initial synchronization types	By default, both Initial Schema Synchronization and Initial Full Data Synchronization are selected. After the precheck, DTS initializes the schema and data of the target objects in the destination cluster as a baseline for incremental synchronization.
   Select the processing mode of conflicting tables	Precheck and Report Errors: Checks if tables with the same names exist in the destination database. If not, the precheck passes. Otherwise, the precheck reports an error and the task does not start. Note If you cannot delete or rename the conflicting tables in the destination database, rename them using the object name mapping feature. Set the names of synchronized objects in the destination instance. Ignore Errors and Proceed: Skips the check for tables with the same names in the destination database. Warning Selecting Ignore Errors and Proceed may cause data inconsistency and business risks: If the schemas are identical, when a record in the destination database has the same primary key as a source record, the destination record is retained and the source record is not synchronized. If the schemas are inconsistent, data initialization may fail, only some columns may be synchronized, or the task may fail.
   Specify whether to merge tables	Select Yes: DTS adds a column named __dts_data_source to each table to store the data source. DDL synchronization is not supported in this case. Select No: This is 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.
   Select the operation types to synchronize	Select the operation types to synchronize based on your business requirements. See Supported SQL operations. All operation types are selected by default.
   Select the objects to synchronize	In the Source Objects box, click 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 synchronization objects. Note If you select an entire database as a synchronization object, all schema changes for objects in that database are synchronized to the destination. If you select a specific table, only ADD COLUMN operations on that table are synchronized to the destination. By default, synchronized object names remain unchanged. To use different names in the destination cluster, use the object name mapping feature. Set the names of synchronized objects in the destination instance.
   Rename databases and tables	Change the names of synchronized objects in the destination instance. For more information, see Map databases, tables, and columns.
   Replicate temporary tables when DMS performs DDL operations	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. Set the table type in the destination database.

    

    Note

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

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 finish initialization and enter the Synchronizing state.

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