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_only=1写入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 (...) ENGINE=InnoDB;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_only=ON时，自动设置read_only=ON
- 即使有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. 高可用架构中的特殊考量
在MGR（MySQL Group Replication）环境中：
- 新加入的节点会自动设置read_only=ON
- 只有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.cc：
static 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