mysql设置了read_only为什么还能写

一、背景

最近在搭建mysql主从，为了防止用户对从库进行写操作，导致主从不一致的情况出现。我将用户的super权限进行了回收，但是发现用户仍然能在设置了read_only的库上面进行写操作。这是为什么呢？

二、实验

设置数据库只读

(root@localhost)[(none)]> set global read_only = 1;

创建用户，并回收super权限

(root@localhost)[(none)]> create user scott@'%' identified by 'tiger';
(root@localhost)[(none)]> grant all on *.* to scott;
(root@localhost)[(none)]> revoke super on *.* from scott;

新开一个窗口

[root@xbz ~]# mysql -uscott -ptiger

(scott@localhost)[(none)]> show global variables like 'read_only';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | ON    |
+---------------+-------+

(scott@localhost)[(none)]> show grants;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for scott@%                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `scott`@`%`                                                                                                                                                                                                                           |
| GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `scott`@`%` |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

(scott@localhost)[(none)]> insert into hello.t1 values(100,'abc');
Query OK, 1 row affected (0.00 sec)

可以看到用户已经没有super权限，但是仍然能在设置了read_only参数的库上进行写操作。通过查阅官方文档，发现这是由于在8.0.18之后，mysql添加了CONNECTION_ADMIN这个权限导致的。官方文档搜索“CONNECTION_ADMIN”

为了验证，再用root回收scott的CONNECTION_ADMIN权限

(root@localhost)[(none)]> revoke CONNECTION_ADMIN on *.* from scott;

回到scott用户窗口，发现执行insert就报错了

(scott@localhost)[(none)]> insert into hello.t1 values(100,'abc');
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement

***************** 实验到此就结束了 *****************

以下面的方式创建一个用户

(root@localhost)[(none)]> create user tom@'%' identified by 'tom';
(root@localhost)[(none)]> grant all on hello.* to scott;

新开一个窗口

[root@xbz ~]# mysql -utom -ptom

(tom@localhost)[(none)]> insert into hello.t1 values(100,'abc');
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement

可以看到如果以库名.*去赋权限用户的话，用户是没办法对设置了read_only参数的库进行修改的，这一点与*.*赋权不同。

三、总结

1. read_only对拥有super和connection_admin这两个权限的用户无效。
2. 尽量用库名.*去赋权，而非*.*