binlog解读3

一、关于事务比较重要的两个事件：

1、GTID EVENT：

a、gtid简介：从MySQL 5.6.5 开始新增了一种基于 GTID 的复制方式。通过 GTID 保证了每个在主库上提交的事务在集群中有一个唯一的ID。这种方式强化了数据库的主备一致性，故障恢复以及容错能力。

（1）、GTID (Global Transaction ID)是全局事务ID，当在主库上提交事务或者被从库应用时，可以定位和追踪每一个事务。它是全局唯一和单调递增的。

（2）、不支持非事务引擎。

（3）、GTID复制与普通复制最大的区别就是 不需要指定二进制文件名和位置。

b、gtid事件内容：

（1）、gtid_flags，占用1个字节。记录binlog格式，如果gtid_flags值为1，表示binlog中可能有以statement方式记录的binlog，如果为0表示，binlog中只有以row格式记录的binlog。

（2）、sid，server uuid，占用16字节，比如：b0d850c2-dbd0-11e9-90c3-080027b8bded，一共16字节。

（3）、gno，占用8字节，比如：b0d850c2-dbd0-11e9-90c3-080027b8bded:1，数字1就是gno，占用8个字节。

（4）、lt_type，占用1字节，打开逻辑并行复制时，lt_type值为2。

（5）、last_committed，占用8字节，lt_type 为2时才会有该值。

（6）、sequence_number，占用8字节，lt_type 为2时才会有该值。

（7）、最后4字节，是这个binlog event的crc32检验值。

（8）、ANONYMOUS_GTID_LOG_EVENT：在ANONYMOUS GTID event中除了 last_committed，sequence_number其他字节为0X00，这是为了提供组提交的信息。

c、（1）、在gtid event中，last_committed=2组提交标识、同一组提交的事务具备相同的last_committed值，可以在从库并行重放，以减少同步延迟。

（2）、sequence_number=3，事务对应的顺序号，该值单调递增，同时也标识了同一组提交事务的顺序，在从库设置slave_preserve_commit_order=1时，依赖该值模拟主库的提交顺序，在从库提交。以达到数据落盘过程完全一致。

在每台MySQL服务器上都是从1开始自增长的序列，一个数值对应一个事务。

d、binlog如何保证同一个gtid的gno的有序性？

对于打开了gtid_mode的实例，每个事务起始位置都会有一个gtid event，其内容输出格式为UUID:gn，gno是一个整型数。

gno生成于事务提交时写binlog的时候。注意这里不是生成binlog，而是将binlog写入磁盘的时候。因此实现上确保了同一个UUID下gno的有序性。

2、XID EVENT：

当事务提交时，不论是statement还是row格式的binlog都会添加一个XID_EVENT作为事务的结束。该事件记录了该事务的ID。在mysql进行崩溃恢复时根据binlog中提交的情况来决定是否提交存储引擎中prepared状态的事务。

a、表示支持内部XA的存储引擎上的事务提交。正常的事务是通过QUERY_EVENT来发送一个BEGIN语句并且通过XID_EVENT来发送一个COMMIT语句（如果事务回滚则发送的是ROLLBACK）实现。

b、XA事务：XA事务存在于binlog和innodb存储引擎之间，在事务提交的时候，binlog日志和重做日志的提交是原子操作，即二进制日志和重做日志必须是同时写入磁盘的。若二进制先写了，而在写入innodb存储引擎时发生了宕机，那么slave可能会收到master并未提交的事务，而造成了主从不一致。为了解决这个问题，MySQL数据库在binlog与innodb存储引擎之间采用XA事务。当事务提交时，innodb存储引擎会先做一个prepare操作，将事务的XID写入，接着进行二进制日志的写入，最后在写入redo log。redo log 与 bin log通过事务Xid进行关联。其中，二进制日志（逻辑日志）只在事务提交完成后进行一次性写入，而innodb存储引擎的redo log（物理日志）在事务进行中就不断被写入。

c、（1）当binlog格式为row，且事务中更新的是事务引擎时，每个事务的结束位置都有Xid，Xid的类型为整型。

（2）MySQL中每个语句都会被分配一个全局递增的query_id(重启会被重置)，每个事务的Xid来源于事务第一个语句的query_id。

mysql query_id记录在INFORMATION_SCHEMA.PROFILING 这个表中，用于分析语句占用的资源情况。Query_ID表示从连接上数据库到现在执行的SQL 语句序号，select也会记录。

（3）xid的无序性：

考虑一个简单的操作顺序：

session 1: begin; select; update;

session 2: begin; select; update; insert; commit;（xid2）

session 1: insert; commit;（xid1）

显然Xid1 > Xid2，但因为事务2会先于事务1记录写binlog，因此在这个binlog中，会出现Xid不是有序的情况。

 

二、解析binlog后的每个事务的第一个TIMESTAMP：

时间戳的有序性可能是被误用最多的。在mysqlbinlog这个工具的输出结果中，每个事务起始有会输出一个SET TIMESTAMP=n。这个值取自第一个更新事件（update）的时间。上一节的例子中，timestamp2>timestamp1,但因为事务2会先于事务1记录写binlog，因此在这个binlog中，会出现TIMESTAMP不是有序的情况。

 

三、MySQL是如何写binlog的？

参考：https://www.bookstack.cn/read/aliyun-rds-core/11ba3d7ee1553c0f.md

1、对于单个事务的两阶段提交过程：

（1）MySQL 采用了如下的过程实现内部 XA 的两阶段提交：

a、Prepare 阶段：InnoDB 将回滚段设置为 prepare 状态；将 redolog 写文件并刷盘；

b、Commit 阶段：Binlog 写入文件；binlog 刷盘；InnoDB commit；

c、innodb redo log这个commit和prepare状态它自身是不会记录的，而是由XA事务来进行标记的。

d、两阶段提交保证了事务在多个引擎和 binlog 之间的原子性，以 binlog 写入成功作为事务提交的标志

（2）单个事务的崩溃恢复：

在崩溃恢复中，是以 binlog 中的 xid 和 redolog 中的 xid 进行比较，xid在 binlog 里存在则提交，不存在则回滚。

a、在 prepare 阶段崩溃，即已经写入 redolog，在写入 binlog 之前崩溃，则会回滚；

b、在 commit 阶段，当没有成功写入 binlog 时崩溃，也会回滚；

c、如果已经写入 binlog，在写入 InnoDB commit 标志时崩溃，则重新写入 commit 标志，完成提交。

2、关于组提交问题：

MySQL 的内部 XA 机制保证了单个事务在 binlog 和 InnoDB 之间的原子性，接下来我们需要考虑，在多个事务并发执行的情况下，怎么保证在 binlog 和 redolog 中的顺序一致？为什么需要保证二进制日志的写入顺序和InnoDB层事务提交顺序一致性呢？

（事务按照T1、T2、T3顺序开始执行，将二进制日志（按照T1、T2、T3顺序）写入日志文件系统缓冲，调用fsync()进行一次group commit将日志文件永久写入磁盘，但是存储引擎提交的顺序为T2、T3、T1。当T2、T3提交事务之后，若通过在线物理备份进行数据库恢复来建立复制时，因为在InnoDB存储引擎层会检测事务T3在上下两层都完成了事务提交，不需要在进行恢复了，此时主备数据不一致）

3、两个写磁盘的参数：

1、innodb_flush_log_at_trx_commit：控制redo log刷盘策略。

a、innodb_flush_log_at_trx_commit=0：每秒一次将Log Buffer中数据写入到Log File中，并且Flush到磁盘。事务提交不会主动触发写磁盘操作。

当innodb_flush_log_at_trx_commit=0时，最近一秒的事务日志存在MySQL的Log Buffer中，无论是MySQL实例停止还是MySQL服务器宕机，都会导致最近一秒的事务日志丢失。

 

b、innodb_flush_log_at_trx_commit=1：每次事务提交时将Log Buffer数据写入到Log File中，并且Flush到磁盘。

c、innodb_flush_log_at_trx_commit=2：每次事务提交时将Log Buffer数据写入到Log File中，但不立即Flush到磁盘，MySQL会每秒一次刷新到磁盘。由于进程调度问题，每条一次操作不能保证每一秒都执行一次。

当innodb_flush_log_at_trx_commit=1时，最近一秒的事务日志存在操作系统的文件缓存中，MySQL实例停止不会导致事务日志丢失，但MySQL服务器宕机会导致最近一秒事务日志丢失。上述的一秒一次刷新，取决于参数innodb_flush_log_at_timeout默认值为1，DDL或其他InnoDB内部操作并不受参数innodb_flush_log_at_trx_commit的限制。

2、sync_binlog：控制binlog落盘操作。

sync_binlog=0：每次事务提交后，将Binlog Cache中的数据写入到Binlog文件，但不立即刷新到磁盘。由文件系统(file system)决定何时刷新到磁盘中。

sync_binlog=N：每N次事务提交后，将Binlog Cache中的数据写入到Binlog文件,调用fdatasync()函数将数据刷新到磁盘中。

 

当sync_binlog=0(默认设置)时，不会执行强制刷盘指令，性能最好同时风险最高。

当sync_binlog=N时，当MySQL服务器宕机后，会导致最近N个事务的BINLOG丢失。

4、binlog的写入过程：

首先，事务语句执行期间，binlog event写入到binlog buffer（内存）；其次，如果binlog buffer超过binlog_cache_size设定的大小后，就会将事件放到临时文件中（disk），这个是落盘的；最后，当commit的时候，将日志写入到binlog file中。

5、版本改进：

（1）在 MySQL 5.6 版本之前，使用 prepare_commit_mutex 对整个 2PC 过程进行加锁，只有当上一个事务 commit 后释放锁，下个事务才可以进行 prepare 操作，这样完全串行化的执行保证了顺序一致。

存在的问题是，prepare_commit_mutex 的锁机制会严重影响高并发时的性能，在每个事务执行过程中，都会调用多次刷盘操作。

（2） 为了提高并发性能，肯定要细化锁粒度。MySQL 5.6 引入了 binlog 的组提交（group commit）功能，prepare 阶段不变，只针对 commit 阶段，将 commit 阶段拆分为三个过程：

    flush stage：多个线程按进入的顺序将 binlog 从 cache 写入文件（不刷盘）；

    sync stage：对 binlog 文件做 fsync 操作（多个线程的 binlog 合并一次刷盘）；

commit stage：各个线程按顺序做 InnoDB commit 操作。

（3）MySQL 5.6 的组提交逻辑中，每个事务各自做 prepare 并写 redo log，只有到了 commit 阶段才进入组提交，因此每个事务的 redolog sync 操作成为性能瓶颈。

在 5.7 版本中，修改了组提交的 flush 阶段，在 prepare 阶段不再让线程各自执行 flush redolog 操作，而是推迟到组提交的 flush 阶段，flush stage 修改成如下逻辑：

a、收集组提交队列，得到 leader 线程，其余 follower 线程进入阻塞；

b、leader 调用 ha_flush_logs 做一次 redo write/sync，即，一次将所有线程的 redolog 刷盘；

c、将队列中 thd 的所有 binlog cache 写到 binlog 文件中。

这个优化是将 redolog 的刷盘延迟到了 binlog group commit 的 flush stage 之中，sync binlog 之前。通过延迟写 redolog 的方式，为 redolog 做了一次组写入，这样 binlog 和 redolog 都进行了优化。