Mysql binlog知识

Posted on Edited on In

由于定制的Otter出现增量丢数据的问题，底层用到了canal，用来解析binlog数据，这篇文章就详细的讲些binlog相关的知识点。

Mysql 日志概述

MySQL中有六种日志文件，分别是：重做日志（redo log）、回滚日志（undo log）、二进制日志（bin log）、错误日志（error log）、慢查询日志（slow query log）、一般查询日志（general log），中继日志（relay log）。
其中bin log和undo log与事务操作息息相关,bin log也与事务操作有一定的关系，这三种日志，对理解MySQL中的事务操作有着重要的意义。

Binlog

是什么

由Mysql的Server层实现,是逻辑日志,记录的是sql语句的原始逻辑，比如”给 ID=2 这一行的C字段加1”。

怎么工作的

binlog会写入指定大小的物理文件中,是追加写入的,当前文件写满则会创建新的文件写入。

产生:事务提交的时候,一次性将事务中的sql语句,按照一定的格式记录到binlog中。
清理:可设置参数expire_logs_days,在生成时间超过配置的天数之后,会被自动删除。

做什么用的

  1. 用于复制,在主从复制中，从库利用主库上的binlog进行重播(执行日志中记录的修改逻辑),实现主从同步。

  2. 用于数据库的基于时间点的还原。

3种模式

  1. statement：基于SQL语句的模式，某些语句中含有一些函数，例如 UUID,NOW 等在复制过程可能导致数据不一致甚至出错。

  2. row：基于行的模式，记录的是行的变化,很安全。但是 binlog 的磁盘占用会比其他两种模式大很多,在一些大表中清除大量数据时在 binlog 中会生成很多条语句,可能导致从库延迟变大。

  3. mixed：混合模式,根据语句来选用是 statement 还是 row 模式。表结构变更使用 statement 模式来记录,如果 SQL 语句是 update 或者 delete 语句,那么使用row模式。

Redolog

是什么

由引擎层的InnoDB引擎实现,是物理日志,记录的是物理数据页修改的信息,比如”某个数据页上内容发生了哪些改动”

怎么工作的

原理:当一条数据需要更新时,InnoDB会先将更新操作记录到rodolog中,并更新到内存中,这个更新就算是完成了。InnoDB引擎会在mysql空闲时将这些更新操作更新到磁盘中(数据文件)。
(这个就是MySql经常说到的WAL技术,Write-Ahead Logging ,关键点是先写日志,再写磁盘)

存储:redolog是顺序写入指定大小的物理文件中的。是循环写入的,当文件快写满时,会边擦除边刷磁盘,即擦除日志记录(redolog file)并将数据刷到磁盘中。

做什么用的

  1. 提供crash-safe 能力(崩溃恢复),确保事务的持久性。
    数据库突然崩溃,有些数据并未刷到数据文件中,当重启MySQL数据库,会从redolog中未刷到磁盘的数据刷到磁盘中。

  2. 利用WAL技术推迟物理数据页的刷新，从而提升数据库吞吐，有效降低了访问时延。

Undolog

是什么

由引擎层的InnoDB引擎实现,是逻辑日志,记录数据修改被修改前的值,比如”把Name=’B’ 修改为Name = ‘B2’ ，那么undo日志就会用来存放Name=’B’的记录”

怎么工作的

当一条数据需要更新前,会先把修改前的记录存储在undolog中,如果这个修改出现异常,,则会使用undo日志来实现回滚操作,保证事务的一致性。

当事务提交之后，undo log并不能立马被删除,而是会被放到待清理链表中,待判断没有事物用到该版本的信息时才可以清理相应undolog。

做什么用的

保存了事务发生之前的数据的一个版本，用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读

总结

3种日志在事物执行过程中的工作，执行sql如下：

1
2
3
4
BEGIN;
update name = 'wk' from user where id = 1;
update name = 'river' from user where id = 2;
Commit;

A. 将id=1的行name的值读取到内存中
B. 记录id=1的行name=ken到undo log
C. 修改name=wk
D. 记录相应数据页的修改到redo log,并更新内存中的数据
E. 将id=2的行name的值读取到内存中
F. 记录id=2的行name=lj到undo log
G. 修改name=lj
H. 记录相应数据页的修改到redo log,并更新内存中的数据
I. 记录事务中所有SQL的逻辑操作到bin log
J. 提交事务
K. MySql服务器空闲时,把redo log中的物理数据页刷到磁盘数据文件中

特性

  1. 保证原子性:更新数据前,记录undo log,为保证在更新数据时发生异常导致更新失败,这时可以使用undo log对数据进行回滚(回滚内存中的数据,并会在redo log中记录回滚操作)

  2. 保证持久性:每更新数据后,记录redo log,为防止服务器突然宕机,导致没有把数据刷到磁盘中,每次重启MySql服务器都会从redo log将脏页(未能及时写到磁盘的数据页)刷到磁盘

  3. 两阶段提交,保证数据的一致性:
    先写redo log,再写bin log,完成后才能认为事务是完整的。从库主要通过bin log进行同步,但如果服务器异常宕机,可能会造成主从数据不一致的情况。

    a. 写完redo log宕机,bin log还没写
    因为两阶段提交机制,MySql会判断redo log 和 bin log是否都完整,如果不完整,则认为事务未提交,在从redo log 刷数据时,就不会刷未提交的事务的数据

    b. 在写bin log的中途宕机
    已经写了部分的bin log,但是没有写完整（binlog 是否完整会有一个标识符标识），仍然认为事务未提交。崩溃恢复和主从复制时,都不会使用未提交的数据,从而实现数据的一致性。

    c. bin log写完了,但未提交事务
    两阶段提交机制认为,只要redo log和bin log都是完整的,则可以认为事务提交了。

binlog格式解析

binlog格式如下：

  • binlog文件以一个值为0Xfe62696e的魔数开头，这个魔数对应0xfe ‘b’’i’’n’。
  • binlog由一系列的binlog event构成。每个binlog event包含header和data两部分。
    • header部分提供的是event的公共的类型信息，包括event的创建时间，服务器等等。
    • data部分提供的是针对该event的具体信息，如具体数据的修改。
  • 从mysql5.0版本开始，binlog采用的是v4版本，第一个event都是format_desc event 用于描述binlog文件的格式版本，这个格式就是event写入binlog文件的格式。关于之前版本的binlog格式，可以参见http://dev.mysql.com/doc/internals/en/binary-log-versions.html
  • 接下来的event就是按照上面的格式版本写入的event。
  • 最后一个rotate event用于说明下一个binlog文件。

接下来分析下几种常见的event，其他的event类型可以参见官方文档。event数据结构如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
+=====================================+
| event  | timestamp         0 : 4    |
| header +----------------------------+
|        | type_code         4 : 1    |
|        +----------------------------+
|        | server_id         5 : 4    |
|        +----------------------------+
|        | event_length      9 : 4    |
|        +----------------------------+
|        | next_position    13 : 4    |
|        +----------------------------+
|        | flags            17 : 2    |
|        +----------------------------+
|        | extra_headers    19 : x-19 |
+=====================================+
| event  | fixed part        x : y    |
| data   +----------------------------+
|        | variable part              |
+=====================================+

format_desc event

作用：binlog文件的第一个event，记录版本号等元数据信息。

对照官方文档中的说明来看下format_desc event格式：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
+=====================================+
| event  | timestamp         0 : 4    |
| header +----------------------------+
|        | type_code         4 : 1    | = FORMAT_DESCRIPTION_EVENT = 15
|        +----------------------------+
|        | server_id         5 : 4    |
|        +----------------------------+
|        | event_length      9 : 4    | >= 91
|        +----------------------------+
|        | next_position    13 : 4    |
|        +----------------------------+
|        | flags            17 : 2    |
+=====================================+
| event  | binlog_version   19 : 2    | = 4
| data   +----------------------------+
|        | server_version   21 : 50   |
|        +----------------------------+
|        | create_timestamp 71 : 4    |
|        +----------------------------+
|        | header_length    75 : 1    |
|        +----------------------------+
|        | post-header      76 : n    | = array of n bytes, one byte per event
|        | lengths for all            |   type that the server knows about
|        | event types                |
+=====================================+

前面4个字节是固定的magic number,值为0x6e6962fe。

接着是一个format_desc event，先看下19个字节的header。这19个字节中前4个字节0x567fb2b8是时间戳，第5个字节0x0f是event type，接着4个字节0x00000004是server_id，再接着4个字节0x00000067是长度103，然后的4个字节0x0000006b是下一个event的起始位置107，接着的2个字节的0x0001是flag（1为LOG_EVENT_BINLOG_IN_USE_F，标识binlog还没有关闭，binlog关闭后，flag会被设置为0），这样4+1+4+4+4+2=19个字节的公共头就完了(extra_headers暂时没有用到)。

然后是这个event的data部分，event的data分为Fixed data和Variable data两部分，其中Fixed data是event的固定长度和格式的数据，Variable data则是长度变化的数据，比如format_desc event的Fixed data长度是0x54=84个字节。下面看下这84=2+50+4+1+27个字节的分配：开始的2个字节0x0004为binlog的版本号4，接着的50个字节为mysql-server版本，如我的版本是5.5.46-0ubuntu0.14.04.2-log，与SELECT version();查看的结果一致。接下来4个字节是binlog创建时间，这里是0；然后的1个字节0x13是指之后所有event的公共头长度，这里都是19；接着的27个字节中每个字节为mysql已知的event（共27个）的Fixed data的长度；可以发现format_desc event自身的Variable data部分为空。

rotate event

作用：连接下一个binlog文件

前面的内容跟之前的几乎一致，除了format_desc event的flag从0x0001变成了0x0000。然后从0x567fb3c2开始是一个rotate event。依照前面的分析，前面19个字节为event的header，其event type是0x04，长度为0x2b=43，下一个event起始位置为0x96=150，然后是flag为0x0000，接着是event data部分，首先的8个字节为Fixed data部分，记录的是下一个binlog的位置偏移4，而余下来的43-19-8=16个字节为Variable data部分，记录的是下一个binlog的文件名mysql-bin.000054。对照mysqlbinlog -vv mysql-bin.000053可以验证。

query event

作用：存储statement类的信息，基于statement的binlog格式记录sql语句，在row模式下记录事务begin标签

刷新binlog，设置binlog_format=statement，创建一个表CREATE TABLEtt(ivarchar(100) DEFAULT NULL) ENGINE=InnoDB, 然后在测试表tt中插入一条数据insert into tt values('abc')，会产生3个event，包括2个query event和1个xid event。其中2个query event分别是BEGIN以及INSERT 语句，而xid event则是事务提交语句（xid event是支持XA的存储引擎才有的，因为测试表tt是innodb引擎的，所以会有。如果是myisam引擎的表，也会有BEGIN和COMMIT,只不过COMMIT会是一个query event而不是xid event）。

1
2
3
4
5
6
7
8
9
mysql> show binlog events in 'mysql-bin.000060';
+------------------+-----+-------------+-----------+-------------+--------------------------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                                   |
+------------------+-----+-------------+-----------+-------------+--------------------------------------------------------+
| mysql-bin.000060 |   4 | Format_desc |         4 |         107 | Server ver: 5.5.46-0ubuntu0.14.04.2-log, Binlog ver: 4 |
| mysql-bin.000060 | 107 | Query       |         4 |         175 | BEGIN                                                  |
| mysql-bin.000060 | 175 | Query       |         4 |         266 | use `test`; insert into tt values('abc')               |
| mysql-bin.000060 | 266 | Xid         |         4 |         293 | COMMIT /* xid=138 */                                   |
+------------------+-----+-------------+-----------+-------------+--------------------------------------------------

table_map event & write_rows event

作用：TABLE_MAP_EVENT: row模式下记录表源数据，对读取行记录提供规则参考

作用：WRITE_ROWS_EVENT/DELETE_ROWS_EVENT/UPDATE_ROWS_EVENT: row模式下记录对应行数据变化的记录

执行语句INSERT INTO trow VALUES(1, NULL), (2, 'a')，这个语句会产生一个query event，一个table_map event、一个write_rows event以及一个xid event。

1
2
3
4
5
6
7
8
9

mysql> show binlog events in 'mysql-bin.000074';
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                                   |
+------------------+-----+-------------+-----------+-------------+--------------------------------------------------------+
| mysql-bin.000074 |   4 | Format_desc |         4 |         107 | Server ver: 5.5.46-0ubuntu0.14.04.2-log, Binlog ver: 4 |
| mysql-bin.000074 | 107 | Query       |         4 |         175 | BEGIN                                                  |
| mysql-bin.000074 | 175 | Table_map   |         4 |         221 | table_id: 50 (test.trow)                               |
| mysql-bin.000074 | 221 | Write_rows  |         4 |         262 | table_id: 50 flags: STMT_END_F                         |
| mysql-bin.000074 | 262 | Xid         |         4 |         289 | COMMIT /* xid=245 */

intvar event

intvar event在binlog_format=statement时使用到，用于自增键类型auto_increment，十分重要。intval event的Fixed data部分为空，而Variable data部分为9个字节，第1个字节用于标识自增事件类型 LAST_INSERT_ID_EVENT = 1 or INSERT_ID_EVENT = 2，余下的8个字节为自增ID。创建一个测试表 create table tinc (i int auto_increment primary key, c varchar(10)) engine=innodb;，然后执行一个插入语句INSERT INTO tinc(c) values('abc');就可以看到intvar event了，这里的自增事件类型为INSERT_ID_EVENT。而如果用语句INSERT INTO tinc(i, c) VALUES(LAST_INSERT_ID()+1, 'abc')，则可以看到自增事件类型为LAST_INSERT_ID_EVENT的intvar event。

1
2
3
4
5
6
7
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                                   |
+------------------+-----+-------------+-----------+-------------
| mysql-bin.000079 |   4 | Format_desc |         4 |         107 | Server ver: 5.5.46-0ubuntu0.14.04.2-log, Binlog ver: 4 |
| mysql-bin.000079 | 107 | Query       |         4 |         175 | BEGIN                                                  |
| mysql-bin.000079 | 175 | Intvar      |         4 |         203 | INSERT_ID=1                                            |
| mysql-bin.000079 | 203 | Query       |         4 |         299 | use `test`; insert into tinc(c) values('abc')          |
| mysql-bin.000079 | 299 | Xid         |         4 |         326 | COMMIT /* xid=263 */

binlog相关命令

查看binlog_format

1
show variables like 'binlog_format'

修改binlog_format

1
set globle binlog_format='MIXED'

修改完成之后，需要重启mysql服务器；如果不重启只能针对以后新建立的连接就会生效配置，旧的连接不生效；

查看是否开启binlog

1
show variables like 'log_bin'

如果binlog没有开启，可以通过set sql_log_bin=1命令来启用;如果想停用binlog,可以使用set sql_log_bin=0。

获取binlog文件列表

1
show binary logs

查看当前正在写入的binlog文件

1
show master status

查看master上的binlog

1
show master logs

只查看第一个binlog文件的内容

1
show binlog events

查看指定binlog文件的内容

1
show binlog events in 'mysql-bin.000002'

用mysqlbinlog工具查看

基于时间查看binlog

1
mysqlbinlog --start-datetime='2020-09-10 00:00:00' --stop-datetime='2013-09-20 01:01:01' -d 库名 二进制文件

需要登录mysql服务器，然后找到binlog文件，最后用mysqlbinlog命令。

基于偏移量查看binlog

1
mysqlbinlog --start-postion=107 --stop-position=1000 -d 库名 二进制文件

需要登录mysql服务器，然后找到binlog文件，最后用mysqlbinlog命令。

远程查看

1
2
mysqlbinlog -u username -p password -hl-127.0.0.1 -P3306 \
--read-from-remote-server --start-datetime='2013-09-20 23:00:00' --stop-datetime='2013-09-20 23:30:00' mysql-bin.000001 > t.binlog

参考资料

Mysql事件详情