网站分类代码,保定网络营销推广,施工企业合同管理制度,网站建设网上商城心得体会文章目录 MySQL45讲 第二十四讲 MySQL是怎么保证主备一致的#xff1f;——阅读总结一、MySQL 主备基本原理#xff08;一#xff09;主备切换流程#xff08;二#xff09;主备数据同步流程 二、binlog 格式及相关问题#xff08;一#xff09;binlog 的三种格式#… 文章目录 MySQL45讲 第二十四讲 MySQL是怎么保证主备一致的——阅读总结一、MySQL 主备基本原理一主备切换流程二主备数据同步流程 二、binlog 格式及相关问题一binlog 的三种格式二格式对比与选择 三、循环复制问题及解决一循环复制现象二解决逻辑 四、总结与思考 MySQL45讲 第二十四讲 MySQL是怎么保证主备一致的——阅读总结 在 MySQL 数据库的世界里主备一致性是确保数据可靠性和高可用性的关键。就深入探讨 MySQL 是如何保证主备一致的这涉及到 binlog 的多种格式、主备复制的详细流程以及双 M 结构下的循环复制问题等诸多重要知识点。 一、MySQL 主备基本原理
一主备切换流程 状态 1 如图 1 所示客户端的读写直接访问节点 A节点 B 作为 A 的备库通过同步 A 的更新来保持数据一致。此时建议将备库 B 设置为只读readonly模式原因如下 防止运营类查询语句在备库上的误操作。避免切换逻辑出现 bug如双写导致主备不一致。可依据 readonly 状态判断节点角色。设置为 readonly那主库怎么和备库进行同步同步更新的线程拥有超级super权限仍可进行数据同步。 状态 2当需要切换时客户端读写访问切换到节点 B节点 A 成为 B 的备库。
二主备数据同步流程
在这里插入图片描述
备库设置在备库 B 上通过 change master 命令设置主库 A 的相关信息包括 IP、端口、用户名、密码以及 binlog 的起始位置包含文件名和日志偏移量然后执行 start slave 命令启动图中 io_thread 和 sql_thread 两个线程。其中 io_thread 负责与主库建立连接。主库操作主库 A 校验完用户名和密码后按照备库 B 的请求位置读取 binlog并将其发送给 B。备库接收与执行备库 B 收到 binlog 后写入本地中转日志relay logsql_thread 读取 relay log解析并执行其中的命令。 二、binlog 格式及相关问题
一binlog 的三种格式 如果要在表中删除一行数据的话我们来看看这个delete语句的binlog是怎么记录的。 mysql CREATE TABLE t (
id int(11) NOTNULL,
a int(11) DEFAULTNULL,
t_modified timestamp NOTNULL DEFAULTCURRENT_TIMESTAMP,
PRIMARY KEY (id),
KEY a (a),
KEY t_modified(t_modified)
) ENGINEInnoDB;
insert into t values(1,1,2018-11-13);
insert into t values(2,2,2018-11-12);
insert into t values(3,3,2018-11-11);
insert into t values(4,4,2018-11-10);
insert into t values(5,5,2018-11-09); statement 格式记录 SQL 语句原文例如执行delete from t /*comment*/ where a4 and t_modified2018-11-10 limit 1语句时binlog 中会如实记录该 SQL 语句包括自动添加的use test命令确保在备库能正确更新到指定库的表同时还会记录注释等信息。然而这种格式可能导致主备数据不一致如上述 delete 语句若主库和备库使用不同索引执行可能删除不同行。 row 格式不记录 SQL 语句原文而是通过 Table_map 和 Delete_rows 等事件来定义操作。例如上述 delete 语句会记录操作的表信息Table_map event以及删除行为Delete_rows event且会记录真实删除行的主键 id保证备库执行时删除正确的行。此外对于 insert 和 update 语句row 格式的 binlog 也会记录足够信息以便数据恢复如 insert 语句会记录所有字段信息update 语句会记录修改前和修改后的整行数据。 该事务从8900开始通过mysqlbinlog -vvdata/master.000001 --start-position8900;指令查看 binlog里面记录了真实删除行的主键id这样 binlog传到备库去的时候就肯定会删除id4的行不会有主备删除不同行的问题。 为什么会有mixed格式的binlog 有些statement格式的binlog虽然简单但可能会导致主备不一致所以要使用row格式。但row格式又比较占用空间所以算是一种折中的办法。mixed格式可以利用statment格式的优点同时又避免了数据不一致的风险。 mixed 格式MySQL 会判断 SQL 语句是否可能引起主备不一致若有可能则使用 row 格式否则使用 statement 格式是一种折中的方案。但在某些情况下如执行insert into t values(10,10, now())时虽可能存在主备不一致风险但仍会记录为 statement 格式不过会通过 SET TIMESTAMP 命令约定 now () 函数的返回时间确保主备数据一致性。
二格式对比与选择
数据恢复优势row 格式在数据恢复方面具有明显优势可通过反转操作恢复误执行的 delete、insert 或 update 语句而 statement 格式相对较难实现精确恢复。空间占用与性能row 格式占用空间较大写 binlog 时耗费 IO 资源影响执行速度statement 格式占用空间小但可能存在数据不一致风险。因此越来越多的场景推荐使用 row 格式的 binlog不过也可根据实际情况选择 mixed 格式而一般不建议使用单纯的 statement 格式。 三、循环复制问题及解决
一循环复制现象
在双 M 结构中业务逻辑在节点 A 更新语句后生成 binlog 发给节点 BB 执行后也生成 binlog若节点 A 同时是节点 B 的备库可能会导致循环执行更新语句即循环复制。 二解决逻辑
server id 设置规定两个库的 server id 必须不同否则不能设定为主备关系。binlog 生成规则备库在重放 binlog 过程中生成与原 binlog 的 server id 相同的新 binlog。日志接收判断每个库收到主库发来的日志后先判断 server id若与自己相同则直接丢弃从而避免循环复制。例如节点 A 更新事务的 binlog 记有 A 的 server id传给节点 B 执行后B 生成的 binlog 的 server id 也是 A 的 server id再传回 A 时A 会因 server id 相同而不处理该日志。 四、总结与思考
MySQL 通过 binlog 实现主备同步binlog 的三种格式各有优劣在保证主备一致性方面发挥着关键作用。主备复制流程涉及多个步骤和线程协作双 M 结构虽有循环复制问题但可通过 server id 机制解决。
