AnalyticDB:Subscribe to binary logs with DTS

Prerequisites

• Your AnalyticDB for MySQL cluster must be one of the following editions: Enterprise Edition, Basic Edition, Data Lakehouse Edition, or Data Warehouse Edition (in elastic mode).

• The AnalyticDB for MySQL cluster must meet the following kernel version requirements:

  • xuanwu_v1 table engine: 3.2.1.0 or later.

  • xuanwu_v2 table engine: 3.2.5.0 or later.

  • To subscribe to binary logs of incremental materialized views, the kernel version must be 3.2.6.9, 3.2.7.1, or later.

  Note

  To view and update the minor version, go to the Configuration Information section on the Cluster Information page in the AnalyticDB for MySQL console.

• Change Data Capture (CDC) is enabled for the AnalyticDB for MySQL cluster.

  Note

  Change Data Capture (CDC) captures data changes to support incremental data synchronization and migration. To enable CDC or check its status, contact technical support.

Procedure

1. 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.

2. Enable binary logging. This example uses a table named source_table.

   Note

   AnalyticDB for MySQL supports enabling binary logging only at the table level.

   When creating a table

   CREATE TABLE source_table (
     `id` INT,
     `num` BIGINT,
     PRIMARY KEY (`id`)
   )DISTRIBUTED BY HASH (id) BINLOG=true;

   For an existing table

   ALTER TABLE source_table BINLOG=true;

3. (Optional) Modify the binary log retention period.

   You can modify the binlog_ttl parameter to adjust the binary log retention period. The default value of this parameter is 6 hours. For example, you can set the binary log retention period for the source_table table to 1 day.

   ALTER TABLE source_table binlog_ttl='1d';

   The binlog_ttl parameter supports the following formats:

   • Milliseconds: A numeric value. Example: 60 represents 60 milliseconds.

   • Seconds: Number + s. Example: 30s represents 30 seconds.

   • Hour: A number followed by h. For example, 2h represents 2 hours.

   • Day: A number followed by d. Example: 1d represents 1 day.

   Note

   • The maximum binary log retention period is 365 days for clusters with the following kernel versions or later versions in their respective series: 3.2.1.9, 3.2.2.14, 3.2.3.8, 3.2.4.4, and 3.2.5.1. For clusters with earlier kernel versions, the maximum retention period is 21 days.

   • We recommend that you set the binary log retention period to a value no less than the default value of the binlog_ttl parameter. If the retention period is too short, files may be purged, which can affect data synchronization.

   • If you need to view the current binary log retention period, run SHOW CREATE TABLE source_table;.

4. Subscribe to binary logs with DTS.

   DTS subscribes to binary logs to enable data synchronization and incremental migration between AnalyticDB for MySQL clusters.

5. (Optional) View binary log information.

   Note

   The following statements return 0 if you have enabled binary logging but have not yet subscribed using DTS. Binary log information appears only after a successful subscription is established.

   • To obtain the file name and position of the latest binary log entry, run the following SQL statement:

     SHOW MASTER STATUS FOR source_table;

   • To view all unpurged binary log files and their sizes, run the following SQL statement:

     SHOW BINARY LOGS FOR source_table;

Performance impact of enabling binary logging

After binary logging is enabled, AnalyticDB for MySQL records additional data change logs, which has the following impact on the cluster:

• CPU overhead: Tables with binary logging enabled incur additional CPU overhead during INSERT, UPDATE, and DELETE operations. In typical workloads, the CPU increase usually does not exceed 10%, but in extreme high-concurrency write scenarios, it may reach approximately 40%. We recommend that you enable binary logging during off-peak hours or evaluate the impact in a test database first.

• Storage overhead: Binary log files grow with the volume of data changes and consume disk space. Storage usage is proportional to the binlog_ttl retention period. Set a reasonable retention period based on actual synchronization requirements to avoid unnecessary storage usage by expired files.

• Query performance: Enabling binary logging does not affect query performance.

FAQ

Does AnalyticDB for MySQL support binary log monitoring?

Yes. AnalyticDB for MySQL supports binary log monitoring at the table level. You can enable binary logging for a specific table and then use DTS or custom applications to subscribe to data change events for that table. Instance-level binary logging is not supported. You cannot use a single subscription to capture change logs for all tables in an instance. To monitor binary logs for multiple tables, enable binary logging and create a subscription for each table separately.