mysql主从复制中的数据一致性保证与问题处理

主从复制延迟导致读到旧数据时，强一致性读必须走主库；从库只读需回收SUPER权限并配置super_read_only；GTID模式下须严防errant transaction。

主从复制延迟导致 select 读到旧数据怎么办

mysql 主从默认异步复制，INSERT/UPDATE/delete 在主库执行成功后，从库可能还没应用 binlog，此时应用直接连从库查数据，就会看到过期状态。这不是 bug，是设计使然。

• 业务上能接受“最终一致性”的场景（如日志、统计报表），可继续读从库，但需在代码里明确注释该查询不保证实时
• 强一致性要求的读操作（如订单支付后立即查余额），必须走主库，或使用 SELECT ... for UPDATE + 主库事务兜底
• 想折中？可用 SELECT SLEEP(0.1) 等待短时间再查——不推荐；更靠谱的是监控 Seconds_Behind_Master，超阈值（如 > 1s）时自动切主库
• 注意：SHOW SLAVE STATUS 中的 Seconds_Behind_Master 是估算值，大事务或网络抖动时可能不准，不能作为唯一判断依据

SET GLOBAL read_only = ON 在从库上没生效？

从库设为只读是防误写的基本操作，但常有人发现设了之后仍能执行 INSERT。根本原因是：只有 super 权限用户才被 read_only 限制，普通用户不受影响；而 root 默认有 super 权限。

• 正确做法：先执行 SET GLOBAL read_only = ON，再回收 root 或其他高权限账号的 SUPER 权限（用 REVOKE SUPER ON *.* FROM 'user'@'host'）
• 更安全的方式是用 super_read_only = ON（MySQL 5.7.8+），它会同时限制 super 用户的写操作
• 注意：这两个变量都只是会话级防护，重启后失效，必须写进配置文件 my.cnf 的 [mysqld] 段落并重启 mysqld 才持久

主库 crash 后从库 Relay_Log_Pos 和 Exec_Master_Log_Pos 不一致怎么处理

这是典型的数据断点不一致问题：主库宕机前最后几条 binlog 没来得及传到从库，或从库已接收但没来得及执行完就中断了。此时 SHOW SLAVE STATUS 中两个位置点对不上，Slave_SQL_Running_State 可能卡在 “Reading Event from the relay log”。

• 先确认主库恢复后最新的 File 和 position（用 SHOW MASTER STATUS）
• 若从库 relay log 还完整，用 mysqlbinlog 解析对应 relay log 文件，找到最后成功执行的 GTID 或 position，再用 CHANGE MASTER TO ... MASTER_LOG_FILE='xxx', MASTER_LOG_POS=yyy 手动跳过损坏段
• 如果 relay log 已损坏或不确定，最稳妥是重建从库：停掉 SQL 线程 → 备份主库当前数据 → 在从库上 RESET SLAVE ALL → 导入备份 → CHANGE MASTER TO 指向主库最新位点
• 别依赖 SLAVE_SKIP_COUNTER 跳过错误，它只跳过一个 event，且不适用于 GTID 模式

GTID 模式下 Errant transaction 导致主从无法切换

当从库手动执行了写操作（比如绕过 read_only），这个事务会在从库生成一个主库没有的 GTID，称为 errant transaction。后续如果做主从切换，新主库会拒绝来自旧主库的 binlog（因为 GTID 集合冲突），报错类似 The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires。

• 检查方法：在从库运行 SELECT * FROM performance_schema.replication_applier_status_by_coordinator;，看是否有未执行的 GTID；再比对 SELECT @@GLOBAL.GTID_EXECUTED; 在主从之间的差异
• 修复方式：在从库执行 SET GTID_NEXT='xxx-yyy-zzz:nnn'; BEGIN; COMMIT; 伪造一个空事务，把 errant GTID “占位”掉，再 SET GTID_NEXT='AUTOMATIC'
• 但伪造前必须确保该 GTID 确实没在主库执行过，否则会引发主键冲突或数据覆盖——所以生产环境强烈建议禁用从库写，并定期用 pt-table-checksum 校验数据一致性

GTID 的自动定位能力很强大，但一旦混入非同步写入，修复成本远高于预防。日常运维中，比监控延迟更值得花精力的，是守住“从库禁止写入”这条红线。