【MySQL 5.7.19】主从复制,修改从库为只读
xixuefeng
MySQL, MySQL-Install
2017-08-25 15:06:32
2,879 次浏览
【MySQL 5.7.19】主从复制,修改从库为只读已关闭评论
MySQL配置好主从复制后,从库是可以做DML操作的。如果是单向复制,那么从库做了DML操作后就会导致主从不一致的情况,避免这种情况的发生,可以将从库改为只读状态(修改后,并不会影响主从复制)。
1、读写改为只读
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 |
## root登录mysql [root@mss mysql]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 10 Server version: 5.7.19-log MySQL Community Server (GPL) Copyright (c) 2000, 2017, 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> ## 默认情况下,read only 参数为off,也就是没有启用只读模式 mysql> show variables like 'read_only'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | read_only | OFF | +---------------+-------+ 1 row in set (0.00 sec) mysql> ## 打开只读参数,当 read only 为 ON 时,只是限制了普通用户只读,特权用户还是可以做DML mysql> set global read_only=1; Query OK, 0 rows affected (0.00 sec) mysql> mysql> show variables like 'read_only'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | read_only | ON | +---------------+-------+ 1 row in set (0.00 sec) mysql> ## 为了避免特权用户做DML操作,所以,还需要给所有的表加上读锁 mysql> flush tables with read lock; Query OK, 0 rows affected (0.00 sec) mysql> ## 验证 mysql> use dm Database changed mysql> create table ttt(x int); ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock mysql> |
2、只读恢复为读写
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
mysql> show variables like 'read_only'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | read_only | ON | +---------------+-------+ 1 row in set (0.00 sec) mysql> mysql> unlock tables; Query OK, 0 rows affected (0.00 sec) mysql> mysql> set global read_only=0; Query OK, 0 rows affected (0.00 sec) mysql> show variables like 'read_only'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | read_only | OFF | +---------------+-------+ 1 row in set (0.00 sec) mysql> |