mysql 從庫設定read-only 對super許可權的使用者無效的問題

標籤：

由於在測試mysql主從讀寫分離的時候，用root使用者佈建從庫為read-only ，但是重啟生效後發現，root使用者照樣可以進行update insert ，此時root用有all privilege許可權，包括super（管理）許可權 測試單獨回收revoke root的super許可權後再試，發現root此時 已經不能寫操作了 因此在給普通使用者，或區分讀寫分離使用者時，注意要回收super許可權，否則對read-only無效。 另外為了防止普通使用者對從庫進行插入，在給使用者指派許可權時要回收super許可權 下面是引用網上一篇文章做例子：

配置：

[[email protected] data]# grep read-only /etc/my.cnf
read-only

實驗過程：

主庫授權ALL

mysql> grant all on *.* to ‘imbyrd‘@‘localhost‘ identified by ‘admin‘;

從庫測試：

[[email protected] data]# /usr/local/mysql/bin/mysql -uimbyrd -p‘admin‘
mysql> use hitest;
mysql> insert into test(id,name) values(14,‘fo‘);
Query OK, 1 row affected (0.14 sec)

主庫授權select,insert,update,delete

mysql> REVOKE all ON *.* FROM ‘imbyrd‘@‘localhost‘;
mysql> grant select,insert,update,delete on  *.* to ‘imbyrd‘@‘localhost‘ identified by ‘admin‘;
mysql> show grants for [email protected]‘localhost‘;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for [email protected]                                                                                                            |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO ‘imbyrd‘@‘localhost‘ IDENTIFIED BY PASSWORD ‘*4ACFE3202A5FF5CF467898FC58AAB1D615029441‘ |
+----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

從庫測試：

mysql> use hitest;
mysql> insert into test(id,name) values(16,‘dddd‘);
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement

主庫配置：

mysql> grant all on *.* to ‘imbyrd‘@‘localhost‘ identified by ‘admin‘;
mysql> show grants for [email protected]‘localhost‘＼G
*************************** 1. row ***************************
Grants for [email protected]: GRANT ALL PRIVILEGES ON *.* TO ‘imbyrd‘@‘localhost‘ IDENTIFIED BY PASSWORD ‘*4ACFE3202A5FF5CF467898FC58AAB1D615029441‘
1 row in set (0.00 sec)
mysql> REVOKE SUPER ON *.* FROM ‘imbyrd‘@‘localhost‘; 
mysql> show grants for [email protected]‘localhost‘＼G
*************************** 1. row ***************************
Grants for [email protected]: 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 ON *.* TO ‘imbyrd‘@‘localhost‘ IDENTIFIED BY PASSWORD ‘*4ACFE3202A5FF5CF467898FC58AAB1D615029441‘
1 row in set (0.00 sec)

從庫測試：

[[email protected] data]# /usr/local/mysql/bin/mysql -uimbyrd -p‘admin‘
mysql> use hitest;
mysql> insert into test(id,name) values(23,‘fddf‘);
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement

結論：當使用者權限中沒有SUPER許可權(ALL許可權是包括SUPER的)時，從庫的read-only生效！

mysql 從庫設定read-only 對super許可權的使用者無效的問題