如何设置从库只读连接并防止误写，实现高效的多服务器主从管理？

本文共计1190个文字，预计阅读时间需要5分钟。

相关专题

MySQL 从库 read_only=1 为什么设了还被写入？

根本原因不是没设，而是设得不完整。mysql 的 read_only 对 super 权限用户无效，而很多运维脚本、监控工具、甚至 dba 自己连的账号都带 super（比如 root 或用 mysql_upgrade 创建的账号）。这时候 read_only=1 形同虚设。

• 必须同步设置 super_read_only=1（5.7.20+ / 8.0 默认启用），它会拦截所有 super 用户的写操作
• 检查当前生效值：SELECT @@read_only, @@super_read_only;，两个都得是 1
• 如果用的是 MySQL 5.6，super_read_only 不存在，只能靠权限控制——删掉从库上所有非只读账号的 UPDATE/INSERT/DELETE/DROP/ALTER 权限，并禁用 SUPER
• 配置文件里写死比运行时 SET 更可靠：read_only = ON 和 super_read_only = ON 放进 my.cnf 的 [mysqld] 段，重启生效

主从切换后从库变主库，read_only 怎么自动关闭？

手动改配置或执行 SET 不现实，一出故障就靠人盯，延迟和误操作风险极高。得让高可用组件（如 MHA、Orchestrator、ProxySQL 或自研脚本）在提升为新主库时主动执行关闭动作。

• MHA：在 master_ip_failover 脚本末尾加一行：mysql -h新主IP -e "SET GLOBAL read_only=0; SET GLOBAL super_read_only=0;"
• Orchestrator：用 PostMasterFailoverProcesses 配置项调用外部脚本，脚本中判断 isMaster 状态后再改变量
• 注意：不要在旧主恢复后自动开 read_only，它此时是“待重建从库”，应由重建流程统一初始化，避免状态错乱
• 验证点：切换完成后立刻查 SHOW SLAVE STATUS\G，如果 Slave_IO_Running: Yes 但 Slave_SQL_Running: No，大概率是新主上 read_only 没关，SQL 线程被拒

应用连接池怎么区分主库写 / 从库读，又不硬编码地址？

靠应用层自己判断 IP 或端口属于主还是从，等于把拓扑逻辑写死进代码，扩容、切换、灰度全要改代码。更稳的方式是交由中间件或 DNS 层抽象。

• 用 ProxySQL：建两个 hostgroup，hg_id=10 给主库，hg_id=20 给从库；再配 mysql_query_rules 把 SELECT 导到 20，其余导到 10；应用只连 ProxySQL 地址即可
• 用 DNS：维护 db-master.company.local 和 db-replica.company.local 两条 A 记录，由自动化工具（如 Consul Template + BIND）动态更新 IP；应用通过域名连接，无需感知后端变更
• 避坑：JDBC 连接串里别加 roundRobinLoadBalance=true 或 allowMultiQueries=true，这些可能绕过读写分离逻辑，直接发到第一个可用节点
• 验证方法：在从库上开启 general_log，跑一次业务请求，看日志里有没有 UPDATE 或 INSERT 出现

多机房部署下，跨机房从库要不要开 read_only？

要，而且更得开。跨机房延迟高、网络抖动频繁，一旦主库异常，容易出现“脑裂式”双主——两个机房各自认为自己是主，同时接受写入，数据彻底不一致。

• 强制所有从库（无论同城还是异地）默认 read_only=1 + super_read_only=1，哪怕它物理上离主库很近
• 异地从库额外加一层保护：在 my.cnf 里设 relay_log_purge=OFF，防止 SQL 线程崩溃后 relay log 被清空，保留追平依据
• 监控必须覆盖 Seconds_Behind_Master 和 Slave_SQL_Running_State，延迟突增或卡在 “Reading event from the relay log” 是典型信号
• 真正麻烦的是“伪从库”：有些服务把从库当缓存用，直接写本地表（比如 session 表），这种得从架构上拆走，不能靠数据库参数拦住

最易被忽略的一点：read_only 不防 DDL 创建临时表，CREATE TEMPORARY TABLE 在从库上仍可执行，且不会报错——但它不影响复制，所以不是漏洞，只是容易让人误以为“能写就是没锁住”，其实只是 MySQL 对临时表的特殊放行。