MySQL45讲之主备数据一致性flowers

Z时代
2024-01-10
分类：综合

database

本文主要介绍 MySQL 主备数据同步的重要日志 binlog 的三种格式，和双 M 结构的循环复制问题。

前言

本文主要介绍 MySQL 主备数据同步的重要日志 binlog 的三种格式，和双 M 结构的循环复制问题。

binlog三种格式

1. statement格式

直接存储了执行的 SQL 语句，并且存储了时间戳，大部分情况下不会出现主从不一致的情况。但是，还是存在某些特殊情况下，比如主从库执行语句时使用的索引不一样（因为存在统计模糊量，所以执行代价评估的时候结果可能不同），最后导致更新的结果不一致。

总结：statement的优点在于占用的存储空间较小，但是可能会出现主从数据不一致的情况，所以基本不采用statement格式。

2. row格式

row 格式下，binlog 中记录了执行前后的行记录字段值，方便数据回滚。binlog_row_image 默认配置为 FULL，所以 binlog 中会记录行的全字段值，如果修改为 MINIMAL，则只会记录一些必要的字段值。

总结：因为 row 格式下记录了修改的字段值，所以主从同步时不会发生数据不一致。但是因为会记录更新前后的字段值，占用的存储空间会大很多。

3. mixed格式

既然有了 row 格式，为什么还要 mixed 格式呢？

mixed 格式就是 statement 格式和 row 格式的混合。MySQL 会判断当语句不会导致主从数据不一致时，就采用 statement 格式记录，否则采用 row 格式节省存储空间。即 mixed 格式结合了 statement 和 row 格式的优点。

虽然 mixed 格式看着比 row 格式更有优势，但是当前更多的场景下还是使用 row 格式。其中，一个比较明显的原因就是 row 格式恢复数据很方便。

循环复制问题

生产环境中使用较多的是双 Master 的结构，即一台主机和一台备机之间互为主备。这样存在一个问题，主机更新后的 binlog 同步到备机后，备机的 binlog 也会更新然后又同步到主机，这样不就发生循环复制了么？

这个问题的解决办法是，row 格式的 binlog 中记录了 server id，备机同步 binlog 时会继续使用主机传过来的 server id，这样从机将 binlog 传回主机时，主机判断 binlog 中的 server id 和本机一致，就会终止 binlog 的同步，从而打断循环复制。