【MySQL】【高可用】基于MHA架构的MySQL高可用故障自动切换架构

njsuntop

Sun Feb  4 12:26:46 2018 - [info] Starting ping health check on 192.168.1.110(192.168.1.110:3110)..　　Sun Feb  4 12:26:46 2018 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond..
　　Sun Feb  4 12:30:41 2018 - [warning] Got error on MySQL select ping: 2006 (MySQL server has gone away)
　　Sun Feb  4 12:30:41 2018 - [info] Executing SSH check script: exit 0
　　Sun Feb  4 12:30:41 2018 - [warning] HealthCheck: SSH to 192.168.1.110 is NOT reachable.
　　Sun Feb  4 12:30:43 2018 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '192.168.1.110' (4))
　　Sun Feb  4 12:30:43 2018 - [warning] Connection failed 2 time(s)..
　　Sun Feb  4 12:30:44 2018 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '192.168.1.110' (4))
　　Sun Feb  4 12:30:44 2018 - [warning] Connection failed 3 time(s)..
　　Sun Feb  4 12:30:45 2018 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '192.168.1.110' (4))
　　Sun Feb  4 12:30:45 2018 - [warning] Connection failed 4 time(s)..
　　Sun Feb  4 12:30:45 2018 - [warning] Master is not reachable from health checker!
　　Sun Feb  4 12:30:45 2018 - [warning] Master 192.168.1.110(192.168.1.110:3110) is not reachable!
　　Sun Feb  4 12:30:45 2018 - [warning] SSH is NOT reachable.
　　Sun Feb  4 12:30:45 2018 - [info] Connecting to a master server failed. Reading configuration file /etc/masterha_default.cnf and /etc/mha/mainBusiness.cnf again, and trying to connect to all servers to check server status..
　　Sun Feb  4 12:30:45 2018 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
　　Sun Feb  4 12:30:45 2018 - [info] Reading application default configuration from /etc/mha/mainBusiness.cnf..
　　Sun Feb  4 12:30:45 2018 - [info] Reading server configuration from /etc/mha/mainBusiness.cnf..
　　Sun Feb  4 12:30:46 2018 - [info] GTID failover mode = 1
　　Sun Feb  4 12:30:46 2018 - [info] Dead Servers:
　　Sun Feb  4 12:30:46 2018 - [info]   192.168.1.110(192.168.1.110:3110)
　　Sun Feb  4 12:30:46 2018 - [info] Alive Servers:
　　Sun Feb  4 12:30:46 2018 - [info]   192.168.1.109(192.168.1.109:3109)
　　Sun Feb  4 12:30:46 2018 - [info] Alive Slaves:
　　Sun Feb  4 12:30:46 2018 - [info]   192.168.1.109(192.168.1.109:3109)  Version=5.7.19-log (oldest major version between slaves) log-bin:enabled
　　Sun Feb  4 12:30:46 2018 - [info]     GTID ON
　　Sun Feb  4 12:30:46 2018 - [info]     Replicating from 192.168.1.110(192.168.1.110:3110)
　　Sun Feb  4 12:30:46 2018 - [info]     Primary candidate for the new Master (candidate_master is set)
　　Sun Feb  4 12:30:46 2018 - [info] Checking slave configurations..
　　Sun Feb  4 12:30:46 2018 - [info] Checking replication filtering settings..
　　Sun Feb  4 12:30:46 2018 - [info]  Replication filtering check ok.
　　Sun Feb  4 12:30:46 2018 - [info] Master is down!
　　Sun Feb  4 12:30:46 2018 - [info] Terminating monitoring script.
　　Sun Feb  4 12:30:46 2018 - [info] Got exit code 20 (Master dead).
　　Sun Feb  4 12:30:46 2018 - [info] MHA::MasterFailover version 0.57.
　　Sun Feb  4 12:30:46 2018 - [info] Starting master failover.
　　Sun Feb  4 12:30:46 2018 - [info]
　　Sun Feb  4 12:30:46 2018 - [info] * Phase 1: Configuration Check Phase..
　　Sun Feb  4 12:30:46 2018 - [info]
　　Sun Feb  4 12:30:47 2018 - [info] GTID failover mode = 1
　　Sun Feb  4 12:30:47 2018 - [info] Dead Servers:
　　Sun Feb  4 12:30:47 2018 - [info]   192.168.1.110(192.168.1.110:3110)
　　Sun Feb  4 12:30:47 2018 - [info] Checking master reachability via MySQL(double check)...
　　Sun Feb  4 12:30:48 2018 - [info]  ok.
　　Sun Feb  4 12:30:48 2018 - [info] Alive Servers:
　　Sun Feb  4 12:30:48 2018 - [info]   192.168.1.109(192.168.1.109:3109)
　　Sun Feb  4 12:30:48 2018 - [info] Alive Slaves:
　　Sun Feb  4 12:30:48 2018 - [info]   192.168.1.109(192.168.1.109:3109)  Version=5.7.19-log (oldest major version between slaves) log-bin:enabled
　　Sun Feb  4 12:30:48 2018 - [info]     GTID ON
　　Sun Feb  4 12:30:48 2018 - [info]     Replicating from 192.168.1.110(192.168.1.110:3110)
　　Sun Feb  4 12:30:48 2018 - [info]     Primary candidate for the new Master (candidate_master is set)
　　Sun Feb  4 12:30:48 2018 - [info] Starting GTID based failover.
　　Sun Feb  4 12:30:48 2018 - [info]
　　Sun Feb  4 12:30:48 2018 - [info] ** Phase 1: Configuration Check Phase completed.
　　Sun Feb  4 12:30:48 2018 - [info]
　　Sun Feb  4 12:30:48 2018 - [info] * Phase 2: Dead Master Shutdown Phase..
　　Sun Feb  4 12:30:48 2018 - [info]
　　Sun Feb  4 12:30:48 2018 - [info] Forcing shutdown so that applications never connect to the current master..
　　Sun Feb  4 12:30:48 2018 - [info] Executing master IP deactivation script:
　　Sun Feb  4 12:30:48 2018 - [info]   /etc/mha/master_ip_failover --orig_master_host=192.168.1.110 --orig_master_ip=192.168.1.110 --orig_master_port=3110 --command=stop
　　Sun Feb  4 12:30:48 2018 - [info]  done.
　　Sun Feb  4 12:30:48 2018 - [warning] shutdown_script is not set. Skipping explicit shutting down of the dead master.
　　Sun Feb  4 12:30:48 2018 - [info] * Phase 2: Dead Master Shutdown Phase completed.
　　Sun Feb  4 12:30:48 2018 - [info]
　　Sun Feb  4 12:30:48 2018 - [info] * Phase 3: Master Recovery Phase..
　　Sun Feb  4 12:30:48 2018 - [info]
　　Sun Feb  4 12:30:48 2018 - [info] * Phase 3.1: Getting Latest Slaves Phase..
　　Sun Feb  4 12:30:48 2018 - [info]
　　Sun Feb  4 12:30:48 2018 - [info] The latest binary log file/position on all slaves is 3110binlog.000073:234

　　Sun Feb  4 12:30:48 2018 - [info] Latest slaves (Slaves that received>　　Sun Feb  4 12:30:48 2018 - [info]   192.168.1.109(192.168.1.109:3109)  Version=5.7.19-log (oldest major version between slaves) log-bin:enabled
　　Sun Feb  4 12:30:48 2018 - [info]     GTID ON
　　Sun Feb  4 12:30:48 2018 - [info]     Replicating from 192.168.1.110(192.168.1.110:3110)
　　Sun Feb  4 12:30:48 2018 - [info]     Primary candidate for the new Master (candidate_master is set)
　　Sun Feb  4 12:30:48 2018 - [info] The oldest binary log file/position on all slaves is 3110binlog.000073:234
　　Sun Feb  4 12:30:48 2018 - [info] Oldest slaves:
　　Sun Feb  4 12:30:48 2018 - [info]   192.168.1.109(192.168.1.109:3109)  Version=5.7.19-log (oldest major version between slaves) log-bin:enabled
　　Sun Feb  4 12:30:48 2018 - [info]     GTID ON
　　Sun Feb  4 12:30:48 2018 - [info]     Replicating from 192.168.1.110(192.168.1.110:3110)
　　Sun Feb  4 12:30:48 2018 - [info]     Primary candidate for the new Master (candidate_master is set)
　　Sun Feb  4 12:30:48 2018 - [info]
　　Sun Feb  4 12:30:48 2018 - [info] * Phase 3.3: Determining New Master Phase..
　　Sun Feb  4 12:30:48 2018 - [info]
　　Sun Feb  4 12:30:48 2018 - [info] Searching new master from slaves..
　　Sun Feb  4 12:30:48 2018 - [info]  Candidate masters from the configuration file:
　　Sun Feb  4 12:30:48 2018 - [info]   192.168.1.109(192.168.1.109:3109)  Version=5.7.19-log (oldest major version between slaves) log-bin:enabled
　　Sun Feb  4 12:30:48 2018 - [info]     GTID ON
　　Sun Feb  4 12:30:48 2018 - [info]     Replicating from 192.168.1.110(192.168.1.110:3110)
　　Sun Feb  4 12:30:48 2018 - [info]     Primary candidate for the new Master (candidate_master is set)
　　Sun Feb  4 12:30:48 2018 - [info]  Non-candidate masters:

　　Sun Feb  4 12:30:48 2018 - [info]  Searching from candidate_master slaves which have received the latest>　　Sun Feb  4 12:30:48 2018 - [info] New master is 192.168.1.109(192.168.1.109:3109)
　　Sun Feb  4 12:30:48 2018 - [info] Starting master failover..
　　Sun Feb  4 12:30:48 2018 - [info]
　　From:
　　192.168.1.110(192.168.1.110:3110) (current master)
　　+--192.168.1.109(192.168.1.109:3109)
　　To:
　　192.168.1.109(192.168.1.109:3109) (new master)
　　Sun Feb  4 12:30:48 2018 - [info]
　　Sun Feb  4 12:30:48 2018 - [info] * Phase 3.3: New Master Recovery Phase..
　　Sun Feb  4 12:30:48 2018 - [info]
　　Sun Feb  4 12:30:48 2018 - [info]  Waiting all logs to be applied..
　　Sun Feb  4 12:30:48 2018 - [info]   done.
　　Sun Feb  4 12:30:48 2018 - [info] Getting new master's binlog name and position..
　　Sun Feb  4 12:30:48 2018 - [info]  3109binlog.000089:234
　　Sun Feb  4 12:30:48 2018 - [info]  All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='192.168.1.109', MASTER_PORT=3109, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='xxx';
　　Sun Feb  4 12:30:48 2018 - [info] Master Recovery succeeded. File:Pos:Exec_Gtid_Set: 3109binlog.000089, 234, 2285de8a-9bc2-11e7-8a15-000c298e9a6f:1-14,
　　28ea40ab-9bbd-11e7-8cd1-000c29c31069:1-15308048
　　Sun Feb  4 12:30:48 2018 - [info] Executing master IP activate script:
　　Sun Feb  4 12:30:48 2018 - [info]   /etc/mha/master_ip_failover --command=start --ssh_user=root --orig_master_host=192.168.1.110 --orig_master_ip=192.168.1.110 --orig_master_port=3110 --new_master_host=192.168.1.109 --new_master_ip=192.168.1.109 --new_master_port=3109 --new_master_user='mha'   --new_master_password=xxx
　　Set read_only=0 on the new master.
　　ssh: connect to host 192.168.1.110 port 22: Connection timed out
　　Sun Feb  4 12:31:35 2018 - [info]  OK.
　　Sun Feb  4 12:31:35 2018 - [info] ** Finished master recovery successfully.
　　Sun Feb  4 12:31:35 2018 - [info] * Phase 3: Master Recovery Phase completed.
　　Sun Feb  4 12:31:35 2018 - [info]
　　Sun Feb  4 12:31:35 2018 - [info] * Phase 4: Slaves Recovery Phase..
　　Sun Feb  4 12:31:35 2018 - [info]
　　Sun Feb  4 12:31:35 2018 - [info]
　　Sun Feb  4 12:31:35 2018 - [info] * Phase 4.1: Starting Slaves in parallel..
　　Sun Feb  4 12:31:35 2018 - [info]
　　Sun Feb  4 12:31:35 2018 - [info] All new slave servers recovered successfully.
　　Sun Feb  4 12:31:35 2018 - [info]
　　Sun Feb  4 12:31:35 2018 - [info] * Phase 5: New master cleanup phase..
　　Sun Feb  4 12:31:35 2018 - [info]
　　Sun Feb  4 12:31:35 2018 - [info] Resetting slave info on the new master..
　　Sun Feb  4 12:31:35 2018 - [info]  192.168.1.109: Resetting slave info succeeded.
　　Sun Feb  4 12:31:35 2018 - [info] Master failover to 192.168.1.109(192.168.1.109:3109) completed successfully.
　　Sun Feb  4 12:31:35 2018 - [info]
　　----- Failover Report -----
　　mainBusiness: MySQL Master failover 192.168.1.110(192.168.1.110:3110) to 192.168.1.109(192.168.1.109:3109) succeeded
　　Master 192.168.1.110(192.168.1.110:3110) is down!
　　Check MHA Manager logs at Client-Cent7-IP008:/data/mha/mainBusiness/manager.log for details.
　　Started automated(non-interactive) failover.
　　Invalidated master IP address on 192.168.1.110(192.168.1.110:3110)
　　Selected 192.168.1.109(192.168.1.109:3109) as a new master.
　　192.168.1.109(192.168.1.109:3109): OK: Applying all logs succeeded.
　　192.168.1.109(192.168.1.109:3109): OK: Activated master IP address.
　　192.168.1.109(192.168.1.109:3109): Resetting slave info succeeded.
　　Master failover to 192.168.1.109(192.168.1.109:3109) completed successfully.
　　Sun Feb  4 12:31:35 2018 - [info] Sending mail..
　　Option new_slave_hosts requires an argument     #双节点，没有多余的从节点，所以会报错，下面也是。
　　Unknown option: conf