更新日期:2011-12-14
环境介绍:
master1与master2是MM结构,与一台monitor做成mysql-mmm
潜在问题:
1,复制延迟会导致master2下线,此时,所有的vip都会转移到master1上面.请求量过大,直接导致master1宕机;
2,在master2上进行备份,短时间内频繁锁表,导致master2变成抖动状态.
解决办法:
让master2不因复制延迟下线,在monitor端增大延迟参数,将max_backlog设置为86400秒.
check_period 5
trap_period 10
timeout 2
restart_after 10000
max_backlog 86400
这样设置之后,需要nagios添加check_mysql_all脚本进行配合,监控复制延迟,如果影响线上,根据压力情况,可人工执行set_offline下线处理.
疑问:如果master2复制延迟,此时,master1宕机,会不会造成数据混乱,或丢失master2的relay-log数据?
先说明不会丢数据,下面有完成的测试过程.
mysql-mmm在处理写库宕机的问题时,会等待从库的复制执行完后,才会进行写vip的切换.
|
主机 |
vip |
|
master1 |
writer(192.168.250.111) |
|
master2 |
reader(192.168.250.112), reader(192.168.250.183) |
1,有一个健康的mysql-mmm环境,agent和monitor工作正常.
2,在master2上进行表锁
FLUSH TABLES WITH READ LOCK;
3,在master1上写入新数据,这样在master2上就会出现复制延迟.
4,在master2上查看复制延迟状态和vip
5,在master1上kill掉mysql,模拟写库宕机
6,master2的复制状态
7,master2的VIP没有变化,写的vip没有漂过来.(注意,如果master2处于REPLICATION_DELAY. Roles: 的状态,读的vip会优先漂过来,这就是mmm智能的地方,不影响前端业务的读取操作)
8,在monitor执行mmm_control @test1 show命令,会一直卡在这里.
9,在master2上解锁,unlock tables;然后查看vip状态,写的vip漂过来了.
10,查看monitor的mmm_control命令,执行结果如下:
monitor日志
2011/12/14 15:16:43 ERROR Check 'mysql' on 'db1' has failed for 10 seconds! Message: ERROR: Connect error (host = 192.168.250.251:3307, user = mmm_monitor)! Lost connection to MySQL server at 'reading initial communication packet', system error: 111
2011/12/14 15:16:43 ERROR Check 'rep_threads' on 'db2' has failed for 10 seconds! Message: ERROR: Replication is broken
2011/12/14 15:16:46 FATAL State of host 'db1' changed from ONLINE to HARD_OFFLINE (ping: OK, mysql: not OK)
2011/12/14 15:16:46 INFO Removing all roles from host 'db1':
2011/12/14 15:16:46 INFO Removed role 'writer(192.168.250.111)' from host 'db1'
2011/12/14 15:16:46 INFO Orphaned role 'writer(192.168.250.111)' has been assigned to 'db2'