MySQL Bugs: #42011: Add a File_date column in the output of SHOW BINARY LOGS

Bug #42011	Add a File_date column in the output of SHOW BINARY LOGS
Submitted:	10 Jan 2009 4:34	Modified:	12 Jan 2009 10:28
Reporter:	Santo Leto	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S4 (Feature request)
Version:	5.1, 6.0	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	binlogs, File_date, purge binlogs, show binary logs

Description:
It would be great to have a 'File_date' column in the output of the query SHOW BINARY LOGS.

This would help purging logs using the clause BEFORE 'yyyy-mm-dd hh:mm:ss' of the syntax PURGE MASTER LOGS also for remote servers, where you don't have access to the filesystem. 

If I am not mistaken, at present time it isn't possible to retrieve date of binlog files using SQL.

How to repeat:
mysql> select user(), version();
+----------------+----------------------+
| user()         | version()            |
+----------------+----------------------+
| root@localhost | 5.1.30-community-log |
+----------------+----------------------+
1 row in set (0.00 sec)

mysql> SHOW BINARY LOGS;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000006 |      1167 |
| mysql-bin.000007 |      3879 |
| mysql-bin.000008 |       263 |
| mysql-bin.000009 |       125 |
| mysql-bin.000010 |    400305 |
| mysql-bin.000011 |      1148 |
| mysql-bin.000012 |       327 |
| mysql-bin.000013 |      2590 |
| mysql-bin.000014 |     86474 |
| mysql-bin.000015 |      2107 |
| mysql-bin.000016 |       746 |
| mysql-bin.000017 |     48925 |
| mysql-bin.000018 |       125 |
| mysql-bin.000019 |       125 |
| mysql-bin.000020 |       125 |
| mysql-bin.000021 |       125 |
| mysql-bin.000022 |       125 |
| mysql-bin.000023 |       125 |
| mysql-bin.000024 |       106 |
+------------------+-----------+
19 rows in set (0.08 sec)

mysql> PURGE MASTER LOGS BEFORE 'yyyy-mm-dd hh:mm:ss'; #Where can I read the File date?

Many thanks for writing a feature request.

We will discuss this.

I've made a proof-of-concept which adds a unix timestamp based on the ctime of the file to the output.

mysql> SHOW BINARY LOGS;
+------------------------------+-----------+------------+
| Log_name                     | File_size | Timestamp  |
+------------------------------+-----------+------------+
| mysql_sandbox5616-bin.000001 |       179 | 1392476093 |
| mysql_sandbox5616-bin.000002 |       179 | 1392476109 |
| mysql_sandbox5616-bin.000003 |       143 | 1392476597 |
| mysql_sandbox5616-bin.000004 |       120 | 1392476789 |
+------------------------------+-----------+------------+
4 rows in set (0.00 sec)

The code is available here:
https://code.launchpad.net/~dveeden/mysql-server/binlogtimestamp

Patch which add the timestamp column (PoC, not production ready)

Attachment: mysql_binlogts.patch (text/x-diff), 1.25 KiB.

Correction: My code uses the mtime instead of the ctime