MySQL读写控制机制与生产环境实战
1. 从一句SQL引发的运维血案SET GLOBAL read_only ON; 这条看似简单的MySQL命令曾让无数DBA在深夜被紧急电话惊醒。我至今记得第一次在生产环境误操作这个参数的场景——整个电商平台的订单系统突然变成只读模式前端支付页面疯狂报错而当时正值双十一流量高峰。这个教训让我深刻认识到越是简单的命令背后隐藏的机制越值得深究。这条命令实际上控制着MySQL实例的全局读写状态。当设置为ON时禁止所有非SUPER权限账户的写操作INSERT/UPDATE/DELETE等允许从库复制线程继续写入如果配置了复制不影响临时表的创建和写入不影响SUPER权限账户的操作2. 命令背后的运行机制解析2.1 内存与磁盘的双重生效当执行SET GLOBAL read_only ON时变化会立即体现在两个层面内存层面全局变量read_only的值被更新所有新连接立即受到限制磁盘层面MySQL 5.7自动将read_only1写入mysqld-auto.cnf文件实现持久化重要提示在MySQL 5.6及以下版本这个设置不会自动持久化重启后失效。这也是许多灵异事件的根源——明明设置了只读重启后却恢复了读写。2.2 线程级读写控制实现MySQL通过线程安全变量thd-variables.read_only控制每个连接的读写权限。当执行写操作时会调用check_readonly()函数进行验证bool check_readonly(THD *thd, bool throw_error) { if (thd-variables.read_only) { if (throw_error) my_error(ER_OPTION_PREVENTS_STATEMENT, MYF(0), --read-only); return true; } return false; }3. 生产环境中的典型应用场景3.1 主从切换的标准流程在计划内主从切换时标准的操作序列应该是在原主库执行SET GLOBAL read_only ON; FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS; -- 记录binlog位置在从库执行STOP SLAVE; RESET SLAVE ALL; SET GLOBAL read_only OFF;修改应用连接串指向新主库3.2 数据迁移保护措施进行大规模数据迁移时我习惯采用以下防护组合SET GLOBAL read_only ON; SET GLOBAL super_read_only ON; -- MySQL 5.7 SET GLOBAL offline_mode ON; -- MySQL 5.6这个三重锁可以防止任何意外写入read_only阻止普通用户写入super_read_only连SUPER用户也无法写入offline_mode拒绝所有新连接4. 那些年踩过的坑与解决方案4.1 复制线程被意外阻塞在配置了复制的环境中如果同时设置SET GLOBAL read_only ON; SET GLOBAL super_read_only ON;但复制账户没有足够权限会导致复制中断。正确的做法是确认复制账户有SUPER或REPLICATION_SLAVE权限使用以下安全设置顺序SET GLOBAL read_only ON; START SLAVE; -- 确保复制正常 SET GLOBAL super_read_only ON;4.2 临时表写入异常虽然文档说临时表不受影响但在某些情况下使用MEMORY存储引擎的临时表在存储过程中创建的临时表 可能仍然会触发只读错误。解决方案是CREATE TEMPORARY TABLE tmp_table (...) ENGINEInnoDB;5. 性能影响与监控要点5.1 系统变量检查开销每次写操作前MySQL都需要检查read_only状态。在高并发写入场景下这会产生可观的CPU开销。通过performance_schema可以监控SELECT * FROM performance_schema.events_waits_global WHERE EVENT_NAME LIKE %read_only%;5.2 正确的状态监控方式不建议频繁执行SHOW VARIABLES LIKE read_only来检查状态因为这会获取全局锁。更好的方法是SELECT GLOBAL.read_only, GLOBAL.super_read_only;或者通过监控系统采集mysqladmin ext | grep -i read_only6. 与相关参数的协同工作6.1 super_read_only的增强保护MySQL 5.7引入了这个强化参数SET GLOBAL super_read_only ON;它的特点是当super_read_onlyON时自动设置read_onlyON即使有SUPER权限的用户也无法写入但复制线程仍然可以正常工作6.2 与offline_mode的配合在MySQL 5.6中offline_mode可以完美补足read_only的不足SET GLOBAL offline_mode ON; SET GLOBAL read_only ON;这样既防止了新连接建立又确保了现有连接不能写入。7. 不同版本的关键差异7.1 MySQL 5.6的坑没有super_read_only参数read_only设置不会自动持久化复制账户需要REPLICATION_SLAVE权限7.2 MySQL 8.0的改进新增SET PERSIST语法持久化变量性能优化减少检查开销更好的错误提示信息8. 高可用架构中的特殊考量在MGRMySQL Group Replication环境中新加入的节点会自动设置read_onlyON只有PRIMARY节点允许写入通过以下视图检查状态SELECT * FROM performance_schema.replication_group_members;在ProxySQL中间件层还需要配置INSERT INTO mysql_query_rules (rule_id,active,match_pattern,destination_hostgroup,apply) VALUES (1,1,^SELECT,1,1),(2,1,^INSERT,2,1);9. 自动化运维中的最佳实践在Ansible剧本中我推荐这样的任务设计- name: Set database to read-only mysql_query: login_host: {{ db_host }} login_user: root login_password: {{ root_password }} query: | SET GLOBAL read_only ON; SET GLOBAL super_read_only ON; when: maintenance_mode true同时配套的验证步骤- name: Verify read-only status mysql_query: login_host: {{ db_host }} login_user: monitor login_password: {{ monitor_pass }} query: SELECT GLOBAL.read_only, GLOBAL.super_read_only register: ro_status failed_when: ro_status.query_result ! [[1, 1]]10. 从内核角度理解read_only在MySQL源码层面关键逻辑位于sql/sys_vars.ccstatic Sys_var_mybool Sys_read_only( read_only, Make all non-temporary tables read-only, GLOBAL_VAR(opt_readonly), CMD_LINE(OPT_ARG), DEFAULT(FALSE));这个全局变量opt_readonly会被多个存储引擎检查InnoDB: 在row_insert_for_mysql()中校验MyISAM: 在mi_write()中校验通过gdb调试可以观察其工作过程gdb -p $(pidof mysqld) b check_readonly continue
)
