Mysql由于binlog日志格式问题出现主从复制数据不一致（5.0版本之前）

梦里梦外; 2022-02-22 13:33 641阅读 0赞

首先我们来熟悉下MySQL中的关键三种日志  重做日志（redo log） 回滚日志（undo log） 二进制日志（binlog）

重做日志：确保事务的持久性。redo日志记录事务执行后的状态，用来恢复未写入data file的已成功事务更新的数据。防止在发生故障的时间点，尚有脏页未写入磁盘，在重启MySQL服务的时候，根据redo log进行重做，从而达到事务的持久性这一特性。

回滚日志：保证数据的原子性，保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读。

二进制文件：用于复制，在主从复制中，从库利用主库上的binlog进行重播，实现主从同步。用于数据库的基于时间点的还原。

今天主要说主从复制问题，二进制文件主要有三种格式的存储   ：Statement,MiXED,以及ROW。至于每一个的优缺点这里就不说了。我们今天主要来说下MySQL5.0之前为啥事务的隔离机制是RC（'read-committed）并且存储格式是Statement时会出现主从复制数据不一致问题。

首先我们创建一个表table并插入一些数据   :

CREATE TABLE tem_table(
    	b1 int,
    	b2 int
    )

把自动提交关闭 执行两个会话：![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2ODI3OTU3_size_16_color_FFFFFF_t_70][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2ODI3OTU3_size_16_color_FFFFFF_t_70 1][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2ODI3OTU3_size_16_color_FFFFFF_t_70 2][]

此时查看会话1的提交结果：

dba> select \* from t1;

\+------+------+

| b1   | b2   |

\+------+------+

|    1 |    4 |

|    2 |    8 |

|    3 |    4 |

|    4 |    8 |

|    5 |    4 |

\+------+------+

5 rows in set (0.00 sec)

这个结果不会有任何问题。

假设在RC隔离级别下支持STATEMENT格式的binlog，并且binlog是打开的。binlog的记录顺序是按照事务commit顺序为序的。那么显而易见，binlog中的顺序为：

会话2：

dba> set tx\_isolation='read-committed';

dba> BEGIN;

dba> update t1 set b2=4 where b2=2;

dba> commit;

会话1：

dba> set tx\_isolation='read-committed';

dba> BEGIN;（开启事务）

dba> update t1 set b2=8  where b2=4;

\#会话1进行提交

dba> commit;

那么此时在主从复制的从库上看到的结果应为：

dba> select \* from t1;

\+------+------+

| b1   | b2   |

\+------+------+

|    1 |    8 |

|    2 |    8 |

|    3 |    8 |

|    4 |    8 |

|    5 |    8 |

\+------+------+

5 rows in set (0.00 sec)

可见，在RC隔离级别下，如果支持STATEMENT格式的binlog，是有可能导致主从数据不一致的！

那么你可能会问，在RC隔离级别下，如果binlog格式为ROW或者MIXED，难道就不会有主从数据不一致的风险吗？答案是肯定的，如果binlog的格式是ROW或者MIXED，在RC隔离级别下，不会导致主从数据不一致。为什么呢？

因为ROW或者MIXED格式的binlog，是基于数据的变动。在进行update或者delete操作，记录到binlog，同时会把数据的原始记录写入到binlog。所以日志文件会比Statement大些，上述演示过程，binlog的记录顺序仍然是按照事务的commit顺序为序的，binlog的顺序仍然为：

会话2：

dba> set tx\_isolation='read-committed';

dba> BEGIN;

dba> update t1 set b2=4 where b2=2;

dba> commit;

会话1：

dba> set tx\_isolation='read-committed';

dba> BEGIN;（开启事务）

dba> update t1 set b2=8  where b2=4;

\#会话1进行提交

dba> commit;

在从库仍然是按照这个binlog的执行时序，进行更新操作。但不同之处在于。会话2的update操作：

dba> update t1 set b2=4 where b2=2;

写入到binlog时，会把原始的记录也记录下来。它是这样记录的：

update dba.t1

where

b1=1

b2=2

set

b1=1

b2=4

update dba.t1

where

b1=3

b2=2

set

b1=3

b2=4

update dba.t1

where

b1=5

b2=2

set

b1=5

b2=4

从库上会话2的更新操作完成之后，接着执行会话1的更新操作：

dba> update t1 set b2=8  where b2=4;

binlog中的记录为：

update dba.t1

where

b1=2

b2=4

set

b1=2

b2=8

update dba.t1

where

b1=4

b2=4

set

b1=4

b2=8

这样从库看到的结果就是：

dba> select \* from t1;

\+------+------+

| b1   | b2   |

\+------+------+

|    1 |    4 |

|    2 |    8 |

|    3 |    4 |

|    4 |    8 |

|    5 |    4 |

\+------+------+

5 rows in set (0.00 sec)

这样，主从数据就是一致的。

参考文章链接：[互联网项目中MySQL应该选什么事务隔离级别][MySQL]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2ODI3OTU3_size_16_color_FFFFFF_t_70]: /images/20220222/54042149f96c4bf0862e12e577d77cea.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2ODI3OTU3_size_16_color_FFFFFF_t_70 1]: /images/20220222/d08c565a15514e178d1295e6524ba689.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2ODI3OTU3_size_16_color_FFFFFF_t_70 2]: /images/20220222/b1e684eed2e149eb83776f1574fc4d5c.png
[MySQL]: https://mp.weixin.qq.com/s?__biz=MzI3NDA4OTk1OQ==&mid=2649902302&idx=1&sn=fa1ea0207ad0a0150136a3eba4a2dffd&chksm=f31fbe56c468374038dddda9e6a2ad541b83c6eea93ead31d1224e7bc733657f68abfd68b9f4&mpshare=1&scene=24&srcid=&key=b29fbb831ffce04c325e926cd6d50e62ff8c00b9ac1810455808261b20f717037c30b13ea52bff4aa006dd58af876d1dbb4fe01faa68e09722b42964f0255a8a4b6cd4983f8002320a79d8345280fb97&ascene=1&uin=MTY5MDU0ODcxNw%3D%3D&devicetype=Windows+10&version=62060739&lang=zh_CN&pass_ticket=hzxC3fpqxkcVaWz80DzLJkS%2BAYaP9EVFoPPOoqPua3HUoqpY6luV6PCu8CQLb%2FCE