MySQL 的read_only & super_read_only

1概述

在一次项目配置主从同步时一直有1023 报错，因为主备数据不一致导致的，数据库中没有使用触发器，slave服务器my.cnf上也配置了read-only选项，为什么还可以在slave中插入/更新数据呢？
经过一番不懈努力，找到了原因：因为使用的是具有super权限的帐号连接的，恰好服务请求配置错了，配置到了从库，导致从库有数据写入，导致主备数据不一致，主从链路异常，改用普通帐号从库就不可写入了，
<1>也就是从库连接账号授权时不能指定有super或all权限（一般情况）;
<2>修改参数 super_read_only ，默认值为OFF，修改为ON，可以使super 用户read-only。

《read_only

Command-Line Format    --read-only[={OFF|ON}]
System Variable    read_only
Scope    Global
Dynamic    Yes
Type    Boolean
Default Value    OFF
If the read_only system variable is enabled, the server permits no client updates except from users who have the SUPER privilege. This variable is disabled by default.

The server also supports a super_read_only system variable (disabled by default), which has these effects:

If super_read_only is enabled, the server prohibits client updates, even from users who have the SUPER privilege.

Setting super_read_only to ON implicitly forces read_only to ON.

Setting read_only to OFF implicitly forces super_read_only to OFF.

如果read_only启用了系统变量，则除了具有SUPER特权的用户之外，服务器不允许客户端更新 。默认情况下禁用此变量。

服务器还支持 super_read_only系统变量（默认禁用），具有以下效果：

如果super_read_only启用，服务器将禁止客户端更新，即使是具有SUPER 权限的用户。

设置super_read_only 为ON隐式强制 read_only为 ON。

设置read_only为 OFF隐式强制 super_read_only为 OFF。》
以上摘自官网https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html

2验证

下面简单测试一下：

2.1 参数值查看

mysql> show global variables like 'read-only%';
Empty set (0.00 sec)

mysql>
mysql> show global variables like 'read_only%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql>
mysql> show global variables like 'super_read%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| super_read_only | OFF   |
+-----------------+-------+
1 row in set (0.01 sec)

mysql>

2.2创建测试用户并授权all

mysql> CREATE USER 'readtest'@'localhost' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> grant all on *.* to 'readtest'@'localhost';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> ^DBye

2.3测试用户登录建表

[root@test-centos ~]# mysql -ureadtest -p
Enter password:
Server version: 5.7.28-log MySQL Community Server (GPL)
mysql>
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| readme_ccc         |
| sys                |
| test              |
+--------------------+
6 rows in set (0.00 sec)
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>
mysql> create table retest (a int);
Query OK, 0 rows affected (0.02 sec)


mysql> drop table retest;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> ^DBye

read-only状态下，super权限用户可以写入。

2.4修改收回测试用户权限

[root@test-centos ~]# mysql -uroot -p
Enter password:
mysql>
mysql> revoke all on *.* from 'readtest'@'localhost';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql>
mysql> ^DBye

2.5测试用户登录写入测试

[root@test-centos ~]# mysql -ureadtest -p
Enter password:
Server version: 5.7.28-log MySQL Community Server (GPL)
mysql> use test;
ERROR 1044 (42000): Access denied for user 'readtest'@'localhost' to database 'test'
mysql> ^DBye

没有操作权限。

2.6修改测试用户权限为没有super

[root@test-centos ~]# mysql -uroot -p
Enter password:
mysql>
mysql> grant all on *.* to 'readtest'@'localhost';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> revoke super on *.* from 'readtest'@'localhost';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> ^DBye

2.7测试用户登录写入测试

[root@test-centos ~]# mysql -ureadtest -p
Enter password:
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql>
mysql> create table retest (a int);
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement
mysql>

没有super权限用户在read-only库不可写入。

mysql> set global super_read_only=on;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> create table test (a int);
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement
mysql>

super_read_only配置为on，所有用户不可写入。

3 参数配置

3.1打开super_read_only=on， read_only也会隐式强制打开为on；

mysql> show global variables like 'read_only%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | ON    |
+---------------+-------+
1 row in set (0.00 sec)
mysql> set global super_read_only=off;
Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like 'read_only%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> set global read_only=off;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> show global variables like 'read_only%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | OFF   |
+---------------+-------+
1 row in set (0.01 sec)

mysql> set global super_read_only=on;
Query OK, 0 rows affected (0.01 sec)

mysql> show global variables like 'read_only%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql>

3.2设置read_only为OFF，super-read-only 会隐式强制转为OFF

mysql> show global variables like '%read_only%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_read_only      | OFF   |
| read_only             | ON    |
| super_read_only       | ON    |
| transaction_read_only | OFF   |
| tx_read_only          | OFF   |
+-----------------------+-------+
5 rows in set (0.04 sec)
mysql> set global read_only=off;
Query OK, 0 rows affected (0.01 sec)

mysql> show global variables like '%read_only%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_read_only      | OFF   |
| read_only             | OFF   |
| super_read_only       | OFF   |
| transaction_read_only | OFF   |
| tx_read_only          | OFF   |
+-----------------------+-------+
5 rows in set (0.01 sec)

mysql>