MySQL - SHOW BINLOG EVENTS Statement

MySQL SHOW BINLOG EVENTS Statement

The SHOW BINLOG EVENTS statement displays the events in the specified binary log. Incase if you haven't specified a log name the first log is displayed. To execute this statement, you need the REPLICATION SLAVE privilege. This statement displays the following details −

• Log_name − Name of the log file.

• Pos − Starting position of the event.

• Event_type − Identifier specifying the type of the event.

• Server_id − ID of the server.

• End_log_pos − ending position of an event in the log.

• Info − More information about the event.

Syntax

Following is the syntax of the MySQL SHOW BINLOG EVENTS Statement −

SHOW BINLOG EVENTS
   [IN 'log_name']
   [FROM pos]
   [LIMIT [offset,] row_count]

Example

You can display the list events in a binary log as shown below −

SHOW BINLOG EVENTS\G;

Output

The above query produces the following output −

********** 1. row **********
   Log_name: TP-bin.000105
        Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 125
       Info: Server ver: 8.0.22, Binlog ver: 4
********** 2. row **********
   Log_name: TP-bin.000105
        Pos: 125
 Event_type: Previous_gtids
  Server_id: 1
End_log_pos: 156
       Info:
********** 3. row **********
   Log_name: TP-bin.000105
        Pos: 156
 Event_type: Anonymous_Gtid
  Server_id: 1
End_log_pos: 233
   Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
********** 4. row **********
   Log_name: TP-bin.000105
        Pos: 233
 Event_type: Query
  Server_id: 1
End_log_pos: 366
   Info: use `test`; DROP TABLE `employee` /* generated by server */ 
              /* xid=8 */
********** 5. row **********
   Log_name: TP-bin.000105
        Pos: 366
 Event_type: Anonymous_Gtid
  Server_id: 1
End_log_pos: 445
   Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
********** 6. row **********
   Log_name: TP-bin.000105
        Pos: 445
 Event_type: Query
  Server_id: 1
End_log_pos: 730
       Info: use `test`; CREATE TABLE EMPLOYEE(
         ID INT NOT NULL,
         FIRST_NAME CHAR(20) NOT NULL,
         LAST_NAME CHAR(20),
         AGE INT,
         SEX CHAR(1),
         INCOME FLOAT,
         CONTACT INT
) /* xid=9 */
********** 7. row **********
   Log_name: TP-bin.000105
        Pos: 730
 Event_type: Anonymous_Gtid
  Server_id: 1
End_log_pos: 809
   Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . .

The IN clause

Using the in clause you can display the details of the events in a particular file.

SHOW BINLOG EVENTS IN TP-bin.000113';

Output

Following is the output of the above mysql query −

The FROM clause

The FROM clause is used to display the list of events from a particular position in a log.

SHOW BINLOG EVENTS IN TP-bin.000111' FROM 1241 \G;

Output

After executing the above query, it will produce the following output −

********** 1. row **********
   Log_name: TP-bin.000111
        Pos: 1241
 Event_type: Anonymous_Gtid
  Server_id: 1
End_log_pos: 1320
Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
********** 2. row **********
   Log_name: TP-bin.000111
        Pos: 1320
 Event_type: Query
  Server_id: 1
End_log_pos: 1543
       Info: use `xo`; CREATE DEFINER=`root`@`localhost` PROCEDURE 
	         `sample4`()
BEGIN
      SELECT 'This is a sample procedure';
END /* xid=12 */
********** 3. row **********
   Log_name: TP-bin.000111
        Pos: 1543
 Event_type: Stop
  Server_id: 1
End_log_pos: 1566
       Info:
3 rows in set (0.00 sec)

The LIMIT clause

You can limit the events in a log while displaying, using the LIMIT clause.

SHOW BINLOG EVENTS IN TP-bin.000113' LIMIT 2\G;

Output

Following is the output of the above query −

********** 1. row **********
   Log_name: TP-bin.000113
        Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 125
       Info: Server ver: 8.0.22, Binlog ver: 4
********** 2. row **********
   Log_name: TP-bin.000113
        Pos: 125
 Event_type: Previous_gtids
  Server_id: 1
End_log_pos: 156
       Info:
2 rows in set (0.00 sec)