Category:Mysql
Article From:https://www.cnblogs.com/zetanchen/p/9059959.html

【Environmental introduction]

System environment: Red Hat Enterprise Linux 7 + 5.7.18 + MHA version 0.57

【Test step: automatic switch

Current database status:

system

IP

host name

Remarks

Edition

xxsystem

192.168.142.111

mysqlmha1

Main library

5.7.18 -log MySQL Community Server (GPL)

192.168.142.112

mysqlmha2

Reserve (pre Main Library)

192.168.142.113

mysqlmha3

Repository & MHA MGM

192.168.142.111

mysqlmha1

VIP

Database status after switching:

system

IP

host name

Remarks

Edition

xxsystem

192.168.142.111

mysqlmha1

Reserve (after repair)

5.7.18 -log MySQL Community Server (GPL)

192.168.142.112

mysqlmha2

Main library

192.168.142.113

mysqlmha3

Repository & MHA MGM

192.168.142.112

mysqlmha1

VIP

 

Confirm the current database status, and confirm the current database status before each check or operation.

Check between nodessshIf the status of mutual trust is normal, if there is a false report, confirm the user, mutual trust and password.

$masterha_check_ssh –conf=/etc/masterha/app1.cnf

inspectmysqlWhether the master slave replication is normal, if there is a report error, confirm whether the script file permission is accurate, and whether the configuration file information is accurate.

$masterha_check_repl –conf=/etc/masterha/app1.cnf

 

Start the MHA automatic handover script at the mysqlmha3 management node, which automatically stops its monitoring process after one switch.

[mha@mysqlmha3 shell]$ sh masterha_manager.sh start
[1]+  Complete the SH masterha_manager.sh start
[mha@mysqlmha3 shell]$
[mha@mysqlmha3 shell]$ ps -ef |grep masterha_manager
mha       3154     1  2 23:34 pts/2    00:00:00 perl /usr/bin/masterha_manager –conf=/etc/masterha/app1.cnf –ignore_last_failover
mha       3169  2977  0 23:34 pts/2    00:00:00 grep –color=auto masterha_manager
[mha@mysqlmha3 shell]$

 

The analog data DOWN dropped on the mysqlmha1 node:

[root@mysqlmha1 ~]# ps -ef|grep mysqld|awk ‘{print “kill -9 “$2}’|sh
sh: The second line: kill: (3602) – no process
[1]+  The mysqld –defaults-file=/etc/mymha.cnf has been killed.
[root@mysqlmha1 ~]#

 

View the mysqlmha3 node to view the automatic handoff log:

[root@mysqlmha3 app1]# cat  manager.log
Wed May 16 23:34:34 2018 – [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Wed May 16 23:34:34 2018 – [info] Reading application default configuration from /etc/masterha/app1.cnf..
Wed May 16 23:34:34 2018 – [info] Reading server configuration from /etc/masterha/app1.cnf..
Wed May 16 23:45:49 2018 – [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Wed May 16 23:45:49 2018 – [info] Reading application default configuration from /etc/masterha/app1.cnf..
Wed May 16 23:45:49 2018 – [info] Reading server configuration from /etc/masterha/app1.cnf..
34:35 2018 – [info]   192.168.142.112(192.168.142.112:3306)
Wed May 16 23:34:35 2018 – [info]   192.168.142.113(192.168.142.113:3306)
Wed May 16 23:34:35 2018 – [info] Alive Slaves:
Wed May 16 23:34:35 2018 – [info]   192.168.142.112(192.168.142.112:3306)  Version=5.7.18-log (oldest major version between slaves) log-bin:enabled
Wed May 16 23:34:35 2018 – [info]     GTID ON
Wed May 16 23:34:35 2018 – [info]     Replicating from 192.168.142.111(192.168.142.111:3306)
Wed May 16 23:34:35 2018 – [info]     Primary candidate for the new Master (candidate_master is set)
Wed May 16 23:34:35 2018 – [info]   192.168.142.113(192.168.142.113:3306)  Version=5.7.18-log (oldest major version between slaves) log-bin:enabled
Wed May 16 23:34:35 2018 – [info]     GTID ON
Wed May 16 23:34:35 2018 – [info]     Replicating from 192.168.142.111(192.168.142.111:3306)
Wed May 16 23:34:35 2018 – [info]     Not candidate for the new Master (no_master is set)
Wed May 16 23:34:35 2018 – [info] Current Alive Master: 192.168.142.111(192.168.142.111:3306)
Wed May 16 23:34:35 2018 – [info] Checking slave configurations..
Wed May 16 23:34:35 2018 – [info]  read_only=1 is not set on slave 192.168.142.112(192.168.142.112:3306).
Wed May 16 23:34:35 2018 – [info]  read_only=1 is not set on slave 192.168.142.113(192.168.142.113:3306).
Wed May 16 23:34:35 2018 – [info] Checking replication filtering settings..
Wed May 16 23:34:35 2018 – [info]  binlog_do_db= , binlog_ignore_db=
Wed May 16 23:34:35 2018 – [info]  Replication filtering check ok.
Wed May 16 23:34:35 2018 – [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.
Wed May 16 23:34:35 2018 – [info] Checking SSH publickey authentication settings on the current master..
Wed May 16 23:34:35 2018 – [info] HealthCheck: SSH to 192.168.142.111 is reachable.
Wed May 16 23:34:35 2018 – [info]
192.168.142.111(192.168.142.111:3306) (current master)  ###Current database information
 +–192.168.142.112(192.168.142.112:3306)
 +–192.168.142.113(192.168.142.113:3306)

Wed May 16 23:34:35 2018 – [info] Checking master_ip_failover_script status:
Wed May 16 23:34:35 2018 – [info]   /usr/bin/master_ip_failover –command=status –ssh_user=mha –orig_master_host=192.168.142.111 –orig_master_ip=192.168.142.111 –orig_master_port=3306

IN SCRIPT TEST====sudo /sbin/ifconfig eno16777736:2 down==sudo /sbin/ifconfig eno16777736:2 192.168.142.114 netmask 255.255.255.0;/sbin/arping -I eno16777736 -c 3 -s 192.168.142.114 192.168.142.2 >/dev/null 2>&1===

Checking the Status of the script.. OK
Wed May 16 23:34:38 2018 – [info]  OK.
Wed May 16 23:34:38 2018 – [warning] shutdown_script is not defined.
Wed May 16 23:34:38 2018 – [info] Set master ping interval 5 seconds.
Wed May 16 23:34:38 2018 – [info] Set secondary check script: /usr/bin/masterha_secondary_check -s 192.168.142.112 -s 192.168.142.113  –user=root –master_host=mysqlmha1 –master_ip=192.168.142.111 –master_port=3306
Wed May 16 23:34:38 2018 – [info] Starting ping health check on 192.168.142.111(192.168.142.111:3306)..
Wed May 16 23:34:38 2018 – [info] Ping(SELECT) succeeded, waiting until MySQL doesn’t respond..
Wed May 16 23:43:53 2018 – [warning] Got timeout on MySQL Ping(SELECT) child process and killed it! at /usr/share/perl5/vendor_perl/MHA/HealthCheck.pm line 431.
Wed May 16 23:43:53 2018 – [info] Executing secondary network check script: /usr/bin/masterha_secondary_check -s 192.168.142.112 -s 192.168.142.113  –user=root –master_host=mysqlmha1 –master_ip=192.168.142.111 –master_port=3306  –user=mha  –master_host=192.168.142.111  –master_ip=192.168.142.111  –master_port=3306 –master_user=mha –master_password=Mha_ahm%0118 –ping_type=SELECT
Wed May 16 23:43:53 2018 – [info] Executing SSH check script: exit 0
Wed May 16 23:43:53 2018 – [info] Ping(SELECT) succeeded, waiting until MySQL doesn’t respond..
Wed May 16 23:43:53 2018 – [info] HealthCheck: SSH to 192.168.142.111 is reachable.
Master is reachable from 192.168.142.112!
Wed May 16 23:43:54 2018 – [warning] Master is reachable from at least one of other monitoring servers. Failover should not happen.
Wed May 16 23:45:33 2018 – [warning] Got error on MySQL select ping: 2006 (MySQL server has gone away)
Wed May 16 23:45:33 2018 – [info] Executing SSH check script: exit 0
Wed May 16 23:45:33 2018 – [info] Executing secondary network check script: /usr/bin/masterha_secondary_check -s 192.168.142.112 -s 192.168.142.113  –user=root –master_host=mysqlmha1 –master_ip=192.168.142.111 –master_port=3306  –user=mha  –master_host=192.168.142.111  –master_ip=192.168.142.111  –master_port=3306 –master_user=mha –master_password=Mha_ahm%0118 –ping_type=SELECT
Wed May 16 23:45:33 2018 – [info] HealthCheck: SSH to 192.168.142.111 is reachable.
Monitoring server 192.168.142.112 is reachable, Master is not reachable from 192.168.142.112. OK.
Monitoring server 192.168.142.113 is reachable, Master is not reachable from 192.168.142.113. OK.
Wed May 16 23:45:34 2018 – [info] Master is not reachable from all other monitoring servers. Failover should start.
Wed May 16 23:45:38 2018 – [warning] Got error on MySQL connect: 2003 (Can’t connect to MySQL server on ‘192.168.142.111’ (111))
Wed May 16 23:45:38 2018 – [warning] Connection failed 2 time(s)..
Wed May 16 23:45:43 2018 – [warning] Got error on MySQL connect: 2003 (Can’t connect to MySQL server on ‘192.168.142.111’ (111))
Wed May 16 23:45:43 2018 – [warning] Connection failed 3 time(s)..
Wed May 16 23:45:48 2018 – [warning] Got error on MySQL connect: 2003 (Can’t connect to MySQL server on ‘192.168.142.111’ (111))
Wed May 16 23:45:48 2018 – [warning] Connection failed 4 time(s)..
Wed May 16 23:45:48 2018 – [warning] Master is not reachable from health checker!
Wed May 16 23:45:48 2018 – [warning] Master 192.168.142.111(192.168.142.111:3306) is not reachable!
Wed May 16 23:45:48 2018 – [warning] SSH is reachable.
Wed May 16 23:45:48 2018 – [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..
Wed May 16 23:45:48 2018 – [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Wed May 16 23:45:48 2018 – [info] Reading application default configuration from /etc/masterha/app1.cnf..
Wed May 16 23:45:48 2018 – [info] Reading server configuration from /etc/masterha/app1.cnf..
Wed May 16 23:45:49 2018 – [info] GTID failover mode = 1
Wed May 16 23:45:49 2018 – [info] Dead Servers:
Wed May 16 23:45:49 2018 – [info]   192.168.142.111(192.168.142.111:3306)
Wed May 16 23:45:49 2018 – [info] Alive Servers:
Wed May 16 23:45:49 2018 – [info]   192.168.142.112(192.168.142.112:3306)
Wed May 16 23:45:49 2018 – [info]   192.168.142.113(192.168.142.113:3306)
Wed May 16 23:45:49 2018 – [info] Alive Slaves:
Wed May 16 23:45:49 2018 – [info]   192.168.142.112(192.168.142.112:3306)  Version=5.7.18-log (oldest major version between slaves) log-bin:enabled
Wed May 16 23:45:49 2018 – [info]     GTID ON
Wed May 16 23:45:49 2018 – [info]     Replicating from 192.168.142.111(192.168.142.111:3306)
Wed May 16 23:45:49 2018 – [info]     Primary candidate for the new Master (candidate_master is set)
Wed May 16 23:45:49 2018 – [info]   192.168.142.113(192.168.142.113:3306)  Version=5.7.18-log (oldest major version between slaves) log-bin:enabled
Wed May 16 23:45:49 2018 – [info]     GTID ON
Wed May 16 23:45:49 2018 – [info]     Replicating from 192.168.142.111(192.168.142.111:3306)
Wed May 16 23:45:49 2018 – [info]     Not candidate for the new Master (no_master is set)
Wed May 16 23:45:49 2018 – [info] Checking slave configurations..
Wed May 16 23:45:49 2018 – [info]  read_only=1 is not set on slave 192.168.142.112(192.168.142.112:3306).
Wed May 16 23:45:49 2018 – [info]  read_only=1 is not set on slave 192.168.142.113(192.168.142.113:3306).
Wed May 16 23:45:49 2018 – [info] Checking replication filtering settings..
Wed May 16 23:45:49 2018 – [info]  Replication filtering check ok.
Wed May 16 23:45:49 2018 – [info] Master is down!
Wed May 16 23:45:49 2018 – [info] Terminating monitoring script.
Wed May 16 23:45:49 2018 – [info] Got exit code 20 (Master dead).
Wed May 16 23:45:49 2018 – [info] MHA::MasterFailover version 0.57.
Wed May 16 23:45:49 2018 – [info] Starting master failover.
Wed May 16 23:45:49 2018 – [info]
Wed May 16 23:45:49 2018 – [info] * Phase 1: Configuration Check Phase..
Wed May 16 23:45:49 2018 – [info]
Wed May 16 23:45:50 2018 – [info] GTID failover mode = 1
Wed May 16 23:45:50 2018 – [info] Dead Servers:
Wed May 16 23:45:50 2018 – [info]   192.168.142.111(192.168.142.111:3306)
Wed May 16 23:45:50 2018 – [info] Checking master reachability via MySQL(double check)…
Wed May 16 23:45:50 2018 – [info]  ok.
Wed May 16 23:45:50 2018 – [info] Alive Servers:
Wed May 16 23:45:50 2018 – [info]   192.168.142.112(192.168.142.112:3306)
Wed May 16 23:45:50 2018 – [info]   192.168.142.113(192.168.142.113:3306)
Wed May 16 23:45:50 2018 – [info] Alive Slaves:
Wed May 16 23:45:50 2018 – [info]   192.168.142.112(192.168.142.112:3306)  Version=5.7.18-log (oldest major version between slaves) log-bin:enabled
Wed May 16 23:45:50 2018 – [info]     GTID ON
Wed May 16 23:45:50 2018 – [info]     Replicating from 192.168.142.111(192.168.142.111:3306)
Wed May 16 23:45:50 2018 – [info]     Primary candidate for the new Master (candidate_master is set)
Wed May 16 23:45:50 2018 – [info]   192.168.142.113(192.168.142.113:3306)  Version=5.7.18-log (oldest major version between slaves) log-bin:enabled
Wed May 16 23:45:50 2018 – [info]     GTID ON
Wed May 16 23:45:50 2018 – [info]     Replicating from 192.168.142.111(192.168.142.111:3306)
Wed May 16 23:45:50 2018 – [info]     Not candidate for the new Master (no_master is set)
Wed May 16 23:45:50 2018 – [info] Starting GTID based failover.
Wed May 16 23:45:50 2018 – [info]
Wed May 16 23:45:50 2018 – [info] ** Phase 1: Configuration Check Phase completed.
Wed May 16 23:45:50 2018 – [info]
Wed May 16 23:45:50 2018 – [info] * Phase 2: Dead Master Shutdown Phase..
Wed May 16 23:45:50 2018 – [info]
Wed May 16 23:45:50 2018 – [info] Forcing shutdown so that applications never connect to the current master..
Wed May 16 23:45:50 2018 – [info] Executing master IP deactivation script:
Wed May 16 23:45:50 2018 – [info]   /usr/bin/master_ip_failover –orig_master_host=192.168.142.111 –orig_master_ip=192.168.142.111 –orig_master_port=3306 –command=stopssh –ssh_user=mha 

IN SCRIPT TEST====sudo /sbin/ifconfig eno16777736:2 down==sudo /sbin/ifconfig eno16777736:2 192.168.142.114 netmask 255.255.255.0;/sbin/arping -I eno16777736 -c 3 -s 192.168.142.114 192.168.142.2 >/dev/null 2>&1===

Disabling the VIP on old master: 192.168.142.111
Wed May 16 23:45:50 2018 – [info]  done.
Wed May 16 23:45:50 2018 – [warning] shutdown_script is not set. Skipping explicit shutting down of the dead master.
Wed May 16 23:45:50 2018 – [info] * Phase 2: Dead Master Shutdown Phase completed.
Wed May 16 23:45:50 2018 – [info]
Wed May 16 23:45:50 2018 – [info] * Phase 3: Master Recovery Phase..
Wed May 16 23:45:50 2018 – [info]
Wed May 16 23:45:50 2018 – [info] * Phase 3.1: Getting Latest Slaves Phase..
Wed May 16 23:45:50 2018 – [info]
Wed May 16 23:45:50 2018 – [info] The latest binary log file/position on all slaves is binlog01.000009:246
Wed May 16 23:45:50 2018 – [info] Retrieved Gtid Set: 8d7abed9-d4cd-11e7-a165-000c29c913a2:6-7
Wed May 16 23:45:50 2018 – [info] Latest slaves (Slaves that received relay log files to the latest):
Wed May 16 23:45:50 2018 – [info]   192.168.142.112(192.168.142.112:3306)  Version=5.7.18-log (oldest major version between slaves) log-bin:enabled
Wed May 16 23:45:50 2018 – [info]     GTID ON
Wed May 16 23:45:50 2018 – [info]     Replicating from 192.168.142.111(192.168.142.111:3306)
Wed May 16 23:45:50 2018 – [info]     Primary candidate for the new Master (candidate_master is set)
Wed May 16 23:45:50 2018 – [info]   192.168.142.113(192.168.142.113:3306)  Version=5.7.18-log (oldest major version between slaves) log-bin:enabled
Wed May 16 23:45:50 2018 – [info]     GTID ON
Wed May 16 23:45:50 2018 – [info]     Replicating from 192.168.142.111(192.168.142.111:3306)
Wed May 16 23:45:50 2018 – [info]     Not candidate for the new Master (no_master is set)
Wed May 16 23:45:50 2018 – [info] The oldest binary log file/position on all slaves is binlog01.000009:246
Wed May 16 23:45:50 2018 – [info] Retrieved Gtid Set: 8d7abed9-d4cd-11e7-a165-000c29c913a2:6-7
Wed May 16 23:45:50 2018 – [info] Oldest slaves:
Wed May 16 23:45:50 2018 – [info]   192.168.142.112(192.168.142.112:3306)  Version=5.7.18-log (oldest major version between slaves) log-bin:enabled
Wed May 16 23:45:50 2018 – [info]     GTID ON
Wed May 16 23:45:50 2018 – [info]     Replicating from 192.168.142.111(192.168.142.111:3306)
Wed May 16 23:45:50 2018 – [info]     Primary candidate for the new Master (candidate_master is set)
Wed May 16 23:45:50 2018 – [info]   192.168.142.113(192.168.142.113:3306)  Version=5.7.18-log (oldest major version between slaves) log-bin:enabled
Wed May 16 23:45:50 2018 – [info]     GTID ON
Wed May 16 23:45:50 2018 – [info]     Replicating from 192.168.142.111(192.168.142.111:3306)
Wed May 16 23:45:50 2018 – [info]     Not candidate for the new Master (no_master is set)
Wed May 16 23:45:50 2018 – [info]
Wed May 16 23:45:50 2018 – [info] * Phase 3.3: Determining New Master Phase..
Wed May 16 23:45:50 2018 – [info]
Wed May 16 23:45:50 2018 – [info] Searching new master from slaves..
Wed May 16 23:45:50 2018 – [info]  Candidate masters from the configuration file:
Wed May 16 23:45:50 2018 – [info]   192.168.142.112(192.168.142.112:3306)  Version=5.7.18-log (oldest major version between slaves) log-bin:enabled
Wed May 16 23:45:50 2018 – [info]     GTID ON
Wed May 16 23:45:50 2018 – [info]     Replicating from 192.168.142.111(192.168.142.111:3306)
Wed May 16 23:45:50 2018 – [info]     Primary candidate for the new Master (candidate_master is set)
Wed May 16 23:45:50 2018 – [info]  Non-candidate masters:
Wed May 16 23:45:50 2018 – [info]   192.168.142.113(192.168.142.113:3306)  Version=5.7.18-log (oldest major version between slaves) log-bin:enabled
Wed May 16 23:45:50 2018 – [info]     GTID ON
Wed May 16 23:45:50 2018 – [info]     Replicating from 192.168.142.111(192.168.142.111:3306)
Wed May 16 23:45:50 2018 – [info]     Not candidate for the new Master (no_master is set)
Wed May 16 23:45:50 2018 – [info]  Searching from candidate_master slaves which have received the latest relay log events..
Wed May 16 23:45:50 2018 – [info] New master is 192.168.142.112(192.168.142.112:3306)
Wed May 16 23:45:50 2018 – [info] Starting master failover..
Wed May 16 23:45:50 2018 – [info]
From:
192.168.142.111(192.168.142.111:3306) (current master)
 +–192.168.142.112(192.168.142.112:3306)
 +–192.168.142.113(192.168.142.113:3306)

To:
192.168.142.112(192.168.142.112:3306) (new master)
 +–192.168.142.113(192.168.142.113:3306)
Wed May 16 23:45:50 2018 – [info]
Wed May 16 23:45:50 2018 – [info] * Phase 3.3: New Master Recovery Phase..
Wed May 16 23:45:50 2018 – [info]
Wed May 16 23:45:50 2018 – [info]  Waiting all logs to be applied..
Wed May 16 23:45:50 2018 – [info]   done.
Wed May 16 23:45:50 2018 – [info] Getting new master’s binlog name and position..
Wed May 16 23:45:50 2018 – [info]  binlog01.000008:286
Wed May 16 23:45:50 2018 – [info]  All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST=’192.168.142.112′, MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER=’repl’, MASTER_PASSWORD=’xxx’;
Wed May 16 23:45:50 2018 – [info] Master Recovery succeeded. File:Pos:Exec_Gtid_Set: binlog01.000008, 286, 42f239e7-5908-11e8-8214-000c2926d694:1,
8d7abed9-d4cd-11e7-a165-000c29c913a2:1-7,
aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-15:1000002-1000075
Wed May 16 23:45:50 2018 – [info] Executing master IP activate script:
Wed May 16 23:45:50 2018 – [info]   /usr/bin/master_ip_failover –command=start –ssh_user=mha –orig_master_host=192.168.142.111 –orig_master_ip=192.168.142.111 –orig_master_port=3306 –new_master_host=192.168.142.112 –new_master_ip=192.168.142.112 –new_master_port=3306 –new_master_user=’mha’   –new_master_password=xxx
Unknown option: new_master_user
Unknown option: new_master_password

IN SCRIPT TEST====sudo /sbin/ifconfig eno16777736:2 down==sudo /sbin/ifconfig eno16777736:2 192.168.142.114 netmask 255.255.255.0;/sbin/arping -I eno16777736 -c 3 -s 192.168.142.114 192.168.142.2 >/dev/null 2>&1===

Enabling the VIP – 192.168.142.114 on the new master – 192.168.142.112
Wed May 16 23:45:54 2018 – [info]  OK.
Wed May 16 23:45:54 2018 – [info] ** Finished master recovery successfully.
Wed May 16 23:45:54 2018 – [info] * Phase 3: Master Recovery Phase completed.
Wed May 16 23:45:54 2018 – [info]
Wed May 16 23:45:54 2018 – [info] * Phase 4: Slaves Recovery Phase..
Wed May 16 23:45:54 2018 – [info]
Wed May 16 23:45:54 2018 – [info]
Wed May 16 23:45:54 2018 – [info] * Phase 4.1: Starting Slaves in parallel..
Wed May 16 23:45:54 2018 – [info]
Wed May 16 23:45:54 2018 – [info] — Slave recovery on host 192.168.142.113(192.168.142.113:3306) started, pid: 3382. Check tmp log /var/log/masterha/app1/192.168.142.113_3306_20180516234549.log if it takes time..
Wed May 16 23:45:55 2018 – [info]
Wed May 16 23:45:55 2018 – [info] Log messages from 192.168.142.113 …
Wed May 16 23:45:55 2018 – [info]
Wed May 16 23:45:54 2018 – [info]  Resetting slave 192.168.142.113(192.168.142.113:3306) and starting replication from the new master 192.168.142.112(192.168.142.112:3306)..
Wed May 16 23:45:54 2018 – [info]  Executed CHANGE MASTER.
Wed May 16 23:45:54 2018 – [info]  Slave started.
Wed May 16 23:45:54 2018 – [info]  gtid_wait(42f239e7-5908-11e8-8214-000c2926d694:1,
8d7abed9-d4cd-11e7-a165-000c29c913a2:1-7,
aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-15:1000002-1000075) completed on 192.168.142.113(192.168.142.113:3306). Executed 2 events.
Wed May 16 23:45:55 2018 – [info] End of log messages from 192.168.142.113.
Wed May 16 23:45:55 2018 – [info] — Slave on host 192.168.142.113(192.168.142.113:3306) started.
Wed May 16 23:45:55 2018 – [info] All new slave servers recovered successfully.
Wed May 16 23:45:55 2018 – [info]
Wed May 16 23:45:55 2018 – [info] * Phase 5: New master cleanup phase..
Wed May 16 23:45:55 2018 – [info]
Wed May 16 23:45:55 2018 – [info] Resetting slave info on the new master..
Wed May 16 23:45:55 2018 – [info]  192.168.142.112: Resetting slave info succeeded.
Wed May 16 23:45:55 2018 – [info] Master failover to 192.168.142.112(192.168.142.112:3306) completed successfully.
Wed May 16 23:45:55 2018 – [info]

—– Failover Report —–

app1: MySQL Master failover 192.168.142.111(192.168.142.111:3306) to 192.168.142.112(192.168.142.112:3306) succeeded

Master 192.168.142.111(192.168.142.111:3306) is down!

Check MHA Manager logs at mysqlmha3:/var/log/masterha/app1/manager.log for details.

Started automated(non-interactive) failover.
Invalidated master IP address on 192.168.142.111(192.168.142.111:3306)
Selected 192.168.142.112(192.168.142.112:3306) as a new master.
192.168.142.112(192.168.142.112:3306): OK: Applying all logs succeeded.
192.168.142.112(192.168.142.112:3306): OK: Activated master IP address.
192.168.142.113(192.168.142.113:3306): OK: Slave started, replicating from 192.168.142.112(192.168.142.112:3306)
192.168.142.112(192.168.142.112:3306): Resetting slave info succeeded.
Master failover to 192.168.142.112(192.168.142.112:3306) completed successfully.
[root@mysqlmha3 app1]#

 

Check whether VIP is drifting on the node 2.

[root@mysqlmha2 ~]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: eno16777736: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 00:0c:29:26:d6:94 brd ff:ff:ff:ff:ff:ff
    inet 192.168.142.112/24 brd 192.168.142.255 scope global eno16777736
       valid_lft forever preferred_lft forever
    inet 192.168.142.114/24 brd 192.168.142.255 scope global secondary eno16777736:2
       valid_lft forever preferred_lft forever
    inet6 fe80::20c:29ff:fe26:d694/64 scope link
       valid_lft forever preferred_lft forever
[root@mysqlmha2 ~]#

 

Check the mysqlmha3 node to view the current cluster status:

[mha@mysqlmha3 shell]$ masterha_check_repl –conf=/etc/masterha/app1.cnf
Wed May 16 23:55:37 2018 – [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Wed May 16 23:55:37 2018 – [info] Reading application default configuration from /etc/masterha/app1.cnf..
Wed May 16 23:55:37 2018 – [info] Reading server configuration from /etc/masterha/app1.cnf..
Wed May 16 23:55:37 2018 – [info] MHA::MasterMonitor version 0.57.
Wed May 16 23:55:38 2018 – [info] GTID failover mode = 1
Wed May 16 23:55:38 2018 – [info] Dead Servers:
Wed May 16 23:55:38 2018 – [info]   192.168.142.111(192.168.142.111:3306)
Wed May 16 23:55:38 2018 – [info] Alive Servers:
Wed May 16 23:55:38 2018 – [info]   192.168.142.112(192.168.142.112:3306)
Wed May 16 23:55:38 2018 – [info]   192.168.142.113(192.168.142.113:3306)
Wed May 16 23:55:38 2018 – [info] Alive Slaves:
Wed May 16 23:55:38 2018 – [info]   192.168.142.113(192.168.142.113:3306)  Version=5.7.18-log (oldest major version between slaves) log-bin:enabled
Wed May 16 23:55:38 2018 – [info]     GTID ON
Wed May 16 23:55:38 2018 – [info]     Replicating from 192.168.142.112(192.168.142.112:3306)
Wed May 16 23:55:38 2018 – [info]     Not candidate for the new Master (no_master is set)
Wed May 16 23:55:38 2018 – [info] Current Alive Master: 192.168.142.112(192.168.142.112:3306)
Wed May 16 23:55:38 2018 – [info] Checking slave configurations..
Wed May 16 23:55:38 2018 – [info]  read_only=1 is not set on slave 192.168.142.113(192.168.142.113:3306).
Wed May 16 23:55:38 2018 – [info] Checking replication filtering settings..
Wed May 16 23:55:38 2018 – [info]  binlog_do_db= , binlog_ignore_db=
Wed May 16 23:55:38 2018 – [info]  Replication filtering check ok.
Wed May 16 23:55:38 2018 – [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln364] None of slaves can be master. Check failover configuration file or log-bin settings in my.cnf
Wed May 16 23:55:38 2018 – [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln427] Error happened on checking configurations.  at /bin/masterha_check_repl line 48.
Wed May 16 23:55:38 2018 – [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln525] Error happened on monitoring servers.
Wed May 16 23:55:38 2018 – [info] Got exit code 1 (Not master dead).

MySQL Replication Health is NOT OK!  ###Output is normal here, because the node is not repaired.
[mha@mysqlmha3 shell]$

 

Repair the node mysqlmha1 node database as a repository:

View the change master script when you automatically switch scripts. You need to add the password when the node is executed.

[root@mysqlmha3 app1]# cd /var/log/masterha/app1
[root@mysqlmha3 app1]# grep -i change manager.log
Wed May 16 23:45:50 2018 – [info]  All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST=’192.168.142.112′, MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER=’repl’, MASTER_PASSWORD=’xxx’;
Wed May 16 23:45:54 2018 – [info]  Executed CHANGE MASTER.
[root@mysqlmha3 app1]#

 

Start the mysqlmha1 node database, confirm that the database starts without error, and then operate later.
[root@mysqlmha1 ~]# mysqld –defaults-file=/etc/mymha.cnf &
[1] 3636
[root@mysqlmha1 ~]#

 

Make the mysqlmha1 node as a repository
[root@mysqlmha1 ~]# mysql -uroot -p -P3306 –protocol=tcp
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.18-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> CHANGE MASTER TO MASTER_HOST=’192.168.142.112′, MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER=’repl’, MASTER_PASSWORD=’repl’;
Query OK, 0 rows affected, 2 warnings (0.36 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.142.112
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog01.000008
          Read_Master_Log_Pos: 286
               Relay_Log_File: relaylog01.000004
                Relay_Log_Pos: 393
        Relay_Master_Log_File: binlog01.000008
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 286
              Relay_Log_Space: 669
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 112
                  Master_UUID: 42f239e7-5908-11e8-8214-000c2926d694
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set: 42f239e7-5908-11e8-8214-000c2926d694:1
            Executed_Gtid_Set: 42f239e7-5908-11e8-8214-000c2926d694:1,
8d7abed9-d4cd-11e7-a165-000c29c913a2:1-7,
aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-15:1000002-1000075
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

mysql> exit
Bye
[root@mysqlmha1 ~]#

 

Check the current state of the database at the MHA management node:

[mha@mysqlmha3 shell]$ masterha_check_repl –conf=/etc/masterha/app1.cnf
Thu May 17 00:02:39 2018 – [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Thu May 17 00:02:39 2018 – [info] Reading application default configuration from /etc/masterha/app1.cnf..
Thu May 17 00:02:39 2018 – [info] Reading server configuration from /etc/masterha/app1.cnf..
Thu May 17 00:02:39 2018 – [info] MHA::MasterMonitor version 0.57.
Thu May 17 00:02:40 2018 – [info] GTID failover mode = 1
Thu May 17 00:02:40 2018 – [info] Dead Servers:
Thu May 17 00:02:40 2018 – [info] Alive Servers:
Thu May 17 00:02:40 2018 – [info]   192.168.142.111(192.168.142.111:3306)  ###Node recovery has been identified
Thu May 17 00:02:40 2018 – [info]   192.168.142.112(192.168.142.112:3306)
Thu May 17 00:02:40 2018 – [info]   192.168.142.113(192.168.142.113:3306)
Thu May 17 00:02:40 2018 – [info] Alive Slaves:
Thu May 17 00:02:40 2018 – [info]   192.168.142.111(192.168.142.111:3306)  Version=5.7.18-log (oldest major version between slaves) log-bin:enabled
Thu May 17 00:02:40 2018 – [info]     GTID ON
Thu May 17 00:02:40 2018 – [info]     Replicating from 192.168.142.112(192.168.142.112:3306)
Thu May 17 00:02:40 2018 – [info]   192.168.142.113(192.168.142.113:3306)  Version=5.7.18-log (oldest major version between slaves) log-bin:enabled
Thu May 17 00:02:40 2018 – [info]     GTID ON
Thu May 17 00:02:40 2018 – [info]     Replicating from 192.168.142.112(192.168.142.112:3306)
Thu May 17 00:02:40 2018 – [info]     Not candidate for the new Master (no_master is set)
Thu May 17 00:02:40 2018 – [info] Current Alive Master: 192.168.142.112(192.168.142.112:3306)  ###The current main library has switched to node 2
Thu May 17 00:02:40 2018 – [info] Checking slave configurations..
Thu May 17 00:02:40 2018 – [info]  read_only=1 is not set on slave 192.168.142.111(192.168.142.111:3306).  ###Node 1 has become a repository
Thu May 17 00:02:40 2018 – [info]  read_only=1 is not set on slave 192.168.142.113(192.168.142.113:3306).  ###Node 3 does not change its edge, it is still a repository
Thu May 17 00:02:40 2018 – [info] Checking replication filtering settings..
Thu May 17 00:02:40 2018 – [info]  binlog_do_db= , binlog_ignore_db=
Thu May 17 00:02:40 2018 – [info]  Replication filtering check ok.
Thu May 17 00:02:40 2018 – [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.
Thu May 17 00:02:40 2018 – [info] Checking SSH publickey authentication settings on the current master..
Thu May 17 00:02:40 2018 – [info] HealthCheck: SSH to 192.168.142.112 is reachable.
Thu May 17 00:02:40 2018 – [info]
192.168.142.112(192.168.142.112:3306) (current master)
 +–192.168.142.111(192.168.142.111:3306)
 +–192.168.142.113(192.168.142.113:3306)

Thu May 17 00:02:40 2018 – [info] Checking replication health on 192.168.142.111..
Thu May 17 00:02:40 2018 – [info]  ok.
Thu May 17 00:02:40 2018 – [info] Checking replication health on 192.168.142.113..
Thu May 17 00:02:40 2018 – [info]  ok.
Thu May 17 00:02:40 2018 – [info] Checking master_ip_failover_script status:
Thu May 17 00:02:40 2018 – [info]   /usr/bin/master_ip_failover –command=status –ssh_user=mha –orig_master_host=192.168.142.112 –orig_master_ip=192.168.142.112 –orig_master_port=3306

IN SCRIPT TEST====sudo /sbin/ifconfig eno16777736:2 down==sudo /sbin/ifconfig eno16777736:2 192.168.142.114 netmask 255.255.255.0;/sbin/arping -I eno16777736 -c 3 -s 192.168.142.114 192.168.142.2 >/dev/null 2>&1===

Checking the Status of the script.. OK
Thu May 17 00:02:44 2018 – [info]  OK.
Thu May 17 00:02:44 2018 – [warning] shutdown_script is not defined.
Thu May 17 00:02:44 2018 – [info] Got exit code 0 (Not master dead).

MySQL Replication Health is OK.
[mha@mysqlmha3 shell]$

 

【Test steps: manual switching]

Current database status:

system

IP

host name

Remarks

Edition

xxsystem

192.168.142.111

mysqlmha1

Reserve

5.7.18 -log MySQL Community Server (GPL)

192.168.142.112

mysqlmha2

Main library

192.168.142.113

mysqlmha3

Repository & MHA MGM

192.168.142.112

mysqlmha1

VIP

Database status after switching:

system

IP

host name

Remarks

Edition

xxsystem

192.168.142.111

mysqlmha1

Main library

5.7.18 -log MySQL Community Server (GPL)

192.168.142.112

mysqlmha2

Reserve (pre Main Library)

192.168.142.113

mysqlmha3

Repository & MHA MGM

192.168.142.111

mysqlmha1

VIP

 

Confirm the current database status, and confirm the current database status before each check or operation.

Check between nodessshIf the status of mutual trust is normal, if there is a false report, confirm the user, mutual trust and password.

$masterha_check_ssh –conf=/etc/masterha/app1.cnf

inspectmysqlWhether the master slave replication is normal, if there is a report error, confirm whether the script file permission is accurate, and whether the configuration file information is accurate.

$masterha_check_repl –conf=/etc/masterha/app1.cnf

 

To view manual handover script in mysqlmha3 management node, the information must be accurate.

[mha@mysqlmha3 shell]$ cat masterha_switch.sh
#!/bin/sh
conf=”/etc/masterha/app1.cnf”
dead_master=”192.168.142.112″  ###The current IP node of the master
new_master=”192.168.142.111″  ###IP nodes that need to switch the target
port=”3306″
user=$(whoami)

if [ $user != “mha” ]
then
    echo “the user is not mha,please check !!!”
    exit
fi

function manual_failover(){
##Manual switching needs to stop the manager process
/usr/bin/masterha_stop –conf=/etc/masterha/app1.cnf
/usr/bin/masterha_master_switch –conf=${conf} –master_state=dead  –dead_master_host=${dead_master} –dead_master_port=${port} –new_master_host=${new_master} –new_master_port=${port} –ignore_last_failover
}
function manual_online_change(){
##Manual switching needs to stop the manager process
/usr/bin/masterha_stop –conf=/etc/masterha/app1.cnf
/usr/bin/masterha_master_switch –conf=${conf} –master_state=alive –new_master_host=${new_master} –new_master_port=${port} –orig_master_is_new_slave –running_updates_limit=10000 –interactive=0 < /dev/null > /var/log/masterha/app1/manual_online_change.log 2>&1 &
}
case “$1” in
manual_failover)
manual_failover
;;
manual_online_change)
manual_online_change
;;
*)
echo “Usage: $0 manual_failover|manual_online_change”
esac
[mha@mysqlmha3 shell]$

 

Manual execution of handoff scripts, no error can be reported

[mha@mysqlmha3 shell]$  sh masterha_switch.sh manual_online_change
MHA Manager is not running on app1(2:NOT_RUNNING).  ###This output is normal because there is no automatic switch script process started, and if there is a startup, the automatic handover process will be stopped.

 

View manual handoff log

[mha@mysqlmha3 app1]$ cat manual_online_change.log
Thu May 17 00:22:39 2018 – [info] MHA::MasterRotate version 0.57.
Thu May 17 00:22:39 2018 – [info] Starting online master switch..
Thu May 17 00:22:39 2018 – [info]
Thu May 17 00:22:39 2018 – [info] * Phase 1: Configuration Check Phase..
Thu May 17 00:22:39 2018 – [info]
Thu May 17 00:22:39 2018 – [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Thu May 17 00:22:39 2018 – [info] Reading application default configuration from /etc/masterha/app1.cnf..
Thu May 17 00:22:39 2018 – [info] Reading server configuration from /etc/masterha/app1.cnf..
Thu May 17 00:22:40 2018 – [info] GTID failover mode = 1
Thu May 17 00:22:40 2018 – [info] Current Alive Master: 192.168.142.112(192.168.142.112:3306)
Thu May 17 00:22:40 2018 – [info] Alive Slaves:
Thu May 17 00:22:40 2018 – [info]   192.168.142.111(192.168.142.111:3306)  Version=5.7.18-log (oldest major version between slaves) log-bin:enabled
Thu May 17 00:22:40 2018 – [info]     GTID ON
Thu May 17 00:22:40 2018 – [info]     Replicating from 192.168.142.112(192.168.142.112:3306)
Thu May 17 00:22:40 2018 – [info]   192.168.142.113(192.168.142.113:3306)  Version=5.7.18-log (oldest major version between slaves) log-bin:enabled
Thu May 17 00:22:40 2018 – [info]     GTID ON
Thu May 17 00:22:40 2018 – [info]     Replicating from 192.168.142.112(192.168.142.112:3306)
Thu May 17 00:22:40 2018 – [info]     Not candidate for the new Master (no_master is set)
Thu May 17 00:22:40 2018 – [info] Executing FLUSH NO_WRITE_TO_BINLOG TABLES. This may take long time..
Thu May 17 00:22:40 2018 – [info]  ok.
Thu May 17 00:22:40 2018 – [info] Checking MHA is not monitoring or doing failover..
Thu May 17 00:22:40 2018 – [info] Checking replication health on 192.168.142.111..
Thu May 17 00:22:40 2018 – [info]  ok.
Thu May 17 00:22:40 2018 – [info] Checking replication health on 192.168.142.113..
Thu May 17 00:22:40 2018 – [info]  ok.
Thu May 17 00:22:40 2018 – [info] 192.168.142.111 can be new master.
Thu May 17 00:22:40 2018 – [info]
From:
192.168.142.112(192.168.142.112:3306) (current master)
 +–192.168.142.111(192.168.142.111:3306)
 +–192.168.142.113(192.168.142.113:3306)

To:
192.168.142.111(192.168.142.111:3306) (new master)
 +–192.168.142.113(192.168.142.113:3306)
 +–192.168.142.112(192.168.142.112:3306)    ###Consistent with demand
Thu May 17 00:22:40 2018 – [info] Checking whether 192.168.142.111(192.168.142.111:3306) is ok for the new master..
Thu May 17 00:22:40 2018 – [info]  ok.
Thu May 17 00:22:40 2018 – [info] 192.168.142.112(192.168.142.112:3306): SHOW SLAVE STATUS returned empty result. To check replication filtering rules, temporarily executing CHANGE MASTER to a dummy host.
Thu May 17 00:22:40 2018 – [info] 192.168.142.112(192.168.142.112:3306): Resetting slave pointing to the dummy host.
Thu May 17 00:22:40 2018 – [info] ** Phase 1: Configuration Check Phase completed.
Thu May 17 00:22:40 2018 – [info]
Thu May 17 00:22:40 2018 – [info] * Phase 2: Rejecting updates Phase..
Thu May 17 00:22:40 2018 – [info]
Thu May 17 00:22:40 2018 – [info] Executing master ip online change script to disable write on the current master:
Thu May 17 00:22:40 2018 – [info]   /usr/bin/master_ip_online_change –command=stop –orig_master_host=192.168.142.112 –orig_master_ip=192.168.142.112 –orig_master_port=3306 –orig_master_user=’mha’ –new_master_host=192.168.142.111 –new_master_ip=192.168.142.111 –new_master_port=3306 –new_master_user=’mha’ –orig_master_ssh_user=mha –new_master_ssh_user=mha   –orig_master_is_new_slave –orig_master_password=xxx –new_master_password=xxx
Thu May 17 00:22:40 2018 694778 Set read_only on the new master.. ok.
Thu May 17 00:22:40 2018 699533 Waiting all running 2 threads are disconnected.. (max 1500 milliseconds)
{‘Time’ => ‘939’,’db’ => undef,’Id’ => ’61’,’User’ => ‘repl’,’State’ => ‘Master has sent all binlog to slave; waiting for more updates’,’Command’ => ‘Binlog Dump GTID’,’Info’ => undef,’Host’ => ‘mysqlmha3:34819’}
{‘Time’ => ‘939’,’db’ => undef,’Id’ => ’62’,’User’ => ‘repl’,’State’ => ‘Master has sent all binlog to slave; waiting for more updates’,’Command’ => ‘Binlog Dump GTID’,’Info’ => undef,’Host’ => ‘mysqlmha1:47539’}
Thu May 17 00:22:41 2018 202982 Waiting all running 2 threads are disconnected.. (max 1000 milliseconds)
{‘Time’ => ‘940’,’db’ => undef,’Id’ => ’61’,’User’ => ‘repl’,’State’ => ‘Master has sent all binlog to slave; waiting for more updates’,’Command’ => ‘Binlog Dump GTID’,’Info’ => undef,’Host’ => ‘mysqlmha3:34819’}
{‘Time’ => ‘940’,’db’ => undef,’Id’ => ’62’,’User’ => ‘repl’,’State’ => ‘Master has sent all binlog to slave; waiting for more updates’,’Command’ => ‘Binlog Dump GTID’,’Info’ => undef,’Host’ => ‘mysqlmha1:47539’}
Thu May 17 00:22:41 2018 706427 Waiting all running 2 threads are disconnected.. (max 500 milliseconds)
{‘Time’ => ‘940’,’db’ => undef,’Id’ => ’61’,’User’ => ‘repl’,’State’ => ‘Master has sent all binlog to slave; waiting for more updates’,’Command’ => ‘Binlog Dump GTID’,’Info’ => undef,’Host’ => ‘mysqlmha3:34819’}
{‘Time’ => ‘940’,’db’ => undef,’Id’ => ’62’,’User’ => ‘repl’,’State’ => ‘Master has sent all binlog to slave; waiting for more updates’,’Command’ => ‘Binlog Dump GTID’,’Info’ => undef,’Host’ => ‘mysqlmha1:47539’}
Thu May 17 00:22:42 2018 210670 Set read_only=1 on the orig master.. ok.
Thu May 17 00:22:42 2018 212115 Waiting all running 2 queries are disconnected.. (max 500 milliseconds)
{‘Time’ => ‘941’,’db’ => undef,’Id’ => ’61’,’User’ => ‘repl’,’State’ => ‘Master has sent all binlog to slave; waiting for more updates’,’Command’ => ‘Binlog Dump GTID’,’Info’ => undef,’Host’ => ‘mysqlmha3:34819’}
{‘Time’ => ‘941’,’db’ => undef,’Id’ => ’62’,’User’ => ‘repl’,’State’ => ‘Master has sent all binlog to slave; waiting for more updates’,’Command’ => ‘Binlog Dump GTID’,’Info’ => undef,’Host’ => ‘mysqlmha1:47539’}
Thu May 17 00:22:42 2018 714337 Killing all application threads..
Thu May 17 00:22:42 2018 716387 done.
Disabling the VIP on old master: 192.168.142.112
Thu May 17 00:22:42 2018 – [info]  ok.
Thu May 17 00:22:42 2018 – [info] Locking all tables on the orig master to reject updates from everybody (including root):
Thu May 17 00:22:42 2018 – [info] Executing FLUSH TABLES WITH READ LOCK..
Thu May 17 00:22:42 2018 – [info]  ok.
Thu May 17 00:22:42 2018 – [info] Orig master binlog:pos is binlog01.000008:286.
Thu May 17 00:22:42 2018 – [info]  Waiting to execute all relay logs on 192.168.142.111(192.168.142.111:3306)..
Thu May 17 00:22:42 2018 – [info]  master_pos_wait(binlog01.000008:286) completed on 192.168.142.111(192.168.142.111:3306). Executed 0 events.
Thu May 17 00:22:42 2018 – [info]   done.
Thu May 17 00:22:42 2018 – [info] Getting new master’s binlog name and position..
Thu May 17 00:22:42 2018 – [info]  binlog01.000011:529
Thu May 17 00:22:42 2018 – [info]  All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST=’192.168.142.111′, MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER=’repl’, MASTER_PASSWORD=’xxx’;
Thu May 17 00:22:42 2018 – [info] Executing master ip online change script to allow write on the new master:
Thu May 17 00:22:42 2018 – [info]   /usr/bin/master_ip_online_change –command=start –orig_master_host=192.168.142.112 –orig_master_ip=192.168.142.112 –orig_master_port=3306 –orig_master_user=’mha’ –new_master_host=192.168.142.111 –new_master_ip=192.168.142.111 –new_master_port=3306 –new_master_user=’mha’ –orig_master_ssh_user=mha –new_master_ssh_user=mha   –orig_master_is_new_slave –orig_master_password=xxx –new_master_password=xxx
Thu May 17 00:22:42 2018 907496 Set read_only=0 on the new master.
Enabling the VIP – 192.168.142.114 on the new master – 192.168.142.111
Thu May 17 00:22:46 2018 – [info]  ok.
Thu May 17 00:22:46 2018 – [info]
Thu May 17 00:22:46 2018 – [info] * Switching slaves in parallel..
Thu May 17 00:22:46 2018 – [info]
Thu May 17 00:22:46 2018 – [info] — Slave switch on host 192.168.142.113(192.168.142.113:3306) started, pid: 3645
Thu May 17 00:22:46 2018 – [info]
Thu May 17 00:22:47 2018 – [info] Log messages from 192.168.142.113 …
Thu May 17 00:22:47 2018 – [info]
Thu May 17 00:22:46 2018 – [info]  Waiting to execute all relay logs on 192.168.142.113(192.168.142.113:3306)..
Thu May 17 00:22:46 2018 – [info]  master_pos_wait(binlog01.000008:286) completed on 192.168.142.113(192.168.142.113:3306). Executed 0 events.
Thu May 17 00:22:46 2018 – [info]   done.
Thu May 17 00:22:46 2018 – [info]  Resetting slave 192.168.142.113(192.168.142.113:3306) and starting replication from the new master 192.168.142.111(192.168.142.111:3306)..
Thu May 17 00:22:46 2018 – [info]  Executed CHANGE MASTER.
Thu May 17 00:22:46 2018 – [info]  Slave started.
Thu May 17 00:22:47 2018 – [info] End of log messages from 192.168.142.113 …
Thu May 17 00:22:47 2018 – [info]
Thu May 17 00:22:47 2018 – [info] — Slave switch on host 192.168.142.113(192.168.142.113:3306) succeeded.
Thu May 17 00:22:47 2018 – [info] Unlocking all tables on the orig master:
Thu May 17 00:22:47 2018 – [info] Executing UNLOCK TABLES..
Thu May 17 00:22:47 2018 – [info]  ok.
Thu May 17 00:22:47 2018 – [info] Starting orig master as a new slave..
Thu May 17 00:22:47 2018 – [info]  Resetting slave 192.168.142.112(192.168.142.112:3306) and starting replication from the new master 192.168.142.111(192.168.142.111:3306)..
Thu May 17 00:22:47 2018 – [info]  Executed CHANGE MASTER.
Thu May 17 00:22:47 2018 – [info]  Slave started.
Thu May 17 00:22:47 2018 – [info] All new slave servers switched successfully.
Thu May 17 00:22:47 2018 – [info]
Thu May 17 00:22:47 2018 – [info] * Phase 5: New master cleanup phase..
Thu May 17 00:22:47 2018 – [info]
Thu May 17 00:22:47 2018 – [info]  192.168.142.111: Resetting slave info succeeded.
Thu May 17 00:22:47 2018 – [info] Switching master to 192.168.142.111(192.168.142.111:3306) completed successfully.
[mha@mysqlmha3 app1]$

 

Using scripts to check current database node information

[mha@mysqlmha3 shell]$  masterha_check_repl –conf=/etc/masterha/app1.cnf
Thu May 17 00:24:01 2018 – [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Thu May 17 00:24:01 2018 – [info] Reading application default configuration from /etc/masterha/app1.cnf..
Thu May 17 00:24:01 2018 – [info] Reading server configuration from /etc/masterha/app1.cnf..
Thu May 17 00:24:01 2018 – [info] MHA::MasterMonitor version 0.57.
Thu May 17 00:24:02 2018 – [info] GTID failover mode = 1
Thu May 17 00:24:02 2018 – [info] Dead Servers:
Thu May 17 00:24:02 2018 – [info] Alive Servers:
Thu May 17 00:24:02 2018 – [info]   192.168.142.111(192.168.142.111:3306)
Thu May 17 00:24:02 2018 – [info]   192.168.142.112(192.168.142.112:3306)
Thu May 17 00:24:02 2018 – [info]   192.168.142.113(192.168.142.113:3306)
Thu May 17 00:24:02 2018 – [info] Alive Slaves:
Thu May 17 00:24:02 2018 – [info]   192.168.142.112(192.168.142.112:3306)  Version=5.7.18-log (oldest major version between slaves) log-bin:enabled
Thu May 17 00:24:02 2018 – [info]     GTID ON
Thu May 17 00:24:02 2018 – [info]     Replicating from 192.168.142.111(192.168.142.111:3306)
Thu May 17 00:24:02 2018 – [info]     Primary candidate for the new Master (candidate_master is set)
Thu May 17 00:24:02 2018 – [info]   192.168.142.113(192.168.142.113:3306)  Version=5.7.18-log (oldest major version between slaves) log-bin:enabled
Thu May 17 00:24:02 2018 – [info]     GTID ON
Thu May 17 00:24:02 2018 – [info]     Replicating from 192.168.142.111(192.168.142.111:3306)
Thu May 17 00:24:02 2018 – [info]     Not candidate for the new Master (no_master is set)
Thu May 17 00:24:02 2018 – [info] Current Alive Master: 192.168.142.111(192.168.142.111:3306)
Thu May 17 00:24:02 2018 – [info] Checking slave configurations..
Thu May 17 00:24:02 2018 – [info]  read_only=1 is not set on slave 192.168.142.113(192.168.142.113:3306).
Thu May 17 00:24:02 2018 – [info] Checking replication filtering settings..
Thu May 17 00:24:02 2018 – [info]  binlog_do_db= , binlog_ignore_db=
Thu May 17 00:24:02 2018 – [info]  Replication filtering check ok.
Thu May 17 00:24:02 2018 – [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.
Thu May 17 00:24:02 2018 – [info] Checking SSH publickey authentication settings on the current master..
Thu May 17 00:24:03 2018 – [info] HealthCheck: SSH to 192.168.142.111 is reachable.
Thu May 17 00:24:03 2018 – [info]
192.168.142.111(192.168.142.111:3306) (current master)  ###Has identified the master slave relationship information after the switch
 +–192.168.142.112(192.168.142.112:3306)
 +–192.168.142.113(192.168.142.113:3306)

Thu May 17 00:24:03 2018 – [info] Checking replication health on 192.168.142.112..
Thu May 17 00:24:03 2018 – [info]  ok.
Thu May 17 00:24:03 2018 – [info] Checking replication health on 192.168.142.113..
Thu May 17 00:24:03 2018 – [info]  ok.
Thu May 17 00:24:03 2018 – [info] Checking master_ip_failover_script status:
Thu May 17 00:24:03 2018 – [info]   /usr/bin/master_ip_failover –command=status –ssh_user=mha –orig_master_host=192.168.142.111 –orig_master_ip=192.168.142.111 –orig_master_port=3306

IN SCRIPT TEST====sudo /sbin/ifconfig eno16777736:2 down==sudo /sbin/ifconfig eno16777736:2 192.168.142.114 netmask 255.255.255.0;/sbin/arping -I eno16777736 -c 3 -s 192.168.142.114 192.168.142.2 >/dev/null 2>&1===

Checking the Status of the script.. OK
Thu May 17 00:24:06 2018 – [info]  OK.
Thu May 17 00:24:06 2018 – [warning] shutdown_script is not defined.
Thu May 17 00:24:06 2018 – [info] Got exit code 0 (Not master dead).

MySQL Replication Health is OK.
[mha@mysqlmha3 shell]$

 

Look at the mysqlmha node 1VIP information

[root@mysqlmha1 ~]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: eno16777736: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 00:0c:29:96:18:d2 brd ff:ff:ff:ff:ff:ff
    inet 192.168.142.111/24 brd 192.168.142.255 scope global eno16777736
       valid_lft forever preferred_lft forever
    inet 192.168.142.114/24 brd 192.168.142.255 scope global secondary eno16777736:2
       valid_lft forever preferred_lft forever
    inet6 fe80::20c:29ff:fe96:18d2/64 scope link
       valid_lft forever preferred_lft forever
[root@mysqlmha1 ~]#

 

【Summing up]

MHAThe VIP and other repository nodes can allocate read and write separation according to the business requirements. Too much configuration may lead to the error of various interfaces in the business and delay the recovery time.

Automatic switching and manual switching should be determined according to the requirements. Although the process scripts for automatic switching are deployed, the host and database levels are also required to deploy monitoring, to find problems before MHA automatic maintenance or normal state, to avoid the impact of business.

Automatic switching may result in misjudgment of the main node state due to network or host causes, resulting in incorrect handover. But the timeliness is high.

Manual switching, manual switching is more flexible, to meet disaster recovery drill and node maintenance needs. But there is a situation of low timeliness.

 

Link of this Article: MySQL MHA high availability test

Leave a Reply

Your email address will not be published. Required fields are marked *