MySQL binlog 设计 mysql

通过这篇文章你能了解到的知识

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

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

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

binlog 类型

基于语句 (Statement-based)的日志记录 (SBL): 事件包含产生数据变化的SQL语句。
基于行 (Row-based) 的日志记录(RBL): 描述单个行的变化
混合 (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| +===================+ | ...| +===================+

【MySQL binlog 设计】一个具体的例子：

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| timestamp0 : 4| | header +----------------------------+ || type_code4 : 1| |+----------------------------+ || server_id5 : 4| |+----------------------------+ || event_length9 : 4| v1 header 共 13 个字节 |+----------------------------+ || next_position13 : 4| v3 版本增加。 |+----------------------------+ || flags17 : 2| v3 版本增加。 v3 header 共 19 个字节 |+----------------------------+ || extra_headers19 : x-19 | v4 版本增加。 v4 header 最少 19 个字节 +=====================================+ | event| fixed partx : 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_thread0 : 4| 触发该语句的线程id | part+----------------------------+ || timestamp4 : 4| 语句执行的时间 |+----------------------------+ || db_name_len8 : 1| 使用数据库名长度 |+----------------------------+ || error_code9 : 2| 错误码 +======================================+ | variable| db_name| db name | part+----------------------------+ || sql_statment| sql 语句 +======================================+

总结