本文介紹了如何使用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 |
+-------------------------------+------+-------------+------------+-------------+----------------------------------------------------------------------------------------------------------+