###从库vdbsrv2停止io进程以模拟io滞后
[root@vdbsrv2 ~]# mysql -e 'stop slave io_thread'
[root@vdbsrv2 ~]# mysql -e 'show slave status\G' | egrep 'Slave_IO|Slave_SQL'
Slave_IO_State:
Slave_IO_Running: No ###Author : Leshami
Slave_SQL_Running: Yes ###Blog :
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
###2分钟后从库vdbsrv2启动io进程
[root@vdbsrv2 ~]# mysql -e 'start slave io_thread'
###从库vdbsrv3停止io进程以模拟io滞后
[root@vdbsrv3 ~]# mysql -e 'stop slave io_thread'
###模拟主库宕机
[root@vdbsrv4 ~]# ssh vdbsrv1 "killall -r mysqld"
###主库的sysbench被强制断开且伴随下列错误提示
[root@vdbsrv1 ~]#
.......
Doing OLTP test.
Running mixed OLTP test
Using Uniform distribution
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Threads started!
ALERT: failed to execute mysql_stmt_execute(): Err1317 Query execution was interrupted
FATAL: database error, exiting...
ALERT: failed to execute mysql_stmt_execute(): Err1317 Query execution was interrupted
FATAL: database error, exiting...
###查看VIP漂移情况
[root@vdbsrv4 app1]# grep VIP /var/log/masterha/app1/manager.log
Disabling the VIP on old master: vdbsrv1
Enabling the VIP - 192.168.1.13/24 on the new master - vdbsrv2
###登陆到主机vdbsrv2 mysql
mysql> show variables like '%hostname%';
+---------------+---------+
| Variable_name | Value |
+---------------+---------+
| hostname | vdbsrv2 |
+---------------+---------+
1 row in set (0.00 sec)
###此时vdbsrv2已提升为master，vdbsrv3指向了vdbsrv2
mysql> show slave hosts;
+-----------+---------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID |
+-----------+---------+------+-----------+--------------------------------------+
| 1002 | vdbsrv3 | 3306 | 1001 | 091f79b8-e386-11e4-93d5-000c2943c830 |
+-----------+---------+------+-----------+--------------------------------------+
1 row in set (0.00 sec)
4、MHA日志初步剖析
[root@vdbsrv4 ~]# ls -hltr /var/log/masterha/*
total 32K
-rw-r--r-- 1 root root 143 Apr 20 14:30 saved_master_binlog_from_vdbsrv1_3306_20150420143022.binlog
-rw-r--r-- 1 root root 26K Apr 20 14:30 manager.log
-rw-r--r-- 1 root root 0 Apr 20 14:30 app1.failover.complete
###具体日志信息manager.log
Mon Apr 20 14:27:41 2015 - [info] MHA::MasterMonitor version 0.56.
Mon Apr 20 14:27:42 2015 - [info] GTID failover mode = 0
Mon Apr 20 14:27:42 2015 - [info] Dead Servers:
Mon Apr 20 14:27:42 2015 - [info] Alive Servers:
Mon Apr 20 14:27:42 2015 - [info] vdbsrv1(192.168.1.6:3306)
Mon Apr 20 14:27:42 2015 - [info] vdbsrv2(192.168.1.7:3306)
Mon Apr 20 14:27:42 2015 - [info] vdbsrv3(192.168.1.8:3306)
Mon Apr 20 14:27:42 2015 - [info] Alive Slaves:
Mon Apr 20 14:27:42 2015 - [info] vdbsrv2(192.168.1.7:3306) Version=5.6.22-log (oldest major version between slaves) log-bin:enabled
Mon Apr 20 14:27:42 2015 - [info] Replicating from 192.168.1.6(192.168.1.6:3306)
Mon Apr 20 14:27:42 2015 - [info] vdbsrv3(192.168.1.8:3306) Version=5.6.22-log (oldest major version between slaves) log-bin:enabled
Mon Apr 20 14:27:42 2015 - [info] Replicating from 192.168.1.6(192.168.1.6:3306)
Mon Apr 20 14:27:42 2015 - [info] Current Alive Master: vdbsrv1(192.168.1.6:3306)
Mon Apr 20 14:27:42 2015 - [info] Checking slave configurations..
Mon Apr 20 14:27:42 2015 - [info] read_only=1 is not set on slave vdbsrv2(192.168.1.7:3306).
Mon Apr 20 14:27:42 2015 - [info] Checking replication filtering settings..
Mon Apr 20 14:27:42 2015 - [info] binlog_do_db= , binlog_ignore_db=
Mon Apr 20 14:27:42 2015 - [info] Replication filtering check ok.
Mon Apr 20 14:27:42 2015 - [info] GTID (with auto-pos) is not supported
Mon Apr 20 14:27:42 2015 - [info] Starting SSH connection tests..
Mon Apr 20 14:27:43 2015 - [info] All SSH connection tests passed successfully.
Mon Apr 20 14:27:43 2015 - [info] Checking MHA Node version..
Mon Apr 20 14:27:44 2015 - [info] Version check ok.
Mon Apr 20 14:27:44 2015 - [info] Checking SSH publickey authentication settings on the current master..
Mon Apr 20 14:27:44 2015 - [info] HealthCheck: SSH to vdbsrv1 is reachable.
Mon Apr 20 14:27:44 2015 - [info] Master MHA Node version is 0.56.
Mon Apr 20 14:27:44 2015 - [info] Checking recovery script configurations on vdbsrv1(192.168.1.6:3306)..
Mon Apr 20 14:27:44 2015 - [info] Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/data/mysqldata
--output_file=/var/tmp/save_binary_logs_test --manager_version=0.56 --start_file=mysql-bin.000020
Mon Apr 20 14:27:44 2015 - [info] Connecting to root@192.168.1.6(vdbsrv1:22)..
Creating /var/tmp if not exists.. ok.
Checking output directory is accessible or not..
ok.
Binlog found at /data/mysqldata, up to mysql-bin.000020
Mon Apr 20 14:27:44 2015 - [info] Binlog setting check done.
Mon Apr 20 14:27:44 2015 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..
Mon Apr 20 14:27:44 2015 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='mha' --slave_host=vdbsrv2
--slave_ip=192.168.1.7 --slave_port=3306 --workdir=/var/tmp --target_version=5.6.22-log --manager_version=0.56
--relay_log_info=/data/mysqldata/relay-log.info --relay_dir=/data/mysqldata/ --slave_pass=xxx
Mon Apr 20 14:27:44 2015 - [info] Connecting to root@192.168.1.7(vdbsrv2:22)..
Checking slave recovery environment settings..
Opening /data/mysqldata/relay-log.info ... ok.
Relay log found at /data/mysqldata, up to vdbsrv2-relay-bin.000002
Temporary relay log file is /data/mysqldata/vdbsrv2-relay-bin.000002
Testing mysql connection and privileges.. done.
Testing mysqlbinlog output.. done.
Cleaning up test file(s).. done.
Mon Apr 20 14:27:45 2015 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='mha'
--slave_host=vdbsrv3 --slave_ip=192.168.1.8 --slave_port=3306 --workdir=/var/tmp --target_version=5.6.22-log
--manager_version=0.56 --relay_log_info=/data/mysqldata/relay-log.info --relay_dir=/data/mysqldata/ --slave_pass=xxx
Mon Apr 20 14:27:45 2015 - [info] Connecting to root@192.168.1.8(vdbsrv3:22)..
Checking slave recovery environment settings..
Opening /data/mysqldata/relay-log.info ... ok.
Relay log found at /data/mysqldata, up to vdbsrv3-relay-bin.000002
Temporary relay log file is /data/mysqldata/vdbsrv3-relay-bin.000002
Testing mysql connection and privileges.. done.
Testing mysqlbinlog output.. done.
Cleaning up test file(s).. done.
Mon Apr 20 14:27:45 2015 - [info] Slaves settings check done.
Mon Apr 20 14:27:45 2015 - [info]
vdbsrv1(192.168.1.6:3306) (current master)
+--vdbsrv2(192.168.1.7:3306)
+--vdbsrv3(192.168.1.8:3306)