凯子の博客

代码的成神之路

0%

MySql——如何保证主备一致

发表于 分类于 Valine： 3.2k 3 分钟
MySql——如何保证主备一致

主备的基本原理

  • 在MySQL中主备库是可以切换的，如下：在状态1中，客户端的读写都直接访问节点A，而节点B是A的备库，只是将节点A的更新都同步过来，到本地执行，这样就可以保证AB的数据是一致的。
  • 当切换节点时，也就是变成状态2后，客户端的读写访问节点都是节点B，而节点A就是节点B的备库。

img

  • 建议把备库设置为只读模式，这样做的原因有以下三点：
    1. 有时候一些运营类的查询语句会被放到备库上去查，设置为只读可以防止误操作；
    2. 防止切换逻辑有 bug，比如切换过程中出现双写，造成主备不一致；
    3. 可以用 readonly 状态，来判断节点的角色。

主备的执行流程

  • 上面的图中，进行主备复制的就是A和B之间的线，那这条线究竟是怎么执行的呢？

img

  • 当需要进行主备复制时，备库B跟主库A之间需要维持一个长连接。主库A内部有一个线程，专门服务B的这个长连接，完整的执行过程时这样的：
    1. 在备库B上通过change master命令，设置主库A的IP、端口、用户名、密码以及要从哪个位置开始请求binlog，这个位置包含了binlog的文件名和日志的偏移量。
    2. 在备库上执行save slave命令，这时备库就会启动两个线程io_thread和sql_thread，一个负责维持与主库的长连接，一个负责执行relay log中的sql语句。
    3. 而主库A在校验完用户名和密码后，开始按照备库B传来的位置，从本地读取binlog并发送给B
    4. 备库B拿到binlog后，写到本地文件，这个文件为relay log，也叫中转日志。
    5. sql_thread解析relay log中的命令并执行。

binlog的三种格式对比

  • binlog一共有三种格式：可以使用show binlog events in [binlog文件位置];命令来查看binlog中的内容。

Statement

  • 在Statement格式下，binlog里面记录的就是SQL语句的原文，它使用commit来判断事务是否提交。
  • 举个例子：执行这行语句delete from t /*comment*/ where a>=4 and t_modified<='2018-11-10' limit 1;，它生成的binlog如下：

img

  • 由上图可以看到：statement格式下，binlog记录的都是SQL语句，而第一行 SET @@SESSION.GTID_NEXT=’ANONYMOUS’使用来做主备切换的。
  • 看起来使用statement格式好像没有什么问题，但在执行这个语句时其实是不安全的，这是因为delete带了limit，这很有可能会出现主备不一致的情况：
    1. 如果 delete 语句使用的是索引 a，那么会根据索引 a 找到第一个满足条件的行，也就是说删除的是 a=4 这一行；
    2. 但如果使用的是索引 t_modified，那么删除的就是 t_modified=’2018-11-09’也就是 a=5 这一行。
  • 因此，使用statement格式是有风险的。

Row

  • 在Row格式下，binlog中记录的是执行SQL语句的event，例如下图：前后的 BEGIN 和 COMMIT 是一样的。但是，row 格式的 binlog 里没有了 SQL 语句的原文，而是替换成了两个 event：Table_map 和 Delete_rows。

img

  1. Table_map event，用于说明接下来要操作的表是 test 库的表 t;
  2. Delete_rows event，用于定义删除的行为。
  • 可以使用mysqlbinlog 工具，用下面这个命令解析和查看 binlog 中的内容。从上图可以知道，这个事务的 binlog 是从 8900 这个位置开始的，所以可以用 start-position 参数来指定从这个位置的日志开始解析。
1
mysqlbinlog  -vv [binlog文件位置] --start-position=8900;

img

  • 从这个图，我们可以知道：
    1. server id 1，表示这个事务是在 server_id=1 的这个库上执行的。
    2. 每个 event 都有 CRC32 的值，这是因为我把参数 binlog_checksum 设置成了 CRC32。
    3. 我们在 mysqlbinlog 的命令中，使用了 -vv 参数是为了把内容都解析出来，所以从结果里面可以看到各个字段的值（比如，@1=4、 @2=4 这些值）。
    4. 最后的 Xid event，用于表示事务被正确地提交了。
  • 从这里可以看到，使用Row格式时，binlog记录的是需要进行操作的rowid=xxx的行，这样binlog传到备库中去的时候，就肯定会删除id=xxx的行，因为主键是唯一的，就不会出现主备删除不同行的问题。

Mixed

  • 由上面可知，statement 格式的 binlog 可能会导致主备不一致，所以要使用 row 格式。
  • 但 row 格式的缺点是，很占空间。比如你用一个 delete 语句删掉 10 万行数据，用 statement 的话就是一个 SQL 语句被记录到 binlog 中，占用几十个字节的空间。但如果用 row 格式的 binlog，就要把这 10 万条记录都写到 binlog 中。这样做，不仅会占用更大的空间，同时写 binlog 也要耗费 IO 资源，影响执行速度。
  • 所以，MySql就取了个折中方案，也就是有了 mixed 格式的 binlog。mixed 格式的意思是，MySQL 自己会判断这条 SQL 语句是否可能引起主备不一致，如果有可能，就用 row 格式，否则就用 statement 格式。

小总结

  • 如果你的线上 MySQL 设置的 binlog 格式是 statement 的话，那基本上就可以认为这是一个不合理的设置。你至少应该把 binlog 的格式设置为 mixed。
  • 但是，越来越多的场景会要求把binlog_format设置成row格式，这么做有一个好处，那就是可以用binlog来恢复数据，因为row格式的binlog会把增删改的操作的所有数据记录起来，当你要恢复数据时，你只需要执行相应的反向操作。
  • 最后，用 binlog 来恢复数据的标准做法是，用 mysqlbinlog 工具解析出来，然后把解析结果整个发给 MySQL 执行。类似下面的命令：
1
mysqlbinlog [binlog文件位置]  --start-position=2738 --stop-position=2973 | mysql -h127.0.0.1 -P13000 -u$user -p$pwd;

双M结构——循环复制

  • 在第一张图我们所用到的是M-S的主备结构，但实际生产上使用得较多的是双M结构，也就是下图：

img

  • 其实这和图一相比，只是多了一条线，这代表了A和B之间是互为主备的关系，在切换客户端连接的时候就不用再修改主备关系了
  • 业务逻辑在节点 A 上更新了一条语句，然后再把生成的 binlog 发给节点 B，节点 B 执行完这条更新语句后也会生成 binlog。（建议把参数 log_slave_updates 设置为 on，表示备库执行· relay log 后生成 binlog）。那么，如果节点 A 同时是节点 B 的备库，相当于又把节点 B 新生成的 binlog 拿过来执行了一次，然后节点 A 和 B 间，会不断地循环执行这个更新语句，也就是循环复制了。这个要怎么解决呢？
  • 从上面的知识可以知道，binlog会记录下 server id 的值，这就可以使用这个id来解决循环复制问题：
    1. 规定两个库的 server id 必须不同，如果相同，则它们之间不能设定为主备关系；
    2. 一个备库接到 binlog 并在重放的过程中，生成与原 binlog 的 server id 相同的新的 binlog；
    3. 每个库在收到从自己的主库发过来的日志后，先判断 server id，如果跟自己的相同，表示这个日志是自己生成的，就直接丢弃这个日志。
  • 则双M结构下，日志的执行流就会变成这样：
    1. 从节点 A 更新的事务，binlog 里面记的都是 A 的 server id；
    2. 传到节点 B 执行一次以后，节点 B 生成的 binlog 的 server id 也是 A 的 server id；
    3. 再传回给节点 A，A 判断到这个 server id 与自己的相同，就不会再处理这个日志。所以，死循环在这里就断掉了。

学习资料：MySql实战45讲

-------------本文结束感谢您的阅读-------------