bin log mysql 分析 mysql binlog原理

binlog 可以用来归档，也可以用来做主备同步，MySQL 能够成为现下最流行的开源数据库，binlog 功不可没。

在最开始，MySQL 是以容易学习和方便的高可用架构，被开发人员青睐的。而几乎所有的高可用架构，都直接依赖于 binlog。虽然这些高可用架构已经呈现出越来越复杂的趋势，但都是从最基本的一主一备演化过来的，那么我们就先来看一下主备的基本原理。

一. MySQL 主备的基本原理

MySQL 主备切换流程如下图所示：

在状态 1 中，客户端的读写都直接访问节点 A，节点 B 是 A 的备库，只是将 A 的更新都同步过来，到本地执行，这样可以保持节点 B 和 A 的数据是相同的。

当需要切换的时候，就切成状态 2，这时候客户端读写访问的都是节点 B，而节点 A 则变为 B 的备库。

虽然备库不会被直接访问，但是我们依然会把它设置为 只读模式（readonly），原因如下：

• 有时候一些运营类的查询语句会被放到备库上去查，设置为只读可以防止误操作；
• 防止切换逻辑有 bug，比如切换过程中出现双写，造成主备不一致；
• 可以用 readonly 状态，来判断节点的角色；

其中，节点 A 到 B 的流程图如下所示：

备库 B 跟主库 A 之间维持了一个长连接。主库 A 内部有一个线程，专门用于服务备库 B 的这个长连接。一个事务日志同步的完整过程如下：

1. 在备库 B 上通过 change master 命令，设置主库 A 的 IP、端口、用户名、密码，以及
   要从哪个位置开始请求 binlog，这个位置包含 文件名 和 日志偏移量 。
2. 在备库 B 上执行 start slave 命令，这时候备库会启动两个线程，就是图中的 io_thread 和 sql_thread。其中 io_thread 负责与主库建立连接。
3. 主库 A 校验完用户名、密码后，开始按照备库 B 传过来的位置，从本地读取 binlog，发给 B。
4. 备库 B 拿到 binlog 后，写到本地文件，称为 中转日志（relay log）。
5. sql_thread 读取中转日志，解析出日志里的命令，并执行。

二. binlog日志记录内容

binlog日志有3种格式：分别是 statement ，row， mixed，其中的 mixed 是前两种格式的混合。

binlog_format = statement

binlog 里面记录的是 SQL 语句的原文，可以用命令查看 binlog 中的内容：

mysql> show binlog events in 'master.000001';

查询结果如下：

第二行的 begin 跟第四行的 commit 对应，表示中间是一个事务；

第三行是实际执行的语句，其中的 use ‘test’ 表示使用的是 test 库表，这个是数据库自动添加的，用来保证日志传到备库去执行的时候，都能够正确地更新到 test 库。之后的 delete 语句就是输入的 SQL 原文；

最后一行的 commit 的 xid=61 是 redo log 和 binlog 的关联字段，崩溃恢复的时候，会按顺序扫描 redo log，如果碰到只有 parepare、而没有 commit 的 redo log，就会拿着 XID 去 binlog 找对应的事务。

在statement 格式下，记录到 binlog 里的是语句原文，因此可能会出现这样一种情况：在主库执行这条 SQL 语句的时候，用的是索引 a；而在备库执行这条 SQL 语句的时候，却使用了索引 t_modified。 而 delete 带 limit，这样就很可能会出现主备数据不一致的情况。

如果改为 binlog_format=‘row’， 就没有这个问题了。

binlog_format = ‘row’

使用 row 格式的 binlog 内容如下：

可以看出，row 格式的 binlog 里没有了 SQL 语句的原文，而是替换成了两个 event：Table_map 和 Delete_rows。

1. Table_map event，用于说明接下来要操作的表是 test 库的表 t;
2. Delete_rows event，用于定义删除的行为。

我们可以用下面这个命令解析和查看 binlog 中的内容：

mysqlbinlog -vv data/master.000001 --start-position=8900;

结果如下：

可以看到，当 binlog_format 使用 row 格式的时候，binlog 里面记录了真实删除行的

主键 id，这样 binlog 传到备库去的时候，就会删除 id=4 的行，不会有主备删除不同行的问题。

binlog_format = ‘mixed’

因为有些 statement 格式的 binlog 可能会导致主备不一致，所以要使用 row 格式，但 row 格式的缺点是，很占空间，会导致写 binlog 耗费 IO 资源大，影响执行速度。

mixed 格式的 binlog 是一个折中的方式：MySQL 自己会判断这条 SQL 语句是否可能引起主备不一致，如果有可能，就用 row 格式，否则就用 statement 格式。

三. 循环复制问题

binlog 的特性确保了在备库执行相同的 binlog，可以得到与主库相同的状态。图 1 中的是 M-S 结构，但实际生产上使用比较多的是双 M结构，如下图所示：

双 M 结构和 M-S 结构的区别在于，节点 A 和 B 之间多了一条线，它们总是互为主备关系，这样在切换的时候就不用再修改了。

业务逻辑在节点 A 上更新了一条语句，然后再把生成的 binlog 发给节点 B，节点 B 执行完这条更新语句后也会生成 binlog。

那么，如果节点 A 同时是节点 B 的备库，相当于又把节点 B 新生成的 binlog 拿过来执行了一次，然后节点 A 和 B 间，会不断地循环执行这个更新语句，也就是循环复制了，这个要怎么解决呢？

1. 规定两个库的 server id 必须不同，如果相同，则它们之间不能设定为主备关系；
2. 一个备库接到 binlog 并在重放的过程中，生成与原 binlog 的 server id 相同的新的 binlog；
3. 每个库在收到从自己的主库发过来的日志后，先判断 server id，如果跟自己的相同，表示这个日志是自己生成的，就直接丢弃这个日志。

四. binlog 的写入机制

事务执行过程中，会先把日志写到 binlog cache，事务提交的时候，再把 binlog cache 写到 binlog 文件中。

一个事务的 binlog 是不能被拆开的，因此不论这个事务多大，也要确保一次性写入，这就涉及到了 binlog cache 的保存问题。

系统给 binlog cache 分配了一片内存，每个线程一个，参数 binlog_cache_size 就是用于控制单个线程内 binlog cache 所占内存的大小。如果超过了这个参数规定的大小，就要暂存到磁盘。

事务提交的时候，执行器把 binlog cache 里的完整事务写入到 binlog 中，并清空 binlog cache，如下图所示：

可以看到，每个线程有自己 binlog cache，但是共用同一份 binlog 文件。图中的 write，是把日志写入到文件系统的 page cache，并没有把数据持久化到磁盘，速度比较快。

write 和 fsync 的时机，是由参数 sync_binlog 控制的：

1. sync_binlog = 0 的时候，表示每次提交事务都只 write，不 fsync；
2. sync_binlog = 1 的时候，表示每次提交事务都会执行 fsync；
3. sync_binlog = N (N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync。

因此，在出现 IO 瓶颈的场景里，将 sync_binlog 设置成一个比较大的值，可以提升性能。在实际的业务场景中，考虑到丢失日志量的可控性，一般不建议将这个参数设成 0，比较常见的是将其设置为 100~1000 中的某个数值。

但是，将 sync_binlog 设置为 N 对应的风险是：如果主机发生异常重启，会丢失最近 N个事务的 binlog 日志。