mysql如何设置只读模式_设置ReadOnly参数防止非法DML写入-mysql教程-PHP中文网

mysql只读模式需分三层：实例级（read_only/super_read_only限制所有写操作，后者禁super用户）、库级（alter database ... read only，8.0.22+支持单库只读）、用户级（grant select并revoke写权限，避免权限叠加）。三者机制独立，混用易失效。

mysql如何设置只读模式_设置readonly参数防止非法dml写入

MySQL 的只读模式不能靠“加个开关”就一劳永逸，必须分清是限制用户、限制实例，还是限制单个数据库——三者机制完全不同，混用会导致权限失效或误拦。

read_only 和 super_read_only 是实例级开关，不是用户权限

这两个参数控制的是整个 MySQL 实例的写入能力，和账号权限体系无关。开启 read_only = ON 后，普通用户执行 INSERT、UPDATE、DROP 等语句会直接报错：ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement。

但拥有 SUPER 权限的用户（如默认 root）仍可绕过 —— 这正是容易踩坑的地方：你以为设了只读就安全了，结果运维人员或监控脚本用 root 登录照样能删库。

要真正锁死所有写操作，必须同时启用 super_read_only = ON（MySQL 5.7.8+ 支持）
super_read_only = ON 会隐式设置 read_only = ON，但反过来不成立
动态启用需有 SUPER 权限：SET GLOBAL read_only = ON;、SET GLOBAL super_read_only = ON;
配置文件中写入更可靠（避免重启失效）：
```
[mysqld]
read_only = ON
super_read_only = ON
```

ALTER DATABASE ... READ ONLY 是库级控制，MySQL 8.0.22+ 才有

这是唯一能对单个数据库生效的只读机制，和 read_only 参数完全独立。比如只想让 backup_db 不可写，其他库照常使用：

ALTER DATABASE `backup_db` READ ONLY = 1;

它立即生效，且对所有连接（包括已存在的）都起作用。但要注意：

MySQL

编写正确的MySQL查询，避免字符集、索引和锁方面的常见陷阱。

下载

仅限 MySQL 8.0.22 及以上版本，低版本执行会报错：ERROR 1064 (42000): You have an error in your SQL syntax
它禁止对该库内所有表的 DML 和 DDL，包括 CREATE TABLE、TRUNCATE、DROP DATABASE（即使你有 SUPER 权限）
它不阻止跨库操作，比如 INSERT INTO backup_db.t1 SELECT * FROM prod_db.t2 仍可能成功（取决于目标库权限）
关闭方式：ALTER DATABASE `backup_db` READ ONLY = 0;

只读用户 ≠ 只读实例，权限必须显式清理

很多人以为 GRANT SELECT ON db.* TO 'ro'@'%' 就万事大吉，结果发现该用户还能 DELETE。这是因为 MySQL 权限是叠加的：如果之前授过 ALL PRIVILEGES 或残留了旧权限，GRANT SELECT 不会自动覆盖或撤销。

真正安全的做法是先清再授：

检查当前权限：SHOW GRANTS FOR 'ro'@'%';
显式回收所有写权限：REVOKE INSERT, UPDATE, DELETE, DROP, CREATE, ALTER, INDEX, LOCK TABLES, EXECUTE ON *.* FROM 'ro'@'%';
再授予最小集合：GRANT SELECT ON `mydb`.* TO 'ro'@'192.168.1.%';
别漏掉 FLUSH PRIVILEGES;（尤其在手动修改 mysql.user 表后）
注意 information_schema 访问：MySQL 8.0+ 中 SHOW CREATE TABLE 可能失败，需额外授权：GRANT SELECT ON `information_schema`.`TABLES` TO 'ro'@'%';

验证只读是否真生效，不能只看命令返回

光查 SHOW VARIABLES LIKE 'read_only'; 返回 ON 不代表业务安全。必须用实际账号做行为验证：

用只读账号执行 SELECT COUNT(*) FROM t1; → 应成功
执行 INSERT INTO t1(id) VALUES(1); → 必须报 ERROR 1142（权限拒绝）或 ERROR 1290（实例只读）
执行 SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST; → 若报错，说明系统库访问被限制，需按需补授权
检查复制状态：SHOW SLAVE STATUS\G 中 SQL_Running 和 IO_Running 必须为 Yes，否则只读设置可能意外中断了从库同步

最易被忽略的一点：活跃连接不会自动刷新权限。改完权限后，旧连接仍持有历史权限快照，必须重新登录才能验证真实效果。