mysql read-only_MySQL的read-only参数

使用read-only，可以让整个mysqld实例处于只读的状态，但是要注意的是，对于拥有SUPER权限的超级用户来说，read-only是无法做限制的。因此对外，我们只能提供普通用户的权限。

read-only可以用于主从同步的从库上，可以很方便的实现从库的安全性。

设置的方法很简单，有两种方法：

1)vi /etc/my.cnfhttps://www.cndba.cn/leo1990/article/2963https://www.cndba.cn/leo1990/article/2963

https://www.cndba.cn/leo1990/article/2963

在my.cnf的[mysqld]下输入read-only=1，然后重启服务；

mysql> show variables like 'read_only';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| read_only | ON |

+---------------+-------+

2)在线修改全局参数read_onlyhttps://www.cndba.cn/leo1990/article/2963

https://www.cndba.cn/leo1990/article/2963https://www.cndba.cn/leo1990/article/2963

mysql> grant select,insert,update,delete on *.* to 'test'@'%' identified by '123456';

Query OK, 0 rows affected, 1 warning (0.10 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.03 sec)

用普通用户验证数据是否可以修改

[root@cndba ~]# mysql -utest -p123456

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or /g.

Your MySQL connection id is 9

Server version: 5.7.17-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '/h' for help. Type '/c' to clear the current input statement.

mysql> use test

Database changed

mysql> insert into test values(1,2);

ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement

普通用户提示不可以修改数据

用supper用户验证数据是否可以修改

[root@cndba ~]# mysql -uroot -proot

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or /g.

Your MySQL connection id is 7

Server version: 5.7.17-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '/h' for help. Type '/c' to clear the current input statement.

mysql> insert test values('9','ff');

Query OK, 1 row affected (0.15 sec)

具有super权限的用户可以修改数据，说明此参数对具有super权限的用户无效。

如果想super权限用户也不能修改数据则在线修改全局参数super_read_onlyhttps://www.cndba.cn/leo1990/article/2963https://www.cndba.cn/leo1990/article/2963

https://www.cndba.cn/leo1990/article/2963https://www.cndba.cn/leo1990/article/2963

[root@cndba ~]# mysql -uroot -proot

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or /g.

Your MySQL connection id is 13

Server version: 5.7.17-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '/h' for help. Type '/c' to clear the current input statement.

mysql> SET GLOBAL super_read_only=1;

Query OK, 0 rows affected (0.00 sec)

mysql> use test

Database changed

mysql> insert into test values(9,'11');

ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement

修改super_read_only参数具有super权限的用户也无法修改表数据

版权声明：本文为博主原创文章，未经博主允许不得转载。