This topic describes how to subscribe to Hologres binlogs.
Usage notes
- Only Hologres V0.9 and later support binlog subscription. To upgrade a Hologres instance from a version earlier than V0.9 to V0.9 or later, submit a ticket or join the Hologres DingTalk group for technical support. For more information about how to join the Hologres DingTalk group, see Obtain online support for Hologres.
- Hologres allows you to separately subscribe to binlogs of each row-oriented table
or column-oriented table. The following table describes the types of Hologres binlog
subscription that are supported.
Flink version Subscription to binlogs of Hologres row-oriented tables Subscription to binlogs of Hologres column-oriented tables Subscription to binlogs of Hologres tables that are both row-oriented and column-oriented (supported in Hologres V1.1 and later) Blink-based Realtime Compute Supported Supported Supported Fully managed Flink Supported from VVP 2.4 Supported from VVP 2.4 Supported from VVP 2.4 Open source Flink Not supported Not supported Not supported JDBC Supported from Hologres V1.1 Supported from Hologres V1.1 Supported from Hologres V1.1
Enable binary logging
- Overview
By default, binary logging is disabled for Hologres tables. You can enable this feature for a Hologres table by setting the table properties binlog.level and binlog.ttl. Theoretically, if your tables are frequently updated, the cost of binary logging for column-oriented tables is greater than the cost of binary logging for row-oriented tables. Therefore, we recommend that you enable binary logging for row-oriented tables.
- Limits
- In Hologres V0.9 and V0.10, you cannot enable binary logging by modifying the table properties of an existing table. To enable binary logging for a table, you must enable the feature when you create the table. In Hologres V1.1 and later, you can enable binary logging for all tables as needed.
- Blink-based Realtime Compute does not support Hologres binlogs that contain data of the TIMESTAMP type. When you create a table in Hologres, specify the TIMESTAMPTZ data type for related fields. Blink-based Realtime Compute also does not support Hologres binlogs that contain data of special types such as the SMALLINT type.
- You cannot subscribe to binlogs of a parent partitioned table. You can subscribe to only the binlogs of a non-partitioned table.
- Theoretically, if your tables are frequently updated, the cost of binary logging for column-oriented tables is greater than the cost of binary logging for row-oriented tables. Therefore, we recommend that you enable binary logging for row-oriented tables.
- Examples
When you create a table, you can set the table properties binlog.level and binlog.ttl to enable binary logging. For more information about the parameters that you can use to create a table, see CREATE TABLE.
begin; create table test_message_src( id int primary key, title text not null, body text); call set_table_property('test_message_src', 'orientation', 'row'); -- Create a row-oriented table named test_message_src. call set_table_property('test_message_src', 'clustering_key', 'id'); -- Create a clustered index for the id column. call set_table_property('test_message_src', 'binlog.level', 'replica'); -- Set the binlog.level property to enable binary logging. call set_table_property('test_message_src', 'binlog.ttl', '86400'); -- Set the binlog.ttl property to specify the time to live (TTL) of binlogs. Unit: seconds. commit;
Property Description binlog.level Specifies whether to enable binary logging. Valid values: - replica: enables binary logging.
- none: disables binary logging.
binlog.ttl The TTL of binlogs. Unit: seconds. Default value: 2592000, which indicates 30 days.
Enable binary logging as needed
- Enable binary logging
You can execute the following statements to enable binary logging for an existing table and specify the TTL of binlogs:
The table_name property specifies the name of the table for which binary logging is to be enabled.-- Set the binlog.level property to enable binary logging. begin; call set_table_property('<table_name>', 'binlog.level', 'replica'); commit; -- Set the binlog.ttl property to specify the TTL of binlogs. Unit: seconds. begin; call set_table_property('<table_name>', 'binlog.ttl', '2592000'); commit;
- Disable binary logging
You can execute the following statements to disable binary logging for a table:
The table_name property specifies the name of the table for which binary logging is to be disabled.-- Set the binlog.level property to disable binary logging. begin; call set_table_property('<table_name>', 'binlog.level', 'none'); commit;
- Change the TTL of binlogs
You can execute the following statements to change the TTL of binlogs for a table. This way, you can set an appropriate binlog retention time based on your business requirements.
The table_name property specifies the name of the table for which the TTL of binlogs is to be changed.begin; call set_table_property('<table_name>', 'binlog.ttl', '8640000'); -- Unit: seconds. commit;
Binlog format
Field | Data type | Description |
---|---|---|
hg_binlog_lsn | BIGINT | The ordinal number of the current binlog. This field is a binlog system field. For binlogs in the same shard, the values of this field monotonically increase and may be discontinuous. For binlogs in different shards, the values of this field may be recurrent and out of order. |
hg_binlog_event_type | BIGINT | The type of operation in the current record. This field is a binlog system field. |
hg_binlog_timestamp_us | BIGINT | The timestamp of the system. Unit: microseconds. This field is a binlog system field. |
user_table_column_1 | User-defined | A user-defined table field. |
... | ... | ... |
user_table_column_n | User-defined | A user-defined table field. |
- The hg_binlog_event_type field has four valid values:
- INSERT=5, which indicates that a row or column is inserted.
- DELETE=2, which indicates that a row or column is deleted.
- BEFORE_UPDATE=3, which indicates that a row or column is saved before it is updated.
- AFTER_UPDATE=7, which indicates that a row or column is saved after it is updated.
- An UPDATE operation generates two binlog records, which indicate that the row or column is saved before and after the update. Binary logging ensures that the ordinal numbers of the two records are continuous and in the right order. The record that is generated after the update comes right after the record that is generated before the update.
- The order of user-defined fields is consistent with the order that is defined in a DDL statement.
Query log backup files
select hg_binlog_lsn,hg_binlog_event_type,hg_binlog_timestamp_us,* from test_message_src;