MySql的binlog协议解析

本文主要是介绍MySql的binlog的构成以及如何用java简单去解析

1、Magic Number

binlog文件用任意文件编辑器打开后展示的是多个16进制的数字组成,其中前四位为固定magic number（魔数） 和大多数文件的魔数一样，作用都是根据这几个字节的内容确定文件类型，binlog的魔数是以下4个字节 fe 62 69 6e

在这里指代fe bin，代表文件的格式(ASCII，前面的fe不是合法的ASCII)，用java获取一个binlog前面四个字节

public static void main(String[] args) throws IOException {
        DataInputStream input = new DataInputStream(new FileInputStream("E:\\mysql\\data\\mysql-bin.000011"));
        byte[] b = new byte[4];
        input.read(b);
        for(byte single : b){
            System.out.println(Integer.toHexString(single & 0xFF));
        }
    }

结果输出

fe 62 69 6e

2、Event

在magic number之后，Binlog文件可能由多个不同类型的event构成。每个event都包括Binlog Event Header、 Post-Header以及PayLoad三部分组成。详情可以看以下链接

https://dev.mysql.com/doc/refman/8.0/en/replication.html

2.1、Binlog Event Header

Binlog Event Header作为binlog里的event开始，后面跟着一系列的event。不同版本的Binlog Event Header长度不同，但一般都为13或者19个字节。以19个字节为例 一般由以下几个域构成(我们称为payload)

timestamp (4)
event_type (1)
server_id (4)
event_size (4)
log_pos (4) //binlog-version > 1:
flags (2) //binlog-version > 1:

Binlog Event Header前面4个字节为时间发生的时间戳，应用服务器是X86架构，在存储的时候为小端模式，即低位字节排放在内存的低地址端，举个例子 当取出timestamp的4个字节为 3d e2 ca 5d时，翻译成时间就是 1573577277。

    public static void main(String[] args) throws IOException {
        DataInputStream input = new DataInputStream(new FileInputStream("E:\\mysql\\data\\mysql-bin.000011"));
        byte[] b = new byte[1024];
        input.read(b);
        long num = ((long)(b[7] & 0XFF) << 24) +
                ((long)(b[6] & 0XFF) << 16) +
                ((long)(b[5] & 0XFF) << 8) +
                ((long)(b[4] & 0XFF));
        System.out.println(num);
    }

接下来1个字节表示Post-Header的事件类型，这里每个事件的Binlog Event Header都是13或者19个字节，但是 事件本身的字节大小随事件类型不同而不同。这里解析event时需要根据这1个字节的event_type确定。 比较常用的事件类型和16进制(1个字节)如下

0x02 QUERY_EVENT 

0x04 ROTATE_EVENT

0x0f FORMAT_DESCRIPTION_EVENT

0x10 XID_EVENT

0x13 TABLE_MAP_EVENT

0x1d ROWS_QUERY_EVENT

0x1e WRITE_ROWS_EVENTv2

0x1f UPDATE_ROWS_EVENTv2

0x20 DELETE_ROWS_EVENTv2

0x21 GTID_EVENT

0x22 ANONYMOUS_GTID_EVENT

0x23 PREVIOUS_GTIDS_EVENT

server_id：标识产生该事件的MySQL服务器的server-id，目的是过滤循环赋值的事件（circular replication）

event_size：事件大小，这个里的事件大小应该是header和post-header和PayLoad三个之和。

前面提到binlog的大小有的为13个字节、有的为19个字节，区别在与19个字节的binlog文件多了log_pos和flags。

binlog版本大于1时 将是19个字节

log_pos：下一个event开始的位置，可以看到与前一个值刚好差4，也就是魔数的长度。

flags：每个events中flags代表的意义大致相同。00 00 代表binlog已经关闭， 00 01代表binlog仍开启
下面是用java解析Binlog Event Header

class EventHeader{
    private long timestamp;
    private short eventType;
    private long serverId;
    private long eventSize;
    private long logPosition;
    private int flags;

    public EventHeader(long timestamp, short eventType, long serverId, long eventSize, long logPosition, int flags) {
        this.timestamp = timestamp;
        this.eventType = eventType;
        this.serverId = serverId;
        this.eventSize = eventSize;
        this.logPosition = logPosition;
        this.flags = flags;
    }

    public void setTimestamp(long timestamp) {
        this.timestamp = timestamp;
    }

    public void setEventType(short eventType) {
        this.eventType = eventType;
    }

    public void setServerId(long serverId) {
        this.serverId = serverId;
    }

    public void setEventSize(long eventSize) {
        this.eventSize = eventSize;
    }

    public void setLogPosition(long logPosition) {
        this.logPosition = logPosition;
    }

    public void setFlags(int flags) {
        this.flags = flags;
    }

    @Override
    public String toString() {
        return "EventHeader{" +
                "timestamp=" + timestamp +
                ", eventType=" + eventType +
                ", serverId=" + serverId +
                ", eventSize=" + eventSize +
                ", logPosition=" + logPosition +
                ", flags=" + flags +
                '}';
    }
}

public static void main(String[] args) throws IOException {
        DataInputStream input = new DataInputStream(new FileInputStream("E:\\mysql\\data\\mysql-bin.000011"));
        byte[] b = new byte[1024];
        input.read(b);
        long timestamp = ((long)(b[7] & 0XFF) << 24) +
                ((long)(b[6] & 0XFF) << 16) +
                ((long)(b[5] & 0XFF) << 8) +
                ((long)(b[4] & 0XFF));
        short eventType = b[8];
        long serverId = ((long)(b[12] & 0XFF) << 24) +
                ((long)(b[11] & 0XFF) << 16) +
                ((long)(b[10] & 0XFF) << 8) +
                ((long)(b[9] & 0XFF));
        long eventSize = ((long)(b[16] & 0XFF) << 24) +
                ((long)(b[15] & 0XFF) << 16) +
                ((long)(b[14] & 0XFF) << 8) +
                ((long)(b[13] & 0XFF));
        long logPosition = ((long)(b[20] & 0XFF) << 24) +
                ((long)(b[19] & 0XFF) << 16) +
                ((long)(b[18] & 0XFF) << 8) +
                ((long)(b[17] & 0XFF));
        int flags = (short)(((b[22] & 0xFF) << 8))+ (short)(((b[21] & 0xFF)));
        EventHeader eventHeader = new EventHeader(timestamp, eventType, serverId, eventSize, logPosition, flags);
        System.out.println(eventHeader);
    }

输出结果

EventHeader{timestamp=1573577277, eventType=15, serverId=1, eventSize=120, logPosition=124, flags=0}

这里的注意点有几个

• 所有的计算都是采用小端模式，即先计算高位左移后再加上底位。
• timestamp时间戳是算成秒的，如果计算毫秒还要乘上1000
• eventType是10进制的15，对应16进制是0x0f 就是事件FORMAT_DESCRIPTION_EVENT
• eventSize是header+post-header+payload(有时也是data)，binlog里通过文件的字节大小表示position，这里下个event position=124 相差就是因为magic number。

Post-Header对于同一类event大小都是相同的，不同的是payload的大小和内容(废话) 由上面的Binlog Event Header可以得知当前的事件是 FORMAT_DESCRIPTION_EVENT，根据MySql官方给出的信息

https://dev.mysql.com/doc/internals/en/format-description-event.html

这个事件用来描述其他事件的布置方式。 注意 FORMAT_DESCRIPTION_EVENT 并没有post-header，从给出的官方文档可以看出只有payload，其中payload 组成有以下几个域

binlog-version (2) 当前binlog的版本，一般有1，3，4
mysql-server version （String[50]） MySql的版本，MySql是用C++写的，所以String[50]应该是char[50]
create_timestamp (4) binlog创建的时间 binlog文件是可追加的，这里应该理解成binlog的追加时间
event_header_length(1) 下一个事件的Binlog Event Header的大小，一般是19
event type header length(String[?])  一个数组表示Binlog Event Type 主要是列出所有事件的Post-Header的大小，每个字节表示一种事件类型。

结合上面的Binlog Event Header 解析如下

class EventHeader{
    private long timestamp;
    private short eventType;
    private long serverId;
    private long eventSize;
    private long logPosition;
    private int flags;

    public EventHeader(long timestamp, short eventType, long serverId, long eventSize, long logPosition, int flags) {
        this.timestamp = timestamp;
        this.eventType = eventType;
        this.serverId = serverId;
        this.eventSize = eventSize;
        this.logPosition = logPosition;
        this.flags = flags;
    }

    public void setTimestamp(long timestamp) {
        this.timestamp = timestamp;
    }

    public void setEventType(short eventType) {
        this.eventType = eventType;
    }

    public void setServerId(long serverId) {
        this.serverId = serverId;
    }

    public void setEventSize(long eventSize) {
        this.eventSize = eventSize;
    }

    public void setLogPosition(long logPosition) {
        this.logPosition = logPosition;
    }

    public void setFlags(int flags) {
        this.flags = flags;
    }

    @Override
    public String toString() {
        return "EventHeader{" +
                "timestamp=" + timestamp +
                ", eventType=" + eventType +
                ", serverId=" + serverId +
                ", eventSize=" + eventSize +
                ", logPosition=" + logPosition +
                ", flags=" + flags +
                '}';
    }
}


class FormatDescriptionEventData{
    private int binlogVersion;
    private String mysqlServerVersion;
    private long createTime;
    private short nextEventHeaderLength;
    private short[] postEventHeader;

    public FormatDescriptionEventData(int binlogVersion, String mysqlServerVersion, long createTime, short nextEventHeaderLength, short[] postEventHeader) {
        this.binlogVersion = binlogVersion;
        this.mysqlServerVersion = mysqlServerVersion;
        this.createTime = createTime;
        this.nextEventHeaderLength = nextEventHeaderLength;
        this.postEventHeader = postEventHeader;
    }

    @Override
    public String toString() {
        return "FormatDescriptionEventData{" +
                "binlogVersion=" + binlogVersion +
                ", mysqlServerVersion='" + mysqlServerVersion + '\'' +
                ", createTime=" + createTime +
                ", nextEventHeaderLength=" + nextEventHeaderLength +
                ", postEventHeader=" + Arrays.toString(postEventHeader) +
                '}';
    }
}
public class Main implements Serializable {

    public static void main(String[] args) throws IOException {
        DataInputStream input = new DataInputStream(new FileInputStream("E:\\mysql\\data\\mysql-bin.000011"));
        byte[] b = new byte[1024];
        input.read(b);
        long timestamp = ((long)(b[7] & 0XFF) << 24) +
                ((long)(b[6] & 0XFF) << 16) +
                ((long)(b[5] & 0XFF) << 8) +
                ((long)(b[4] & 0XFF));
        short eventType = b[8];
        long serverId = ((long)(b[12] & 0XFF) << 24) +
                ((long)(b[11] & 0XFF) << 16) +
                ((long)(b[10] & 0XFF) << 8) +
                ((long)(b[9] & 0XFF));
        long eventSize = ((long)(b[16] & 0XFF) << 24) +
                ((long)(b[15] & 0XFF) << 16) +
                ((long)(b[14] & 0XFF) << 8) +
                ((long)(b[13] & 0XFF));
        long logPosition = ((long)(b[20] & 0XFF) << 24) +
                ((long)(b[19] & 0XFF) << 16) +
                ((long)(b[18] & 0XFF) << 8) +
                ((long)(b[17] & 0XFF));
        int flags = (short)(((b[22] & 0xFF) << 8))+ (short)(((b[21] & 0xFF)));
        EventHeader eventHeader = new EventHeader(timestamp, eventType, serverId, eventSize, logPosition, flags);
        System.out.println(eventHeader);

        int binlogVersion = ((short)(b[24] & 0XFF << 8)) + ((short)(b[23] & 0XFF));
        byte[] b2 = new byte[50];
        System.arraycopy(b,25,b2,0,50);
        String mysqlVersion = bytetoString(b2);
        long createTime = ((long)(b[78] & 0XFF) << 24) +
                ((long)(b[77] & 0XFF) << 16) +
                ((long)(b[76] & 0XFF) << 8) +
                ((long)(b[75] & 0XFF));
        short nextEventHeaderLength = b[79];
        int eventMount = (int)(eventSize - 19 - 57 - 4);
        short[] otherEventPostHeaderSize = new short[eventMount];
        for(int i=0;i<eventMount;i++){
            otherEventPostHeaderSize[i] = b[i+79];
        }
        long crc32CheckSum = ((long)(b[120] & 0XFF) << 24) +
                ((long)(b[119] & 0XFF) << 16) +
                ((long)(b[118] & 0XFF) << 8) +
                ((long)(b[117] & 0XFF));
        FormatDescriptionEventData formatDescriptionEventData = new FormatDescriptionEventData(binlogVersion,
                mysqlVersion.trim(),createTime,nextEventHeaderLength,otherEventPostHeaderSize);
        System.out.println(formatDescriptionEventData);
        System.out.println(crc32CheckSum);
    }


    public static String bytetoString(byte[] bytearray) {
        String result = "";
        char temp;

        int length = bytearray.length;
        for (int i = 0; i < length; i++) {
            temp = (char) bytearray[i];
            result += temp;
        }
        return result;
    }
}

输出结果

EventHeader{timestamp=1573577277, eventType=15, serverId=1, eventSize=120, logPosition=124, flags=0}
FormatDescriptionEventData{binlogVersion=4, mysqlServerVersion='8.0.11', createTime=1573577277, nextEventHeaderLength=19, postEventHeader=[19, 0, 13, 0, 8, 0, 0, 0, 0, 4, 0, 4, 0, 0, 0, 96, 0, 4, 26, 8, 0, 0, 0, 8, 8, 8, 2, 0, 0, 0, 10, 10, 10, 42, 42, 0, 18, 52, 0, 10]}
1006701056

最后一个是checksum