一个redo和binlog两段提交的思考

偏执的太偏执、 2023-05-22 04:37 1阅读 0赞

对于事务提交，是这样一个流程：  
1.发出commit语句；  
2.redo进入prepare阶段；  
3.写binlog；  
4.redo进入commit阶段；  
5.事务完成提交

还有两个参数有关联，  
1.innodb\_flush\_log\_at\_trx\_commit  
各取值含义：  
0，事务提交时，将redo保留在redo buffer，redo日志既不write到OS cache，也不fsync到磁盘，等待MySQL后台线程每秒对redo buffer执行write&fsync；  
1，事务提交时，将redo buffer执行write & fsync；  
2，事务提交时，将redo buffer执行write到OS cache，但不执行fsync

2.sync\_binlog  
0，事务提交时，将binlog cache执行write到OS cache，但不执行fsync；  
1，事务提交时，将binlog cache执行write & fsync；  
N，每提交N个事务，将binlog cache执行write & fsync

那么在实例异常关闭，重启时执行实例恢复，是这样判断一个事务是否已提交：  
1.如果存在commit阶段的redo，就认为是已提交的事务；  
2.如果存在prepare阶段的redo，又存在对应的binlog，就认为是已提交的事务；  
3.如果存在prepare阶段的redo，但不存在binlog，就认为该事务未提交，需要回滚。

针对这些规则，会有一些意外情况：  
假设innodb\_flush\_log\_at\_trx\_commit=0,sync\_binlog=1  
那么当一个事务A提交时，会将binlog fsync到磁盘，而redo保留在redo buffer，同时事务A做的更改也还在buffer pool里。  
显然这时从库是已经收到事务A的binlog  
考虑一种情况，假设在主库提交事务A后，从库收到了事务A的binlog，但是redo和事务更新都还在主库内存时，主库断电了，会怎样？  
1.对于从库，即使主库断电了，但是已经收到了事务A的binlog，也就是说这个事务在从库已经存在了；  
2.对于主库，重启后，由于事务A的更改在断电前是在buffer pool，重启之后就没了，需要实例恢复；同时由于断电前，事务A的redo也还在redo buffer里，没有write到OS cache，那么在redo文件也是没有事务A的redo的，也就是重启后，主库只有事务A的binlog，但是连prepare阶段的redo都没有，显然主库的事务A是无法恢复了。  
3.对于主从复制，重启后，主库没有事务A，从库有事务A，就造成了主从不一致。

造成这种情况的原因是，对于一个事务，有binlog记录，但是没有redo记录。那么就是说，在binlog刷出文件之前，我们至少要保证对应的事务redo已经刷出文件了，这样异常重启后，才不会导致主从的数据不一致。

假设innodb\_flush\_log\_at\_trx\_commit=1,sync\_binlog=100  
那么当提交了50个事务，redo已经flush到磁盘，而binlog还保留在cache里（假设也没有被OS刷盘），此时机器挂了，下一次MySQL重启时，由于这50个事务已经提交了，MySQL可以恢复，但由于binlog还未刷盘，所以重启机器后binlog文件里就没有这50个事务对应的binlog了，以后所有依赖于这些binlog的操作，都会与主库的数据不一致。

那么对于innodb\_flush\_log\_at\_trx\_commit和sync\_binlog的参数配合就有所讲究了。  
双1是没问题的，但是在高负载下，我们往往会取消双1，来提高MySQL的响应效率。  
在非双1的情况下，我们往往推荐是，innodb\_flush\_log\_at\_trx\_commit=2，sync\_binlog=N。  
这个配合的好处是，两个参数的0值都是带有不可控因素。而innodb\_flush\_log\_at\_trx\_commit=2至少已经刷出到OS cache，只要操作系统没问题，redo就不会丢失；而sync\_binlog是精确地控制最多丢失多少个事务。同时这个配合，只要操作系统不挂，那么就不会出现本文提到的特殊情况，因为redo在每个事务提交时已经保存在OS cache了。当然如果操作系统出现问题，还是会出现一个事务的binlog存在，但redo没刷到磁盘的情况。除了双1，都是存在不可控的情况，只是innodb\_flush\_log\_at\_trx\_commit=2，sync\_binlog=N这一种参数配合，对不可控的发生降到了最低。  
当然若能保证OS不挂，innodb\_flush\_log\_at\_commit=2,sync\_binlog=0也是可以的。