Mysql show binlog events

Willing to know more about Mysql show binlog events? We can help you.

At Bobcares, we offer solutions for every query, big and small, as a part of our Server Management Service.

Let’s take a look at how our Support Team help a customer  deal with this MySQL query.

Mysql show binlog events

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]

Use this statement to display the events in a binary logfile. Use the IN clause to specify a particular logfile. If the IN clause is omitted, the current file is used. To obtain a list of binary logfiles, use the SHOW MASTER LOGS statement.

The IN clause

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

mysql> SHOW BINLOG EVENTS IN TP-bin.000113';
+---------------+-----+----------------+-----------+-------------+-----------------------------------+
| Log_name      | Pos | Event_type     | Server_id | End_log_pos | Info                              |
+---------------+-----+----------------+-----------+-------------+-----------------------------------+
| TP-bin.000113 | 4   | Format_desc    | 1         | 125         | Server ver: 8.0.22, Binlog ver: 4 |
| TP-bin.000113 | 125 | Previous_gtids | 1         | 156         |                                   |
| TP-bin.000113 | 156 | Stop           | 1         | 179         |                                   |
+---------------+-----+----------------+-----------+-------------+-----------------------------------+
3 rows in set (0.00 sec)

The FROM clause

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

mysql> SHOW BINLOG EVENTS IN TP-bin.000111' FROM 1241 \G;
*************************** 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.

mysql> SHOW BINLOG EVENTS IN TP-bin.000113' LIMIT 2\G;
*************************** 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)

[Need a solution to another query? We are just a click away.]

Conclusion

Today, we saw how our Support Engineers described about about show binlog events.