MySQL更新数据时，日志（redo log、binlog）执行流程

困知勉行1985

已于 2023-01-31 16:49:55 修改

阅读量1.6k

点赞数 3

分类专栏：数据库文章标签： mysql 数据库 java

于 2022-09-21 14:36:05 首次发布

原文链接：https://www.cnblogs.com/wkynf/p/15855073.html

版权

数据库专栏收录该内容

21 篇文章 1 订阅

订阅专栏

1.背景

项目需要做ES和数据库的同步，而手动在代码中进行数据同步又是ES的一些不必要的数据同步操作和业务逻辑耦合，所以使用的是读取mysql的binlog日志的方式将数据库数据同步到ES。

问题1：根据binlog同步数据的时候会不会出现业务逻辑利用事务操作数据的时候，当事务还没有提交的时候，是否能够读到binlog，也就是binlog的写入时机（是事务提交的之前写，还是事务提交后写）。
问题2：如果事务提交之前写入binlog，那么事务提交之前，事务回滚，那么binlog又会出现什么情况？

首先我们做一下实验（前提是必须要打开binlog），SQL初始化语句（下面语句全部执行）

create table user
(
    id bigint not null auto_increment primary key,
    name varchar(64) null,
    status tinyint(1)  null
);
insert into user (name,status)values ('张三',1);
set autocommit = 0;

2.事务对binlog的影响

2.1：事务提交对binlog的影响

我们先确定一下MySQL执行更新SQL语句，在执行commit命令前后，binlog会有什么变化。

然后我们先使用 show master status 看一下binlog的位置(Position字段)

show master status;

然后我们执行一下更新语句：

update user set status = status + 1;

再次使用 show master status 查看发现Position并没有变。

但是，执行commit命令之后，发现Position由141169 变为 141506，说明只有事务提交之后binlog才被写入到磁盘，从库只有在主库提交之后才能读到主库写入的binlog日志。

2.2：事务回滚对binlog的影响

我们再确定一下MySQL执行更新SQL语句之后，执行rollback命令前后，binlog会有什么变化。

然后我们先使用 show master status 看一下binlog的位置

然后我们执行一下更新语句

update user set status = status + 1;

再次使用 show master status 查看发现Position并没有变，执行rollback命令之后，发现Position仍然没有变化，说明事务回滚之后binlog并不会写入磁盘。

难道binlog是在事务提交之后才写入磁盘的嘛？确实是这样的。

那redo log 又是什么时候写入磁盘的呢？有上面的问题又引发一系列的问题，带着这些问题，我们来进行mysql日志的深入学习。

3：MySQL更新数据的执行流程

首先我们要先了解一下当我们做一条数据的更新操作的时候，数据库的底层到底是如何执行的？

MySQL更新数据执行流程：

判断数据页是否在内存中（innodb Buffer pool），若为否，则从磁盘读取数据到内存中，返回数据行
若是数据页在内存中，则直接返回数据行
执行数据更新操作
数据写入内存（innodb buffer pool），同时redolog写入到内存（redo log buffer），binlog写入内存(binlog log buffer)。
执行commit操作（此commit是SQL命令操作，而不是数据的commit状态）
执行commit命令之后，则进行两阶段提交操作。
写入内存中的redolog到磁盘中(fsync操作)，此时redolog处于prepare状态
写入binlog到磁盘
提交事务（buffer pool里的更新数据也会写入到表中），此时事务处于commit状态，将redo log里这个事务的相关记录状态置为commit状态。
结束。

注：以上操作为参数innodb_flush_log_at_trx_commit (redo log)为1和sync_binlog (binlog)为1的时候。

对应MySQL的更新语句执行流程图，如图1-1。

redo log：被称之为重做日志，是在数据库发生意外时（提交失败），进行数据恢复（保证事务的一致性），redo log会备份事务执行过程中的修改数据。
binlog：被称为归档日志，是一个二进制格式的文件，用于记录用户对数据库更新的SQL语句信息，格式分为（statement、row、mixed）
redo log 和binlog的差异如下表：

redo log	binlog
InnoDB引擎	MySQL Serve
物理日志	逻辑日志
循环写入	追加写入

MySQL执行commit命令之后是使用两阶段提交的办法来保证事物的原子行的，至于为什么使用

两阶段提交，而不是其他的提交方式，由于篇幅有限，不做多余解释，请参考自行查询资料。

两阶段提交保证了redo log和binlog的一致性。

到目前为止，关于上面binlog的问题也就迎刃而解了，

对于问题一：MySQL在事务提交之前会写入redo log和binlog到内存缓存中，在执行commit命令之后进行两阶段提交操作，将redo log和binlog写入磁盘，因此在事务提交之前不能读取到binlog日志（前提binlog没有进行被动刷盘），当然也不能看到redo log。
对于问题二：binlog是在执行commit命令之后进行的刷盘，但是是在事务变成commit状态之前写入的磁盘。根据上面的实验可以看出，事务回滚对于binlog并没有什么影响（还在缓存中）。
小结：执行更新数据，数据到内存（buffer pool）之后，就会写入redo log和binlog到缓存中(redo log buffer和binlog buffer中)，当执行了commit命令之后，进行两阶段提交操作：将redo log（prepare状态）和binlog写入磁盘操作，这2步算是第1阶段（预提交）；没问题的话进行第2阶段，提交事务，修改事务状态为commit状态，并且将redo log状态设置为commit状态。

为什么要先将redo log和binlog写入磁盘？因为是顺序读写，速度很快，而将数据写入到表中是随机读写，速度慢。

关于MySQL的日志刷盘机制是由参数innodb_flush_log_at_trx_commit 和sync_binlog控制的，具体请参考下一篇文章。

4：commit命令和commit状态区别解释

我们上面说的commit命令是指MySQL语法中的commit命令，用于提交事务，一般跟 begin/start transaction 配对使用。
而我们上图中用到的这个“commit 步骤”，指的是事务提交过程中的一个小步骤，也是最后一步。当这个步骤执行完成后，这个事务就提交完成了。
“commit 命令”执行的时候，会包含“commit 步骤”。