AnalyticDB for MySQL clusters generate binary logs that record incremental data changes. By subscribing to these logs with Data Transmission Service (DTS), you can replicate data in real time between clusters — without full data exports or downtime. Common use cases include:
Data synchronization: Keep a secondary AnalyticDB for MySQL cluster in sync with a primary cluster
Incremental migration: Migrate only changed data from a source cluster to a target cluster
Prerequisites
Before you begin, make sure that:
The cluster is running Enterprise Edition, Basic Edition, Data Lakehouse Edition, or Data Warehouse Edition in elastic mode
The cluster minor version is 3.2.1.0 or later. To view and update the minor version, go to the Configuration Information section on the Cluster Information page of the AnalyticDB for MySQL console
Change Data Capture (CDC) is enabled for the cluster. To enable CDC or verify its status, contact technical support
Limitations
Binary logging is not supported for XUANWU_V2 tables. DTS cannot subscribe to binary logs from XUANWU_V2 tables, which means data synchronization and incremental migration are not available for those tables.
Enable binary logging
Log on to the AnalyticDB for MySQL console. In the upper-left corner of the console, select a region. In the left-side navigation pane, click Clusters. Find the cluster that you want to manage and click the cluster ID.
Binary logging can only be enabled on tables within AnalyticDB for MySQL.
Enable binary logging when creating a table
Add BINLOG=true to your CREATE TABLE statement:
CREATE TABLE source_table (
`id` INT,
`num` BIGINT,
PRIMARY KEY (`id`)
) DISTRIBUTED BY HASH (id) BINLOG=true;Enable binary logging on an existing table
Run the following ALTER TABLE statement:
ALTER TABLE source_table BINLOG=true;Set the binary log retention period
The binlog_ttl parameter controls how long binary logs are retained. To change the retention period, run:
ALTER TABLE source_table binlog_ttl='1d';
-- Default: 6h
-- Supported formats:
-- Milliseconds: pure number, e.g. 60 (60 ms)
-- Seconds: number + s, e.g. 30s
-- Hours: number + h, e.g. 2h
-- Days: number + d, e.g. 1dMaximum retention period by kernel version:
| Kernel version series | Minimum kernel version | Maximum retention period |
|---|---|---|
| 3.2.1.x | 3.2.1.9 | 365 days |
| 3.2.2.x | 3.2.2.14 | 365 days |
| 3.2.3.x | 3.2.3.8 | 365 days |
| 3.2.4.x | 3.2.4.4 | 365 days |
| 3.2.5.x | 3.2.5.1 | 365 days |
| Earlier versions | — | 21 days |
Set binlog_ttl to a value greater than or equal to the default (6h). If the retention period is too short, binary logs may be purged before DTS can consume them, which breaks data synchronization.
To check the current retention period, run:
SHOW CREATE TABLE source_table;Subscribe to binary logs with DTS
After enabling binary logging on the source table, configure a DTS subscription to start consuming the logs. DTS uses the subscribed binary logs to perform:
View binary log information
Use the following SQL statements to inspect binary log status after setting up the DTS subscription.
These statements return 0 if binary logging is enabled but no DTS subscription is active. Log data appears only after the subscription is configured.
To view the file name and position of the most recent binary log:
SHOW MASTER STATUS FOR source_table;To list all uncleared historical binary logs and their sizes:
SHOW BINARY LOGS FOR source_table;