本文介绍了如何使用SHOW BINLOG EVENTS语句查看Binlog文件中的具体事件信息。
语法
SHOW BINLOG EVENTS
[WITH 'stream_name']
[IN 'log_name']
[FROM pos]
[LIMIT [offset,] row_count]说明：
执行上述SQL需要有REPLICATION SLAVE权限，权限操作请参见账号权限管理。
不加
WITH子句，可查看单流服务中global binlog文件中的事件信息。添加
WITH子句，可查看多流服务中某个流下面的binlog文件中的事件信息。参数stream_name表示某个具体的流名称。从5.4.18版本开始，当指定了具体binlog文件的名字时，可以不用填入with选项即可直接查看多流binlog文件的event信息。
更多详情，请参见SHOW BINLOG EVENTS。
示例1
执行以下语句，查看单流服务中global binlog文件中的事件信息。
SHOW BINLOG EVENTS;返回结果如下：
+---------------+------+------------+-----------+-------------+--------------------------------------------------------+
| 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 |
+---------------+------+------------+-----------+-------------+--------------------------------------------------------+示例2
执行以下语句：
SHOW BINLOG EVENTS IN 'binlog.000010' LIMIT 10,10;返回结果如下：
+---------------+------+------------+------------+-------------+-------------------------------------------------------------+
| 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 */ |
+---------------+------+------------+------------+-------------+-------------------------------------------------------------+示例3
执行以下语句：
SHOW BINLOG EVENTS IN 'binlog.000010' FROM 1004 LIMIT 10,10;返回结果如下：
+---------------+------+------------+------------+-------------+-------------------------------------------------------------+
| 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 |
+---------------+------+------------+------------+-------------+-------------------------------------------------------------+示例4
执行以下语句：
SHOW BINLOG EVENTS WITH 'group1_stream_0' IN 'group1_stream_0_binlog.000001' LIMIT 10;返回结果如下：
+-------------------------------+-----+-------------+------------+-------------+-------------------------------------------------------------+
| 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 |
+-------------------------------+-----+-------------+------------+-------------+-------------------------------------------------------------+示例5
执行以下语句：
SHOW BINLOG EVENTS WITH 'group1_stream_0' IN 'group1_stream_0_binlog.000010' FROM 285948 LIMIT 10,10;返回结果如下：
+-------------------------------+--------+-------------+-----------+-------------+------------------------------------------------------------------------------------------------------------------+
| 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) |
+-------------------------------+--------+-------------+-----------+-------------+------------------------------------------------------------------------------------------------------------------+示例6
在大于等于5.4.18的版本，执行不带WITH的语句直接查看多流binlog的event：
SHOW BINLOG EVENTS IN 'group1_stream_0_binlog.000003' LIMIT 10;返回结果如下：
+-------------------------------+------+-------------+------------+-------------+----------------------------------------------------------------------------------------------------------+
| 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 |
+-------------------------------+------+-------------+------------+-------------+----------------------------------------------------------------------------------------------------------+文档内容是否对您有帮助？