Use Data Transmission Service (DTS) to synchronize data from a self-managed TiDB database to an AnalyticDB for MySQL V3.0 cluster for real-time analytics.
How it works
DTS reads incremental changes from TiDB through a Kafka cluster acting as a change data capture (CDC) buffer, then writes the data to AnalyticDB for MySQL V3.0. The full pipeline is:
TiDB → Kafka cluster → DTS → AnalyticDB for MySQL V3.0
TiDB does not expose binary logs directly to DTS. You must set up one of the following CDC components to publish incremental changes to a Kafka topic:
TiDB Binlog — uses Pump and Drainer components
TiCDC — uses the Canal-JSON format
DTS reads from partition 0 of the Kafka topic and applies changes to the destination cluster.
Limitations
Review these limitations before you configure the task.
Source database limits
| Limit | Details |
|---|---|
| Primary key or unique constraint required | Tables to synchronize must have a PRIMARY KEY or UNIQUE constraint with all unique fields. Tables without this constraint may produce duplicate records in the destination. |
| DDL restrictions during sync | Do not run DDL statements that change database or table schemas during schema synchronization or full data synchronization. The task will fail. |
| Prefix index length loss | TiDB does not store prefix index length in metadata. The length is lost when data is synchronized to AnalyticDB for MySQL V3.0, which may cause the destination instance to fail. Manually fix prefix index lengths for any tables that use them. |
| Table limit per task | If you select individual tables (not entire databases) and rename them, one task supports up to 1,000 tables. If you exceed this limit, configure multiple tasks or synchronize entire databases instead. |
| Kafka setup required | Deploy a Kafka cluster and install TiDB Binlog or TiCDC before creating the DTS task. |
| Outbound bandwidth | The server hosting TiDB must have enough outbound bandwidth. Insufficient bandwidth reduces synchronization speed. |
Destination and task limits
| Limit | Details |
|---|---|
| Kafka partition | DTS reads only from partition 0 of the Kafka topic. Create the topic with exactly one partition. |
| Custom primary key required | Specify a custom primary key in AnalyticDB for MySQL V3.0, or configure Primary Key Column in the Configurations for Databases, Tables, and Columns step. Otherwise, the task may fail. |
| Disk usage threshold | If disk usage on any node in the AnalyticDB for MySQL V3.0 cluster exceeds 80%, the DTS task is delayed and returns errors. Estimate required disk space before starting. |
| Backup conflicts | If the destination cluster is being backed up while DTS is running, the task fails. |
| Heterogeneous data types | TiDB and AnalyticDB for MySQL V3.0 data types do not map one-to-one. See Data type mappings for initial schema synchronization for details. |
| FLOAT/DOUBLE precision | DTS uses ROUND(COLUMN,PRECISION) to retrieve FLOAT and DOUBLE values. FLOAT defaults to 38 digits of precision, DOUBLE to 308 digits. Verify that these defaults meet your requirements. |
| Task offset initialization | After creating the task, perform operations on the source database or insert test data promptly. This updates the task offset. If you skip this step, the task may fail due to excessive latency. |
| Data from other sources | Do not write data to the destination cluster from other sources during synchronization. This causes data inconsistency. |
| Failed task resumption | DTS automatically retries failed tasks for up to 7 days. Before switching workloads to the destination cluster, stop or release any failed tasks. Alternatively, run REVOKE to remove DTS write permissions from the destination database so that a resumed task cannot overwrite new data. |
| Full data synchronization performance | Full data synchronization increases load on both the source and destination databases. Run it during off-peak hours, when CPU usage is below 30%. |
| Tablespace size after full sync | Concurrent INSERT operations during full data synchronization cause table fragmentation. The destination tablespace will be larger than the source. |
| DDL failures | If a DDL statement fails in the destination database, the task continues. Check task logs to view failed DDL statements. |
| DTS technical support SLA | If a DTS task fails to run, DTS technical support will try to restore the task within 8 hours. During restoration, the task may be restarted and task parameters may be modified. Only task parameters may be modified — database parameters are not changed. |
SQL operations for incremental synchronization
| Operation type | Supported statements |
|---|---|
| DML | INSERT, UPDATE, DELETE |
| DDL | CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE, ADD COLUMN, DROP COLUMN |
When DTS writes UPDATE statements to AnalyticDB for MySQL V3.0, it automatically converts them to REPLACE INTO. If the UPDATE statement affects the primary key, DTS converts it to DELETE followed by INSERT.Billing
| Synchronization type | Cost |
|---|---|
| Schema synchronization and full data synchronization | Free |
| Incremental data synchronization | Charged. See Billing overview. |
Prerequisites
Before you begin, ensure that you have:
An AnalyticDB for MySQL V3.0 cluster with available storage larger than the total data size of your TiDB database. See Create a cluster
A Kafka cluster or ApsaraMQ for Kafka instance deployed and accessible from the TiDB server
The required database account permissions (see Required permissions)
Required permissions
| Database | Required permissions | Reference |
|---|---|---|
| TiDB | SELECT on objects to synchronize, SHOW VIEW | Privilege Management |
| AnalyticDB for MySQL V3.0 | Read and write permissions on the destination database | Create a database account |
Set up incremental data capture
DTS requires incremental change events from TiDB to be published to a Kafka topic. Choose one of the following methods.
Choose a method
| TiDB Binlog | TiCDC | |
|---|---|---|
| Components | Pump + Drainer | TiCDC (managed via TiUP) |
| Output format | Default TiDB binlog format | Canal-JSON format |
| Best for | Existing TiDB Binlog deployments | New setups and TiDB v4.0+ |
| DTS setting | Use the default binlog format of the TiDB database | Use the TiCDC Canal-JSON format |
Prepare the Kafka cluster
Both methods require a Kafka cluster. Use one of the following:
Self-managed Kafka cluster: Deploy Apache Kafka. See Apache Kafka documentation
ApsaraMQ for Kafka instance: Create an instance. See Getting started overview
Regardless of which option you choose:
Deploy the Kafka cluster in the same network or virtual private cloud (VPC) as the TiDB server to minimize network latency.
Set the Kafka broker parameters
message.max.bytesandreplica.fetch.max.bytesto large values, and set the consumer parameterfetch.message.max.bytesto a matching large value. This makes sure the cluster can handle the volume of binary log data from TiDB. See Kafka configuration reference.
After setting up the cluster, create a topic with exactly one partition. DTS reads only from partition 0, so multiple partitions will cause data loss.
Option 1: TiDB Binlog
Deploy Pump and Drainer on servers in the same internal network as TiDB. See TiDB Binlog cluster deployment.
Edit the Drainer configuration file to point to your Kafka cluster as the downstream sink. See Binlog Consumer Client user guide. Verify that the TiDB server can connect to the Kafka cluster before proceeding.
Add the CIDR blocks of DTS servers to the TiDB database allowlist. See Add the CIDR blocks of DTS servers.
Option 2: TiCDC
Install TiCDC using TiUP. Add a new TiCDC node or scale out an existing node in the TiDB cluster. See Deploy and maintain TiCDC.
Create a changefeed to replicate incremental data from TiDB to your Kafka cluster. Use
tiup cdc cli changefeed createand specify the Canal-JSON sink URI. See Replicate data to KafkaSync data to Kafka. Verify that the TiDB server can connect to the Kafka cluster before proceeding.
Create a data synchronization task
Step 1: Go to the Data Synchronization page
Use the DTS console or the DMS console.
DTS console
Log on to the DTS console.DTS console
In the left-side navigation pane, click Data Synchronization.
In the upper-left corner, select the region where the synchronization instance will reside.
DMS console
Steps may vary based on the DMS console layout. See Simple mode and Customize the layout and style of the DMS console.
Log on to the DMS console.DMS console
In the top navigation bar, move the pointer over Data + AI and choose DTS (DTS) > Data Synchronization.
From the drop-down list to the right of Data Synchronization Tasks, select the region where the synchronization instance will reside.
Step 2: Configure source and destination databases
Click Create Task, then configure the following parameters.
Source database
| Parameter | Description |
|---|---|
| Task Name | A name for the DTS task. DTS generates one automatically. Specify a descriptive name for easy identification. The name does not need to be unique. |
| Select a DMS database instance | Select an existing registered database, or leave this blank and fill in the parameters below. To register a database: in the DMS console, click Add DMS Database Instance; in the DTS console, use the Database Connections page. See Register an Alibaba Cloud database instance and Register a database hosted on a third-party cloud service or a self-managed database. |
| Database Type | Select TiDB. |
| Access Method | Select the access method that matches where TiDB is deployed. This example uses Self-managed Database on ECS. For other access methods, prepare the required environment first. See Preparation overview. |
| Instance Region | The region where TiDB resides. |
| ECS Instance ID | The ID of the ECS instance hosting TiDB. |
| Port Number | The TiDB service port. Default: 4000. |
| Database Account | The TiDB account with the required permissions. |
| Database Password | The password for the TiDB account. |
| Migrate Incremental Data | Always set to Yesalert notification settings. This cannot be changed. To synchronize without incremental data, create a data migration task instead. See Migrate data from a self-managed TiDB database to an AnalyticDB for MySQL V3.0 cluster. |
| Kafka Cluster Type | Select the access method that matches where your Kafka cluster is deployed. This example uses Self-managed Database on ECS. If you select Express Connect, VPN Gateway, or Smart Access Gateway, also select a VPC from Connected VPC and specify Domain Name or IP. |
| Kafka Data Source Component | Select Use the default binlog format of the TiDB database (for TiDB Binlog) or Use the TiCDC Canal-JSON format (for TiCDC), based on what you set up in Set up incremental data capture. |
| ECS Instance ID | The ID of the ECS instance where the Kafka cluster is deployed. |
| Port Number | The Kafka service port. |
| Kafka Cluster Account / Kafka Cluster Password | The Kafka username and password. Leave blank if authentication is not enabled. |
| Kafka Version | The Kafka version. If the version is 1.0 or later, select 1.0. |
| Encryption | Select Non-encrypted or SCRAM-SHA-256 based on your security requirements. |
| Topic | The Kafka topic that receives incremental data from TiDB. |
Destination database
| Parameter | Description |
|---|---|
| Select a DMS database instance | Select an existing registered database, or leave this blank and fill in the parameters below. |
| Database Type | Select AnalyticDB for MySQL 3.0. |
| Access Method | Select Alibaba Cloud Instance. |
| Instance Region | The region where the AnalyticDB for MySQL V3.0 cluster resides. |
| Instance ID | The ID of the destination AnalyticDB for MySQL V3.0 cluster. |
| Database Account | The database account with read and write permissions. |
| Database Password | The password for the database account. |
Step 3: Test connectivity
Click Test Connectivity and Proceed. In the CIDR Blocks of DTS Servers dialog box, click Test Connectivity.
If DTS CIDR blocks are not already added to your database security settings, add them before proceeding. See Add the CIDR blocks of DTS servers.
Step 4: Configure objects to synchronize
In the Configure Objects step, set the following parameters.
| Parameter | Description |
|---|---|
| Synchronization Types | Select Schema Synchronization, Full Data Synchronization, and Incremental Data Synchronization. All three are required. Incremental Data Synchronization is selected by default; you must also select the other two. Full data synchronization provides the historical data baseline for incremental synchronization. |
| Processing Mode of Conflicting Tables | Precheck and Report Errors (default): fails the precheck if tables with identical names exist in the destination. Use object name mapping to resolve conflicts without deleting destination tables. See Map object names. Ignore Errors and Proceed: skips the precheck for identical table names. During full synchronization, existing destination records are retained. During incremental synchronization, destination records are overwritten. Use with caution — this may cause data inconsistency. |
| Capitalization of Object Names in Destination Instance | Controls capitalization of database, table, and column names in the destination. Default: DTS default policy. See Specify the capitalization of object names in the destination instance. |
| Source Objects | Select databases or tables to synchronize and click the arrow icon to move them to Selected Objects. |
| Selected Objects | To rename an object in the destination, right-click it and use object name mapping. To rename multiple objects at once, click Batch Edit. To filter rows with SQL WHERE conditions, right-click a table and specify conditions. See Specify filter conditions. Renaming an object may break dependent objects. |
Step 5: Configure advanced settings
Click Next: Advanced Settings and configure the following.
| Parameter | Description |
|---|---|
| Retry Time for Failed Connections | How long DTS retries failed connections after the task starts. Range: 10–1440 minutes. Default: 720 minutes. Set to more than 30 minutes. If multiple tasks share the same source or destination database, the shortest retry time takes precedence. |
| Retry Time for Other Issues | How long DTS retries failed DDL or DML operations. Range: 1–1440 minutes. Default: 10 minutes. Set to more than 10 minutes. This value must be less than Retry Time for Failed Connections. |
| Enable Throttling for Full Data Synchronization | Limits the read and write load during full data synchronization. Configure QPS (queries per second) to the source database, RPS of Full Data Migration, and Data migration speed for full migration (MB/s). Available only when Full Data Synchronization is selected. |
| Enable Throttling for Incremental Data Synchronization | Limits the write load during incremental data synchronization. Configure RPS of Incremental Data Synchronization and Data synchronization speed for incremental synchronization (MB/s). |
| Environment Tag | An optional tag to identify the DTS instance by environment (for example, production or test). |
| Configure ETL | Select Yes to enable extract, transform, and load (ETL) and enter data processing statements. Select No to skip. See What is ETL? |
| Monitoring and Alerting | Select Yes to configure alerts for task failures or latency threshold breaches. Configure the alert threshold and notification settings. See Configure monitoring and alerting. |
Step 6: Configure data verification (optional)
Click Next Step: Data Verification to set up data verification. See Configure a data verification task.
Step 7: Configure database and table fields (optional)
Click Next: Configure Database and Table Fields to configure AnalyticDB for MySQL V3.0-specific settings for each table.
This step is only available when Schema Synchronization is selected. Set Definition Status to All to view and modify all tables.
| Parameter | Description |
|---|---|
| Type | The table type in AnalyticDB for MySQL V3.0. |
| Primary Key Column | The primary key columns for the table. Supports composite primary keys. |
| Distribution Key | The distribution key for the table. Must be one or more of the primary key columns. |
| Partition Key / Partitioning Rules / Partition Lifecycle | Partitioning settings for the table. See CREATE TABLE. |
Step 8: Save the task and run the precheck
To preview the API parameters for this task configuration, move the pointer over Next: Save Task Settings and Precheck and click Preview OpenAPI parameters.
Click Next: Save Task Settings and Precheck to save and start the precheck.
If the precheck fails:
Click View Details next to each failed item, fix the issue, and run the precheck again.
If an item shows an alert that can be ignored, click Confirm Alert Details, then Ignore, then OK, and then Precheck Again. Ignoring alerts may cause data inconsistency.
Step 9: Purchase an instance
Wait for Success Rate to reach 100%, then click Next: Purchase Instance.
On the purchase page, configure the following.
| Parameter | Description |
|---|---|
| Billing Method | Subscription: pay upfront for a fixed term. More cost-effective for long-term use. Pay-as-you-go: billed hourly. Suitable for short-term use. Release the instance when no longer needed to avoid unnecessary charges. |
| Resource Group Settings | The resource group for the synchronization instance. Default: default resource group. See What is Resource Management? |
| Instance Class | The synchronization speed tier. See Instance classes of data synchronization instances. |
| Subscription Duration | Available for the subscription billing method. Options: 1–9 months, 1 year, 2 years, 3 years, or 5 years. |
Read and select Data Transmission Service (Pay-as-you-go) Service Terms.
Click Buy and Start, then click OK in the confirmation dialog box.
The task appears in the task list. Monitor its progress there.