This topic describes how to execute the SHOW BINLOG EVENTS statement to query the events in binary log files.
Syntax
SHOW BINLOG EVENTS
[WITH 'stream_name']
[IN 'log_name']
[FROM pos]
[LIMIT [offset,] row_count]Usage notes
Before you execute the preceding SQL statement, make sure that your account is granted the REPLICATION SLAVE permission. For information about how to manage permissions, see Manage accounts and permissions.
If a
WITHclause is not added to the SQL statement, you can query the events in the binary log files generated in single-stream mode.If a
WITHclause is added to the SQL statement, you can query the events in the binary log files whose stream name is specified and that are generated in multi-stream mode. Thestream_nameparameter specifies the name of a specific stream.For PolarDB-X instances whose kernel version is V5.4.18 or later, if you specify the name of the binary log file, you don't need to specify the WITH parameter to query the events in binary log files in the multi-stream mode.
For more information about the SQL statement, see SHOW BINLOG EVENTS.
Example 1
Execute the following statement to query the events in the binary log files generated in single-stream mode:
SHOW BINLOG EVENTS;The following result is returned:
+---------------+------+------------+-----------+-------------+--------------------------------------------------------+
| LOG_NAME | POS | EVENT_TYPE | SERVER_ID | END_LOG_POS | INFO |
+---------------+------+------------+-----------+-------------+--------------------------------------------------------+
| binlog.000001 | 745 | Query | 1 | 807 | BEGIN |
| binlog.000001 | 807 | Table_map | 1 | 868 | table_id: 258 (transfer_test.accounts) |
| binlog.000001 | 868 | Write_rows | 1 | 912 | table_id: 258 flags: STMT_END_F |
| binlog.000001 | 912 | Table_map | 1 | 973 | table_id: 367 (transfer_test.accounts) |
| binlog.000001 | 973 | Write_rows | 1 | 1017 | table_id: 367 flags: STMT_END_F |
| binlog.000001 | 1017 | Table_map | 1 | 1078 | table_id: 366 (transfer_test.accounts) |
| binlog.000001 | 1078 | Write_rows | 1 | 1122 | table_id: 366 flags: STMT_END_F |
| binlog.000001 | 1122 | Table_map | 1 | 1183 | table_id: 365 (transfer_test.accounts) |
| binlog.000001 | 1183 | Write_rows | 1 | 1227 | table_id: 365 flags: STMT_END_F |
| binlog.000001 | 1227 | Table_map | 1 | 1288 | table_id: 365 (transfer_test.accounts) |
| binlog.000001 | 1288 | Write_rows | 1 | 1332 | table_id: 365 flags: STMT_END_F |
| binlog.000001 | 1332 | Xid | 1 | 1363 | COMMIT /* xid=1 */ |
| binlog.000001 | 1363 | Rows_query | 1 | 1441 | 677707399066754342413081391897447014400000000000132369 |
+---------------+------+------------+-----------+-------------+--------------------------------------------------------+Example 2
Execute the following statement:
SHOW BINLOG EVENTS IN 'binlog.000010' LIMIT 10,10;The following result is returned:
+---------------+------+------------+------------+-------------+-------------------------------------------------------------+
| LOG_NAME | POS | EVENT_TYPE | SERVER_ID | END_LOG_POS | INFO |
+---------------+------+------------+------------+-------------+-------------------------------------------------------------+
| binlog.000010 | 563 | Rows_query | 2381662369 | 606 | # TSO HEARTBEAT TXN |
| binlog.000010 | 606 | Xid | 2381662369 | 637 | COMMIT /* xid=4649 */ |
| binlog.000010 | 637 | Rows_query | 2381662369 | 720 | CTS::718600843552620550417170736510664744970000000000000000 |
| binlog.000010 | 720 | Query | 2381662369 | 762 | BEGIN |
| binlog.000010 | 762 | Rows_query | 2381662369 | 805 | # TSO HEARTBEAT TXN |
| binlog.000010 | 805 | Xid | 2381662369 | 836 | COMMIT /* xid=4650 */ |
| binlog.000010 | 836 | Rows_query | 2381662369 | 919 | CTS::718600843643217516817170736519766384640000000000000000 |
| binlog.000010 | 919 | Query | 2381662369 | 961 | BEGIN |
| binlog.000010 | 961 | Rows_query | 2381662369 | 1004 | # TSO HEARTBEAT TXN |
| binlog.000010 | 1004 | Xid | 2381662369 | 1035 | COMMIT /* xid=4651 */ |
+---------------+------+------------+------------+-------------+-------------------------------------------------------------+Example 3
Execute the following statement:
SHOW BINLOG EVENTS IN 'binlog.000010' FROM 1004 LIMIT 10,10;The following result is returned:
+---------------+------+------------+------------+-------------+-------------------------------------------------------------+
| LOG_NAME | POS | EVENT_TYPE | SERVER_ID | END_LOG_POS | INFO |
+---------------+------+------------+------------+-------------+-------------------------------------------------------------+
| binlog.000010 | 1516 | Query | 2381662369 | 1558 | BEGIN |
| binlog.000010 | 1558 | Rows_query | 2381662369 | 1601 | # TSO HEARTBEAT TXN |
| binlog.000010 | 1601 | Xid | 2381662369 | 1632 | COMMIT /* xid=4654 */ |
| binlog.000010 | 1632 | Rows_query | 2381662369 | 1715 | CTS::718600856207674579217170737776170147840000000000000000 |
| binlog.000010 | 1715 | Query | 2381662369 | 1757 | BEGIN |
| binlog.000010 | 1757 | Rows_query | 2381662369 | 1800 | # TSO HEARTBEAT TXN |
| binlog.000010 | 1800 | Xid | 2381662369 | 1831 | COMMIT /* xid=4655 */ |
| binlog.000010 | 1831 | Rows_query | 2381662369 | 1914 | CTS::718600868492790995217170739004681789440000000000000000 |
| binlog.000010 | 1914 | Query | 2381662369 | 1956 | BEGIN |
| binlog.000010 | 1956 | Rows_query | 2381662369 | 1999 | # TSO HEARTBEAT TXN |
+---------------+------+------------+------------+-------------+-------------------------------------------------------------+Example 4
Execute the following statement:
SHOW BINLOG EVENTS WITH 'group1_stream_0' IN 'group1_stream_0_binlog.000001' LIMIT 10;The following result is returned:
+-------------------------------+-----+-------------+------------+-------------+-------------------------------------------------------------+
| LOG_NAME | POS | EVENT_TYPE | SERVER_ID | END_LOG_POS | INFO |
+-------------------------------+-----+-------------+------------+-------------+-------------------------------------------------------------+
| group1_stream_0#binlog.000001 | 4 | Format_desc | 2065077497 | 123 | Server ver: 5.6.29-TDDL-5.4.13-16504348, Binlog ver: 4 |
| group1_stream_0#binlog.000001 | 123 | Rows_query | 2065077497 | 206 | CTS::692310358647373830414541688019972300810000000000640450 |
| group1_stream_0#binlog.000001 | 206 | Rows_query | 2065077497 | 289 | CTS::692310358730001619214541688028360949770000000000640450 |
| group1_stream_0#binlog.000001 | 289 | Rows_query | 2065077497 | 372 | CTS::692310358815145990414541688036749516810000000000640450 |
| group1_stream_0#binlog.000001 | 372 | Rows_query | 2065077497 | 455 | CTS::692310358897773779214541688045138165770000000000640450 |
| group1_stream_0#binlog.000001 | 455 | Rows_query | 2065077497 | 538 | CTS::692310358982498720014541688053526732810000000000640450 |
| group1_stream_0#binlog.000001 | 538 | Rows_query | 2065077497 | 621 | CTS::692310371229027539214541689278263541770000000000640450 |
| group1_stream_0#binlog.000001 | 621 | Rows_query | 2065077497 | 704 | CTS::692310371313333049614541689286652108810000000000640450 |
| group1_stream_0#binlog.000001 | 704 | Rows_query | 2065077497 | 787 | CTS::692310371397219129614541689295040757770000000000640450 |
| group1_stream_0#binlog.000001 | 787 | Rows_query | 2065077497 | 870 | CTS::692310371481105209614541689303429324810000000000640450 |
+-------------------------------+-----+-------------+------------+-------------+-------------------------------------------------------------+Example 5
Execute the following statement:
SHOW BINLOG EVENTS WITH 'group1_stream_0' IN 'group1_stream_0_binlog.000010' FROM 285948 LIMIT 10,10;The following result is returned:
+-------------------------------+--------+-------------+-----------+-------------+------------------------------------------------------------------------------------------------------------------+
| LOG_NAME | POS | EVENT_TYPE | SERVER_ID | END_LOG_POS | INFO |
+-------------------------------+--------+-------------+-----------+-------------+------------------------------------------------------------------------------------------------------------------+
| group1_stream_0_binlog.000010 | 286661 | Rows_query | 181818 | 286744 | CTS::718609494295393081617171601585109770270000000000000000 |
| group1_stream_0_binlog.000010 | 286744 | Query | 181818 | 286786 | BEGIN |
| group1_stream_0_binlog.000010 | 286786 | Rows_query | 181818 | 286922 | /*DRDS /10.1.38.30/17d49410f7801001-4/0/181818/ */
# CTS::718609494297490233617171601585277542410000000000000000 |
| group1_stream_0_binlog.000010 | 286922 | Table_map | 181818 | 286995 | table_id: 1 (cdc_transfer_test.binlogx_accounts) |
| group1_stream_0_binlog.000010 | 286995 | Update_rows | 181818 | 287049 | table_id: 1 |
| group1_stream_0_binlog.000010 | 287049 | Xid | 181818 | 287080 | COMMIT /* xid=584 */ |
| group1_stream_0_binlog.000010 | 287080 | Rows_query | 181818 | 287163 | CTS::718609494297490233617171601585277542410000000000000000 |
| group1_stream_0_binlog.000010 | 287163 | Query | 181818 | 287205 | BEGIN |
| group1_stream_0_binlog.000010 | 287205 | Rows_query | 181818 | 287341 | /*DRDS /10.1.38.30/17d49410f9801001-2/0/181818/ */
# CTS::718609494300845676817171601585613086730000000000000000 |
| group1_stream_0_binlog.000010 | 287341 | Table_map | 181818 | 287414 | table_id: 1 (cdc_transfer_test.binlogx_accounts) |
+-------------------------------+--------+-------------+-----------+-------------+------------------------------------------------------------------------------------------------------------------+Example 6
For PolarDB-X instances whose kernel version is V5.4.18 or later, you can execute the following statement that does not contain the WITH parameter to query the events in binary log files in the multi-stream mode.
SHOW BINLOG EVENTS IN 'group1_stream_0_binlog.000003' LIMIT 10;The following result is returned:
+-------------------------------+------+-------------+------------+-------------+----------------------------------------------------------------------------------------------------------+
| LOG_NAME | POS | EVENT_TYPE | SERVER_ID | END_LOG_POS | INFO |
+-------------------------------+------+-------------+------------+-------------+----------------------------------------------------------------------------------------------------------+
| group1_stream_0_binlog.000003 | 4 | Format_desc | 2381662369 | 123 | Server ver: 5.6.29-TDDL-5.4.18-SNAPSHOT, Binlog ver: 4 |
| group1_stream_0_binlog.000003 | 123 | Query | 2381662369 | 165 | BEGIN |
| group1_stream_0_binlog.000003 | 165 | Rows_query | 2381662369 | 293 | /*DRDS /10.2.29.72/17d44302a2001000/0// */
# CTS::718600582101100140817170710365219102720000000000000000 |
| group1_stream_0_binlog.000003 | 293 | Table_map | 2381662369 | 424 | table_id: 184 (drds_polarx1_part_qatest_app.update_delete_base_broadcast) |
| group1_stream_0_binlog.000003 | 424 | Write_rows | 2381662369 | 552 | table_id: 184 |
| group1_stream_0_binlog.000003 | 552 | Write_rows | 2381662369 | 685 | table_id: 184 |
| group1_stream_0_binlog.000003 | 685 | Write_rows | 2381662369 | 821 | table_id: 184 |
| group1_stream_0_binlog.000003 | 821 | Write_rows | 2381662369 | 960 | table_id: 184 |
| group1_stream_0_binlog.000003 | 960 | Xid | 2381662369 | 991 | COMMIT /* xid=8387 */ |
| group1_stream_0_binlog.000003 | 991 | Rows_query | 2381662369 | 1074 | CTS::718600582101100140817170710365219102720000000000000000 |
+-------------------------------+------+-------------+------------+-------------+----------------------------------------------------------------------------------------------------------+