环境:
Master:192.168.100.50
Slave1:192.168.100.48
Slave2:192.168.100.141 (MHA管理节点)
模拟主库mysql进程异常down掉,MHA的整个切换过程
主库上kill掉mysql进程
[root@goolen ~]# ps aux | grep mysql |grep -v grep | awk '{print $2}' | xargs kill -9
观察MHA切换日志,了解整个切换过程:
[root@test11g log]# tail -f mha.log
Wed Nov 19 18:18:46 2014 - [warning] Got error on MySQL select ping: 2006 (MySQL server has gone away)
Wed Nov 19 18:18:46 2014 - [info] Executing secondary network check script: /usr/bin/masterha_secondary_check -s coredb -s goolen --user=root --master_host=goolen --master_ip=192.168.100.50 --master_port=3306 --user=root --master_host=192.168.100.50 --master_ip=192.168.100.50 --master_port=3306 --master_user=root --master_password=root --ping_type=SELECT
Wed Nov 19 18:18:46 2014 - [info] Executing SSH check script: save_binary_logs --command=test --start_pos=4 --binlog_dir=/tol/mysql/binlog --output_file=/tmp/save_binary_logs_test --manager_version=0.56 --binlog_prefix=mysql-bin
Wed Nov 19 18:18:46 2014 - [info] HealthCheck: SSH to 192.168.100.50 is reachable.
Monitoring server coredb is reachable, Master is not reachable from coredb. OK.
Monitoring server goolen is reachable, Master is not reachable from goolen. OK.
Wed Nov 19 18:18:46 2014 - [info] Master is not reachable from all other monitoring servers. Failover should start.
Wed Nov 19 18:18:47 2014 - [warning] Got error on MySQL connect: 2013 (Lost connection to MySQL server at 'reading initial communication packet', system error: 111)
Wed Nov 19 18:18:47 2014 - [warning] Connection failed 2 time(s)..
Wed Nov 19 18:18:48 2014 - [warning] Got error on MySQL connect: 2013 (Lost connection to MySQL server at 'reading initial communication packet', system error: 111)
Wed Nov 19 18:18:48 2014 - [warning] Connection failed 3 time(s)..
Wed Nov 19 18:18:49 2014 - [warning] Got error on MySQL connect: 2013 (Lost connection to MySQL server at 'reading initial communication packet', system error: 111)
Wed Nov 19 18:18:49 2014 - [warning] Connection failed 4 time(s)..
Wed Nov 19 18:18:49 2014 - [warning] Master is not reachable from health checker!
Wed Nov 19 18:18:49 2014 - [warning] Master 192.168.100.50(192.168.100.50:3306) is not reachable!
Wed Nov 19 18:18:49 2014 - [warning] SSH is reachable.
Wed Nov 19 18:18:49 2014 - [info] Connecting to a master server failed. Reading configuration file /etc/masterha_default.cnf and /etc/masterha/mha.cnf again, and trying to connect to all servers to check server status..
Wed Nov 19 18:18:49 2014 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Wed Nov 19 18:18:49 2014 - [info] Reading application default configuration from /etc/masterha/mha.cnf..
Wed Nov 19 18:18:49 2014 - [info] Reading server configuration from /etc/masterha/mha.cnf..
Wed Nov 19 18:18:49 2014 - [info] GTID failover mode = 0
Wed Nov 19 18:18:49 2014 - [info] Dead Servers:
Wed Nov 19 18:18:49 2014 - [info] 192.168.100.50(192.168.100.50:3306)
Wed Nov 19 18:18:49 2014 - [info] Alive Servers:
Wed Nov 19 18:18:49 2014 - [info] 192.168.100.48(192.168.100.48:3306)
Wed Nov 19 18:18:49 2014 - [info] 192.168.100.141(192.168.100.141:3306)
Wed Nov 19 18:18:49 2014 - [info] Alive Slaves:
Wed Nov 19 18:18:49 2014 - [info] 192.168.100.48(192.168.100.48:3306) Version=5.5.18-log (oldest major version between slaves) log-bin:enabled
Wed Nov 19 18:18:49 2014 - [info] Replicating from 192.168.100.50(192.168.100.50:3306)
Wed Nov 19 18:18:49 2014 - [info] Primary candidate for the new Master (candidate_master is set)
Wed Nov 19 18:18:49 2014 - [info] 192.168.100.141(192.168.100.141:3306) Version=5.5.18-log (oldest major version between slaves) log-bin:enabled
Wed Nov 19 18:18:49 2014 - [info] Replicating from 192.168.100.50(192.168.100.50:3306)
Wed Nov 19 18:18:49 2014 - [info] Checking slave configurations..
Wed Nov 19 18:18:49 2014 - [info] Checking replication filtering settings..
Wed Nov 19 18:18:49 2014 - [info] Replication filtering check ok.
Wed Nov 19 18:18:49 2014 - [info] Master is down!
Wed Nov 19 18:18:49 2014 - [info] Terminating monitoring script.
Wed Nov 19 18:18:49 2014 - [info] Got exit code 20 (Master dead).
Wed Nov 19 18:18:49 2014 - [info] MHA::MasterFailover version 0.56.
Wed Nov 19 18:18:49 2014 - [info] Starting master failover.
Wed Nov 19 18:18:49 2014 - [info]
Wed Nov 19 18:18:49 2014 - [info] * Phase 1: Configuration Check Phase..
Wed Nov 19 18:18:49 2014 - [info]
Wed Nov 19 18:18:49 2014 - [info] GTID failover mode = 0
Wed Nov 19 18:18:49 2014 - [info] Dead Servers:
Wed Nov 19 18:18:49 2014 - [info] 192.168.100.50(192.168.100.50:3306)
Wed Nov 19 18:18:49 2014 - [info] Checking master reachability via MySQL(double check)...
Wed Nov 19 18:18:49 2014 - [info] ok.
Wed Nov 19 18:18:49 2014 - [info] Alive Servers:
Wed Nov 19 18:18:49 2014 - [info] 192.168.100.48(192.168.100.48:3306)
Wed Nov 19 18:18:49 2014 - [info] 192.168.100.141(192.168.100.141:3306)
Wed Nov 19 18:18:49 2014 - [info] Alive Slaves:
Wed Nov 19 18:18:49 2014 - [info] 192.168.100.48(192.168.100.48:3306) Version=5.5.18-log (oldest major version between slaves) log-bin:enabled
Wed Nov 19 18:18:49 2014 - [info] Replicating from 192.168.100.50(192.168.100.50:3306)
Wed Nov 19 18:18:49 2014 - [info] Primary candidate for the new Master (candidate_master is set)
Wed Nov 19 18:18:49 2014 - [info] 192.168.100.141(192.168.100.141:3306) Version=5.5.18-log (oldest major version between slaves) log-bin:enabled
Wed Nov 19 18:18:49 2014 - [info] Replicating from 192.168.100.50(192.168.100.50:3306)
Wed Nov 19 18:18:49 2014 - [info] Starting Non-GTID based failover.
Wed Nov 19 18:18:49 2014 - [info]
Wed Nov 19 18:18:49 2014 - [info] ** Phase 1: Configuration Check Phase completed.
Wed Nov 19 18:18:49 2014 - [info]
Wed Nov 19 18:18:49 2014 - [info] * Phase 2: Dead Master Shutdown Phase..
Wed Nov 19 18:18:49 2014 - [info]
Wed Nov 19 18:18:49 2014 - [info] Forcing shutdown so that applications never connect to the current master..
Wed Nov 19 18:18:49 2014 - [info] Executing master IP deactivation script:
Wed Nov 19 18:18:49 2014 - [info] /usr/bin/masterha_ip_failover --orig_master_host=192.168.100.50 --orig_master_ip=192.168.100.50 --orig_master_port=3306 --command=stopssh --ssh_user=root
Disabling the VIP on old master: 192.168.100.50
Wed Nov 19 18:18:49 2014 - [info] done.
Wed Nov 19 18:18:49 2014 - [warning] shutdown_script is not set. Skipping explicit shutting down of the dead master.
Wed Nov 19 18:18:49 2014 - [info] * Phase 2: Dead Master Shutdown Phase completed.
Wed Nov 19 18:18:49 2014 - [info]
Wed Nov 19 18:18:49 2014 - [info] * Phase 3: Master Recovery Phase..
Wed Nov 19 18:18:49 2014 - [info]
Wed Nov 19 18:18:49 2014 - [info] * Phase 3.1: Getting Latest Slaves Phase..
Wed Nov 19 18:18:49 2014 - [info]
Wed Nov 19 18:18:49 2014 - [info] The latest binary log file/position on all slaves is mysql-bin.000003:2612
Wed Nov 19 18:18:49 2014 - [info] Latest slaves (Slaves that received relay log files to the latest):
Wed Nov 19 18:18:49 2014 - [info] 192.168.100.48(192.168.100.48:3306) Version=5.5.18-log (oldest major version between slaves) log-bin:enabled
Wed Nov 19 18:18:49 2014 - [info] Replicating from 192.168.100.50(192.168.100.50:3306)
Wed Nov 19 18:18:49 2014 - [info] Primary candidate for the new Master (candidate_master is set)
Wed Nov 19 18:18:49 2014 - [info] 192.168.100.141(192.168.100.141:3306) Version=5.5.18-log (oldest major version between slaves) log-bin:enabled
Wed Nov 19 18:18:49 2014 - [info] Replicating from 192.168.100.50(192.168.100.50:3306)
Wed Nov 19 18:18:49 2014 - [info] The oldest binary log file/position on all slaves is mysql-bin.000003:2612
Wed Nov 19 18:18:49 2014 - [info] Oldest slaves:
Wed Nov 19 18:18:49 2014 - [info] 192.168.100.48(192.168.100.48:3306) Version=5.5.18-log (oldest major version between slaves) log-bin:enabled
Wed Nov 19 18:18:49 2014 - [info] Replicating from 192.168.100.50(192.168.100.50:3306)
Wed Nov 19 18:18:49 2014 - [info] Primary candidate for the new Master (candidate_master is set)
Wed Nov 19 18:18:49 2014 - [info] 192.168.100.141(192.168.100.141:3306) Version=5.5.18-log (oldest major version between slaves) log-bin:enabled
Wed Nov 19 18:18:49 2014 - [info] Replicating from 192.168.100.50(192.168.100.50:3306)
Wed Nov 19 18:18:49 2014 - [info]
Wed Nov 19 18:18:49 2014 - [info] * Phase 3.2: Saving Dead Master's Binlog Phase..
Wed Nov 19 18:18:49 2014 - [info]
Wed Nov 19 18:18:49 2014 - [info] Fetching dead master's binary logs..
Wed Nov 19 18:18:49 2014 - [info] Executing command on the dead master 192.168.100.50(192.168.100.50:3306): save_binary_logs --command=save --start_file=mysql-bin.000003 --start_pos=2612 --binlog_dir=/tol/mysql/binlog --output_file=/tmp/saved_master_binlog_from_192.168.100.50_3306_20141119181849.binlog --handle_raw_binlog=1 --disable_log_bin=0 --manager_version=0.56
Creating /tmp if not exists.. ok.
Concat binary/relay logs from mysql-bin.000003 pos 2612 to mysql-bin.000003 EOF into /tmp/saved_master_binlog_from_192.168.100.50_3306_20141119181849.binlog ..
Dumping binlog format description event, from position 0 to 107.. ok.
No need to dump effective binlog data from /tol/mysql/binlog/mysql-bin.000003 (pos starts 2612, filesize 2612). Skipping.
/tmp/saved_master_binlog_from_192.168.100.50_3306_20141119181849.binlog has no effective data events.
Event not exists.
Wed Nov 19 18:18:50 2014 - [info] Additional events were not found from the orig master. No need to save.
Wed Nov 19 18:18:50 2014 - [info]
Wed Nov 19 18:18:50 2014 - [info] * Phase 3.3: Determining New Master Phase..
Wed Nov 19 18:18:50 2014 - [info]
Wed Nov 19 18:18:50 2014 - [info] Finding the latest slave that has all relay logs for recovering other slaves..
Wed Nov 19 18:18:50 2014 - [info] All slaves received relay logs to the same position. No need to resync each other.
Wed Nov 19 18:18:50 2014 - [info] Searching new master from slaves..
Wed Nov 19 18:18:50 2014 - [info] Candidate masters from the configuration file:
Wed Nov 19 18:18:50 2014 - [info] 192.168.100.48(192.168.100.48:3306) Version=5.5.18-log (oldest major version between slaves) log-bin:enabled
Wed Nov 19 18:18:50 2014 - [info] Replicating from 192.168.100.50(192.168.100.50:3306)
Wed Nov 19 18:18:50 2014 - [info] Primary candidate for the new Master (candidate_master is set)
Wed Nov 19 18:18:50 2014 - [info] Non-candidate masters:
Wed Nov 19 18:18:50 2014 - [info] Searching from candidate_master slaves which have received the latest relay log events..
Wed Nov 19 18:18:50 2014 - [info] New master is 192.168.100.48(192.168.100.48:3306)
Wed Nov 19 18:18:50 2014 - [info] Starting master failover..
Wed Nov 19 18:18:50 2014 - [info]
From:
192.168.100.50(192.168.100.50:3306) (current master)
+--192.168.100.48(192.168.100.48:3306)
+--192.168.100.141(192.168.100.141:3306)
To:
192.168.100.48(192.168.100.48:3306) (new master)
+--192.168.100.141(192.168.100.141:3306)
Wed Nov 19 18:18:50 2014 - [info]
Wed Nov 19 18:18:50 2014 - [info] * Phase 3.3: New Master Diff Log Generation Phase..
Wed Nov 19 18:18:50 2014 - [info]
Wed Nov 19 18:18:50 2014 - [info] This server has all relay logs. No need to generate diff files from the latest slave.
Wed Nov 19 18:18:50 2014 - [info]
Wed Nov 19 18:18:50 2014 - [info] * Phase 3.4: Master Log Apply Phase..
Wed Nov 19 18:18:50 2014 - [info]
Wed Nov 19 18:18:50 2014 - [info] *NOTICE: If any error happens from this phase, manual recovery is needed.
Wed Nov 19 18:18:50 2014 - [info] Starting recovery on 192.168.100.48(192.168.100.48:3306)..
Wed Nov 19 18:18:50 2014 - [info] This server has all relay logs. Waiting all logs to be applied..
Wed Nov 19 18:18:50 2014 - [info] done.
Wed Nov 19 18:18:50 2014 - [info] All relay logs were successfully applied.
Wed Nov 19 18:18:50 2014 - [info] Getting new master's binlog name and position..
Wed Nov 19 18:18:50 2014 - [info] mysql-bin.000005:1110
Wed Nov 19 18:18:50 2014 - [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='192.168.100.48', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=1110, MASTER_USER='backup', MASTER_PASSWORD='xxx';
Wed Nov 19 18:18:50 2014 - [info] Executing master IP activate script:
Wed Nov 19 18:18:50 2014 - [info] /usr/bin/masterha_ip_failover --command=start --ssh_user=root --orig_master_host=192.168.100.50 --orig_master_ip=192.168.100.50 --orig_master_port=3306 --new_master_host=192.168.100.48 --new_master_ip=192.168.100.48 --new_master_port=3306 --new_master_user='root' --new_master_password='root'
Unknown option: new_master_user
Unknown option: new_master_password
Enabling the VIP - 192.168.100.170 on the new master - 192.168.100.48
Wed Nov 19 18:18:50 2014 - [info] OK.
Wed Nov 19 18:18:50 2014 - [info] Setting read_only=0 on 192.168.100.48(192.168.100.48:3306)..
Wed Nov 19 18:18:50 2014 - [info] ok.
Wed Nov 19 18:18:50 2014 - [info] ** Finished master recovery successfully.
Wed Nov 19 18:18:50 2014 - [info] * Phase 3: Master Recovery Phase completed.
Wed Nov 19 18:18:50 2014 - [info]
Wed Nov 19 18:18:50 2014 - [info] * Phase 4: Slaves Recovery Phase..
Wed Nov 19 18:18:50 2014 - [info]
Wed Nov 19 18:18:50 2014 - [info] * Phase 4.1: Starting Parallel Slave Diff Log Generation Phase..
Wed Nov 19 18:18:50 2014 - [info]
Wed Nov 19 18:18:50 2014 - [info] -- Slave diff file generation on host 192.168.100.141(192.168.100.141:3306) started, pid: 11480. Check tmp log /tol/mha//192.168.100.141_3306_20141119181849.log if it takes time..
Wed Nov 19 18:18:50 2014 - [info]
Wed Nov 19 18:18:50 2014 - [info] Log messages from 192.168.100.141 ...
Wed Nov 19 18:18:50 2014 - [info]
Wed Nov 19 18:18:50 2014 - [info] This server has all relay logs. No need to generate diff files from the latest slave.
Wed Nov 19 18:18:50 2014 - [info] End of log messages from 192.168.100.141.
Wed Nov 19 18:18:50 2014 - [info] -- 192.168.100.141(192.168.100.141:3306) has the latest relay log events.
Wed Nov 19 18:18:50 2014 - [info] Generating relay diff files from the latest slave succeeded.
Wed Nov 19 18:18:50 2014 - [info]
Wed Nov 19 18:18:50 2014 - [info] * Phase 4.2: Starting Parallel Slave Log Apply Phase..
Wed Nov 19 18:18:50 2014 - [info]
Wed Nov 19 18:18:50 2014 - [info] -- Slave recovery on host 192.168.100.141(192.168.100.141:3306) started, pid: 11482. Check tmp log /tol/mha//192.168.100.141_3306_20141119181849.log if it takes time..
Wed Nov 19 18:18:50 2014 - [info]
Wed Nov 19 18:18:50 2014 - [info] Log messages from 192.168.100.141 ...
Wed Nov 19 18:18:50 2014 - [info]
Wed Nov 19 18:18:50 2014 - [info] Starting recovery on 192.168.100.141(192.168.100.141:3306)..
Wed Nov 19 18:18:50 2014 - [info] This server has all relay logs. Waiting all logs to be applied..
Wed Nov 19 18:18:50 2014 - [info] done.
Wed Nov 19 18:18:50 2014 - [info] All relay logs were successfully applied.
Wed Nov 19 18:18:50 2014 - [info] Resetting slave 192.168.100.141(192.168.100.141:3306) and starting replication from the new master 192.168.100.48(192.168.100.48:3306)..
Wed Nov 19 18:18:50 2014 - [info] Executed CHANGE MASTER.
Wed Nov 19 18:18:50 2014 - [info] Slave started.
Wed Nov 19 18:18:50 2014 - [info] End of log messages from 192.168.100.141.
Wed Nov 19 18:18:50 2014 - [info] -- Slave recovery on host 192.168.100.141(192.168.100.141:3306) succeeded.
Wed Nov 19 18:18:50 2014 - [info] All new slave servers recovered successfully.
Wed Nov 19 18:18:50 2014 - [info]
Wed Nov 19 18:18:50 2014 - [info] * Phase 5: New master cleanup phase..
Wed Nov 19 18:18:50 2014 - [info]
Wed Nov 19 18:18:50 2014 - [info] Resetting slave info on the new master..
Wed Nov 19 18:18:50 2014 - [info] 192.168.100.48: Resetting slave info succeeded.
Wed Nov 19 18:18:50 2014 - [info] Master failover to 192.168.100.48(192.168.100.48:3306) completed successfully.
Wed Nov 19 18:18:50 2014 - [info] Deleted server1 entry from /etc/masterha/mha.cnf .
Wed Nov 19 18:18:50 2014 - [info]
----- Failover Report -----
mha: MySQL Master failover 192.168.100.50(192.168.100.50:3306) to 192.168.100.48(192.168.100.48:3306) succeeded
Master 192.168.100.50(192.168.100.50:3306) is down!
Check MHA Manager logs at test11g:/tol/mha/log/mha.log for details.
Started automated(non-interactive) failover.
Invalidated master IP address on 192.168.100.50(192.168.100.50:3306)
The latest slave 192.168.100.48(192.168.100.48:3306) has all relay logs for recovery.
Selected 192.168.100.48(192.168.100.48:3306) as a new master.
192.168.100.48(192.168.100.48:3306): OK: Applying all logs succeeded.
192.168.100.48(192.168.100.48:3306): OK: Activated master IP address.
192.168.100.141(192.168.100.141:3306): This host has the latest relay log events.
Generating relay diff files from the latest slave succeeded.
192.168.100.141(192.168.100.141:3306): OK: Applying all logs succeeded. Slave started, replicating from 192.168.100.48(192.168.100.48:3306)
192.168.100.48(192.168.100.48:3306): Resetting slave info succeeded.
Master failover to 192.168.100.48(192.168.100.48:3306) completed successfully.
从上面的输出可以看出整个MHA的切换过程,共包括以下几个步骤
1,配置文件检查阶段,这个阶段将会检查整个集群配置文件设置
2,泵机的master处理,这个阶段包括虚拟IP摘除操作,主机关机等操作
3,复制dead master和最新slave相差的relay log,并保存到MHA manager具体目录下
4,识别含有最新更新的slave
5,应用差异的中继日志(relay log)到其他的slave
6,应用从master保存的二进制日志事件(binlog events)
7,提升一个slave为新的master
8,使其他的slave连接新的master进行复制
来自 “ ITPUB博客 ” ,链接:https://blog.itpub.net/23249684/viewspace-1339378/
,如需转载,请注明出处,否则将追究法律责任。