今天一个朋友碰到个问题
主从复制从卡在sql线程上,IO线程正常
errlog里面有些信息
看信息是read-only只读了,朋友说在从库设置了read_only参数,当然,sql线程阻塞和这个没关系拉...
库里很多Myisam表,第一有可能Myisam表损坏导致sql阻塞有这个可能,但设计到涉及到重启Mysqld服务,errlog未出现相关Myisam表损坏信息,暂时排除。第二,有长时间锁表操作,这个可能性看来极大。排查过程就不详细说明了。谈到read_only,就想到了read_only作用范围是什么,一起来看下吧:
read_only
When this variable is set to ON, the server allows no updates except from users that have the SUPER
privilege or (on a slave server) from updates performed by slave threads. On a slave server, this can be
useful to ensure that the slave accepts updates only from its master server and not from clients. As of
MySQL 5.0.16, this variable does not apply to TEMPORARY tables.
read_only exists only as a GLOBAL variable, so changes to its value require the SUPER privilege. Changes to
read_only on a master server are not replicated to slave servers. The value can be set on a slave server
independent of the setting on the master.
当打开了read_only参数,global参数,只允许拥有super权限的用户或者通过slave线程执行的修改。