AnalyticDB:Use DTS to subscribe to AnalyticDB for MySQL binary logs

Prerequisites

• The AnalyticDB for MySQL clusters are of the Enterprise Edition, Basic Edition, Data Lakehouse Edition, or Data Warehouse Edition in elastic mode.

• The minor versions of the AnalyticDB for MySQL clusters are 3.2.1.0 or later.

  Note

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

• The Change Data Capture (CDC) feature is enabled for the AnalyticDB for MySQL clusters.

  Note

  The CDC feature captures data changes and supports incremental data synchronization and migration. To enable the CDC feature or check whether it is enabled, contact technical support.

Limits

The binary logging feature cannot be enabled for XUANWU_V2 tables. In this case, you cannot perform data synchronization or incremental data migration on XUANWU_V2 tables between AnalyticDB for MySQL clusters by subscribing to binary logs.

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 the binary logging feature for a table in the source AnalyticDB for MySQL cluster. In this example, a table named source_table is used.

   Note

   You can enable the binary logging feature only for tables in AnalyticDB for MySQL.

   Enable the binary logging feature when you create a table

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

   Enable the binary logging feature for an existing table

   ALTER TABLE source_table BINLOG=true;

3. (Optional) Change the retention period of binary logs.

   You can modify the binlog_ttl parameter to change the retention period of binary logs. The default value of the parameter is 6h. Execute the following statement to change the retention period of binary logs to 1 day for the source_table table:

   ALTER TABLE source_table binlog_ttl='1d';

   The binlog_ttl parameter supports values in the following formats:

   • Millisecond: pure number. For example, 60 specifies 60 milliseconds.

   • Second: number + s. For example, 30s specifies 30 seconds.

   • Hour: number + h. For example, 2h specifies 2 hours.

   • Day: number + d. For example, 1d specifies 1 day.

   Note

   • For clusters with kernel versions 3.2.1.9 or later (for version 3.2.1), 3.2.2.14 or later (for version 3.2.2), 3.2.3.8 or later (for version 3.2.3), 3.2.4.4 or later (for version 3.2.4), or 3.2.5.1 or later (for version 3.2.5), the maximum retention period of binary logs is 365 days. For clusters with kernel versions earlier than those mentioned, the maximum retention period of binary logs is 21 days.

   • We recommend that you set the retention period of binary logs to a value that is greater than or equal to the default value of the binlog_ttl parameter. If you set the retention period to a small value, binary logs may be deleted and data synchronization fails.

   • To query the current retention period of binary logs, execute the SHOW CREATE TABLE source_table; statement.

4. Use DTS to subscribe to AnalyticDB for MySQL binary logs.

   DTS can use the subscribed binary logs to perform data synchronization and incremental data migration between AnalyticDB for MySQL clusters.

5. (Optional) View information about binary logs.

   Note

   After you execute the following SQL statements to query information about binary logs, 0 is returned if you only enable the binary logging feature. Log information is displayed only after you subscribe to binary logs.

   • To query the file names and locations of the most recent binary logs, execute the following statement:

     SHOW MASTER STATUS FOR source_table;

   • To query all the uncleared historical binary logs and their sizes, execute the following statement:

     SHOW BINARY LOGS FOR source_table;