一、背景介绍

MySQL read_only参数可以让整个MySQL实例普通权限用户处于只读状态，但是其并不能限制拥有super权限的用户。read_only参数一般是用于主从复制从库的配置，目的是为了规避从库误写数据，导致主从复制异常或者主从数据不一致的隐患。另外，为了避免从库被super权限用户误写数据，MySQL官方在MySQL5.7版本引入了super_read_only参数来限制super用户在从库的只读属性。

官网地址：https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_super_read_only

二、配置方法

2.1 ROOT用户获取SUPER权限

update mysql.user set Super_priv='Y' where User='root'; # root用户获取超级权限
flush privileges;                                       # 刷新权限表

2.2 命令临时设置只读参数

（1）查看read_only与super_read_only设置

# 查看当前read_only和super_read_only设置（从库执行）
mysql> SELECT @@global.read_only, @@global.super_read_only;
+--------------------+--------------------------+
| @@global.read_only | @@global.super_read_only |
+--------------------+--------------------------+
|                  1 |                        0 |
+--------------------+--------------------------+
1 row in set (0.01 sec)

# 查看当前连接用户信息（从库执行）
mysql> select user() ,current_user();
+-------------------+----------------+
| user()            | current_user() |
+-------------------+----------------+
| root@10.25.102.12 | root@%         |
+-------------------+----------------+
1 row in set (0.00 sec)

# 查看root用户当前拥有的权限，可以看出此时root用户没有super权限
mysql> show grants for 'root'@'%'\G;（从库执行）
*************************** 1. row ***************************
Grants for root@%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE ON *.* TO 'root'@'%' WITH GRANT OPTION
1 row in set (0.00 sec)

# root用户在从库创建数据库db1（从库执行）
mysql> create database db1;
Query OK, 1 row affected (0.00 sec)

（2）设置super_read_only配置

# root用户获取超级权限（主库执行）
mysql> update mysql.user set Super_priv='Y' where User='root';
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> flush privileges; 
Query OK, 0 rows affected (0.00 sec)

# 设置super_read_only参数（从库执行）
mysql> set global super_read_only=1;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@global.read_only, @@global.super_read_only; 
+--------------------+--------------------------+
| @@global.read_only | @@global.super_read_only |
+--------------------+--------------------------+
|                  1 |                        1 |
+--------------------+--------------------------+
1 row in set (0.00 sec)

# 查看当前root用户已经拥有super权限（主库执行）
mysql> show grants for 'root'@'%'\G;
*************************** 1. row ***************************
Grants for root@%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE ON *.* TO 'root'@'%' WITH GRANT OPTION
1 row in set (0.00 sec)

# 设置完super_read_only参数后，从库再次创建数据库，出现了报错
mysql> create database db2;
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement

注：由上可以看出，设置完super_read_only参数后，即使拥有super权限的用户，也不能在从库写数据！！！

作者：UStarGao
链接：https://www.starcto.com/mysql/276.html
来源：STARCTO
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

UCloud新用户专属注册连接

UCloud CDN超值特惠专场

UCloud全球云主机（UHost/VPS）大促页面

UCloud快杰云主机大促页面