现在来说,我们的MHA已经完全搭建起来了,下面测试一下故障转移看看有没有问题:
(1)下面开始测试手动的故障转移:
手动故障转移:
masterha_master_switch --master_state=dead --conf=/etc/masterha/app1.cnf --dead_master_host=172.16.16.34 --dead_master_port=3306 --new_master_host=172.16.16.35 --new_master_port=3306 --ignore_last_failover
执行完成后就能够看到主库的地址已经是172.16.16.35:3306,VIP:172.16.16.20也已经转移到了172.16.16.35上了。
(2)测试一下自动故障转移
现在我们重新做主从
VIP:172.16.16.20在server2:172.16.16.35上,MySQL master是:172.16.16.35:3306
从库:172.16.16.34:3306和172.16.16.35:3307
手动kill 掉主库
发现已经自动切换了,接下来看一下日志:
Mon Jun 5 14:23:13 2017 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Mon Jun 5 14:23:13 2017 - [info] Reading application default configuration from /etc/masterha/app1.cnf.. Mon Jun 5 14:23:13 2017 - [info] Reading server configuration from /etc/masterha/app1.cnf.. Mon Jun 5 14:23:13 2017 - [info] MHA::MasterMonitor version 0.57. Mon Jun 5 14:23:14 2017 - [info] GTID failover mode = 1 Mon Jun 5 14:23:14 2017 - [info] Dead Servers: Mon Jun 5 14:23:14 2017 - [info] Alive Servers: Mon Jun 5 14:23:14 2017 - [info] 172.16.16.35(172.16.16.35:3306) Mon Jun 5 14:23:14 2017 - [info] 172.16.16.34(172.16.16.34:3306) Mon Jun 5 14:23:14 2017 - [info] 172.16.16.35(172.16.16.35:3307) Mon Jun 5 14:23:14 2017 - [info] Alive Slaves: Mon Jun 5 14:23:14 2017 - [info] 172.16.16.34(172.16.16.34:3306) Version=5.7.14-log (oldest major version between slaves) log-bin:enabled Mon Jun 5 14:23:14 2017 - [info] GTID ON Mon Jun 5 14:23:14 2017 - [info] Replicating from 172.16.16.35(172.16.16.35:3306) Mon Jun 5 14:23:14 2017 - [info] Primary candidate for the new Master (candidate_master is set) Mon Jun 5 14:23:14 2017 - [info] 172.16.16.35(172.16.16.35:3307) Version=5.7.14-log (oldest major version between slaves) log-bin:enabled Mon Jun 5 14:23:14 2017 - [info] GTID ON Mon Jun 5 14:23:14 2017 - [info] Replicating from 172.16.16.35(172.16.16.35:3306) Mon Jun 5 14:23:14 2017 - [info] Current Alive Master: 172.16.16.35(172.16.16.35:3306) Mon Jun 5 14:23:14 2017 - [info] Checking slave configurations.. Mon Jun 5 14:23:14 2017 - [info] Checking replication filtering settings.. Mon Jun 5 14:23:14 2017 - [info] binlog_do_db= , binlog_ignore_db= Mon Jun 5 14:23:14 2017 - [info] Replication filtering check ok. Mon Jun 5 14:23:14 2017 - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking. Mon Jun 5 14:23:14 2017 - [info] Checking SSH publickey authentication settings on the current master.. Mon Jun 5 14:23:14 2017 - [info] HealthCheck: SSH to 172.16.16.35 is reachable. Mon Jun 5 14:23:14 2017 - [info] 172.16.16.35(172.16.16.35:3306) (current master) +--172.16.16.34(172.16.16.34:3306) +--172.16.16.35(172.16.16.35:3307) Mon Jun 5 14:23:14 2017 - [info] Checking master_ip_failover_script status: Mon Jun 5 14:23:14 2017 - [info] /usr/local/bin/master_ip_failover --command=status --ssh_user=root --orig_master_host=172.16.16.35 --orig_master_ip=172.16.16.35 --orig_master_port=3306 IN SCRIPT TEST====/sbin/ifconfig eth0:1 down==/sbin/ifconfig eth0:1 172.16.16.20/24=== Checking the Status of the script.. OK Mon Jun 5 14:23:14 2017 - [info] OK. Mon Jun 5 14:23:14 2017 - [warning] shutdown_script is not defined. Mon Jun 5 14:23:14 2017 - [info] Set master ping interval 1 seconds. Mon Jun 5 14:23:14 2017 - [warning] secondary_check_script is not defined. It is highly recommended setting it to check master reachability from two or more routes. Mon Jun 5 14:23:14 2017 - [info] Starting ping health check on 172.16.16.35(172.16.16.35:3306).. Mon Jun 5 14:23:14 2017 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond.. Mon Jun 5 14:23:46 2017 - [warning] Got error on MySQL select ping: 2006 (MySQL server has gone away) Mon Jun 5 14:23:46 2017 - [info] Executing SSH check script: exit 0 Mon Jun 5 14:23:46 2017 - [info] HealthCheck: SSH to 172.16.16.35 is reachable. Mon Jun 5 14:23:47 2017 - [warning] Got error on MySQL connect: 2013 (Lost connection to MySQL server at 'reading initial communication packet', system error: 111) Mon Jun 5 14:23:47 2017 - [warning] Connection failed 2 time(s).. Mon Jun 5 14:23:48 2017 - [warning] Got error on MySQL connect: 2013 (Lost connection to MySQL server at 'reading initial communication packet', system error: 111) Mon Jun 5 14:23:48 2017 - [warning] Connection failed 3 time(s).. Mon Jun 5 14:23:49 2017 - [warning] Got error on MySQL connect: 2013 (Lost connection to MySQL server at 'reading initial communication packet', system error: 111) Mon Jun 5 14:23:49 2017 - [warning] Connection failed 4 time(s).. Mon Jun 5 14:23:49 2017 - [warning] Master is not reachable from health checker! Mon Jun 5 14:23:49 2017 - [warning] Master 172.16.16.35(172.16.16.35:3306) is not reachable! Mon Jun 5 14:23:49 2017 - [warning] SSH is reachable. Mon Jun 5 14:23:49 2017 - [info] Connecting to a master server failed. Reading configuration file /etc/masterha_default.cnf and /etc/masterha/app1.cnf again, and trying to connect to all servers to check server status.. Mon Jun 5 14:23:49 2017 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Mon Jun 5 14:23:49 2017 - [info] Reading application default configuration from /etc/masterha/app1.cnf.. Mon Jun 5 14:23:49 2017 - [info] Reading server configuration from /etc/masterha/app1.cnf.. Mon Jun 5 14:23:49 2017 - [info] GTID failover mode = 1 Mon Jun 5 14:23:49 2017 - [info] Dead Servers: Mon Jun 5 14:23:49 2017 - [info] 172.16.16.35(172.16.16.35:3306) Mon Jun 5 14:23:49 2017 - [info] Alive Servers: Mon Jun 5 14:23:49 2017 - [info] 172.16.16.34(172.16.16.34:3306) Mon Jun 5 14:23:49 2017 - [info] 172.16.16.35(172.16.16.35:3307) Mon Jun 5 14:23:49 2017 - [info] Alive Slaves: Mon Jun 5 14:23:49 2017 - [info] 172.16.16.34(172.16.16.34:3306) Version=5.7.14-log (oldest major version between slaves) log-bin:enabled Mon Jun 5 14:23:49 2017 - [info] GTID ON Mon Jun 5 14:23:49 2017 - [info] Replicating from 172.16.16.35(172.16.16.35:3306) Mon Jun 5 14:23:49 2017 - [info] Primary candidate for the new Master (candidate_master is set) Mon Jun 5 14:23:49 2017 - [info] 172.16.16.35(172.16.16.35:3307) Version=5.7.14-log (oldest major version between slaves) log-bin:enabled Mon Jun 5 14:23:49 2017 - [info] GTID ON Mon Jun 5 14:23:49 2017 - [info] Replicating from 172.16.16.35(172.16.16.35:3306) Mon Jun 5 14:23:49 2017 - [info] Checking slave configurations.. Mon Jun 5 14:23:49 2017 - [info] Checking replication filtering settings.. Mon Jun 5 14:23:49 2017 - [info] Replication filtering check ok. Mon Jun 5 14:23:49 2017 - [info] Master is down! Mon Jun 5 14:23:49 2017 - [info] Terminating monitoring script. Mon Jun 5 14:23:49 2017 - [info] Got exit code 20 (Master dead). Mon Jun 5 14:23:49 2017 - [info] MHA::MasterFailover version 0.57. Mon Jun 5 14:23:49 2017 - [info] Starting master failover. Mon Jun 5 14:23:49 2017 - [info] Mon Jun 5 14:23:49 2017 - [info] * Phase 1: Configuration Check Phase.. Mon Jun 5 14:23:49 2017 - [info] Mon Jun 5 14:23:49 2017 - [info] GTID failover mode = 1 Mon Jun 5 14:23:49 2017 - [info] Dead Servers: Mon Jun 5 14:23:49 2017 - [info] 172.16.16.35(172.16.16.35:3306) Mon Jun 5 14:23:49 2017 - [info] Checking master reachability via MySQL(double check)... Mon Jun 5 14:23:49 2017 - [info] ok. Mon Jun 5 14:23:49 2017 - [info] Alive Servers: Mon Jun 5 14:23:49 2017 - [info] 172.16.16.34(172.16.16.34:3306) Mon Jun 5 14:23:49 2017 - [info] 172.16.16.35(172.16.16.35:3307) Mon Jun 5 14:23:49 2017 - [info] Alive Slaves: Mon Jun 5 14:23:49 2017 - [info] 172.16.16.34(172.16.16.34:3306) Version=5.7.14-log (oldest major version between slaves) log-bin:enabled Mon Jun 5 14:23:49 2017 - [info] GTID ON Mon Jun 5 14:23:49 2017 - [info] Replicating from 172.16.16.35(172.16.16.35:3306) Mon Jun 5 14:23:49 2017 - [info] Primary candidate for the new Master (candidate_master is set) Mon Jun 5 14:23:49 2017 - [info] 172.16.16.35(172.16.16.35:3307) Version=5.7.14-log (oldest major version between slaves) log-bin:enabled Mon Jun 5 14:23:49 2017 - [info] GTID ON Mon Jun 5 14:23:49 2017 - [info] Replicating from 172.16.16.35(172.16.16.35:3306) Mon Jun 5 14:23:49 2017 - [info] Starting GTID based failover. Mon Jun 5 14:23:49 2017 - [info] Mon Jun 5 14:23:49 2017 - [info] ** Phase 1: Configuration Check Phase completed. Mon Jun 5 14:23:49 2017 - [info] Mon Jun 5 14:23:49 2017 - [info] * Phase 2: Dead Master Shutdown Phase.. Mon Jun 5 14:23:49 2017 - [info] Mon Jun 5 14:23:49 2017 - [info] Forcing shutdown so that applications never connect to the current master.. Mon Jun 5 14:23:49 2017 - [info] Executing master IP deactivation script: Mon Jun 5 14:23:49 2017 - [info] /usr/local/bin/master_ip_failover --orig_master_host=172.16.16.35 --orig_master_ip=172.16.16.35 --orig_master_port=3306 --command=stopssh --ssh_user=root IN SCRIPT TEST====/sbin/ifconfig eth0:1 down==/sbin/ifconfig eth0:1 172.16.16.20/24=== Disabling the VIP on old master: 172.16.16.35 Mon Jun 5 14:23:49 2017 - [info] done. Mon Jun 5 14:23:49 2017 - [warning] shutdown_script is not set. Skipping explicit shutting down of the dead master. Mon Jun 5 14:23:49 2017 - [info] * Phase 2: Dead Master Shutdown Phase completed. Mon Jun 5 14:23:49 2017 - [info] Mon Jun 5 14:23:49 2017 - [info] * Phase 3: Master Recovery Phase.. Mon Jun 5 14:23:49 2017 - [info] Mon Jun 5 14:23:49 2017 - [info] * Phase 3.1: Getting Latest Slaves Phase.. Mon Jun 5 14:23:49 2017 - [info] Mon Jun 5 14:23:49 2017 - [info] The latest binary log file/position on all slaves is mysql-bin.000003:194 Mon Jun 5 14:23:49 2017 - [info] Retrieved Gtid Set: 806ede0c-357e-11e7-9719-00505693235d:1 Mon Jun 5 14:23:49 2017 - [info] Latest slaves (Slaves that received relay log files to the latest): Mon Jun 5 14:23:49 2017 - [info] 172.16.16.34(172.16.16.34:3306) Version=5.7.14-log (oldest major version between slaves) log-bin:enabled Mon Jun 5 14:23:49 2017 - [info] GTID ON Mon Jun 5 14:23:49 2017 - [info] Replicating from 172.16.16.35(172.16.16.35:3306) Mon Jun 5 14:23:49 2017 - [info] Primary candidate for the new Master (candidate_master is set) Mon Jun 5 14:23:49 2017 - [info] 172.16.16.35(172.16.16.35:3307) Version=5.7.14-log (oldest major version between slaves) log-bin:enabled Mon Jun 5 14:23:49 2017 - [info] GTID ON Mon Jun 5 14:23:49 2017 - [info] Replicating from 172.16.16.35(172.16.16.35:3306) Mon Jun 5 14:23:49 2017 - [info] The oldest binary log file/position on all slaves is mysql-bin.000003:194 Mon Jun 5 14:23:49 2017 - [info] Retrieved Gtid Set: 806ede0c-357e-11e7-9719-00505693235d:1 Mon Jun 5 14:23:49 2017 - [info] Oldest slaves: Mon Jun 5 14:23:49 2017 - [info] 172.16.16.34(172.16.16.34:3306) Version=5.7.14-log (oldest major version between slaves) log-bin:enabled Mon Jun 5 14:23:49 2017 - [info] GTID ON Mon Jun 5 14:23:49 2017 - [info] Replicating from 172.16.16.35(172.16.16.35:3306) Mon Jun 5 14:23:49 2017 - [info] Primary candidate for the new Master (candidate_master is set) Mon Jun 5 14:23:49 2017 - [info] 172.16.16.35(172.16.16.35:3307) Version=5.7.14-log (oldest major version between slaves) log-bin:enabled Mon Jun 5 14:23:49 2017 - [info] GTID ON Mon Jun 5 14:23:49 2017 - [info] Replicating from 172.16.16.35(172.16.16.35:3306) Mon Jun 5 14:23:49 2017 - [info] Mon Jun 5 14:23:49 2017 - [info] * Phase 3.3: Determining New Master Phase.. Mon Jun 5 14:23:49 2017 - [info] Mon Jun 5 14:23:49 2017 - [info] Searching new master from slaves.. Mon Jun 5 14:23:49 2017 - [info] Candidate masters from the configuration file: Mon Jun 5 14:23:49 2017 - [info] 172.16.16.34(172.16.16.34:3306) Version=5.7.14-log (oldest major version between slaves) log-bin:enabled Mon Jun 5 14:23:49 2017 - [info] GTID ON Mon Jun 5 14:23:49 2017 - [info] Replicating from 172.16.16.35(172.16.16.35:3306) Mon Jun 5 14:23:49 2017 - [info] Primary candidate for the new Master (candidate_master is set) Mon Jun 5 14:23:49 2017 - [info] Non-candidate masters: Mon Jun 5 14:23:49 2017 - [info] Searching from candidate_master slaves which have received the latest relay log events.. Mon Jun 5 14:23:49 2017 - [info] New master is 172.16.16.34(172.16.16.34:3306) Mon Jun 5 14:23:49 2017 - [info] Starting master failover.. Mon Jun 5 14:23:49 2017 - [info] From: 172.16.16.35(172.16.16.35:3306) (current master) +--172.16.16.34(172.16.16.34:3306) +--172.16.16.35(172.16.16.35:3307) To: 172.16.16.34(172.16.16.34:3306) (new master) +--172.16.16.35(172.16.16.35:3307) Mon Jun 5 14:23:49 2017 - [info] Mon Jun 5 14:23:49 2017 - [info] * Phase 3.3: New Master Recovery Phase.. Mon Jun 5 14:23:49 2017 - [info] Mon Jun 5 14:23:49 2017 - [info] Waiting all logs to be applied.. Mon Jun 5 14:23:49 2017 - [info] done. Mon Jun 5 14:23:49 2017 - [info] Getting new master's binlog name and position.. Mon Jun 5 14:23:49 2017 - [info] mysql-bin.000001:427 Mon Jun 5 14:23:49 2017 - [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='172.16.16.34', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='root', MASTER_PASSWORD='xxx'; Mon Jun 5 14:23:49 2017 - [info] Master Recovery succeeded. File:Pos:Exec_Gtid_Set: mysql-bin.000001, 427, 806ede0c-357e-11e7-9719-00505693235d:1 Mon Jun 5 14:23:49 2017 - [info] Executing master IP activate script: Mon Jun 5 14:23:49 2017 - [info] /usr/local/bin/master_ip_failover --command=start --ssh_user=root --orig_master_host=172.16.16.35 --orig_master_ip=172.16.16.35 --orig_master_port=3306 --new_master_host=172.16.16.34 --new_master_ip=172.16.16.34 --new_master_port=3306 --new_master_user='root' --new_master_password=xxx Unknown option: new_master_user Unknown option: new_master_password IN SCRIPT TEST====/sbin/ifconfig eth0:1 down==/sbin/ifconfig eth0:1 172.16.16.20/24=== Enabling the VIP - 172.16.16.20/24 on the new master - 172.16.16.34 Mon Jun 5 14:23:50 2017 - [info] OK. Mon Jun 5 14:23:50 2017 - [info] Setting read_only=0 on 172.16.16.34(172.16.16.34:3306).. Mon Jun 5 14:23:50 2017 - [info] ok. Mon Jun 5 14:23:50 2017 - [info] ** Finished master recovery successfully. Mon Jun 5 14:23:50 2017 - [info] * Phase 3: Master Recovery Phase completed. Mon Jun 5 14:23:50 2017 - [info] Mon Jun 5 14:23:50 2017 - [info] * Phase 4: Slaves Recovery Phase.. Mon Jun 5 14:23:50 2017 - [info] Mon Jun 5 14:23:50 2017 - [info] Mon Jun 5 14:23:50 2017 - [info] * Phase 4.1: Starting Slaves in parallel.. Mon Jun 5 14:23:50 2017 - [info] Mon Jun 5 14:23:50 2017 - [info] -- Slave recovery on host 172.16.16.35(172.16.16.35:3307) started, pid: 636. Check tmp log /var/log/mha/app1.log/172.16.16.35_3307_20170605142349.log if it takes time.. Mon Jun 5 14:23:50 2017 - [info] Mon Jun 5 14:23:50 2017 - [info] Log messages from 172.16.16.35 ... Mon Jun 5 14:23:50 2017 - [info] Mon Jun 5 14:23:50 2017 - [info] Resetting slave 172.16.16.35(172.16.16.35:3307) and starting replication from the new master 172.16.16.34(172.16.16.34:3306).. Mon Jun 5 14:23:50 2017 - [info] Executed CHANGE MASTER. Mon Jun 5 14:23:50 2017 - [info] Slave started. Mon Jun 5 14:23:50 2017 - [info] gtid_wait(806ede0c-357e-11e7-9719-00505693235d:1) completed on 172.16.16.35(172.16.16.35:3307). Executed 0 events. Mon Jun 5 14:23:50 2017 - [info] End of log messages from 172.16.16.35. Mon Jun 5 14:23:50 2017 - [info] -- Slave on host 172.16.16.35(172.16.16.35:3307) started. Mon Jun 5 14:23:50 2017 - [info] All new slave servers recovered successfully. Mon Jun 5 14:23:50 2017 - [info] Mon Jun 5 14:23:50 2017 - [info] * Phase 5: New master cleanup phase.. Mon Jun 5 14:23:50 2017 - [info] Mon Jun 5 14:23:50 2017 - [info] Resetting slave info on the new master.. Mon Jun 5 14:23:50 2017 - [info] 172.16.16.34: Resetting slave info succeeded. Mon Jun 5 14:23:50 2017 - [info] Master failover to 172.16.16.34(172.16.16.34:3306) completed successfully. Mon Jun 5 14:23:50 2017 - [info] Deleted server1 entry from /etc/masterha/app1.cnf . Mon Jun 5 14:23:50 2017 - [info] ----- Failover Report ----- app1: MySQL Master failover 172.16.16.35(172.16.16.35:3306) to 172.16.16.34(172.16.16.34:3306) succeeded Master 172.16.16.35(172.16.16.35:3306) is down! Check MHA Manager logs at localhost.localdomain:/var/log/mha/app1/manager.log for details. Started automated(non-interactive) failover. Invalidated master IP address on 172.16.16.35(172.16.16.35:3306) Selected 172.16.16.34(172.16.16.34:3306) as a new master. 172.16.16.34(172.16.16.34:3306): OK: Applying all logs succeeded. 172.16.16.34(172.16.16.34:3306): OK: Activated master IP address. 172.16.16.35(172.16.16.35:3307): OK: Slave started, replicating from 172.16.16.34(172.16.16.34:3306) 172.16.16.34(172.16.16.34:3306): Resetting slave info succeeded. Master failover to 172.16.16.34(172.16.16.34:3306) completed successfully. Mon Jun 5 14:23:50 2017 - [info] Sending mail.. sh: /usr/local/bin/send_report: No such file or directory Mon Jun 5 14:23:50 2017 - [error][/usr/share/perl5/vendor_perl/MHA/MasterFailover.pm, ln2066] Failed to send mail with return code 127:0 tail: /var/log/mha/app1/manager.log: file truncated Mon Jun 5 14:48:26 2017 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Mon Jun 5 14:48:26 2017 - [info] Reading application default configuration from /etc/masterha/app1.cnf.. Mon Jun 5 14:48:26 2017 - [info] Reading server configuration from /etc/masterha/app1.cnf.. Mon Jun 5 14:48:26 2017 - [info] MHA::MasterMonitor version 0.57. Mon Jun 5 14:48:26 2017 - [info] GTID failover mode = 1 Mon Jun 5 14:48:26 2017 - [info] Dead Servers: Mon Jun 5 14:48:26 2017 - [info] Alive Servers: Mon Jun 5 14:48:26 2017 - [info] 172.16.16.34(172.16.16.34:3306) Mon Jun 5 14:48:26 2017 - [info] 172.16.16.35(172.16.16.35:3306) Mon Jun 5 14:48:26 2017 - [info] 172.16.16.35(172.16.16.35:3307) Mon Jun 5 14:48:26 2017 - [info] Alive Slaves: Mon Jun 5 14:48:26 2017 - [info] 172.16.16.35(172.16.16.35:3306) Version=5.7.14-log (oldest major version between slaves) log-bin:enabled Mon Jun 5 14:48:26 2017 - [info] GTID ON Mon Jun 5 14:48:26 2017 - [info] Replicating from 172.16.16.34(172.16.16.34:3306) Mon Jun 5 14:48:26 2017 - [info] Primary candidate for the new Master (candidate_master is set) Mon Jun 5 14:48:26 2017 - [info] 172.16.16.35(172.16.16.35:3307) Version=5.7.14-log (oldest major version between slaves) log-bin:enabled Mon Jun 5 14:48:26 2017 - [info] GTID ON Mon Jun 5 14:48:26 2017 - [info] Replicating from 172.16.16.34(172.16.16.34:3306) Mon Jun 5 14:48:26 2017 - [info] Current Alive Master: 172.16.16.34(172.16.16.34:3306) Mon Jun 5 14:48:26 2017 - [info] Checking slave configurations.. Mon Jun 5 14:48:26 2017 - [info] Checking replication filtering settings.. Mon Jun 5 14:48:26 2017 - [info] binlog_do_db= , binlog_ignore_db= Mon Jun 5 14:48:26 2017 - [info] Replication filtering check ok. Mon Jun 5 14:48:26 2017 - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking. Mon Jun 5 14:48:26 2017 - [info] Checking SSH publickey authentication settings on the current master.. Mon Jun 5 14:48:26 2017 - [info] HealthCheck: SSH to 172.16.16.34 is reachable. Mon Jun 5 14:48:26 2017 - [info] 172.16.16.34(172.16.16.34:3306) (current master) +--172.16.16.35(172.16.16.35:3306) +--172.16.16.35(172.16.16.35:3307) Mon Jun 5 14:48:26 2017 - [info] Checking master_ip_failover_script status: Mon Jun 5 14:48:26 2017 - [info] /usr/local/bin/master_ip_failover --command=status --ssh_user=root --orig_master_host=172.16.16.34 --orig_master_ip=172.16.16.34 --orig_master_port=3306 IN SCRIPT TEST====/sbin/ifconfig eth0:1 down==/sbin/ifconfig eth0:1 172.16.16.20/24=== Checking the Status of the script.. OK Mon Jun 5 14:48:26 2017 - [info] OK. Mon Jun 5 14:48:26 2017 - [warning] shutdown_script is not defined. Mon Jun 5 14:48:26 2017 - [info] Set master ping interval 1 seconds. Mon Jun 5 14:48:26 2017 - [warning] secondary_check_script is not defined. It is highly recommended setting it to check master reachability from two or more routes. Mon Jun 5 14:48:26 2017 - [info] Starting ping health check on 172.16.16.34(172.16.16.34:3306).. Mon Jun 5 14:48:26 2017 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond.. ^C [root@localhost ~]# tail -100 /var/log/mha/app1/manager.log Mon Jun 5 14:48:26 2017 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Mon Jun 5 14:48:26 2017 - [info] Reading application default configuration from /etc/masterha/app1.cnf.. Mon Jun 5 14:48:26 2017 - [info] Reading server configuration from /etc/masterha/app1.cnf.. Mon Jun 5 14:48:26 2017 - [info] MHA::MasterMonitor version 0.57. Mon Jun 5 14:48:26 2017 - [info] GTID failover mode = 1 Mon Jun 5 14:48:26 2017 - [info] Dead Servers: Mon Jun 5 14:48:26 2017 - [info] Alive Servers: Mon Jun 5 14:48:26 2017 - [info] 172.16.16.34(172.16.16.34:3306) Mon Jun 5 14:48:26 2017 - [info] 172.16.16.35(172.16.16.35:3306) Mon Jun 5 14:48:26 2017 - [info] 172.16.16.35(172.16.16.35:3307) Mon Jun 5 14:48:26 2017 - [info] Alive Slaves: Mon Jun 5 14:48:26 2017 - [info] 172.16.16.35(172.16.16.35:3306) Version=5.7.14-log (oldest major version between slaves) log-bin:enabled Mon Jun 5 14:48:26 2017 - [info] GTID ON Mon Jun 5 14:48:26 2017 - [info] Replicating from 172.16.16.34(172.16.16.34:3306) Mon Jun 5 14:48:26 2017 - [info] Primary candidate for the new Master (candidate_master is set) Mon Jun 5 14:48:26 2017 - [info] 172.16.16.35(172.16.16.35:3307) Version=5.7.14-log (oldest major version between slaves) log-bin:enabled Mon Jun 5 14:48:26 2017 - [info] GTID ON Mon Jun 5 14:48:26 2017 - [info] Replicating from 172.16.16.34(172.16.16.34:3306) Mon Jun 5 14:48:26 2017 - [info] Current Alive Master: 172.16.16.34(172.16.16.34:3306) Mon Jun 5 14:48:26 2017 - [info] Checking slave configurations.. Mon Jun 5 14:48:26 2017 - [info] Checking replication filtering settings.. Mon Jun 5 14:48:26 2017 - [info] binlog_do_db= , binlog_ignore_db= Mon Jun 5 14:48:26 2017 - [info] Replication filtering check ok. Mon Jun 5 14:48:26 2017 - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking. Mon Jun 5 14:48:26 2017 - [info] Checking SSH publickey authentication settings on the current master.. Mon Jun 5 14:48:26 2017 - [info] HealthCheck: SSH to 172.16.16.34 is reachable. Mon Jun 5 14:48:26 2017 - [info] 172.16.16.34(172.16.16.34:3306) (current master) +--172.16.16.35(172.16.16.35:3306) +--172.16.16.35(172.16.16.35:3307) Mon Jun 5 14:48:26 2017 - [info] Checking master_ip_failover_script status: Mon Jun 5 14:48:26 2017 - [info] /usr/local/bin/master_ip_failover --command=status --ssh_user=root --orig_master_host=172.16.16.34 --orig_master_ip=172.16.16.34 --orig_master_port=3306 IN SCRIPT TEST====/sbin/ifconfig eth0:1 down==/sbin/ifconfig eth0:1 172.16.16.20/24=== Checking the Status of the script.. OK Mon Jun 5 14:48:26 2017 - [info] OK. Mon Jun 5 14:48:26 2017 - [warning] shutdown_script is not defined. Mon Jun 5 14:48:26 2017 - [info] Set master ping interval 1 seconds. Mon Jun 5 14:48:26 2017 - [warning] secondary_check_script is not defined. It is highly recommended setting it to check master reachability from two or more routes. Mon Jun 5 14:48:26 2017 - [info] Starting ping health check on 172.16.16.34(172.16.16.34:3306)..
切换以后我们发现现在主从是OK的了,但是我们忘记了很重要的一个问题,那就是slave的relay log,主从复制在缺省情况下从库的relay logs会在SQL线程执行完毕后被自动删除,但是对于MHA场景下,对于某些滞后从库的恢复依赖于其他从库的relay log,因此采取禁用自动删除功能以及定期清理的办法。对于清理过多过大的relay log需要注意引起的复制延迟资源开销等。所以这里要将relay log的自动清除设置为OFF,采用手动清除relay log的方式:
mysql -uroot -h172.16.16.35 -P3306 -p123456 -e'set global relay_log_purge=OFF;' mysql -uroot -h172.16.16.35 -P3306 -p123456 -e'set global relay_log_purge=OFF;'
默认设置为OFF,这样relay lay每次SQL执行线程完毕后并不会被自动删除了,所以说我们需要手动删除掉relay log,在mha node的工具包里面有个purge_relay_logs工具来直接处理这个事情,
[root@mxqmongodb2 data]# purge_relay_logs --user=root --password=123456 --host=172.16.16.35 --port=3306 2017-06-06 09:11:23: purge_relay_logs script started. Opening /home/mysql/db3306/data/mxqmongodb2-relay-bin.000001 .. Opening /home/mysql/db3306/data/mxqmongodb2-relay-bin.000002 .. Opening /home/mysql/db3306/data/mxqmongodb2-relay-bin.000003 .. Executing SET GLOBAL relay_log_purge=1; FLUSH LOGS; sleeping a few seconds so that SQL thread can delete older relay log files (if it keeps up); SET GLOBAL relay_log_purge=0; .. ok. 2017-06-06 09:11:26: All relay log purging operations succeeded. [root@mxqmongodb2 data]# purge_relay_logs --user=root --password=123456 --host=172.16.16.35 --port=3307 2017-06-06 09:11:41: purge_relay_logs script started. Opening /home/mysql/db3307/data/mxqmongodb2-relay-bin.000001 .. Opening /home/mysql/db3307/data/mxqmongodb2-relay-bin.000002 .. Opening /home/mysql/db3307/data/mxqmongodb2-relay-bin.000003 .. Executing SET GLOBAL relay_log_purge=1; FLUSH LOGS; sleeping a few seconds so that SQL thread can delete older relay log files (if it keeps up); SET GLOBAL relay_log_purge=0; .. ok. 2017-06-06 09:11:44: All relay log purging operations succeeded.
我们这样就可以手动清除掉relay log了,也可以加到定时人物里面定时执行。
/usr/bin/purge_relay_logs --user=root --password=123456 --host=172.16.16.35 --port=3307
这样算是搭建完了。
本文永久更新链接地址:http://www.linuxidc.com/Linux/2017-06/144615.htm