MySQL主备同步与主从同步

香草星冰乐

墨衍会员 · AI 创作全网分发码龄7年

关注

VIP免费原创最新推荐文章于 2024-12-19 09:30:00 发布 2.4k 阅读 AI 写同款· GEO 优化 ›

MySQL主备同步与主从同步

主备同步
一主多从
- 读写分离

主备同步

MySQL通过binlog实现主备同步，其中实现的主要原理是binlog。在主备同步中从库B会设置为readonly。在状态 1 中，客户端的读写都直接访问节点 A，而节点 B 是 A 的备库，只是将 A 的更新都同步过来，到本地执行。这样可以保持节点 B 和 A 的数据是相同的。当需要切换的时候，就切成状态 2。这时候客户端读写访问的都是节点 B，而节点 A 是 B 的备库。
在这里插入图片描述
备库 B 跟主库 A 之间维持了一个长连接。主库 A 内部有一个线程，专门用于服务备库 B 的这个长连接。一个事务日志同步的完整过程是这样的：

在备库 B 上通过 change master 命令，设置主库 A 的 IP、端口、用户名、密码，以及要从哪个位置开始请求 binlog，这个位置包含文件名和日志偏移量。
在备库 B 上执行 start slave 命令，这时候备库会启动两个线程，就是图中的 io_thread 和 sql_thread。其中 io_thread 负责与主库建立连接。
主库 A 校验完用户名、密码后，开始按照备库 B 传过来的位置，从本地读取 binlog，发给 B。
备库 B 拿到 binlog 后，写到本地文件，称为中转日志（relay log）。
sql_thread 读取中转日志，解析出日志里的命令，并执行。

binlog三种格式对比

binlog_format=statement 时，binlog 里面记录的就是 SQL 语句的原文。（可能造成主备不一致，原因在于如果删除语句加了limit，那么主库备库可能使用的索引不一样，导致主备不一致）。
binlog_format=‘row’，借助 mysqlbinlog 工具，binlog 里面记录了真实删除行的主键 id（但是占空间较大）。
binlog_format=‘mixed’:statement格式会造成主备不一致，row格式又比较占空间，设置为 mixed 后，就会记录为 row 格式；而如果执行的语句去掉 limit 1，就会记录为 statement 格式。

目前最优row,其原因在于：

delete 语句，row 格式的 binlog 也会把被删掉的行的整行信息保存起来。所以，如果你在执行完一条 delete 语句以后，发现删错数据了，可以直接把 binlog 中记录的 delete 语句转成 insert，把被错删的数据插入回去就可以恢复了。
insert 语句，那就更直接了。row 格式下，insert 语句的 binlog 里会记录所有的字段信息，这些信息可以用来精确定位刚刚被插入的那一行。这时，你直接把 insert 语句转成 delete 语句，删除掉这被误插入的一行数据就可以了。
update 语句，binlog 里面会记录修改前整行的数据和修改后的整行数据。所以，如果你误执行了 update 语句的话，只需要把这个 event 前后的两行信息对调一下，再去数据库里面执行，就能恢复这个更新操作了。

循环复制问题

文章中第一张图描述的是M-S结构下的主备同步，即一个Master一个Slave，而双M结构下互为主备关系：
在这里插入图片描述

问题背景:业务逻辑在节点 A 上更新了一条语句，然后再把生成的 binlog 发给节点 B，节点 B 执行完这条更新语句后也会生成 binlog。那么，如果节点 A 同时是节点 B 的备库，相当于又把节点 B 新生成的 binlog 拿过来执行了一次，然后节点 A 和 B 间，会不断地循环执行这个更新语句，也就是循环复制了。
解决办法(判断serverid，相同则丢弃):