【mysql】read_only全局系统变量
时间:2015-08-29 01:08:35 作者:beebol 标签: mysql read_only 分类: mysql
最近在沟通在线切换从库时，将master设置为read_only后，再进行做切换好不好使在纠结，所以做下实验。首先看下资料，read_only仅是一个全局变量，改变它需要有super权限，如果不是会报如下错误:
mysql> set read_only=0;ERROR 1229 (HY000): Variable 'read_only' is a GLOBAL variable and should be set with SET GLOBAL ERROR 1229 (HY000): Variable 'read_only' is a GLOBAL variable and should be set with SET GLOBAL
如下是测试使用tpcc-mysql进行load数据，然后将read_only开启，最后看是否能阻塞tpcc的insert操作。答案是肯定的。不过需要注意下测试方法，tpcc_load数据时用户不能为root，为什么？后面有。
创建一个用户：
grant update,insert,select on *.* to test@'127.0.0.1' identified by '123456';flush privileges; flush privileges;
进行loaddata:
[root@localhost tpcc-mysql]# ./tpcc_load 127.0.0.1 tpcc1000 test 123456 100 ... .................................................. 70000 .................................................. 75000 .................................................. 80000 .................................................. 85000 .................................................. 90000.................................................. 95000 .................................................. 95000
使用super用户登录mysql进行设置read_only：
mysql> set global read_only=1;Query OK, 0 rows affected (0.07 sec) Query OK, 0 rows affected (0.07 sec)
很明显insert失败
..1290, HY000, The MySQL server is running with the --read-only option so it cannot execute this statement 1290, HY000, The MySQL server is running with the --read-only option so it cannot execute this statement
由于insert失败，整个事务回滚
mysql> show processlist; +-----+-------+---------------------+----------+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +-----+-------+---------------------+----------+---------+------+-------+------------------+ | 161 | root | localhost | NULL | Query | 0 | NULL | show processlist | | 162 | slave | 192.168.0.104:35121 | sbtest | Sleep | 4465 | | NULL | | 238 | test | 127.0.0.1:37066 | tpcc1000 | Query | 29 | NULL | rollback | | 289 | root | localhost | sbtest | Sleep | 1862 | | NULL | | 306 | root | localhost | sbtest | Sleep | 1578 | | NULL |+-----+-------+---------------------+----------+---------+------+-------+------------------+ +-----+-------+---------------------+----------+---------+------+-------+------------------+
如下是read_only详细的描述：
| Command-Line Format | --read_only |
||
| System Variable | Name | read_only |
|
| Variable Scope | Global | ||
| Dynamic Variable | Yes | ||
| Permitted Values | Type | boolean |
|
| Default | OFF |
||
read_only系统变量默认是关闭的，当开启后，mysql不允许客户端进行DML/DDL，除了有SUPER权限的用户。但是在有些情况下，即使这个变量ON的状态，也可以允许出下操作：
1、如果是slave，slave 线程是可以进行更新的，所以说slave设置read_only后，不影响复制；
2、analyze table和optimize table可以执行，read_only开始状态下保护的是表结构及数据的变更，analyze和optimize不属于这类变更；
3、temporary表可以进行变更；
4、日志表可以进行日志写入，如mysql.general_log,mysql.slow_log
如下是具体的测试过程：
1、主从就不在测试了，这个基本上都是把从进行了read_only设置；
2、analyze or optimize
mysql> set global read_only = 1; Query OK, 0 rows affected (0.01 sec) mysql> analyze table t1; +-----------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +-----------+---------+----------+----------+ | sbtest.t1 | analyze | status | OK | +-----------+---------+----------+----------+1 row in set (0.00 sec) 1 row in set (0.00 sec)
3、tepmorary table
mysql> set global read_only = 1; Query OK, 0 rows affected (0.01 sec) mysql> create temporary table t2 (id int,name varchar(20))engine=innodb; Query OK, 0 rows affected (0.16 sec) mysql> insert into t2 (id,name)values(1,'ding'); Query OK, 1 row affected (0.68 sec)
不过一个线上非常忙的库中执行read_only也会有阻塞情况。如下就是几种情况下会阻塞read_only，官方资料：
lock table
mysql> use sbtest Database changed mysql> lock table t1 write; Query OK, 0 rows affected (0.05 sec) mysql> set global read_only=1; 这里就会hang住
metadata lock
session 1:
mysql> use sbtest Database changed mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * from t1; +------------+ | nane | +------------+ | 1234521231 | | 1234521231 | | 2222 | +------------+ 3 rows in set (0.05 sec) mysql> update t1 set nane = '444' where nane = '2222'; Query OK, 1 row affected (0.05 sec) Rows matched: 1 Changed: 1 Warnings: 0
session 2:
mysql> alter table t1 add column id int not null; 这里被阻塞
此时metadata lock已经存在：
mysql> show processlist; +-----+-------+---------------------+----------+---------+------+---------------------------------+------------------------------------------------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +-----+-------+---------------------+----------+---------+------+---------------------------------+------------------------------------------------------------------------------------------------------+ | 161 | root | localhost | NULL | Query | 0 | NULL | show processlist | | 162 | slave | 192.168.0.104:35121 | sbtest | Sleep | 919 | | NULL | | 238 | test | 127.0.0.1:37066 | tpcc1000 | Execute | 0 | update | INSERT INTO stock values(33585,18,97,'GQjdF6ctBBjVNjW0hF4RsAQY','NiPIf1W5j8InkByvtRIcxHJA','i0U9Y4UE | | 289 | root | localhost | sbtest | Query | 4 | Waiting for table metadata lock | alter table t1 add primary key(id) | | 306 | root | localhost | sbtest | Sleep | 7 | | NULL | +-----+-------+---------------------+----------+---------+------+---------------------------------+------------------------------------------------------------------------------------------------------+5 rows in set (0.00 sec) 5 rows in set (0.00 sec)
session 3:
mysql> set global read_only=1; 这里也被阻塞
所以说，需要等待session 1提交或者回滚后，session 2执行完毕，session 3的read_only设置才能成功。