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>