In MySQL we can use the SHOW BINARY LOGS statement to return a list of binary log files on the server.

Syntax

The syntax goes like this:

SHOW BINARY LOGS
SHOW MASTER LOGS

So we have the option of specifying BINARY or MASTER, either way it does the same thing.

Example

Here’s one way to run the statement:

SHOW BINARY LOGS;

Sample output:

+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000074 |     21063 | No        |
| binlog.000075 |       180 | No        |
| binlog.000076 |       181 | No        |
| binlog.000077 |     20789 | No        |
| binlog.000078 |     39621 | No        |
| binlog.000079 |     10160 | No        |
| binlog.000080 |     10757 | No        |
+---------------+-----------+-----------+

That’s a list of log files on my system.

Using SHOW MASTER LOGS Instead

As mentioned, SHOW MASTER LOGS is the equivalent of SHOW BINARY LOGS, so we can use that to do the same thing:

SHOW MASTER LOGS;

Output:

+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000074 |     21063 | No        |
| binlog.000075 |       180 | No        |
| binlog.000076 |       181 | No        |
| binlog.000077 |     20789 | No        |
| binlog.000078 |     39621 | No        |
| binlog.000079 |     10160 | No        |
| binlog.000080 |     10757 | No        |
+---------------+-----------+-----------+

As expected, this produced the same output as the previous example.

Use Case

The SHOW BINARY LOGS statement can be handy for when we run other statements that accept the name of a binary log, where we need to know the name of the log file.

Here’s an example of such a statement:

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)

In this case I knew the name of the log file because I used SHOW BINARY LOGS to return a list of binary log files.