In MySQL we can use the SHOW BINLOG EVENTS statement to return a list of events in the binary log. We can specify which binary log to use, or we can let it default to the first one.
Syntax
The syntax goes like this:
SHOW BINLOG EVENTS
[IN 'log_name']
[FROM pos]
[LIMIT [offset,] row_count]Example
Here’s one way to run the statement:
SHOW BINLOG EVENTS;Partial output:
+---------------+-------+----------------+-----------+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+-------+----------------+-----------+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| binlog.000074 | 4 | Format_desc | 1 | 126 | Server ver: 8.0.33, Binlog ver: 4 |
| binlog.000074 | 126 | Previous_gtids | 1 | 157 | |
| binlog.000074 | 157 | Anonymous_Gtid | 1 | 236 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000074 | 236 | Query | 1 | 311 | BEGIN |
| binlog.000074 | 311 | Table_map | 1 | 366 | table_id: 92 (test.t1) |
| binlog.000074 | 366 | Write_rows | 1 | 412 | table_id: 92 flags: STMT_END_F |
| binlog.000074 | 412 | Xid | 1 | 443 | COMMIT /* xid=395 */ |
| binlog.000074 | 443 | Anonymous_Gtid | 1 | 520 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000074 | 520 | Query | 1 | 663 | use `krankykranes`; DROP TABLE `t1` /* generated by server */ /* xid=1008 */ |
| binlog.000074 | 663 | Anonymous_Gtid | 1 | 740 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000074 | 740 | Query | 1 | 884 | use `krankykranes`; DROP TABLE IF EXISTS `t1` /* generated by server */ |
| binlog.000074 | 884 | Anonymous_Gtid | 1 | 963 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000074 | 963 | Query | 1 | 1167 | use `krankykranes`; CREATE TABLE t1
(
c1 varchar(20),
c2 varchar(20),
c3 varchar(20) INVISIBLE
) /* xid=1022 */ |
| binlog.000074 | 1167 | Anonymous_Gtid | 1 | 1246 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
...
That’s a partial result because the output is quite large.
Running the command like this without any arguments can use up a lot of resources and take a very long time to run. Because of this, the MySQL documentation suggests using the mysqlbinlog utility to save the binary log to a text file for later examination and analysis.
The LIMIT Clause
We can use the LIMIT clause to limit the number of rows that are returned by the statement. This works the same as when we use the LIMIT clause with the SELECT statement.
Example:
SHOW BINLOG EVENTS LIMIT 3;Output:
+---------------+-----+----------------+-----------+-------------+--------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +---------------+-----+----------------+-----------+-------------+--------------------------------------+ | binlog.000074 | 4 | Format_desc | 1 | 126 | Server ver: 8.0.33, Binlog ver: 4 | | binlog.000074 | 126 | Previous_gtids | 1 | 157 | | | binlog.000074 | 157 | Anonymous_Gtid | 1 | 236 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | +---------------+-----+----------------+-----------+-------------+--------------------------------------+ 3 rows in set (0.00 sec)
Here I limited the output to just three rows.
The FROM Clause
We can use the FROM clause to specify where to start the output:
SHOW BINLOG EVENTS FROM 126 LIMIT 3;Output:
+---------------+-----+----------------+-----------+-------------+--------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +---------------+-----+----------------+-----------+-------------+--------------------------------------+ | binlog.000074 | 126 | Previous_gtids | 1 | 157 | | | binlog.000074 | 157 | Anonymous_Gtid | 1 | 236 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | binlog.000074 | 236 | Query | 1 | 311 | BEGIN | +---------------+-----+----------------+-----------+-------------+--------------------------------------+ 3 rows in set (0.00 sec)
When we use the FROM clause, we specify the position at which the event occurs. This is value in the Pos column, which is not necessarily the row number from the output. We can see from the above examples that the Pos value goes from 4 to 126, then to 157 and so on. I specified 126 which therefore started the output from the second row.
We don’t necessarily need to know the exact value of the row though. For example we could do this:
SHOW BINLOG EVENTS FROM 100 LIMIT 3;Output:
+---------------+-----+----------------+-----------+-------------+--------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +---------------+-----+----------------+-----------+-------------+--------------------------------------+ | binlog.000074 | 126 | Previous_gtids | 1 | 157 | | | binlog.000074 | 157 | Anonymous_Gtid | 1 | 236 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | binlog.000074 | 236 | Query | 1 | 311 | BEGIN | +---------------+-----+----------------+-----------+-------------+--------------------------------------+ 3 rows in set (0.00 sec)
The IN Clause
We can use the IN clause to specify which log file to use:
SHOW BINLOG EVENTS IN 'binlog.000078' LIMIT 3;Output:
+---------------+-----+----------------+-----------+-------------+--------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +---------------+-----+----------------+-----------+-------------+--------------------------------------+ | binlog.000078 | 4 | Format_desc | 1 | 127 | Server ver: 8.3.0, Binlog ver: 4 | | binlog.000078 | 127 | Previous_gtids | 1 | 158 | | | binlog.000078 | 158 | Anonymous_Gtid | 1 | 235 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | +---------------+-----+----------------+-----------+-------------+--------------------------------------+ 3 rows in set (0.01 sec)
We can use the SHOW BINARY LOGS statement to get a list of binary logs on the server.