如何设置MySQL数据库为只读模式以实现高效的数据读取？

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

最直接的方式是使用`SHOW + read_only + 系统变量`，它控制的是非`super`用户能否执行写操作。注意：

SELECT @@global.read_only, @@global.super_read_only;

返回值为 1 表示启用，0 表示关闭。常见误判点是只查了 session 级变量（@@read_only），但该变量不可设，仅反映 global 值的副本。

设置只读从库的正确顺序和关键参数

在主从架构中，只读应作为从库的默认状态，而非临时开关。配置需在 my.cnf 中静态设定，并配合复制状态校验：

• read_only = ON 必须写在 [mysqld] 段下，重启生效；动态 SET 不推荐用于生产从库，因重启后丢失
• 务必同时设置 super_read_only = ON（MySQL 5.7+），否则具有 SUPER 权限的账号仍可写入，破坏数据一致性
• 确保 skip_slave_start = OFF（默认），避免意外停止复制后手动启动时绕过只读检查
• 设置完成后，用 SHOW SLAVE STATUS\G 确认 Slave_IO_Running 和 Slave_SQL_Running 均为 Yes，否则只读无意义

为什么有些写操作在 read_only=ON 下仍能成功

这不是 bug，而是 MySQL 的权限与只读机制分层设计导致的。以下情况会绕过 read_only 限制：

• 用户拥有 SUPER 权限且未启用 super_read_only —— 这是最常见的漏配点
• 执行 DDL 操作如 CREATE TEMPORARY TABLE、SET SESSION 变量等，它们不修改数据库元数据或用户数据
• 使用 INSERT ... SELECT 从只读表读取但写入临时表，只要目标表非系统库且用户有权限，也可能通过
• 触发器或存储过程内部的写操作，若定义者权限足够高，可能不受限制（取决于 sql_log_bin 和定义者上下文）

只读模式对应用连接和监控的影响

应用层不能假设“只读从库”绝对安全。很多 ORM 或中间件会尝试执行 SET autocommit=1、SELECT @@tx_isolation 甚至 SELECT NOW() 等语句，这些通常没问题；但一旦应用逻辑里混入隐式写操作（比如某些框架的 session 存储自动建表），就会报错：

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

监控脚本也需适配：检查复制延迟的脚本若包含 UPDATE 或 INSERT INTO 临时表操作，必须改用纯 SELECT 方式。另外，Percona Toolkit 工具如 pt-heartbeat 默认写入，需指定 --update 以外的模式（如 --monitor）或在只读库上禁用写入逻辑。

真正麻烦的不是配置本身，而是确认所有连接用户都没有 SUPER 权限，以及所有定时任务、ETL 脚本、监控探针都经过只读环境验证——这点最容易被忽略。