SET GLOBAL read_only = ON；的庖丁解牛

SET GLOBAL read_only = ON;是 MySQL 中用于将实例置于只读模式的关键命令。其作用远不止“禁止写入”，而是一套涉及权限、复制、高可用切换的系统级机制。

一、命令本质与作用机制

1.功能定义

• 开启后：除具有SUPER权限（MySQL 8.0 为SYSTEM_USER/SYSTEM_VARIABLES_ADMIN）的用户外，所有会话禁止执行写操作。
• 写操作包括：
  • DML：INSERT、UPDATE、DELETE、REPLACE
  • DDL：CREATE、ALTER、DROP
  • 权限变更：GRANT、REVOKE
  • 事务控制：BEGIN/START TRANSACTION仍允许，但COMMIT若含写操作则失败

2.内部实现

• 状态标志：MySQL 服务器维护全局变量read_only（布尔值）。
• 权限检查点：
  在SQL 层解析后、执行前，调用check_readonly()函数：

  if(read_only&&!thd->security_context->has_super()){my_error(ER_OPTION_PREVENTS_STATEMENT,...);returntrue;// 拒绝执行}

• 不涉及存储引擎：InnoDB/MyISAM 本身无“只读”状态，完全由 Server 层拦截。

二、作用域与权限控制

✅关键设计：
复制线程必须能写入，否则主从架构崩溃。MySQL 内部对复制线程有特殊标识（thd->slave_thread = true），自动绕过检查。

三、典型应用场景

1.主从切换（Failover）

• 流程：
  1. 原主库执行SET GLOBAL read_only = ON;→ 禁止新写入
  2. 等待从库追平（Seconds_Behind_Master = 0）
  3. 提升从库为主库
  4. 原主库（现从库）执行CHANGE MASTER指向新主
• 目的：防止切换过程中双写导致数据不一致。

2.从库保护

• 永久设置：在从库my.cnf中配置：

  [mysqld] read_only = ON

• 效果：即使应用误连从库，也无法写入（除非用 SUPER 用户）。

3.紧急只读维护

• 场景：主库负载过高，需临时禁止写入以排查问题。
• 操作：

  SETGLOBALread_only=ON;-- 立即生效-- 排查完成后SETGLOBALread_only=OFF;

四、重要限制与陷阱

1.不阻止临时表操作

• 允许：CREATE TEMPORARY TABLE、DROP TEMPORARY TABLE
• 原因：临时表仅当前会话可见，不影响其他用户或复制。

2.不阻止非事务性操作

• 允许：
  • SET变量（会话级）
  • SELECT、SHOW、EXPLAIN
  • ANALYZE TABLE、OPTIMIZE TABLE（MyISAM）

3.GTID 模式下的特殊行为

• MySQL 5.7+ GTID：
  即使read_only=ON，GTID_PURGED等复制相关操作仍可能被允许（需 SUPER）。

4.不持久化

• 重启失效：SET GLOBAL仅运行时生效。
• 持久化方法：
  • 写入my.cnf：read_only = ON
  • MySQL 8.0+：SET PERSIST read_only = ON;（写入mysqld-auto.cnf）

五、与类似机制的区别

⚠️super_read_only更严格：
用于MGR（组复制）或InnoDB Cluster，确保实例完全只读（连 DBA 也不能写）。

六、验证与监控

1.检查当前状态

SHOWVARIABLESLIKE'read_only';-- +---------------+-------+-- | Variable_name | Value |-- +---------------+-------+-- | read_only | ON |-- +---------------+-------+

2.测试写入是否被拒

INSERTINTOtest.tVALUES(1);-- ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement

3.监控只读状态（运维）

• Prometheus：mysql_global_variables_read_only
• 日志：无直接日志，但应用会报ER_OPTION_PREVENTS_STATEMENT

七、总结：核心要点

• 目的：保护数据一致性，而非性能优化。
• 豁免：复制线程、SUPER 用户（或 MySQL 8.0 精细权限）。
• 场景：主从切换、从库保护、紧急维护。
• 风险：
  • 误开read_only导致应用写入失败
  • 未持久化导致重启后失效
• 最佳实践：
  • 从库永久配置read_only=ON
  • 主库切换时先设只读，再切从库
  • 使用super_read_only替代（如用 InnoDB Cluster）

💡本质：
read_only是MySQL 复制架构的基石安全机制，确保“一主多从”模型的数据流向可控。