关于mysql:MySQL-binlog-设计

通过这篇文章你能理解到的常识

• binlog 是什么
• binlog 的作用
• binlog 的三种类型，以及各自优缺点
• binlog 文件的构造与内容

binlog 是什么

Binary Log，顾名思义是一种二进制格局的日志。具体来说，binlog 日志是一组蕴含了对 MySQL server 实例进行数据批改（update/delete/insert/...）信息的文件。

binlog 作用

• 用于复制，如主从复制
• 数据恢复

binlog 类型

1. 基于 语句 (Statement-based) 的日志记录 (SBL): 事件蕴含产生数据变动的 SQL 语句。
2. 基于 行 (Row-based) 的日志记录(RBL): 形容单个行的变动
3. 混合 (Mixed)：上述两者联合应用, 以 SBL 为主，非凡状况下切换到 RBL

binlog 为什么会有这三种类型？

一开始只有 statement-based，但 statement-based 存在不少问题，前面才有的 row 与 mixed。

SBL 长处

• 产生的日志文件少，io 次数少

SBL 毛病

• 在一些 不平安的语句上，主从复制做不到数据统一，比方

  1. 含有零碎函数的语句，可能会在正本上返回不同的值，如 RAND(), USER(),UUID(), SYSDATE() ....
  2. 更新一个有 AUTO_INCREMENT 列的表
  3. Updates using LIMIT
  4. …
• 慢 SQL 会在正本中再执行一次

RBL 长处

• 这是最平安的复制模式
• 在 INSERT/UPDATE/DELETE 语句中，正本绝对主行锁范畴更小。

RBL 毛病

• RBL 日志文件更大

总结

举荐应用 RBL，利大于弊，最新 MySQL 版本默认也是应用 RBL。

binlog 构造

binlog 文件构造

binlog.index : 文本文件，如上面的例子，列出了以后的二进制日志文件（当初有 6 个）。

binlog.xxxxxx: log 二进制文件，

binlog.000001
binlog.000002
binlog.000003
binlog.000004
binlog.000005
binlog.000006
binlog.index 

每个日志文件以一个 4 字节魔数 (0xfe b i n) 结尾，前面是一组 形容数据批改的事件，文件格式如下：

+===================+
| Magic Number        |
+===================+
| Start Event        |
+===================+
| Event 1            |
+===================+
| ...                |
+===================+

一个具体的例子：

 show BINLOG EVENTS in 'binlog.000001'
+---------------+---------+----------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name      | Pos     | Event_type     | Server_id | End_log_pos | Info                                                              |
+---------------+---------+----------------+-----------+-------------+-------------------------------------------------------------------+
| binlog.000001 | 4       | Format_desc    | 1         | 125         | Server ver: 8.0.26, Binlog ver: 4                                 |
| binlog.000001 | 125     | Previous_gtids | 1         | 156         |                                                                   |
| binlog.000001 | 156     | Anonymous_Gtid | 1         | 233         | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                              |
| binlog.000001 | 233     | Query          | 1         | 337         | use `mysql`; TRUNCATE TABLE time_zone /* xid=3 */                 |
| binlog.000001 | 337     | Anonymous_Gtid | 1         | 414         | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                              |
| binlog.000001 | 414     | Query          | 1         | 523         | use `mysql`; TRUNCATE TABLE time_zone_name /* xid=4 */            |

事件 Event 常见分类

• START_EVENT_V3: 每个日志文件的第一个事件，该事件在 MySQL 3.23 至 4.1 中应用，在 MySQL 5.0 中被 FORMAT_DESCRIPTION_EVENT 所取代，从第五个字节开始（魔数后）
• QUERY_EVENT : SBL 的 binlog 记录 sql 语句，在 row 模式下记录事务 begin
• STOP_EVENT : mysqld 过程进行时记录
• ROTATE_EVENT: log 文件大小达到设置的最大值，切换到新的 log 文件时写入，切换事件
• TABLE_MAP_EVENT: 在 binlog 文件是以 ROW 格局记录才会应用，TABLE_MAP_EVENT 中记录了表的定义（包含 database name, table name，字段定义），记录的每个更改的记录之前都会有一个对应要操作的表的 TABLE_MAP_EVENT
• WRITE_ROWS_EVENT: 插入记录，ROW 格局记录才会应用
• UPDATE_ROWS_EVENT: 更新记录，ROW 格局记录才会应用
• DELETE_ROWS_EVENT: 删除记录，ROW 格局记录才会应用
• …

Event 构造

binlog 版本的演变本质上是 Event 的演变

• v1：在 MySQL 3.23 中应用
• v3：在 MySQL 4.0.2 和 4.1 中应用
• v4：在 MySQL 5.0 及以上版本中应用

v2 曾经在 4.0 中应用过，曾经废除了

其中 Event 又分为两个局部: Event header 和 Event data。header 局部的长度由 binlog 版本决定，data 局部的长度又由 header 决定

+===================+
| event header      |
+===================+
| event data        |
+===================+

以下是 Event 的 具体构造 以及 各版本之间的差别

其中数字代表着这个字段在 Event 中的 limit 与 offset，例如 0:4，代表着在这个字段的地位在 Event 的 0-4 个字节。

+=====================================+
| event  | timestamp         0 : 4    |
| header +----------------------------+
|        | type_code         4 : 1    |
|        +----------------------------+
|        | server_id         5 : 4    |
|        +----------------------------+
|        | event_length      9 : 4    | v1 header 共 13 个字节
|        +----------------------------+
|        | next_position    13 : 4    | v3 版本减少。|        +----------------------------+
|        | flags            17 : 2    | v3 版本减少。v3 header 共 19 个字节
|        +----------------------------+
|        | extra_headers    19 : x-19 | v4 版本减少。v4 header 起码 19 个字节
+=====================================+
| event  | fixed part        x : y    |
| data   +----------------------------+
|        | variable part              |
+=====================================+

header 局部各字段解释

• timestamp: 事物开始执行的工夫，单位秒
• type_code: 事件类型，见上文 事件常见分类，它决定了 data 局部的写入数据
• server_id: 来自服务器配置文件中为复制目标设置的 server-id 选项，破除复制中可能的死循环。
• event_length: 这个 event 的总长度
• next_position: v3 新加，在 v3 版本中代表这个事件开始的地位，在 v4 版本中这个事件完结的地位
• flags: v3 新加，一些标记位 https://dev.mysql.com/doc/int…
• extra_header: v4 新加，目前是 0，即空

data 局部

由事件类型决定，如 v1，v3 query 事件，data 局部形成如下：

+======================================+
| fixed   | issue_thread      0 : 4    | 触发该语句的线程 id
| part    +----------------------------+
|         | timestamp         4 : 4    | 语句执行的工夫
|         +----------------------------+
|         | db_name_len       8 : 1    | 应用数据库名长度
|         +----------------------------+
|         | error_code        9 : 2    | 错误码
+======================================+
| variable| db_name                    | db name
| part    +----------------------------+
|         | sql_statment               | sql 语句
+======================================+

总结

• binlog 文件由魔数与事件列表形成，事件列表分成三局部：开始事件 + 数据更新相干的事件 + 切换事件
• 事件的类型决定了事件的格局，因为每种事件所需的信息是不统一的

常见问题

1. 主从复制，采取的是 push 模式还是 pull 模式
2. MySQL binlog 为什么不默认应用 Mixed 模式
3. 在应用 RBL 前提下，DDL（CREATE/ALTER）语句怎么记录
4. 应用 NOW()的语句是不平安么（SBL主从复制）
5. 如果让你来设计 开始事件 类型的构造，data 局部须要什么数据
6. binlog 与 redo log 区别

参考

1. https://dev.mysql.com/doc/int…
2. http://mysql.taobao.org/month…
3. https://zhuanlan.zhihu.com/p/…