MySQL设置从库只读模式
可以理解为开设一个账号,权限为只读
创建并授权用户
GRANT select ON *.* to 'test_readonly'@'localhost' identified by '1234';
不必先insert into user…以后再进行授权,以上语句是创建用户的同时并且已授权
mysql> set global read_only=1;
Query OK, 0 rows affected (0.00 sec)
#set global read_only=0 为取消普通账号的只读模式
注意:set global read_only=1 对拥有super权限的账号是不生效的,所以在授权账号的时候尽量避免添加super权限
那么我们在做数据迁移的时候不想发生任何数据的修改,包括super权限修改也要限制。
可以用锁表:
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.18 sec)
使用root账号测试:
mysql> delete from student where sid=13;
ERROR 1223 (HY000): Can't execute the query because you have a conflicting read
lock
解锁测试:
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from student where sid=13;
Query OK, 0 rows affected (0.00 sec)