【转】mysql只读模式的设置方法与实验

1. (test01@172.32.1.200) [data03]> show tables;  
2. +------------------+  
3. | Tables_in_data03 |  
4. +------------------+  
5. | t01              |  
6. | t02              |  
7. | user             |  
8. +------------------+  
9. 3 rows in set (0.00 sec)  
10.   
11. (test01@172.32.1.200) [data03]>   
12. (test01@172.32.1.200) [data03]>   
13. (test01@172.32.1.200) [data03]> show global variables like "%read_only%";  
14. +------------------+-------+  
15. | Variable_name    | Value |  
16. +------------------+-------+  
17. | innodb_read_only | OFF   |  
18. | read_only        | ON    |  
19. | tx_read_only     | OFF   |  
20. +------------------+-------+  
21. 3 rows in set (0.00 sec)  
22.   
23. (test01@172.32.1.200) [data03]>   
24. (test01@172.32.1.200) [data03]>   
25. (test01@172.32.1.200) [data03]> delete from t01 where id1=3;  
26. <span style="color:#ff0000;">ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement</span>  
27. (test01@172.32.1.200) [data03]> update t01 set id1=id1+30 where id1=3;  
28. ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement  
29. (test01@172.32.1.200) [data03]> insert into t01(id1,a1,b1) values(9,9,9);  
30. ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement  
31. (test01@172.32.1.200) [data03]>   
32. (test01@172.32.1.200) [data03]> select * from t01;  
33. +-----+------+------+  
34. | id1 | a1   | b1   |  
35. +-----+------+------+  
36. |   1 |    1 |    1 |  
37. |   2 |    2 |    2 |  
38. |   4 |    4 |    4 |  
39. |   5 |    5 |    5 |  
40. |   6 |    6 |    6 |  
41. +-----+------+------+  
42. 5 rows in set (0.00 sec)  
43.   
44. (test01@172.32.1.200) [data03]>   

实验二：设定了全局读写后，具有super权限的用户进行数据修改后，也会提示错误ERROR 1223：

1. mysql> use data03;  
2. Reading table information for completion of table and column names  
3. You can turn off this feature to get a quicker startup with -A  
4.   
5. Database changed  
6. mysql> show tables;  
7. +------------------+  
8. | Tables_in_data03 |  
9. +------------------+  
10. | t01              |  
11. | t02              |  
12. | user             |  
13. +------------------+  
14. 3 rows in set (0.00 sec)  
15.   
16. mysql> select * from t01;  
17. +-----+------+------+  
18. | id1 | a1   | b1   |  
19. +-----+------+------+  
20. |   1 |    1 |    1 |  
21. |   2 |    2 |    2 |  
22. |   4 |    4 |    4 |  
23. |   5 |    5 |    5 |  
24. |   6 |    6 |    6 |  
25. +-----+------+------+  
26. 5 rows in set (0.00 sec)  
27.   
28. mysql>   
29. mysql>  show global variables like "%read_only%";  
30. +------------------+-------+  
31. | Variable_name    | Value |  
32. +------------------+-------+  
33. | innodb_read_only | OFF   |  
34. | read_only        | ON    |  
35. | tx_read_only     | OFF   |  
36. +------------------+-------+  
37. 3 rows in set (0.00 sec)  
38.   
39. mysql>   
40. mysql>   
41. mysql> insert into t01(id1,a1,b1) values(8,8,8);         
42. Query OK, 1 row affected (0.00 sec)  
43.   
44. mysql> update t01 set a1=a1+10 where id1=2;  
45. Query OK, 1 row affected (0.00 sec)  
46. Rows matched: 1  Changed: 1  Warnings: 0  
47.   
48. mysql> delete from t01 where id1=4;  
49. Query OK, 1 row affected (0.00 sec)  
50.   
51. mysql> select * from t01;  
52. +-----+------+------+  
53. | id1 | a1   | b1   |  
54. +-----+------+------+  
55. |   1 |    1 |    1 |  
56. |   2 |   12 |    2 |  
57. |   5 |    5 |    5 |  
58. |   6 |    6 |    6 |  
59. |   8 |    8 |    8 |  
60. +-----+------+------+  
61. 5 rows in set (0.00 sec)  
62.   
63. mysql>   
64. mysql> flush tables with read lock;  
65. Query OK, 0 rows affected (0.00 sec)  
66.   
67. mysql>   
68. mysql> insert into t01(id1,a1,b1) values(9,9,9);  
69. <span style="color:#ff0000;">ERROR 1223 (HY000): Can‘t execute the query because you have a conflicting read lock</span>  
70. mysql>   
71. mysql> update t01 set a1=a1+10 where id1=5;  
72. ERROR 1223 (HY000): Can‘t execute the query because you have a conflicting read lock  
73. mysql>   
74. mysql> delete from t01 where id1=5;  
75. ERROR 1223 (HY000): Can‘t execute the query because you have a conflicting read lock  
76. mysql>   
77. mysql>   

实验三：MySQL从库设定read_only=1后主从同步正常，设定表读锁后，不能同步，解除读锁后，主从同步恢复。

1. mysql>   
2. mysql> show databases;  
3. +--------------------+  
4. | Database           |  
5. +--------------------+  
6. | information_schema |  
7. | bitvc              |  
8. | data03             |  
9. | ga                 |  
10. | jiradb             |  
11. | meibi              |  
12. | meibi02            |  
13. | mysql              |  
14. | performance_schema |  
15. | sbtest             |  
16. +--------------------+  
17. 10 rows in set (0.00 sec)  
18.   
19. mysql>   
20. mysql>   
21. mysql>   
22. mysql>  show global variables like "%read_only%";  
23. +------------------+-------+  
24. | Variable_name    | Value |  
25. +------------------+-------+  
26. | innodb_read_only | OFF   |  
27. | read_only        | ON    |  
28. | tx_read_only     | OFF   |  
29. +------------------+-------+  
30. 3 rows in set (0.00 sec)  
31.   
32. mysql>   
33. mysql> show slave status\G  
34. *************************** 1. row ***************************  
35.                Slave_IO_State: Waiting for master to send event  
36.                   Master_Host: 172.32.1.200  
37.                   Master_User: repl  
38.                   Master_Port: 3307  
39.                 Connect_Retry: 60  
40.               Master_Log_File: mysql-bin.000009  
41.           Read_Master_Log_Pos: 5853  
42.                Relay_Log_File: huobiDBtest-relay-bin.000002  
43.                 Relay_Log_Pos: 6016  
44.         Relay_Master_Log_File: mysql-bin.000009  
45.              Slave_IO_Running: Yes  
46.             Slave_SQL_Running: Yes  
47.               Replicate_Do_DB:   
48.           Replicate_Ignore_DB:   
49.            Replicate_Do_Table:   
50.        Replicate_Ignore_Table:   
51.       Replicate_Wild_Do_Table:   
52.   Replicate_Wild_Ignore_Table:   
53.                    Last_Errno: 0  
54.                    Last_Error:   
55.                  Skip_Counter: 0  
56.           Exec_Master_Log_Pos: 5853  
57.               Relay_Log_Space: 6195  
58.               Until_Condition: None  
59.                Until_Log_File:   
60.                 Until_Log_Pos: 0  
61.            Master_SSL_Allowed: No  
62.            Master_SSL_CA_File:   
63.            Master_SSL_CA_Path:   
64.               Master_SSL_Cert:   
65.             Master_SSL_Cipher:   
66.                Master_SSL_Key:   
67.         Seconds_Behind_Master: 0  
68. Master_SSL_Verify_Server_Cert: No  
69.                 Last_IO_Errno: 0  
70.                 Last_IO_Error:   
71.                Last_SQL_Errno: 0  
72.                Last_SQL_Error:   
73.   Replicate_Ignore_Server_Ids:   
74.              Master_Server_Id: 2003307  
75.                   Master_UUID: 6f68eea7-76e9-11e4-8f99-00221904cd5d  
76.              Master_Info_File: /data/mysqldata/3308/data/master.info  
77.                     SQL_Delay: 0  
78.           SQL_Remaining_Delay: NULL  
79.       Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it  
80.            Master_Retry_Count: 86400  
81.                   Master_Bind:   
82.       Last_IO_Error_Timestamp:   
83.      Last_SQL_Error_Timestamp:   
84.                Master_SSL_Crl:   
85.            Master_SSL_Crlpath:   
86.            Retrieved_Gtid_Set:   
87.             Executed_Gtid_Set:   
88.                 Auto_Position: 0  
89. 1 row in set (0.00 sec)  
90.   
91. mysql>   
92. mysql> flush tables with read lock;  
93. Query OK, 0 rows affected (0.00 sec)  
94.   
95. mysql> show slave status\G  
96. *************************** 1. row ***************************  
97.                Slave_IO_State: Waiting for master to send event  
98.                   Master_Host: 172.32.1.200  
99.                   Master_User: repl  
100.                   Master_Port: 3307  
101.                 Connect_Retry: 60  
102.        <span style="color:#ff0000;">       Master_Log_File: mysql-bin.000009  
103.           Read_Master_Log_Pos: 6531</span>  
104.                Relay_Log_File: huobiDBtest-relay-bin.000002  
105.                 Relay_Log_Pos: 6016  
106.         Relay_Master_Log_File: mysql-bin.000009  
107.              Slave_IO_Running: Yes  
108.             Slave_SQL_Running: Yes  
109.               Replicate_Do_DB:   
110.           Replicate_Ignore_DB:   
111.            Replicate_Do_Table:   
112.        Replicate_Ignore_Table:   
113.       Replicate_Wild_Do_Table:   
114.   Replicate_Wild_Ignore_Table:   
115.                    Last_Errno: 0  
116.                    Last_Error:   
117.                  Skip_Counter: 0  
118.         <span style="color:#ff0000;">  Exec_Master_Log_Pos: 5853</span>  
119.               Relay_Log_Space: 6873  
120.               Until_Condition: None  
121.                Until_Log_File:   
122.                 Until_Log_Pos: 0  
123.            Master_SSL_Allowed: No  
124.            Master_SSL_CA_File:   
125.            Master_SSL_CA_Path:   
126.               Master_SSL_Cert:   
127.             Master_SSL_Cipher:   
128.                Master_SSL_Key:   
129.         Seconds_Behind_Master: 120  
130. Master_SSL_Verify_Server_Cert: No  
131.                 Last_IO_Errno: 0  
132.                 Last_IO_Error:   
133.                Last_SQL_Errno: 0  
134.                Last_SQL_Error:   
135.   Replicate_Ignore_Server_Ids:   
136.              Master_Server_Id: 2003307  
137.                   Master_UUID: 6f68eea7-76e9-11e4-8f99-00221904cd5d  
138.              Master_Info_File: /data/mysqldata/3308/data/master.info  
139.                     SQL_Delay: 0  
140.           SQL_Remaining_Delay: NULL  
141.       Slave_SQL_Running_State: Waiting for global read lock  
142.            Master_Retry_Count: 86400  
143.                   Master_Bind:   
144.       Last_IO_Error_Timestamp:   
145.      Last_SQL_Error_Timestamp:   
146.                Master_SSL_Crl:   
147.            Master_SSL_Crlpath:   
148.            Retrieved_Gtid_Set:   
149.             Executed_Gtid_Set:   
150.                 Auto_Position: 0  
151. 1 row in set (0.00 sec)  
152.   
153. mysql>   
154. mysql>   
155. mysql> select * from data03.t01;  
156. +-----+------+------+  
157. | id1 | a1   | b1   |  
158. +-----+------+------+  
159. |   1 |    1 |    1 |  
160. |   2 |    2 |    2 |  
161. |   4 |    4 |    4 |  
162. |   5 |    5 |    5 |  
163. |   6 |    6 |    6 |  
164. +-----+------+------+  
165. 5 rows in set (0.00 sec)  
166.   
167. mysql>   
168. mysql> unlock tables;  
169. Query OK, 0 rows affected (0.00 sec)  
170.   
171. mysql> show slave status\G  
172. *************************** 1. row ***************************  
173.                Slave_IO_State: Waiting for master to send event  
174.                   Master_Host: 172.32.1.200  
175.                   Master_User: repl  
176.                   Master_Port: 3307  
177.                 Connect_Retry: 60  
178. <span style="color:#ff0000;">              Master_Log_File: mysql-bin.000009  
179.           Read_Master_Log_Pos: 6531</span>  
180.                Relay_Log_File: huobiDBtest-relay-bin.000002  
181.                 Relay_Log_Pos: 6694  
182.         Relay_Master_Log_File: mysql-bin.000009  
183.              Slave_IO_Running: Yes  
184.             Slave_SQL_Running: Yes  
185.               Replicate_Do_DB:   
186.           Replicate_Ignore_DB:   
187.            Replicate_Do_Table:   
188.        Replicate_Ignore_Table:   
189.       Replicate_Wild_Do_Table:   
190.   Replicate_Wild_Ignore_Table:   
191.                    Last_Errno: 0  
192.                    Last_Error:   
193.                  Skip_Counter: 0  
194.          <span style="color:#ff0000;"> Exec_Master_Log_Pos: 6531</span>  
195.               Relay_Log_Space: 6873  
196.               Until_Condition: None  
197.                Until_Log_File:   
198.                 Until_Log_Pos: 0  
199.            Master_SSL_Allowed: No  
200.            Master_SSL_CA_File:   
201.            Master_SSL_CA_Path:   
202.               Master_SSL_Cert:   
203.             Master_SSL_Cipher:   
204.                Master_SSL_Key:   
205.         Seconds_Behind_Master: 0  
206. Master_SSL_Verify_Server_Cert: No  
207.                 Last_IO_Errno: 0  
208.                 Last_IO_Error:   
209.                Last_SQL_Errno: 0  
210.                Last_SQL_Error:   
211.   Replicate_Ignore_Server_Ids:   
212.              Master_Server_Id: 2003307  
213.                   Master_UUID: 6f68eea7-76e9-11e4-8f99-00221904cd5d  
214.              Master_Info_File: /data/mysqldata/3308/data/master.info  
215.                     SQL_Delay: 0  
216.           SQL_Remaining_Delay: NULL  
217.       Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it  
218.            Master_Retry_Count: 86400  
219.                   Master_Bind:   
220.       Last_IO_Error_Timestamp:   
221.      Last_SQL_Error_Timestamp:   
222.                Master_SSL_Crl:   
223.            Master_SSL_Crlpath:   
224.            Retrieved_Gtid_Set:   
225.             Executed_Gtid_Set:   
226.                 Auto_Position: 0  
227. 1 row in set (0.00 sec)  
228.   
229. mysql> select * from data03.t01;  
230. +-----+------+------+  
231. | id1 | a1   | b1   |  
232. +-----+------+------+  
233. |   1 |    1 |    1 |  
234. |   2 |   12 |    2 |  
235. |   5 |    5 |    5 |  
236. |   6 |    6 |    6 |  
237. |   8 |    8 |    8 |  
238. +-----+------+------+  
239. 5 rows in set (0.00 sec) 

【转】mysql只读模式的设置方法与实验

标签：man   nod   key   关系   mat   知识库   insert   target   trie