MySQL MHA故障模拟及恢复

模拟故障

[root@db01 ~]# pkill mysqld
db02 [(none)]>show slave status \G
Empty set (0.00 sec)从库状态消失
[root@db03 ~]# masterha_check_status --conf=/etc/mha/app1.cnf
app1 is stopped(2:NOT_RUNNING).
db03 [(none)]>show slave status\G
db03 [(none)]>show slave status\G
Master_Host: 10.0.0.201
[root@db03 ~]# cat /etc/mha/app1.cnf
[server1]消失
[root@db03 ~]# cat /var/log/mha/app1/manager
Master failover to 10.0.0.201(10.0.0.201:3306) completed successfully.

修复过程

修复主库
[root@db01 ~]# /etc/init.d/mysqld start

恢复主从结构

在[root@db03 ~]# cat /var/log/mha/app1/manager日志中会有如下提示恢复MHA:
CHANGE MASTER TO 
MASTER_HOST='10.0.0.201', 
MASTER_PORT=3306, 
MASTER_AUTO_POSITION=1, 
MASTER_USER='repl', 
MASTER_PASSWORD='123';
db01 [(none)]>start slave;
Query OK, 0 rows affected (0.01 sec)
db01 [(none)]>show slave status \G

修改配置文件

[root@db03 ~]# vim /etc/mha/app1.cnf
[server1]
hostname=10.0.0.200
port=3306

启动MHA

nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null> /var/log/mha/app1/manager.log 2>&1 &

查看MHA状态

[root@db03 ~]# masterha_check_status --conf=/etc/mha/app1.cnf
app1 (pid:9973) is running(0:PING_OK), master:10.0.0.201
[root@db03 ~]# mysql -umha -pmha -h 10.0.0.200 -e "show variables like 'server_id'"
[root@db03 ~]# mysql -umha -pmha -h 10.0.0.201 -e "show variables like 'server_id'" 
[root@db03 ~]# mysql -umha -pmha -h 10.0.0.203 -e "show variables like 'server_id'"