Mysql从库read-only配置详解
Mysql从库read-only配置详解www.111cn.net 更新:2015-03-06 编辑:kp12345 来源:转载
在做mysql主从同步时我们会碰到一个从库read-only了，下面小编来为各位介绍Mysql从库read-only配置详解，希望文章对各位有用．
为了防止用户对从库进行插入，采用read-only参数：
配置：
# grep read-only /etc/my.cnf
read-only
或者
read-only = 1
试验过程：
主库授权ALL
mysql> grant all on *.* to 'imbyrd'@'localhost'> 从库测试：
# /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'> mysql> show grants for imbyrd@'localhost';
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for imbyrd@localhost |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'imbyrd'@'localhost'> +----------------------------------------------------------------------------------------------------------------------------------------+
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'> mysql> show grants for imbyrd@'localhost'＼G
*************************** 1. row ***************************
Grants for imbyrd@localhost: GRANT ALL PRIVILEGES ON *.* TO 'imbyrd'@'localhost'> 1 row in set (0.00 sec)
mysql> REVOKE SUPER ON *.* FROM 'imbyrd'@'localhost';
mysql> show grants for imbyrd@'localhost'＼G
*************************** 1. row ***************************
Grants for imbyrd@localhost: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP,> 1 row in set (0.00 sec)
查看从库的read_only变量：
mysql> show variables like 'read_only';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only | ON |
+---------------+-------+
1 row in set (0.00 sec)
查看数据库变量：
mysql> show variables;
从库测试：
# /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生效！
原文：http://www.111cn.net/database/mysql/79628.htm
页:
[1]