mysql binlog解析_MySQL Binlog解析（1）

一、Binlog File

Binlog files start with a Binlog File Header followed by a series of Binlog Event

Binlog文件从一个Binlog文件头开始，接着是一系列的Binlog事件。

1 Binlog File Header(文件头)

A binlog file starts with a Binlog File Header [ fe 'bin' ]

每一个binlog文件头都是fe bin，十六进制如下(fe 62 69 6e)：

$ hexdump -C /tmp/binlog-test.log

00000000 fe 62 69 6e 19 6f c9 4c 0f 01 00 00 00 66 00 00 |.bin.o.L.....f..|

00000010 00 6a 00 00 00 00 00 04 00 6d 79 73 71 6c 2d 70 |.j.......mysql-p|

00000020 72 6f 78 79 2d 30 2e 37 2e 30 00 00 00 00 00 00 |roxy-0.7.0......|

2 Binlog Event(binlog事件)

The events contain the actual data that should be shipped from the master to the slave. Depending on the use, different events are sent.

事件包含主服务器发送到slave实际数据。根据实际使用情况，从而发送不同的事件。

The first event is either a START_EVENT_V3 or a FORMAT_DESCRIPTION_EVENT while the last event is either a STOP_EVENT or a ROTATE_EVENT.

第一个事件要么是START_EVENT_V3，要么是FORMAT_DESCRIPTION_EVENT，而最后一个事件要么是STOP_EVENT，要么是ROTATE_EVENT。在MySQL5.0以上版本中(binlog版本4)，第一个事件都是FORMAT_DESCRIPTION_EVENT，最后一个是ROTATE_EVENT

A binlog event starts with a Binlog Event header and is followed by a Binlog Event Type specific data part

每个binlog事件都以一个binlog事件头开始，然后是一个binlog事件类型特定的数据部分。

2.1 FORMAT_DESCRIPTION_EVENT

A format description event is the first event of a binlog for binlog-version 4. It describes how the other events are layed out.added in MySQL 5.0.0 as replacement for START_EVENT_V3

格式描述事件是binlog(版本4)的第一个事件。它描述了其他事件是如何发生的。在MySQL5.0版本中添加用来替代START_EVENT_V3事件

1) Event header(事件头)

The binlog event header starts each event and is either 13 or 19 bytes long, depending on the binlog version.

事件头根据binlog版本不同，分为13字节和19字节。mysql5+版本都是binlog v4版本，都是19个字节。

Binlog header field(字段组成)：

timestamp (4) -- seconds since unix epoch：第一个字段是时间戳，占4个字节

event_type(1) -- see Binlog Event Type ：第二个字段是事件类型，占1个字节

server_id (4) -- server-id of the originating mysql-server. Used to filter out events in circular replication ：第三个字段是源mysql主机的serverid，占4个字节，用于过滤循环复制中的事件。

event_size (4) -- size of the event (header, post-header, body)：第四个字段是事件大小，占4个字节，包括事件头、事件体的总大小

log_pos (4) -- position of the next event：第五个字段是位置，占4个字节，表示下一个事件的位置

flags (2) -- see Binlog Event Flag：最后一个字段标志状态，占2个字节

2) Event body(事件体)

Binlog body field(字段组成)：

binlog-version (2) -- version of this binlog format. #binlog文件版本号，2个字节

mysql-server version (string.fix_len) -- [len=50] version of the MySQL Server that created the binlog. The string is evaluted to apply work-arounds in the slave. #创建binlog的MySQL服务器版本，50个字节

create_timestamp (4) -- seconds since Unix epoch when the binlog was created #binlog创建时的时间戳，4个字节

event_header_length (1) -- length of the Binlog Event Header of next events. Should always be 19。

event type header length (string.EOF) -- a array indexed by Binlog Event Type - 1 to extract the length of the event specific header. 这些字节由已经定义的event的个数决定，5.6，5.7为39个，也就是39个字节说明了他们fixed data(posted header)的长度

CRC32(4): CRC32校验位,4个字节，文档上没有该字段，我通过分析binlog文件加上的。

3)【实战解析】：

分析FORMAT_DESCRIPTION_EVENT在binlog文件中的实际存储：

[root@shvm-5-39 mysql.bin]# /usr/local/mysql/bin/mysqlbinlog -vvv --base64-output='decode-rows' shvm-5-39.000001

# at 4

#171103 10:28:01 server id 2490050396 end_log_pos 123 CRC32 0x6c7b82a6 Start: binlog v 4, server v 5.7.19-log created 171103 10:28:01 at startup

ROLLBACK/*!*/;

# at 123

#171103 10:28:01 server id 2490050396 end_log_pos 154 CRC32 0x15e3db98 Previous-GTIDs

# [empty]

# at 154

#171103 10:28:04 server id 2490050396 end_log_pos 177 CRC32 0xbfbf2ea2 Stop

SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;

DELIMITER ;

# End of log file

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

--------------------------------------------------------------------------------------------------------

下面是十六进制格式：

[root@shvm-5-39 mysql.bin]# hexdump -C shvm-5-39.000001

00000000 fe 62 69 6e 31 d4 fb 59 0f 5c 27 6b 94 77 00 00 |.bin1..Y.\'k.w..|

00000010 00 7b 00 00 00 00 00 04 00 35 2e 37 2e 31 39 2d |.{.......5.7.19-|

00000020 6c 6f 67 00 00 00 00 00 00 00 00 00 00 00 00 00 |log.............|

00000030 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|

00000040 00 00 00 00 00 00 00 00 00 00 00 31 d4 fb 59 13 |...........1..Y.|

00000050 38 0d 00 08 00 12 00 04 04 04 04 12 00 00 5f 00 |8............._.|

00000060 04 1a 08 00 00 00 08 08 08 02 00 00 00 0a 0a 0a |................|

00000070 2a 2a 00 12 34 00 01 a6 82 7b 6c 31 d4 fb 59 23 |**..4....{l1..Y#|

00000080 5c 27 6b 94 1f 00 00 00 9a 00 00 00 80 00 00 00 |\'k.............|

00000090 00 00 00 00 00 00 98 db e3 15 34 d4 fb 59 03 5c |..........4..Y.\|

000000a0 27 6b 94 17 00 00 00 b1 00 00 00 00 00 a2 2e bf |'k..............|

000000b0 bf |.|

000000b1

-------------------------------------------------------------------------------------------------------

分析如下：

第一部分：binlog文件头，不同于事件头。上面解释过，前4个字节 fe 62 69 6e这个魔数表示文件头，固定的。

第二部分：接下来就是binlog事件了，事件分事件头和事件体，下面分析FORMAT_DESCRIPTION_EVENT如何存储的？

1) 31 d4 fb 59：前4个字节代表字段timestamp，采用小端字节序，值是0x59fbd431 换算成十进制：5X16^7+9X16^6+f*16^5+b*16^4+d*16^3+4X16^2+3*16^1+1X16^0=1509676081 换成时间：2017-11-03 10:28:01

2) 0f:第5个字节代表event_type=15，代表是FORMAT_DESCRIPTION_EVENT

3) 5c 27 6b 94：再4个字节代表server_id=0x946b275c 换算成十进制：9x16^7+4x16^6+6x16^5+b*16^4+2x16^3+7x16^2+5x16+c=2490050396

4) 77 00 00 00：这4个字节代表event_size=0x00000077 换成十进制：7*16^1+7*16^0=119

5) 7b 00 00 00：4个字节代表log_pos=0x0000007b 换成十进制：123，表示下一个event起始偏移，对应binlog文件中end_log_pos=123

6) 00 00：2个字节表示flags=0，表示该binlog文件已经被关闭，如果打开时为1，最后一个正在写入的binlog文件flags=1

7) 04 00：2个字节表示binlog版本号=4

8) 35 2e 37 2e 31 39 2d 6c 6f 67 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00：这50字节MySQL服务器版本server v 5.7.19-log

9) 31 d4 fb 59：0x59fbd431表示binlog创建时间戳，1509676081 换成时间：2017-11-03 10:28:01

10)13：这一个字节表示event_header_length=0x13=19，事件头是19字节

11)38 0d 00 08 00 12 00 04 04 04 04 12 00 00 5f 00 04 1a 08 00 00 00 08 08 08 02 00 00 00 0a 0a 0a 2a 2a 00 12 34 00 01：这39个字节表示有39个event

12)a6 82 7b 6c：0x6c7b82a6表示CRC32的值，同binlog文件中的值

以上分析的内容同binlog文件里看到的一致。

2.2 ROTATE_EVENT

1)触发条件

When a binary log file exceeds a size limit, a ROTATE_EVENT is written at the end of the file that points to the next file in the squence. This event is information for the slave to know the name of the next binary log it is going to receive.

# 当一个二进制日志文件超过大小限制时(max_binlog_size)，ROTATE_EVENT将被触发，在文件的末尾写入，指向下一个文件。这个事件是给slave的信息，以便知道它将要接收的下一个二进制日志的名称。

# 当执行flush logs触发ROTATE_EVENT并生成新二进制文件，宕机不能触发该事件

2)存储格式

Fixed data part:

8 bytes. The position of the first event in the next log file. Always contains the number 4 (meaning the next event starts at position 4 in the next binary log). This field is not present in v1; presumably the value is assumed to be 4.

Variable data part:

The name of the next binary log. The filename is not null-terminated. Its length is the event size minus the size of the fixed parts.

1、事件头部分上面已经说过，占用19个字节，所有事件都是一样的。

2、事件体部分：

固定数据部分：8个字节，记录下一个二进制文件第一个事件的位置，该值总是等于4，也就是在新的binlog文件at 4.

可变数据部分：记录下一个binlog文件名，该值不能为空字符。长度等于 event size减去固定数据部分。

2.3 STOP_EVENT

1)触发条件

A master writes the event to the binary log when it shuts down

A slave writes the event to the relay log when it shuts down or when a RESET SLAVE statement is executed

master开启binlog，当数据库shut down时

slave开启binlog，当数据库shut down或者执行reset slave语句时

2)存储格式

1、事件头，占用19个字节。

2、事件体部分：

固定数据部分：Empty

可变数据部分：Empty

CRC校验：占用4个字节

一共占用23个字节

2.4 QUERY_EVENT

The query event is used to send text querys right the binlog.

查询事件用于向binlog发送文本查询。

1)触发条件

QUERY_EVENT类型的事件通常在以下几种情况下使用：

# 事务开始时，执行的BEGIN操作

# STATEMENT格式中的DML操作

# ROW格式中的DDL操作

2)存储格式

1、事件头，占用19个字节。

2、事件体部分：

固定数据部分：

# 4 bytes. The ID of the thread that issued this statement. Needed for temporary tables. This is also useful for a DBA for knowing who did what on the master.//4个字节存储thread id

# 4 bytes. The time in seconds that the statement took to execute. Only useful for inspection by the DBA.//4个字节 存储执行时间，单位秒

# 1 byte. The length of the name of the database which was the default database when the statement was executed. This name appears later, in the variable data part. It is necessary for statements such as INSERT INTO t VALUES(1) that don't specify the database and rely on the default database previously selected by USE.//1个字节，存储执行sql默认数据库名的长度

# 2 bytes. The error code resulting from execution of the statement on the master. //2个字节存储错误编码值，0代表没有错误。如果非零一般出现在非事务性表操作，如insert...select在插入1000条到mysiam失败了，部分数据写入到了binlog文件。

# 2 bytes (not present in v1, v3). The length of the status variable block.//2个字节存储状态变量块的长度

可变数据部分：

# Zero or more status variables (not present in v1, v3). Each status variable consists of one byte code identifying the variable stored, followed by the value of the variable. The format of the value is variable-specific, as described later.

存储上下文一些set 变量，key-value形式存储，它的长度在固定数据最后一个字段已经知道

# The default database name (null-terminated).

默认的数据库名，在固定数据部分已经知道它的长度

# 00：默认的1个字节

# The SQL statement. The slave knows the size of the other fields in the variable part (the sizes are given in the fixed data part), so by subtraction it can know the size of the statement.

存储SQL语句，它的长度可以通过event-size 减去 公有事件头19 减去 固定数据部分长度 减去 可变数据的变量长度 减去可变数据的数据库名长度 减去 CRC32(4个字节) 减去 1个字节(默认00)

3)实战分析

结合hexdump出来的数据和mysqlbinlog解析出的日志进行分析：

# at 259

#180530 18:42:31 server id 2490050396 end_log_pos 370 CRC32 0x6a9644c6 Query thread_id=7322991 exec_time=0 error_code=0

use `darren`/*!*/;

SET TIMESTAMP=1527676951/*!*/;

SET @@session.pseudo_thread_id=7322991/*!*/;

SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;

SET @@session.sql_mode=0/*!*/;

SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;

/*!\C utf8 *//*!*/;

SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;

SET @@session.lc_time_names=0/*!*/;

SET @@session.collation_database=DEFAULT/*!*/;

alter table t add index idx_1(id)

/*!*/;

-------公有事件头--------

19个字节这里再分析

--------固定数据部分(私有事件头)-----

1、thread_id：4个字节，6f bd 6f 00，十进制即7322991.存储了不同连接或会话的线程ID

2、execution time：4个字节，00 00 00 00，查询从开始执行到记录到binlog所花时间，单位s

3、schema length：1个字节，06，schema字符长度6。即darren

4、error code：2个字节，00 00，错误码

5、status-var length：2个字节，23 00，status-var的长度，为35

---------可变数据部分(事件体)--------

6、status var:35个字节，即00 00 00 00 00 01 00 00 00 00 00 00 00 00 06 03 73 74 64 04 21 00 21 00 21 00 0c 01 64 61 72 72 65 6e 00。以KV对的形式保存起来的一些了由SET命令设置的上下文信息。如SET @@session.sql_mode=0 等

7、schema：6字节，即64 61 72 72 65 6e，查询对应的asci码，刚好对应darren。表示选择的数据库是darren

8、00：默认1个字节

9、sql文本：33个字节，即：61 6c 74 65 72 20 74 61 62 6c 65 20 74 20 61 64 64 20 69 6e 64 65 78 20 69 64 78 5f 31 28 69 64 29，query的文本格式，里面存储的是BEGIN或原生的SQL等