Start Time and end time of reading mysql binlog

Mysql binlog records all operations that may involve updates and can be used as an option for Incremental backup. To manage binlog, you need to read the exact start time and end time of each binlog file. The mysqlbinlog tool can be used to parse the binlog file, so it can also be obtained by analyzing the output results. However, mysqlbinlog can only read records sequentially. If only the analysis start time is good, you must wait for it to finish processing the entire binlog. When the binlog file size is large, the cost is higher. Fortunately, the format of binlog files in mysql is public, so we can achieve it directly by parsing the file.
 
The format of the binlog file can be found in the http://forge.mysql.com/wiki/MySQL_Internals_Binary_Log. Each binlog file has the same start: 0xfe 0 × 62 0 × 69 0x6e. That is, add bin after 0xfe. After that, event data is generated one by one. There are many types of binlog events, but the first event of each binlog file must be a format description event, which describes the format version information of the binlog file; the last time must be a rotate event, which records the file name of the next binlog and the start offset of the event. Each event has a consistent event header, including the event Timestamp and event type. Read the information of the first and last events to obtain the exact start time and end time of the binlog file.
 
It is easier to read the first event format description event. seek skips the file header and reads the event header. It takes a little time to read the last event. Because the event length is not fixed. For rotation events, in addition to the event header, there is a 64-bit integer start position offset and the file name of the next binlog. The uncertain part is the last part of the file name. Fortunately, the offset is a fixed value: 4 (that is, skipping the file header), so you can read it from the back and use it as a marker to check whether the file name has been read. You can skip the file name and offset to read the event header of the last event.
 
The php code is as follows:
 
<? Php
/**
* Read binlog info
*
* A mysql binlog file is begin with a head "\ xfebin" and then log evnets.
* First event is a format description event, the last event is a rotate event.
*
* For more infomation about mysql binlog format, see http://forge.mysql.com/wiki/MySQL_Internals_Binary_Log
*/
Class BinlogInfo {
Const EVENT_HEAD_SIZE = 19;
Const FORMAT_DESCRIPTION_EVENT_DATA_SIZE = 59;
Const BINLOG_HEAD = "\ xfebin ";
Const FORMAT_DESCRIPTION_EVENT = 15;
Const ROTATE_EVENT = 4;
 
Private $ eventHeadPackStr = '';
Private $ formatDescriptionEventDataPackStr = '';
 
Function _ construct (){
$ This-> eventHeadPackStr = $ this-> eventHeadPackStr ();
$ This-> formatDescriptionEventDataPackStr = $ this-> formatDescriptionEventDataPackStr ();
}
 
Protected function eventHeadPackStr (){
$ Event_header_struct = array (
'Timestamp' => 'l ',
'Type _ Code' => 'C ',
'Server _ id' => 'l ',
'Event _ length' => 'l ',
'Next _ position' => 'l ',
'Flags' =>'s ',
);
Return $ this-> toPackStr ($ event_header_struct );
}
 
Protected function formatDescriptionEventDataPackStr (){
$ Format_description_event_data_struct = array (
'Binlog _ version' =>'s ',
'Server _ version' => 'a50 ',
'Create _ timestamp' => 'l ',
'Head _ length' => 'C'
);
Return $ this-> toPackStr ($ format_description_event_data_struct );
}
 
Protected function toPackStr ($ arr ){
$ Ret = '';
Foreach ($ arr as $ k =>$ v ){
$ Ret. = '/'. $ v. $ k;
}
$ Ret = substr ($ ret, 1 );
Return $ ret;
}
 
/**
* @ Param resource $ file
*
* Mysql binlog file begin with a 4 bytes head: "\ xfebin ".
*/
Protected function isBinlog ($ file ){
Rewind ($ file );
$ Head = fread ($ file, strlen (self: BINLOG_HEAD ));
Return $ head = self: BINLOG_HEAD;
}
 
/**
* @ Param resource $ file
*
* Format description event is the first event of a binlog file
*/
Protected function readFormatDescriptionEvent ($ file ){
Fseek ($ file, strlen (self: BINLOG_HEAD), SEEK_SET );
$ Head_str = fread ($ file, self: EVENT_HEAD_SIZE );
$ Head = unpack ($ this-> eventHeadPackStr, $ head_str );
If ($ head ['Type _ Code']! = Self: FORMAT_DESCRIPTION_EVENT ){
Return null;
}
$ Data_str = fread ($ file, self: FORMAT_DESCRIPTION_EVENT_DATA_SIZE );
$ Data = unpack ($ this-> formatDescriptionEventDataPackStr, $ data_str );
 
Return array ('head' => $ head, 'data' => $ data );
}
 
/**
* @ Param resource $ file
*
* Rotate event is the last event of a binglog.
* After event header, there is a 64bit int indicate the first event
* Position of next binlog file and next binlog file name without \ 0 at end.
* The position is always be 4 (hex: 0400000000000000 ).
*
*/
Protected function readRotateEvent ($ file)
{
/**
* Rotate event size is 19 (head size) + 8 (pos) + len (filename ).
* 100 bytes can contain a filename which length less than 73 bytes and
* It is short than the length of format description event so filesize-
* Bufsize will never be negative.
*/
$ Bufsize = 100;
$ Size_pos = 8;
Fseek ($ file,-$ bufsize, SEEK_END );
$ Buf = fread ($ file, $ bufsize );
$ Min_begin = strlen (self: BINLOG_HEAD) + self: EVENT_HEAD_SIZE + $ size_pos;
$ OK = false;
For ($ I = $ bufsize-1; $ I >$ min_begin; $ I --){
If ($ buf [$ I] = "\ 0 "){
$ OK = true;
Break;
}
}
If (! $ OK ){
Return null;
}
$ Next_filename = substr ($ buf, $ I + 1 );
 
$ Head_str = substr ($ buf, $ I + 1-$ size_pos-self: EVENT_HEAD_SIZE, self: EVENT_HEAD_SIZE );
$ Head = unpack ($ this-> eventHeadPackStr, $ head_str );
If ($ head ['Type _ Code']! = Self: ROTATE_EVENT ){
Return null;
}
Return array ('head' => $ head, 'nextfile' => $ next_filename );
}
 
/**
* @ Param string $ path to binlog file
*/
Function read ($ path ){
$ File = fopen ($ path, 'R ');
If (! $ File ){
Return null;
}
If (! $ This-> isBinlog ($ file )){
Fclose ($ file );
Return null;
}
 
$ Fde = $ this-> readFormatDescriptionEvent ($ file );
$ Re = $ this-> readRotateEvent ($ file );
Fclose ($ file );
Return array (
'Beginat' => $ fde ['head'] ['timestamp'],
'Enabled' => $ re ['head'] ['timestamp'],
'Nextfile' => $ re ['nextfile'],
'Serverversion' => $ fde ['data'] ['server _ version'],
);
}
}