Bug #80418 Please enhance SHOW BINLOG|RELAYLOG EVENTS output
Submitted: 17 Feb 2016 23:58 Modified: 19 Jul 2016 8:15
Reporter: Simon Mudd (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Replication Severity:S4 (Feature request)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any
Tags: SHOW BINLOG EVENTS, show relaylog events

[17 Feb 2016 23:58] Simon Mudd
Description:
The 2 commands SHOW BINLOG EVENTS and SHOW RELAYLOG EVENTS are probably not used frequently. The manual here: http://dev.mysql.com/doc/refman/5.7/en/show-binlog-events.html recommends using the external command mysqlbinlog to process information in the binlog or relaylogs of a server.

However, this is most inconvenient as it requires shell access to the server and a DBA would prefer to manage (except for stopping and starting the service) the MySQL service as much as possible from the SQL command prompt (locally or remotely).

Being able to examine the contents of the binlogs or relay logs can be most important for tools such as Orchestrator: (https://github.com/outbrain/orchestrator) and in contrast to something like MHA (https://code.google.com/p/mysql-master-ha/) which does require local access this local SQL access to the binlogs is very convenient.

Unfortunately, and especially with the introduction of RBR in replication the output of SHOW BINLOG EVENTS is quite basic and limited in the information it can provide compared to the output you can see if using the mysqlbinlog command line tool that comes with MySQL.  

This complicates reading the information and thus makes it harder for tools such as Orchestrator to work effectively.

How to repeat:
Compare the output of mysqlbinlog with the output you can get from SHOW BINLOG EVENTS.  The output of the former is much richer.

Whereas a few years ago no-one paid much attention to the binlogs with the growth of larger environments and the need to export information from MySQL into other systems using this stream of data becomes more interesting than it's traditional "just for replication" usage. Enhancing this feature would be very useful and allow the DBA or the application programmer to be able to manage the server much better without having to access the physical server to see this data and certainly for tools such as Orchestrator this would make things easier.

Suggested fix:
Provide an interface which is more complete so that more information that's available in the binlog events is extractable via the SQL command, and make this work for both STATEMENT and ROW-based replication and irrespective of whether GTID mode is enabled or not.

If you need to use a better syntax, such as SHOW FULL BINLOG EVENTS / SHOW FULL RELAYLOGS EVENTS that is good. It would also be convenient to be able to filter out some of the events using a filter. Perhaps SHOW BINLOG EVENTS WHERE ..... LIMIT ... OFFSET, as this would allow a user to look for specific entries (based on the columns which were specified) without having to pull all the data off the server.
[18 Feb 2016 0:21] Simon Mudd
While the descriptive version of each binlog event would be good as it's easier to read and diagnose, having access to the raw event with perhaps just the start/end positions and maybe checksum etc would also be good as that would avoid any overhead of having to convert the data that needs to be sent to the client.

It would require the documentation to describe the complete format of all events and I assume that current documentation on MySQL internals already shows all of these details, or could be adapted to if not done already.
[18 Jun 2016 21:36] Omer Barnir
Posted by developer:
 
Reported version value updated to reflect release name change from 5.8 to 8.0
[19 Jul 2016 8:15] MySQL Verification Team
Hello Simon,

Thank you for the feature request!

Thanks,
Umesh