MHA

最新推荐文章于 2024-09-24 09:49:09 发布
最新推荐文章于 2024-09-24 09:49:09 发布
阅读量1.7k 收藏 2
点赞数
分类专栏： MySQL 复制 文章标签： MHA
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/wanbin6470398/article/details/83303930
版权

MHA

1. MHA简介

MHA（Master High Availability）目前在MySQL高可用方面是一个相对成熟的解决方案，该工具仅适用于MySQL Replication 环境，目的在于维持master主库的高可用性。MHA 是自动的master 故障转移和slave提升的软件包，基于标准的MySQL复制（异步/半同步）。

MHA由两部分组成：MHA Manager（管理节点）和 MHA Node（数据节点）。

  • MHA Manager可以单独部署在一台独立的机器上管理多个master-slave集群，也可以部署在一台slave节点上。
  • MHA Node运行在每台MySQL服务器上。

MHA Manager会定时探测集群中的master节点，当master出现故障时，它可以自动将最新数据的slave提升为新的master，然后将所有其他的slave重新指向新的master。整个故障转移过程对应用程序完全透明。

在MHA自动故障切换过程中，MHA试图从宕机的主服务器上保存二进制日志，最大程度的保证数据的不丢失，但这并不总是可行的。例如，如果主服务器硬件故障或无法通过ssh访问，MHA没法保存二进制日志，只进行故障转移而丢失了最新的数据。使用半同步复制，可以大大降低数据丢失的风险。MHA可以与半同步复制结合起来。如果只有一个slave已经收到了最新的二进制日志，MHA可以将最新的二进制日志应用于其他所有的slave服务器上，因此可以保证所有节点的数据一致性。

1.1 MHA工作原理总结为如下

（1）从宕机崩溃的master保存二进制日志事件（binlog events）;

（2）识别含有最新更新的slave；

（3）apply差异的中继日志（relay log）到其他的slave；

（4）apply从master保存的二进制日志事件（binlog events）；

（5）提升一个slave为新的master；

（6）使其他的slave连接新的master进行复制。

1.2 MHA工具包介绍

Manager管理工具

`masterha_check_ssh`              检查MHA的SSH配置状况
`masterha_check_repl`             检查MySQL复制状况
`masterha_manger`                 启动MHA
`masterha_check_status`           检测当前MHA运行状态
`masterha_master_monitor`         检测master是否宕机
`masterha_master_switch`          控制故障转移（自动或者手动）
`masterha_conf_host`              添加或删除配置的server信息

Node数据节点工具

`save_binary_logs`                保存和复制master的二进制日志
`apply_diff_relay_logs`           识别差异的中继日志事件并将其差异的事件应用于其他的slave
`filter_mysqlbinlog`              去除不必要的ROLLBACK事件（MHA已不再使用这个工具）
`purge_relay_logs`                清除中继日志（不会阻塞SQL线程）

2. 部署MHA

2.1 环境介绍

系统主机名mysql角色MHA角色server_idMySQL 版本VIP
CentOS 7.5mysqldb1(100)Maternode10033065.7.23192.168.56.111
CentOS 7.5mysqldb2(200)Slavenode
manager		20033065.7.23
CentOS 7.5mysqldb3(210)Slavenode21033065.7.23

2.2 一主两从复制搭建

搭建过程参考MySQL 使用GTID进行复制

创建mha管理账号

#在（mysqldb1）master上执行
GRANT ALL PRIVILEGES ON *.* TO 'mha_rep'@'192.168.56.%' IDENTIFIED BY '123456';

从库上执行

#设置为只读模式
set global read_only=1; 
#read_only=1只读模式，可以限定普通用户进行数据修改的操作，但不会限定具有super权限的用户的数据修改操作

#禁用relay log自动清除         
set global relay_log_purge=0;

MHA在发生切换的过程中，从库的恢复过程中依赖于relay log的相关信息，所以这里要将relay log的自动清除设置为OFF，采用手动清除relay log的方式。在默认情况下，从服务器上的中继日志会在SQL线程执行完毕后被自动删除。但是在MHA环境中，这些中继日志在恢复其他从服务器时可能会被用到，因此需要禁用中继日志的自动删除功能。定期清除中继日志需要考虑到复制延时的问题。在ext3的文件系统下，删除大的文件需要一定的时间，会导致严重的复制延时。为了避免复制延时，需要暂时为中继日志创建硬链接，因为在linux系统中通过硬链接删除大文件速度会很快。（在mysql数据库中，删除大表时，通常也采用建立硬链接的方式）

2.3 配置互信

（1） 在三台机器上生成各自的key文件

[root@mysqldb1 11:10:53 /root]
# ssh-keygen -t rsa
[root@mysqldb2 11:10:56 /root]
# ssh-keygen -t rsa
[root@mysqldb3 11:10:58 /root]
# ssh-keygen -t rsa

（2）用ssh-copy-id把公钥复制到每台主机上

[root@mysqldb1 11:15:35 /root]
# ssh-copy-id -i .ssh/id_rsa.pub root@192.168.56.100
# ssh-copy-id -i .ssh/id_rsa.pub root@192.168.56.200
# ssh-copy-id -i .ssh/id_rsa.pub root@192.168.56.210

[root@mysqldb2 11:11:15 /root]
# ssh-copy-id -i .ssh/id_rsa.pub root@192.168.56.100
# ssh-copy-id -i .ssh/id_rsa.pub root@192.168.56.200
# ssh-copy-id -i .ssh/id_rsa.pub root@192.168.56.210

[root@mysqldb3 11:17:53 /root]
# ssh-copy-id -i .ssh/id_rsa.pub root@192.168.56.100
# ssh-copy-id -i .ssh/id_rsa.pub root@192.168.56.200
# ssh-copy-id -i .ssh/id_rsa.pub root@192.168.56.210

【注意】：配置完成后，采用 ssh [hostname] date 进行验证

2.4 下载MHA

网址：
https://github.com/yoshinorim/mha4mysql-manager
https://github.com/wubx/mha4mysql-manager 建议使用
文件：mha4mysql-manager-master.zip

网址：
https://github.com/yoshinorim/mha4mysql-node
https://github.com/wubx/mha4mysql-node #建议使用
文件：mha4mysql-node-master.zip

2.5 安装MHA

每个节点都安装manager和node，但只有mysqldb2上启动manager。

1）解压安装包

unzip mha4mysql-manager-master.zip 
unzip mha4mysql-node-master.zip

2）安装依赖包

--yum search perl |grep install
yum install cpan
yum install perl-Module-Install.noarch
yum install perl-DBI
yum install perl-DBD-MySQL
yum install perl-Time-HiRes.x86_64
yum install perl-Config-Tiny.noarch
yum install perl-Log-Dispatch.noarch 
yum install perl-Parallel-ForkManager.noarch

3）编译安装（node）

# cd /opt/mha4mysql-node-master/

# perl Makefile.PL 
include /opt/mha4mysql-node-master/inc/Module/Install.pm
include inc/Module/Install/Metadata.pm
include inc/Module/Install/Base.pm
include inc/Module/Install/Makefile.pm
include inc/Module/Install/Scripts.pm
include inc/Module/Install/AutoInstall.pm
include inc/Module/Install/Include.pm
include inc/Module/AutoInstall.pm
*** Module::AutoInstall version 1.06
*** Checking for Perl dependencies...
[Core Features]
- DBI        ...loaded. (1.627)
- DBD::mysql ...loaded. (4.023)
*** Module::AutoInstall configuration finished.
include inc/Module/Install/WriteAll.pm
include inc/Module/Install/Win32.pm
include inc/Module/Install/Can.pm
include inc/Module/Install/Fetch.pm
Checking if your kit is complete...
Warning: the following files are missing in your kit:
	META.yml
Please inform the author.
Writing Makefile for mha4mysql::node
Writing MYMETA.yml and MYMETA.json
Writing META.yml

# make && make install
cp lib/MHA/BinlogManager.pm blib/lib/MHA/BinlogManager.pm
cp lib/MHA/BinlogPosFindManager.pm blib/lib/MHA/BinlogPosFindManager.pm
cp lib/MHA/BinlogPosFinderXid.pm blib/lib/MHA/BinlogPosFinderXid.pm
cp lib/MHA/BinlogHeaderParser.pm blib/lib/MHA/BinlogHeaderParser.pm
cp lib/MHA/BinlogPosFinder.pm blib/lib/MHA/BinlogPosFinder.pm
cp lib/MHA/BinlogPosFinderElp.pm blib/lib/MHA/BinlogPosFinderElp.pm
cp lib/MHA/NodeUtil.pm blib/lib/MHA/NodeUtil.pm
cp lib/MHA/SlaveUtil.pm blib/lib/MHA/SlaveUtil.pm
cp lib/MHA/NodeConst.pm blib/lib/MHA/NodeConst.pm
cp bin/filter_mysqlbinlog blib/script/filter_mysqlbinlog
/usr/bin/perl "-Iinc" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/filter_mysqlbinlog
cp bin/apply_diff_relay_logs blib/script/apply_diff_relay_logs
/usr/bin/perl "-Iinc" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/apply_diff_relay_logs
cp bin/purge_relay_logs blib/script/purge_relay_logs
/usr/bin/perl "-Iinc" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/purge_relay_logs
cp bin/save_binary_logs blib/script/save_binary_logs
/usr/bin/perl "-Iinc" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/save_binary_logs
Manifying blib/man1/filter_mysqlbinlog.1
Manifying blib/man1/apply_diff_relay_logs.1
Manifying blib/man1/purge_relay_logs.1
Manifying blib/man1/save_binary_logs.1
Installing /usr/local/share/perl5/MHA/BinlogManager.pm
Installing /usr/local/share/perl5/MHA/BinlogPosFindManager.pm
Installing /usr/local/share/perl5/MHA/BinlogPosFinderXid.pm
Installing /usr/local/share/perl5/MHA/BinlogHeaderParser.pm
Installing /usr/local/share/perl5/MHA/BinlogPosFinder.pm
Installing /usr/local/share/perl5/MHA/BinlogPosFinderElp.pm
Installing /usr/local/share/perl5/MHA/NodeUtil.pm
Installing /usr/local/share/perl5/MHA/SlaveUtil.pm
Installing /usr/local/share/perl5/MHA/NodeConst.pm
Installing /usr/local/share/man/man1/filter_mysqlbinlog.1
Installing /usr/local/share/man/man1/apply_diff_relay_logs.1
Installing /usr/local/share/man/man1/purge_relay_logs.1
Installing /usr/local/share/man/man1/save_binary_logs.1
Installing /usr/local/bin/filter_mysqlbinlog
Installing /usr/local/bin/apply_diff_relay_logs
Installing /usr/local/bin/purge_relay_logs
Installing /usr/local/bin/save_binary_logs
Appending installation info to /usr/lib64/perl5/perllocal.pod

4）编译安装（manager）

# cd mha4mysql-manager-master/

# perl Makefile.PL 
include /opt/mha4mysql-manager-master/inc/Module/Install.pm
include inc/Module/Install/Metadata.pm
include inc/Module/Install/Base.pm
include inc/Module/Install/Makefile.pm
include inc/Module/Install/Scripts.pm
include inc/Module/Install/AutoInstall.pm
include inc/Module/Install/Include.pm
include inc/Module/AutoInstall.pm
*** Module::AutoInstall version 1.06
*** Checking for Perl dependencies...
[Core Features]
- DBI                   ...loaded. (1.627)
- DBD::mysql            ...loaded. (4.023)
- Time::HiRes           ...loaded. (1.9725)
- Config::Tiny          ...loaded. (2.14)
- Log::Dispatch         ...loaded. (2.41)
- Parallel::ForkManager ...loaded. (1.18)
- MHA::NodeConst        ...loaded. (0.57)
*** Module::AutoInstall configuration finished.
include inc/Module/Install/WriteAll.pm
include inc/Module/Install/Win32.pm
include inc/Module/Install/Can.pm
include inc/Module/Install/Fetch.pm
Checking if your kit is complete...
Warning: the following files are missing in your kit:
	META.yml
Please inform the author.
Writing Makefile for mha4mysql::manager
Writing MYMETA.yml and MYMETA.json
Writing META.yml

# make && make install
cp lib/MHA/ManagerUtil.pm blib/lib/MHA/ManagerUtil.pm
cp lib/MHA/Config.pm blib/lib/MHA/Config.pm
cp lib/MHA/HealthCheck.pm blib/lib/MHA/HealthCheck.pm
cp lib/MHA/ManagerConst.pm blib/lib/MHA/ManagerConst.pm
cp lib/MHA/ServerManager.pm blib/lib/MHA/ServerManager.pm
cp lib/MHA/FileStatus.pm blib/lib/MHA/FileStatus.pm
cp lib/MHA/ManagerAdmin.pm blib/lib/MHA/ManagerAdmin.pm
cp lib/MHA/ManagerAdminWrapper.pm blib/lib/MHA/ManagerAdminWrapper.pm
cp lib/MHA/MasterFailover.pm blib/lib/MHA/MasterFailover.pm
cp lib/MHA/MasterMonitor.pm blib/lib/MHA/MasterMonitor.pm
cp lib/MHA/MasterRotate.pm blib/lib/MHA/MasterRotate.pm
cp lib/MHA/SSHCheck.pm blib/lib/MHA/SSHCheck.pm
cp lib/MHA/Server.pm blib/lib/MHA/Server.pm
cp lib/MHA/DBHelper.pm blib/lib/MHA/DBHelper.pm
cp bin/masterha_stop blib/script/masterha_stop
/usr/bin/perl "-Iinc" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/masterha_stop
cp bin/masterha_conf_host blib/script/masterha_conf_host
/usr/bin/perl "-Iinc" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/masterha_conf_host
cp bin/masterha_check_repl blib/script/masterha_check_repl
/usr/bin/perl "-Iinc" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/masterha_check_repl
cp bin/masterha_check_status blib/script/masterha_check_status
/usr/bin/perl "-Iinc" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/masterha_check_status
cp bin/masterha_master_monitor blib/script/masterha_master_monitor
/usr/bin/perl "-Iinc" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/masterha_master_monitor
cp bin/masterha_check_ssh blib/script/masterha_check_ssh
/usr/bin/perl "-Iinc" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/masterha_check_ssh
cp bin/masterha_master_switch blib/script/masterha_master_switch
/usr/bin/perl "-Iinc" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/masterha_master_switch
cp bin/masterha_secondary_check blib/script/masterha_secondary_check
/usr/bin/perl "-Iinc" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/masterha_secondary_check
cp bin/masterha_manager blib/script/masterha_manager
/usr/bin/perl "-Iinc" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/masterha_manager
Manifying blib/man1/masterha_stop.1
Manifying blib/man1/masterha_conf_host.1
Manifying blib/man1/masterha_check_repl.1
Manifying blib/man1/masterha_check_status.1
Manifying blib/man1/masterha_master_monitor.1
Manifying blib/man1/masterha_check_ssh.1
Manifying blib/man1/masterha_master_switch.1
Manifying blib/man1/masterha_secondary_check.1
Manifying blib/man1/masterha_manager.1
Installing /usr/local/share/perl5/MHA/ManagerUtil.pm
Installing /usr/local/share/perl5/MHA/Config.pm
Installing /usr/local/share/perl5/MHA/HealthCheck.pm
Installing /usr/local/share/perl5/MHA/ManagerConst.pm
Installing /usr/local/share/perl5/MHA/ServerManager.pm
Installing /usr/local/share/perl5/MHA/FileStatus.pm
Installing /usr/local/share/perl5/MHA/ManagerAdmin.pm
Installing /usr/local/share/perl5/MHA/ManagerAdminWrapper.pm
Installing /usr/local/share/perl5/MHA/MasterFailover.pm
Installing /usr/local/share/perl5/MHA/MasterMonitor.pm
Installing /usr/local/share/perl5/MHA/MasterRotate.pm
Installing /usr/local/share/perl5/MHA/SSHCheck.pm
Installing /usr/local/share/perl5/MHA/Server.pm
Installing /usr/local/share/perl5/MHA/DBHelper.pm
Installing /usr/local/share/man/man1/masterha_stop.1
Installing /usr/local/share/man/man1/masterha_conf_host.1
Installing /usr/local/share/man/man1/masterha_check_repl.1
Installing /usr/local/share/man/man1/masterha_check_status.1
Installing /usr/local/share/man/man1/masterha_master_monitor.1
Installing /usr/local/share/man/man1/masterha_check_ssh.1
Installing /usr/local/share/man/man1/masterha_master_switch.1
Installing /usr/local/share/man/man1/masterha_secondary_check.1
Installing /usr/local/share/man/man1/masterha_manager.1
Installing /usr/local/bin/masterha_stop
Installing /usr/local/bin/masterha_conf_host
Installing /usr/local/bin/masterha_check_repl
Installing /usr/local/bin/masterha_check_status
Installing /usr/local/bin/masterha_master_monitor
Installing /usr/local/bin/masterha_check_ssh
Installing /usr/local/bin/masterha_master_switch
Installing /usr/local/bin/masterha_secondary_check
Installing /usr/local/bin/masterha_manager
Appending installation info to /usr/lib64/perl5/perllocal.pod

2.6 配置MHA

2.6.1创建MHA工作目录
mkdir -p /etc/masterha
mkdir -p /var/log/masterha/app1
2.6.2 创建配置文件
cp /opt/mha4mysql-manager-master/samples/conf/app1.cnf /etc/masterha/
cd /etc/masterha/

#修改app1.cnf配置文件
vi app1.cnf 

[server default]
#设置manager的工作目录
manager_workdir=/var/log/masterha/app1
#设置manager的日志
manager_log=/var/log/masterha/app1/manager.log
#通过多种网络路径检测ssh是否能够连接到master脚本，若其中一个路径不通，则会通过另一个路径ssh连接到master
secondary_check_script=/usr/local/bin/masterha_secondary_check -s mysqldb2 -s mysqldb1
#故障转移脚本
master_ip_failover_script=/usr/local/bin/master_ip_failover
#master_ip_failover_script＝/usr/bin/master_ip_failover
#master_ip_online_change_script=/usr/bin/master_ip_online_change
#shutdown_script=/usr/bin/power_manager
#report_script=/usr/bin/send_report


#设置监控的用户
user=mha_rep
#设置监控用户的密码
password=123456

#设置ssh的登录用户名
ssh_user=root

#设置复制环境中的复制用户名
repl_user=repl
#设置复制用户的密码
repl_password=wanbin

#设置监控主库，发送ping包的时间间隔，默认是3秒，尝试三次没有回应的时候自动进行railover
ping_interval=1

[server1]
hostname=192.168.56.100
#候选master
candidate_master=1
master_binlog_dir=/data/mysql/mysql3306/logs

[server2]
hostname=192.168.56.200
#候选master
candidate_master=1
master_binlog_dir=/data/mysql/mysql3306/logs

[server3]
hostname=192.168.56.210
#该服务器不会成为new master
no_master=1
master_binlog_dir=/data/mysql/mysql3306/logs
2.6.3 编辑故障转移脚本
# cd /opt/mha4mysql-manager-master/samples/scripts/

# ls
master_ip_failover  master_ip_online_change  power_manager  send_report

# cp master_ip_failover /usr/local/bin/

# 编辑master_ip_failover脚本
#!/usr/bin/env perl

#  Copyright (C) 2011 DeNA Co.,Ltd.
#
#  This program is free software; you can redistribute it and/or modify
#  it under the terms of the GNU General Public License as published by
#  the Free Software Foundation; either version 2 of the License, or
#  (at your option) any later version.
#
#  This program is distributed in the hope that it will be useful,
#  but WITHOUT ANY WARRANTY; without even the implied warranty of
#  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#  GNU General Public License for more details.
#
#  You should have received a copy of the GNU General Public License
#   along with this program; if not, write to the Free Software
#  Foundation, Inc.,
#  51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA

## Note: This is a sample script and is not complete. Modify the script based on your environment.

use strict;
use warnings FATAL => 'all';

use Getopt::Long;

my (
    $command,          $ssh_user,        $orig_master_host, $orig_master_ip,
    $orig_master_port, $new_master_host, $new_master_ip,    $new_master_port
);

my $vip = '192.168.56.111/24';
my $key = '0';
my $ssh_start_vip = "/sbin/ifconfig eth0:$key $vip up";
my $ssh_stop_vip  = "/sbin/ifconfig eth0:$key down";
my $ssh_Bcast_arp = "/usr/bin/arping -c 3 -A 192.168.56.111";   #ARP回复模式。要是不加则服务器会自动等到vip缓存失效，期间VIP会有一定时间的不可用。

GetOptions(
    'command=s'          => \$command,
    'ssh_user=s'         => \$ssh_user,
    'orig_master_host=s' => \$orig_master_host,
    'orig_master_ip=s'   => \$orig_master_ip,
    'orig_master_port=i' => \$orig_master_port,
    'new_master_host=s'  => \$new_master_host,
    'new_master_ip=s'    => \$new_master_ip,
    'new_master_port=i'  => \$new_master_port,
);

exit &main();

sub main {

    print "\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n";

    if ( $command eq "stop" || $command eq "stopssh" ) {

        my $exit_code = 1;
        eval {
            print "Disabling the VIP on old master: $orig_master_host \n";
            &stop_vip();
            $exit_code = 0;
        };
        if ($@) {
            warn "Got Error: $@\n";
            exit $exit_code;
        }
        exit $exit_code;
    }
    elsif ( $command eq "start" ) {

        my $exit_code = 10;
        eval {
            print "Enabling the VIP - $vip on the new master - $new_master_host \n";
            &start_vip();
            &start_arp();
            $exit_code = 0;
        };
        if ($@) {
            warn $@;
            exit $exit_code;
        }
        exit $exit_code;
    }
    elsif ( $command eq "status" ) {
        print "Checking the Status of the script.. OK \n";
        exit 0;
    }

    else {
        &usage();
        exit 1;
    }
}

sub start_vip() {
    `ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`;
}
sub start_arp() {
    `ssh $ssh_user\@$new_master_host \" $ssh_Bcast_arp \"`;
}
sub stop_vip() {
    `ssh $ssh_user\@$orig_master_host \" $ssh_stop_vip \"`;
}

sub usage {
  print
"Usage: master_ip_failover --command=start|stop|stopssh|status --orig_master_host=host --orig_master_ip=ip --orig_master_port=port --new_master_host=host --new_master_ip=ip --new_master_port=port\n";
}
2.6.4 配置VIP

在用虚拟IP的时候，需要在开启MHA程序之前要把虚拟IP先设置到主上去，否则MHA是不会自己的去设置VIP，第一次设置VIP之后，后续脚本的故障转移等功能会自动的对VIP进行切换。

# ifconfig eth0:0 192.168.56.111 up

# /sbin/arping -c 3 -A 192.168.56.111 -I eth0
ARPING 192.168.56.111 from 192.168.56.111 eth0

Sent 3 probes (3 broadcast(s))
Received 0 response(s)
2.6.5 检查配置文件

利用mha工具检测ssh

# masterha_check_ssh --conf=/etc/masterha/app1.cnf
Mon Oct 29 09:55:27 2018 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Mon Oct 29 09:55:27 2018 - [info] Reading application default configuration from /etc/masterha/app1.cnf..
Mon Oct 29 09:55:27 2018 - [info] Reading server configuration from /etc/masterha/app1.cnf..
Mon Oct 29 09:55:27 2018 - [info] Starting SSH connection tests..
Mon Oct 29 09:55:28 2018 - [debug] 
Mon Oct 29 09:55:27 2018 - [debug]  Connecting via SSH from root@192.168.56.100(192.168.56.100:22) to root@192.168.56.200(192.168.56.200:22)..
Mon Oct 29 09:55:27 2018 - [debug]   ok.
Mon Oct 29 09:55:27 2018 - [debug]  Connecting via SSH from root@192.168.56.100(192.168.56.100:22) to root@192.168.56.210(192.168.56.210:22)..
Mon Oct 29 09:55:27 2018 - [debug]   ok.
Mon Oct 29 09:55:28 2018 - [debug] 
Mon Oct 29 09:55:27 2018 - [debug]  Connecting via SSH from root@192.168.56.200(192.168.56.200:22) to root@192.168.56.100(192.168.56.100:22)..
Mon Oct 29 09:55:28 2018 - [debug]   ok.
Mon Oct 29 09:55:28 2018 - [debug]  Connecting via SSH from root@192.168.56.200(192.168.56.200:22) to root@192.168.56.210(192.168.56.210:22)..
Mon Oct 29 09:55:28 2018 - [debug]   ok.
Mon Oct 29 09:55:29 2018 - [debug] 
Mon Oct 29 09:55:28 2018 - [debug]  Connecting via SSH from root@192.168.56.210(192.168.56.210:22) to root@192.168.56.100(192.168.56.100:22)..
Mon Oct 29 09:55:28 2018 - [debug]   ok.
Mon Oct 29 09:55:28 2018 - [debug]  Connecting via SSH from root@192.168.56.210(192.168.56.210:22) to root@192.168.56.200(192.168.56.200:22)..
Mon Oct 29 09:55:29 2018 - [debug]   ok.
Mon Oct 29 09:55:29 2018 - [info] All SSH connection tests passed successfully.
2.6.6 利用mha工具check检查repl环境
# /usr/local/bin/masterha_check_repl --conf=/etc/masterha/app1.cnf
Mon Oct 29 10:08:13 2018 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Mon Oct 29 10:08:13 2018 - [info] Reading application default configuration from /etc/masterha/app1.cnf..
Mon Oct 29 10:08:13 2018 - [info] Reading server configuration from /etc/masterha/app1.cnf..
Mon Oct 29 10:08:13 2018 - [info] MHA::MasterMonitor version 0.57.
Mon Oct 29 10:08:14 2018 - [info] GTID failover mode = 1
Mon Oct 29 10:08:14 2018 - [info] Dead Servers:
Mon Oct 29 10:08:14 2018 - [info] Alive Servers:
Mon Oct 29 10:08:14 2018 - [info]   192.168.56.100(192.168.56.100:3306)
Mon Oct 29 10:08:14 2018 - [info]   192.168.56.200(192.168.56.200:3306)
Mon Oct 29 10:08:14 2018 - [info]   192.168.56.210(192.168.56.210:3306)
Mon Oct 29 10:08:14 2018 - [info] Alive Slaves:
Mon Oct 29 10:08:14 2018 - [info]   192.168.56.200(192.168.56.200:3306)  Version=5.7.23-log (oldest major version between slaves) log-bin:enabled
Mon Oct 29 10:08:14 2018 - [info]     GTID ON
Mon Oct 29 10:08:14 2018 - [info]     Replicating from 192.168.56.100(192.168.56.100:3306)
Mon Oct 29 10:08:14 2018 - [info]     Primary candidate for the new Master (candidate_master is set)
Mon Oct 29 10:08:14 2018 - [info]   192.168.56.210(192.168.56.210:3306)  Version=5.7.23-log (oldest major version between slaves) log-bin:enabled
Mon Oct 29 10:08:14 2018 - [info]     GTID ON
Mon Oct 29 10:08:14 2018 - [info]     Replicating from 192.168.56.100(192.168.56.100:3306)
Mon Oct 29 10:08:14 2018 - [info]     Not candidate for the new Master (no_master is set)
Mon Oct 29 10:08:14 2018 - [info] Current Alive Master: 192.168.56.100(192.168.56.100:3306)
Mon Oct 29 10:08:14 2018 - [info] Checking slave configurations..
Mon Oct 29 10:08:14 2018 - [info]  read_only=1 is not set on slave 192.168.56.200(192.168.56.200:3306).
Mon Oct 29 10:08:14 2018 - [info]  read_only=1 is not set on slave 192.168.56.210(192.168.56.210:3306).
Mon Oct 29 10:08:14 2018 - [info] Checking replication filtering settings..
Mon Oct 29 10:08:14 2018 - [info]  binlog_do_db= , binlog_ignore_db= 
Mon Oct 29 10:08:14 2018 - [info]  Replication filtering check ok.
Mon Oct 29 10:08:14 2018 - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.
Mon Oct 29 10:08:14 2018 - [info] Checking SSH publickey authentication settings on the current master..
Mon Oct 29 10:08:14 2018 - [info] HealthCheck: SSH to 192.168.56.100 is reachable.
Mon Oct 29 10:08:14 2018 - [info] 
192.168.56.100(192.168.56.100:3306) (current master)
 +--192.168.56.200(192.168.56.200:3306)
 +--192.168.56.210(192.168.56.210:3306)

Mon Oct 29 10:08:14 2018 - [info] Checking replication health on 192.168.56.200..
Mon Oct 29 10:08:14 2018 - [info]  ok.
Mon Oct 29 10:08:14 2018 - [info] Checking replication health on 192.168.56.210..
Mon Oct 29 10:08:14 2018 - [info]  ok.
Mon Oct 29 10:08:14 2018 - [info] Checking master_ip_failover_script status:
Mon Oct 29 10:08:14 2018 - [info]   /usr/local/bin/master_ip_failover --command=status --ssh_user=root --orig_master_host=192.168.56.100 --orig_master_ip=192.168.56.100 --orig_master_port=3306 


IN SCRIPT TEST====/sbin/ifconfig eth0:0 down==/sbin/ifconfig eth0:0 192.168.56.111/24 up===

Checking the Status of the script.. OK 
Mon Oct 29 10:08:14 2018 - [info]  OK.
Mon Oct 29 10:08:14 2018 - [warning] shutdown_script is not defined.
Mon Oct 29 10:08:14 2018 - [info] Got exit code 0 (Not master dead).

MySQL Replication Health is OK.
2.6.7 开启MHA Manager，并查看状态
# masterha_manager --conf=/etc/masterha/app1.cnf > /var/log/masterha/app1/manager.log &


# tail -100f /var/log/masterha/app1/manager.log
Mon Oct 29 10:13:40 2018 - [info] MHA::MasterMonitor version 0.57.
Mon Oct 29 10:13:41 2018 - [info] GTID failover mode = 1
Mon Oct 29 10:13:41 2018 - [info] Dead Servers:
Mon Oct 29 10:13:41 2018 - [info] Alive Servers:
Mon Oct 29 10:13:41 2018 - [info]   192.168.56.100(192.168.56.100:3306)
Mon Oct 29 10:13:41 2018 - [info]   192.168.56.200(192.168.56.200:3306)
Mon Oct 29 10:13:41 2018 - [info]   192.168.56.210(192.168.56.210:3306)
Mon Oct 29 10:13:41 2018 - [info] Alive Slaves:
Mon Oct 29 10:13:41 2018 - [info]   192.168.56.200(192.168.56.200:3306)  Version=5.7.23-log (oldest major version between slaves) log-bin:enabled
Mon Oct 29 10:13:41 2018 - [info]     GTID ON
Mon Oct 29 10:13:41 2018 - [info]     Replicating from 192.168.56.100(192.168.56.100:3306)
Mon Oct 29 10:13:41 2018 - [info]     Primary candidate for the new Master (candidate_master is set)
Mon Oct 29 10:13:41 2018 - [info]   192.168.56.210(192.168.56.210:3306)  Version=5.7.23-log (oldest major version between slaves) log-bin:enabled
Mon Oct 29 10:13:41 2018 - [info]     GTID ON
Mon Oct 29 10:13:41 2018 - [info]     Replicating from 192.168.56.100(192.168.56.100:3306)
Mon Oct 29 10:13:41 2018 - [info]     Not candidate for the new Master (no_master is set)
Mon Oct 29 10:13:41 2018 - [info] Current Alive Master: 192.168.56.100(192.168.56.100:3306)
Mon Oct 29 10:13:41 2018 - [info] Checking slave configurations..
Mon Oct 29 10:13:41 2018 - [info] Checking replication filtering settings..
Mon Oct 29 10:13:41 2018 - [info]  binlog_do_db= , binlog_ignore_db= 
Mon Oct 29 10:13:41 2018 - [info]  Replication filtering check ok.
Mon Oct 29 10:13:41 2018 - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.
Mon Oct 29 10:13:41 2018 - [info] Checking SSH publickey authentication settings on the current master..
Mon Oct 29 10:13:42 2018 - [info] HealthCheck: SSH to 192.168.56.100 is reachable.
Mon Oct 29 10:13:42 2018 - [info] 
192.168.56.100(192.168.56.100:3306) (current master)
 +--192.168.56.200(192.168.56.200:3306)
 +--192.168.56.210(192.168.56.210:3306)

Mon Oct 29 10:13:42 2018 - [info] Checking master_ip_failover_script status:
Mon Oct 29 10:13:42 2018 - [info]   /usr/local/bin/master_ip_failover --command=status --ssh_user=root --orig_master_host=192.168.56.100 --orig_master_ip=192.168.56.100 --orig_master_port=3306 


IN SCRIPT TEST====/sbin/ifconfig eth0:0 down==/sbin/ifconfig eth0:0 192.168.56.111/24 up===

Checking the Status of the script.. OK 
Mon Oct 29 10:13:42 2018 - [info]  OK.
Mon Oct 29 10:13:42 2018 - [warning] shutdown_script is not defined.
Mon Oct 29 10:13:42 2018 - [info] Set master ping interval 1 seconds.
Mon Oct 29 10:13:42 2018 - [info] Set secondary check script: /usr/local/bin/masterha_secondary_check -s mysqldb2 -s mysqldb1
Mon Oct 29 10:13:42 2018 - [info] Starting ping health check on 192.168.56.100(192.168.56.100:3306)..
Mon Oct 29 10:13:42 2018 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond..

#查看状态
# /usr/local/bin/masterha_check_status --conf=/etc/masterha/app1.cnf
app1 (pid:3579) is running(0:PING_OK), master:192.168.56.100

3. 测试MHA

3.1 模拟master（mysqldb1）故障

（1）查看VIP漂移和MySQL自动切换情况

#kill mysqldb1上的mysqld进程
# ps -ef|grep mysqld
avahi      741     1  0 08:50 ?        00:00:00 avahi-daemon: running [mysqldb1.local]
mysql     2349  2271  0 09:08 pts/0    00:00:29 mysqld --defaults-file=/etc/my3306.cnf
root      2986  2657  0 09:45 pts/1    00:00:00 ssh mysqldb1
root      4285  4228  0 10:36 pts/3    00:00:00 grep --color=auto mysqld

# kill -9 2349

（2）在mysqldb2上观察manager日志：

# tail -f  /var/log/masterha/app1/manager.log

IN SCRIPT TEST====/sbin/ifconfig eth0:0 down==/sbin/ifconfig eth0:0 192.168.56.111/24 up===

Checking the Status of the script.. OK 
Mon Oct 29 10:13:42 2018 - [info]  OK.
Mon Oct 29 10:13:42 2018 - [warning] shutdown_script is not defined.
Mon Oct 29 10:13:42 2018 - [info] Set master ping interval 1 seconds.
Mon Oct 29 10:13:42 2018 - [info] Set secondary check script: /usr/local/bin/masterha_secondary_check -s mysqldb2 -s mysqldb1
Mon Oct 29 10:13:42 2018 - [info] Starting ping health check on 192.168.56.100(192.168.56.100:3306)..
Mon Oct 29 10:13:42 2018 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond..
Mon Oct 29 10:37:56 2018 - [warning] Got error on MySQL select ping: 2006 (MySQL server has gone away)
Mon Oct 29 10:37:56 2018 - [info] Executing secondary network check script: /usr/local/bin/masterha_secondary_check -s mysqldb2 -s mysqldb1  --user=root  --master_host=192.168.56.100  --master_ip=192.168.56.100  --master_port=3306 --master_user=mha_rep --master_password=123456 --ping_type=SELECT
Mon Oct 29 10:37:56 2018 - [info] Executing SSH check script: exit 0
Mon Oct 29 10:37:56 2018 - [info] HealthCheck: SSH to 192.168.56.100 is reachable.
Monitoring server mysqldb2 is reachable, Master is not reachable from mysqldb2. OK.
Monitoring server mysqldb1 is reachable, Master is not reachable from mysqldb1. OK.
Mon Oct 29 10:37:57 2018 - [info] Master is not reachable from all other monitoring servers. Failover should start.
Mon Oct 29 10:37:57 2018 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '192.168.56.100' (111))
Mon Oct 29 10:37:57 2018 - [warning] Connection failed 2 time(s)..
Mon Oct 29 10:37:58 2018 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '192.168.56.100' (111))
Mon Oct 29 10:37:58 2018 - [warning] Connection failed 3 time(s)..
Mon Oct 29 10:37:59 2018 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '192.168.56.100' (111))
Mon Oct 29 10:37:59 2018 - [warning] Connection failed 4 time(s)..
Mon Oct 29 10:37:59 2018 - [warning] Master is not reachable from health checker!
Mon Oct 29 10:37:59 2018 - [warning] Master 192.168.56.100(192.168.56.100:3306) is not reachable!
Mon Oct 29 10:37:59 2018 - [warning] SSH is reachable.
Mon Oct 29 10:37:59 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..
Mon Oct 29 10:37:59 2018 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Mon Oct 29 10:37:59 2018 - [info] Reading application default configuration from /etc/masterha/app1.cnf..
Mon Oct 29 10:37:59 2018 - [info] Reading server configuration from /etc/masterha/app1.cnf..
Mon Oct 29 10:38:00 2018 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Mon Oct 29 10:38:00 2018 - [info] Reading application default configuration from /etc/masterha/app1.cnf..
Mon Oct 29 10:38:00 2018 - [info] Reading server configuration from /etc/masterha/app1.cnf..
Mon Oct 29 10:38:00 2018 - [info] GTID failover mode = 1
Mon Oct 29 10:38:00 2018 - [info] Dead Servers:
Mon Oct 29 10:38:00 2018 - [info]   192.168.56.100(192.168.56.100:3306)
Mon Oct 29 10:38:00 2018 - [info] Alive Servers:
Mon Oct 29 10:38:00 2018 - [info]   192.168.56.200(192.168.56.200:3306)
Mon Oct 29 10:38:00 2018 - [info]   192.168.56.210(192.168.56.210:3306)
Mon Oct 29 10:38:00 2018 - [info] Alive Slaves:
Mon Oct 29 10:38:00 2018 - [info]   192.168.56.200(192.168.56.200:3306)  Version=5.7.23-log (oldest major version between slaves) log-bin:enabled
Mon Oct 29 10:38:00 2018 - [info]     GTID ON
Mon Oct 29 10:38:00 2018 - [info]     Replicating from 192.168.56.100(192.168.56.100:3306)
Mon Oct 29 10:38:00 2018 - [info]     Primary candidate for the new Master (candidate_master is set)
Mon Oct 29 10:38:00 2018 - [info]   192.168.56.210(192.168.56.210:3306)  Version=5.7.23-log (oldest major version between slaves) log-bin:enabled
Mon Oct 29 10:38:00 2018 - [info]     GTID ON
Mon Oct 29 10:38:00 2018 - [info]     Replicating from 192.168.56.100(192.168.56.100:3306)
Mon Oct 29 10:38:00 2018 - [info]     Not candidate for the new Master (no_master is set)
Mon Oct 29 10:38:00 2018 - [info] Checking slave configurations..
Mon Oct 29 10:38:00 2018 - [info] Checking replication filtering settings..
Mon Oct 29 10:38:00 2018 - [info]  Replication filtering check ok.
Mon Oct 29 10:38:00 2018 - [info] Master is down!
Mon Oct 29 10:38:00 2018 - [info] Terminating monitoring script.
Mon Oct 29 10:38:00 2018 - [info] Got exit code 20 (Master dead).
Mon Oct 29 10:38:00 2018 - [info] MHA::MasterFailover version 0.57.
Mon Oct 29 10:38:00 2018 - [info] Starting master failover.
Mon Oct 29 10:38:00 2018 - [info] 
Mon Oct 29 10:38:00 2018 - [info] * Phase 1: Configuration Check Phase..
Mon Oct 29 10:38:00 2018 - [info] 
Mon Oct 29 10:38:01 2018 - [info] GTID failover mode = 1
Mon Oct 29 10:38:01 2018 - [info] Dead Servers:
Mon Oct 29 10:38:01 2018 - [info]   192.168.56.100(192.168.56.100:3306)
Mon Oct 29 10:38:01 2018 - [info] Checking master reachability via MySQL(double check)...
Mon Oct 29 10:38:01 2018 - [info]  ok.
Mon Oct 29 10:38:01 2018 - [info] Alive Servers:
Mon Oct 29 10:38:01 2018 - [info]   192.168.56.200(192.168.56.200:3306)
Mon Oct 29 10:38:01 2018 - [info]   192.168.56.210(192.168.56.210:3306)
Mon Oct 29 10:38:01 2018 - [info] Alive Slaves:
Mon Oct 29 10:38:01 2018 - [info]   192.168.56.200(192.168.56.200:3306)  Version=5.7.23-log (oldest major version between slaves) log-bin:enabled
Mon Oct 29 10:38:01 2018 - [info]     GTID ON
Mon Oct 29 10:38:01 2018 - [info]     Replicating from 192.168.56.100(192.168.56.100:3306)
Mon Oct 29 10:38:01 2018 - [info]     Primary candidate for the new Master (candidate_master is set)
Mon Oct 29 10:38:01 2018 - [info]   192.168.56.210(192.168.56.210:3306)  Version=5.7.23-log (oldest major version between slaves) log-bin:enabled
Mon Oct 29 10:38:01 2018 - [info]     GTID ON
Mon Oct 29 10:38:01 2018 - [info]     Replicating from 192.168.56.100(192.168.56.100:3306)
Mon Oct 29 10:38:01 2018 - [info]     Not candidate for the new Master (no_master is set)
Mon Oct 29 10:38:01 2018 - [info] Starting GTID based failover.
Mon Oct 29 10:38:01 2018 - [info] 
Mon Oct 29 10:38:01 2018 - [info] ** Phase 1: Configuration Check Phase completed.
Mon Oct 29 10:38:01 2018 - [info] 
Mon Oct 29 10:38:01 2018 - [info] * Phase 2: Dead Master Shutdown Phase..
Mon Oct 29 10:38:01 2018 - [info] 
Mon Oct 29 10:38:01 2018 - [info] Forcing shutdown so that applications never connect to the current master..
Mon Oct 29 10:38:01 2018 - [info] Executing master IP deactivation script:
Mon Oct 29 10:38:01 2018 - [info]   /usr/local/bin/master_ip_failover --orig_master_host=192.168.56.100 --orig_master_ip=192.168.56.100 --orig_master_port=3306 --command=stopssh --ssh_user=root  


IN SCRIPT TEST====/sbin/ifconfig eth0:0 down==/sbin/ifconfig eth0:0 192.168.56.111/24 up===

Disabling the VIP on old master: 192.168.56.100 
Mon Oct 29 10:38:01 2018 - [info]  done.
Mon Oct 29 10:38:01 2018 - [warning] shutdown_script is not set. Skipping explicit shutting down of the dead master.
Mon Oct 29 10:38:01 2018 - [info] * Phase 2: Dead Master Shutdown Phase completed.
Mon Oct 29 10:38:01 2018 - [info] 
Mon Oct 29 10:38:01 2018 - [info] * Phase 3: Master Recovery Phase..
Mon Oct 29 10:38:01 2018 - [info] 
Mon Oct 29 10:38:01 2018 - [info] * Phase 3.1: Getting Latest Slaves Phase..
Mon Oct 29 10:38:01 2018 - [info] 
Mon Oct 29 10:38:01 2018 - [info] The latest binary log file/position on all slaves is my3306_binlog.000025:194
Mon Oct 29 10:38:01 2018 - [info] Latest slaves (Slaves that received relay log files to the latest):
Mon Oct 29 10:38:01 2018 - [info]   192.168.56.200(192.168.56.200:3306)  Version=5.7.23-log (oldest major version between slaves) log-bin:enabled
Mon Oct 29 10:38:01 2018 - [info]     GTID ON
Mon Oct 29 10:38:01 2018 - [info]     Replicating from 192.168.56.100(192.168.56.100:3306)
Mon Oct 29 10:38:01 2018 - [info]     Primary candidate for the new Master (candidate_master is set)
Mon Oct 29 10:38:01 2018 - [info]   192.168.56.210(192.168.56.210:3306)  Version=5.7.23-log (oldest major version between slaves) log-bin:enabled
Mon Oct 29 10:38:01 2018 - [info]     GTID ON
Mon Oct 29 10:38:01 2018 - [info]     Replicating from 192.168.56.100(192.168.56.100:3306)
Mon Oct 29 10:38:01 2018 - [info]     Not candidate for the new Master (no_master is set)
Mon Oct 29 10:38:01 2018 - [info] The oldest binary log file/position on all slaves is my3306_binlog.000025:194
Mon Oct 29 10:38:01 2018 - [info] Oldest slaves:
Mon Oct 29 10:38:01 2018 - [info]   192.168.56.200(192.168.56.200:3306)  Version=5.7.23-log (oldest major version between slaves) log-bin:enabled
Mon Oct 29 10:38:01 2018 - [info]     GTID ON
Mon Oct 29 10:38:01 2018 - [info]     Replicating from 192.168.56.100(192.168.56.100:3306)
Mon Oct 29 10:38:01 2018 - [info]     Primary candidate for the new Master (candidate_master is set)
Mon Oct 29 10:38:01 2018 - [info]   192.168.56.210(192.168.56.210:3306)  Version=5.7.23-log (oldest major version between slaves) log-bin:enabled
Mon Oct 29 10:38:01 2018 - [info]     GTID ON
Mon Oct 29 10:38:01 2018 - [info]     Replicating from 192.168.56.100(192.168.56.100:3306)
Mon Oct 29 10:38:01 2018 - [info]     Not candidate for the new Master (no_master is set)
Mon Oct 29 10:38:01 2018 - [info] 
Mon Oct 29 10:38:01 2018 - [info] * Phase 3.3: Determining New Master Phase..
Mon Oct 29 10:38:01 2018 - [info] 
Mon Oct 29 10:38:01 2018 - [info] Searching new master from slaves..
Mon Oct 29 10:38:01 2018 - [info]  Candidate masters from the configuration file:
Mon Oct 29 10:38:01 2018 - [info]   192.168.56.200(192.168.56.200:3306)  Version=5.7.23-log (oldest major version between slaves) log-bin:enabled
Mon Oct 29 10:38:01 2018 - [info]     GTID ON
Mon Oct 29 10:38:01 2018 - [info]     Replicating from 192.168.56.100(192.168.56.100:3306)
Mon Oct 29 10:38:01 2018 - [info]     Primary candidate for the new Master (candidate_master is set)
Mon Oct 29 10:38:01 2018 - [info]  Non-candidate masters:
Mon Oct 29 10:38:01 2018 - [info]   192.168.56.210(192.168.56.210:3306)  Version=5.7.23-log (oldest major version between slaves) log-bin:enabled
Mon Oct 29 10:38:01 2018 - [info]     GTID ON
Mon Oct 29 10:38:01 2018 - [info]     Replicating from 192.168.56.100(192.168.56.100:3306)
Mon Oct 29 10:38:01 2018 - [info]     Not candidate for the new Master (no_master is set)
Mon Oct 29 10:38:01 2018 - [info]  Searching from candidate_master slaves which have received the latest relay log events..
Mon Oct 29 10:38:01 2018 - [info] New master is 192.168.56.200(192.168.56.200:3306)
Mon Oct 29 10:38:01 2018 - [info] Starting master failover..
Mon Oct 29 10:38:01 2018 - [info] 
From:
192.168.56.100(192.168.56.100:3306) (current master)
 +--192.168.56.200(192.168.56.200:3306)
 +--192.168.56.210(192.168.56.210:3306)

To:
192.168.56.200(192.168.56.200:3306) (new master)
 +--192.168.56.210(192.168.56.210:3306)
Mon Oct 29 10:38:01 2018 - [info] 
Mon Oct 29 10:38:01 2018 - [info] * Phase 3.3: New Master Recovery Phase..
Mon Oct 29 10:38:01 2018 - [info] 
Mon Oct 29 10:38:01 2018 - [info]  Waiting all logs to be applied.. 
Mon Oct 29 10:38:01 2018 - [info]   done.
Mon Oct 29 10:38:01 2018 - [info] Getting new master's binlog name and position..
Mon Oct 29 10:38:01 2018 - [info]  my3306_binlog.000015:210
Mon Oct 29 10:38:01 2018 - [info]  All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='192.168.56.200', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='xxx';
Mon Oct 29 10:38:01 2018 - [info] Master Recovery succeeded. File:Pos:Exec_Gtid_Set: my3306_binlog.000015, 210, 7390a401-b705-11e8-9ed9-080027b0b461:1-140922
Mon Oct 29 10:38:01 2018 - [info] Executing master IP activate script:
Mon Oct 29 10:38:01 2018 - [info]   /usr/local/bin/master_ip_failover --command=start --ssh_user=root --orig_master_host=192.168.56.100 --orig_master_ip=192.168.56.100 --orig_master_port=3306 --new_master_host=192.168.56.200 --new_master_ip=192.168.56.200 --new_master_port=3306 --new_master_user='mha_rep'   --new_master_password=xxx
Unknown option: new_master_user
Unknown option: new_master_password


IN SCRIPT TEST====/sbin/ifconfig eth0:0 down==/sbin/ifconfig eth0:0 192.168.56.111/24 up===

Enabling the VIP - 192.168.56.111/24 on the new master - 192.168.56.200 
bash: /usr/bin/arping: No such file or directory
Mon Oct 29 10:38:02 2018 - [info]  OK.
Mon Oct 29 10:38:02 2018 - [info] Setting read_only=0 on 192.168.56.200(192.168.56.200:3306)..
Mon Oct 29 10:38:02 2018 - [info]  ok.
Mon Oct 29 10:38:02 2018 - [info] ** Finished master recovery successfully.
Mon Oct 29 10:38:02 2018 - [info] * Phase 3: Master Recovery Phase completed.
Mon Oct 29 10:38:02 2018 - [info] 
Mon Oct 29 10:38:02 2018 - [info] * Phase 4: Slaves Recovery Phase..
Mon Oct 29 10:38:02 2018 - [info] 
Mon Oct 29 10:38:02 2018 - [info] 
Mon Oct 29 10:38:02 2018 - [info] * Phase 4.1: Starting Slaves in parallel..
Mon Oct 29 10:38:02 2018 - [info] 
Mon Oct 29 10:38:02 2018 - [info] -- Slave recovery on host 192.168.56.210(192.168.56.210:3306) started, pid: 5507. Check tmp log /var/log/masterha/app1/192.168.56.210_3306_20181029103800.log if it takes time..
Mon Oct 29 10:38:03 2018 - [info] 
Mon Oct 29 10:38:03 2018 - [info] Log messages from 192.168.56.210 ...
Mon Oct 29 10:38:03 2018 - [info] 
Mon Oct 29 10:38:02 2018 - [info]  Resetting slave 192.168.56.210(192.168.56.210:3306) and starting replication from the new master 192.168.56.200(192.168.56.200:3306)..
Mon Oct 29 10:38:02 2018 - [info]  Executed CHANGE MASTER.
Mon Oct 29 10:38:02 2018 - [info]  Slave started.
Mon Oct 29 10:38:02 2018 - [info]  gtid_wait(7390a401-b705-11e8-9ed9-080027b0b461:1-140922) completed on 192.168.56.210(192.168.56.210:3306). Executed 0 events.
Mon Oct 29 10:38:03 2018 - [info] End of log messages from 192.168.56.210.
Mon Oct 29 10:38:03 2018 - [info] -- Slave on host 192.168.56.210(192.168.56.210:3306) started.
Mon Oct 29 10:38:03 2018 - [info] All new slave servers recovered successfully.
Mon Oct 29 10:38:03 2018 - [info] 
Mon Oct 29 10:38:03 2018 - [info] * Phase 5: New master cleanup phase..
Mon Oct 29 10:38:03 2018 - [info] 
Mon Oct 29 10:38:03 2018 - [info] Resetting slave info on the new master..
Mon Oct 29 10:38:03 2018 - [info]  192.168.56.200: Resetting slave info succeeded.
Mon Oct 29 10:38:03 2018 - [info] Master failover to 192.168.56.200(192.168.56.200:3306) completed successfully.
Mon Oct 29 10:38:03 2018 - [info] 

----- Failover Report -----

app1: MySQL Master failover 192.168.56.100(192.168.56.100:3306) to 192.168.56.200(192.168.56.200:3306) succeeded

Master 192.168.56.100(192.168.56.100:3306) is down!

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

Started automated(non-interactive) failover.
Invalidated master IP address on 192.168.56.100(192.168.56.100:3306)
Selected 192.168.56.200(192.168.56.200:3306) as a new master.
192.168.56.200(192.168.56.200:3306): OK: Applying all logs succeeded.
192.168.56.200(192.168.56.200:3306): OK: Activated master IP address.
192.168.56.210(192.168.56.210:3306): OK: Slave started, replicating from 192.168.56.200(192.168.56.200:3306)
192.168.56.200(192.168.56.200:3306): Resetting slave info succeeded.
Master failover to 192.168.56.200(192.168.56.200:3306) completed successfully.

（3）查看vip是否漂移到mysqldb2上

# ifconfig
eth0: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 192.168.56.200  netmask 255.255.255.0  broadcast 192.168.56.255
...

eth0:0: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 192.168.56.111  netmask 255.255.255.0  broadcast 192.168.56.255
        ether 08:00:27:5b:8a:9a  txqueuelen 1000  (Ethernet)
...

3.2 mysqldb1重新加入集群

一旦发生MHA切换，管理进程（Manager）将会退出，无法进行再次测试，需将故障数据库解决掉之后，重新change加入到MHA环境中来，并且要保证app1.failover.complete不存在或则加上--ignore_last_failover参数忽略，才能再次开启管理进程。

# mysqld --defaults-file=/etc/my3306.cnf &

mysql> CHANGE MASTER TO master_host='192.168.56.200',
 master_port=3306,
 master_user='repl',
 master_password='wanbin',
 master_auto_position=1;

mysql> start slave;

3.3 masterha_master_switch工具进行主从切换

（1）查看mha状态

在手动切换的同时需要保证没有启用MHA自动切换功能。

# masterha_check_status --conf=/etc/masterha/app1.cnf
app1 is stopped(2:NOT_RUNNING).

（2） 手工切换

# masterha_master_switch --conf=/etc/masterha/app1.cnf --master_state=alive --new_master_host=192.168.56.100 --orig_master_is_new_slave

#参数解释
--master_state：代表当前主库的状态为alive
--new_master_host：代表切换后新主库为192.168.56.100
--orig_master_is_new_slave：将原来的主库变更为slave节点。

Mon Oct 29 13:38:09 2018 - [info] MHA::MasterRotate version 0.57.
Mon Oct 29 13:38:09 2018 - [info] Starting online master switch..
Mon Oct 29 13:38:09 2018 - [info] 
Mon Oct 29 13:38:09 2018 - [info] * Phase 1: Configuration Check Phase..
Mon Oct 29 13:38:09 2018 - [info] 
Mon Oct 29 13:38:09 2018 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Mon Oct 29 13:38:09 2018 - [info] Reading application default configuration from /etc/masterha/app1.cnf..
Mon Oct 29 13:38:09 2018 - [info] Reading server configuration from /etc/masterha/app1.cnf..
Mon Oct 29 13:38:10 2018 - [info] GTID failover mode = 1
Mon Oct 29 13:38:10 2018 - [info] Current Alive Master: 192.168.56.200(192.168.56.200:3306)
Mon Oct 29 13:38:10 2018 - [info] Alive Slaves:
Mon Oct 29 13:38:10 2018 - [info]   192.168.56.100(192.168.56.100:3306)  Version=5.7.23-log (oldest major version between slaves) log-bin:enabled
Mon Oct 29 13:38:10 2018 - [info]     GTID ON
Mon Oct 29 13:38:10 2018 - [info]     Replicating from 192.168.56.200(192.168.56.200:3306)
Mon Oct 29 13:38:10 2018 - [info]     Primary candidate for the new Master (candidate_master is set)
Mon Oct 29 13:38:10 2018 - [info]   192.168.56.210(192.168.56.210:3306)  Version=5.7.23-log (oldest major version between slaves) log-bin:enabled
Mon Oct 29 13:38:10 2018 - [info]     GTID ON
Mon Oct 29 13:38:10 2018 - [info]     Replicating from 192.168.56.200(192.168.56.200:3306)
Mon Oct 29 13:38:10 2018 - [info]     Not candidate for the new Master (no_master is set)

It is better to execute FLUSH NO_WRITE_TO_BINLOG TABLES on the master before switching. Is it ok to execute on 192.168.56.200(192.168.56.200:3306)? (YES/no): YES     
Mon Oct 29 13:38:19 2018 - [info] Executing FLUSH NO_WRITE_TO_BINLOG TABLES. This may take long time..
Mon Oct 29 13:38:19 2018 - [info]  ok.
Mon Oct 29 13:38:19 2018 - [info] Checking MHA is not monitoring or doing failover..
Mon Oct 29 13:38:19 2018 - [info] Checking replication health on 192.168.56.100..
Mon Oct 29 13:38:19 2018 - [info]  ok.
Mon Oct 29 13:38:19 2018 - [info] Checking replication health on 192.168.56.210..
Mon Oct 29 13:38:19 2018 - [info]  ok.
Mon Oct 29 13:38:19 2018 - [info] 192.168.56.100 can be new master.
Mon Oct 29 13:38:19 2018 - [info] 
From:
192.168.56.200(192.168.56.200:3306) (current master)
 +--192.168.56.100(192.168.56.100:3306)
 +--192.168.56.210(192.168.56.210:3306)

To:
192.168.56.100(192.168.56.100:3306) (new master)
 +--192.168.56.210(192.168.56.210:3306)
 +--192.168.56.200(192.168.56.200:3306)

Starting master switch from 192.168.56.200(192.168.56.200:3306) to 192.168.56.100(192.168.56.100:3306)? (yes/NO): yes
Mon Oct 29 13:38:28 2018 - [info] Checking whether 192.168.56.100(192.168.56.100:3306) is ok for the new master..
Mon Oct 29 13:38:28 2018 - [info]  ok.
Mon Oct 29 13:38:28 2018 - [info] 192.168.56.200(192.168.56.200:3306): SHOW SLAVE STATUS returned empty result. To check replication filtering rules, temporarily executing CHANGE MASTER to a dummy host.
Mon Oct 29 13:38:28 2018 - [info] 192.168.56.200(192.168.56.200:3306): Resetting slave pointing to the dummy host.
Mon Oct 29 13:38:28 2018 - [info] ** Phase 1: Configuration Check Phase completed.
Mon Oct 29 13:38:28 2018 - [info] 
Mon Oct 29 13:38:28 2018 - [info] * Phase 2: Rejecting updates Phase..
Mon Oct 29 13:38:28 2018 - [info] 
master_ip_online_change_script is not defined. If you do not disable writes on the current master manually, applications keep writing on the current master. Is it ok to proceed? (yes/NO): yes
Mon Oct 29 13:38:31 2018 - [info] Locking all tables on the orig master to reject updates from everybody (including root):
Mon Oct 29 13:38:31 2018 - [info] Executing FLUSH TABLES WITH READ LOCK..
Mon Oct 29 13:38:31 2018 - [info]  ok.
Mon Oct 29 13:38:31 2018 - [info] Orig master binlog:pos is my3306_binlog.000016:250.
Mon Oct 29 13:38:31 2018 - [info]  Waiting to execute all relay logs on 192.168.56.100(192.168.56.100:3306)..
Mon Oct 29 13:38:31 2018 - [info]  master_pos_wait(my3306_binlog.000016:250) completed on 192.168.56.100(192.168.56.100:3306). Executed 0 events.
Mon Oct 29 13:38:31 2018 - [info]   done.
Mon Oct 29 13:38:31 2018 - [info] Getting new master's binlog name and position..
Mon Oct 29 13:38:31 2018 - [info]  my3306_binlog.000026:598
Mon Oct 29 13:38:31 2018 - [info]  All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='192.168.56.100', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='xxx';
Mon Oct 29 13:38:31 2018 - [info] 
Mon Oct 29 13:38:31 2018 - [info] * Switching slaves in parallel..
Mon Oct 29 13:38:31 2018 - [info] 
Mon Oct 29 13:38:31 2018 - [info] -- Slave switch on host 192.168.56.210(192.168.56.210:3306) started, pid: 12548
Mon Oct 29 13:38:31 2018 - [info] 
Mon Oct 29 13:38:32 2018 - [info] Log messages from 192.168.56.210 ...
Mon Oct 29 13:38:32 2018 - [info] 
Mon Oct 29 13:38:31 2018 - [info]  Waiting to execute all relay logs on 192.168.56.210(192.168.56.210:3306)..
Mon Oct 29 13:38:31 2018 - [info]  master_pos_wait(my3306_binlog.000016:250) completed on 192.168.56.210(192.168.56.210:3306). Executed 0 events.
Mon Oct 29 13:38:31 2018 - [info]   done.
Mon Oct 29 13:38:31 2018 - [info]  Resetting slave 192.168.56.210(192.168.56.210:3306) and starting replication from the new master 192.168.56.100(192.168.56.100:3306)..
Mon Oct 29 13:38:31 2018 - [info]  Executed CHANGE MASTER.
Mon Oct 29 13:38:31 2018 - [info]  Slave started.
Mon Oct 29 13:38:32 2018 - [info] End of log messages from 192.168.56.210 ...
Mon Oct 29 13:38:32 2018 - [info] 
Mon Oct 29 13:38:32 2018 - [info] -- Slave switch on host 192.168.56.210(192.168.56.210:3306) succeeded.
Mon Oct 29 13:38:32 2018 - [info] Unlocking all tables on the orig master:
Mon Oct 29 13:38:32 2018 - [info] Executing UNLOCK TABLES..
Mon Oct 29 13:38:32 2018 - [info]  ok.
Mon Oct 29 13:38:32 2018 - [info] Starting orig master as a new slave..
Mon Oct 29 13:38:32 2018 - [info]  Resetting slave 192.168.56.200(192.168.56.200:3306) and starting replication from the new master 192.168.56.100(192.168.56.100:3306)..
Mon Oct 29 13:38:32 2018 - [info]  Executed CHANGE MASTER.
Mon Oct 29 13:38:33 2018 - [info]  Slave started.
Mon Oct 29 13:38:33 2018 - [info] All new slave servers switched successfully.
Mon Oct 29 13:38:33 2018 - [info] 
Mon Oct 29 13:38:33 2018 - [info] * Phase 5: New master cleanup phase..
Mon Oct 29 13:38:33 2018 - [info] 
Mon Oct 29 13:38:33 2018 - [info]  192.168.56.100: Resetting slave info succeeded.
Mon Oct 29 13:38:33 2018 - [info] Switching master to 192.168.56.100(192.168.56.100:3306) completed successfully.

手工切换后，vip没有自动漂移到mysqldb1上，因为还没设置master_ip_online_change脚本。

（3）编辑master_ip_online_change脚本

# cp /opt/mha4mysql-manager-master/samples/scripts/master_ip_online_change /usr/local/bin/master_ip_online_change 

# vi  /usr/local/bin/master_ip_online_change

#!/usr/bin/env perl

use strict;
use warnings FATAL => 'all';

use Getopt::Long;
use MHA::DBHelper;
use MHA::NodeUtil;
use Time::HiRes qw( sleep gettimeofday tv_interval );
use Data::Dumper;

my $_tstart;
my $_running_interval = 0.1;
my (
  $command,          
  $orig_master_is_new_slave, $orig_master_host, $orig_master_ip,  $orig_master_port, $orig_master_user,    $orig_master_password, $orig_master_ssh_user,
  $new_master_host,          $new_master_ip,    $new_master_port, $new_master_user,  $new_master_password, $new_master_ssh_user,
);

my $vip = '192.168.56.111/24';
my $key = '0';
my $ssh_start_vip = "/sbin/ifconfig eth0:$key $vip up";
my $ssh_stop_vip  = "/sbin/ifconfig eth0:$key down";
my $ssh_Bcast_arp = "/usr/bin/arping -c 3 -A 192.168.56.111";

GetOptions(
  'command=s'                => \$command,
  'orig_master_is_new_slave' => \$orig_master_is_new_slave,
  'orig_master_host=s'       => \$orig_master_host,
  'orig_master_ip=s'         => \$orig_master_ip,
  'orig_master_port=i'       => \$orig_master_port,
  'orig_master_user=s'       => \$orig_master_user,
  'orig_master_password=s'   => \$orig_master_password,
  'orig_master_ssh_user=s'   => \$orig_master_ssh_user,
  'new_master_host=s'        => \$new_master_host,
  'new_master_ip=s'          => \$new_master_ip,
  'new_master_port=i'        => \$new_master_port,
  'new_master_user=s'        => \$new_master_user,
  'new_master_password=s'    => \$new_master_password,
  'new_master_ssh_user=s'    => \$new_master_ssh_user,
);

exit &main();

sub current_time_us {
  my ( $sec, $microsec ) = gettimeofday();
  my $curdate = localtime($sec);
  return $curdate . " " . sprintf( "%06d", $microsec );
}

sub sleep_until {
  my $elapsed = tv_interval($_tstart);
  if ( $_running_interval > $elapsed ) {
    sleep( $_running_interval - $elapsed );
  }
}

sub get_threads_util {
  my $dbh                    = shift;
  my $my_connection_id       = shift;
  my $running_time_threshold = shift;
  my $type                   = shift;
  $running_time_threshold = 0 unless ($running_time_threshold);
  $type                   = 0 unless ($type);
  my @threads;

  my $sth = $dbh->prepare("SHOW PROCESSLIST");
  $sth->execute();

  while ( my $ref = $sth->fetchrow_hashref() ) {
    my $id         = $ref->{Id};
    my $user       = $ref->{User};
    my $host       = $ref->{Host};
    my $command    = $ref->{Command};
    my $state      = $ref->{State};
    my $query_time = $ref->{Time};
    my $info       = $ref->{Info};
    $info =~ s/^\s*(.*?)\s*$/$1/ if defined($info);
    next if ( $my_connection_id == $id );
    next if ( defined($query_time) && $query_time < $running_time_threshold );
    next if ( defined($command)    && $command eq "Binlog Dump" );
    next if ( defined($user)       && $user eq "system user" );
    next
      if ( defined($command)
      && $command eq "Sleep"
      && defined($query_time)
      && $query_time >= 1 );

    if ( $type >= 1 ) {
      next if ( defined($command) && $command eq "Sleep" );
      next if ( defined($command) && $command eq "Connect" );
    }

    if ( $type >= 2 ) {
      next if ( defined($info) && $info =~ m/^select/i );
      next if ( defined($info) && $info =~ m/^show/i );
    }

    push @threads, $ref;
  }
  return @threads;
}

sub main {
  if ( $command eq "stop" ) {
    ## Gracefully killing connections on the current master
    # 1. Set read_only= 1 on the new master
    # 2. DROP USER so that no app user can establish new connections
    # 3. Set read_only= 1 on the current master
    # 4. Kill current queries
    # * Any database access failure will result in script die.
    my $exit_code = 1;
    eval {
      ## Setting read_only=1 on the new master (to avoid accident)
      my $new_master_handler = new MHA::DBHelper();

      # args: hostname, port, user, password, raise_error(die_on_error)_or_not
      $new_master_handler->connect( $new_master_ip, $new_master_port,
        $new_master_user, $new_master_password, 1 );
      print current_time_us() . " Set read_only on the new master.. ";
      $new_master_handler->enable_read_only();
      if ( $new_master_handler->is_read_only() ) {
        print "ok.\n";
      }
      else {
        die "Failed!\n";
      }
      $new_master_handler->disconnect();

      # Connecting to the orig master, die if any database error happens
      my $orig_master_handler = new MHA::DBHelper();
      $orig_master_handler->connect( $orig_master_ip, $orig_master_port,
        $orig_master_user, $orig_master_password, 1 );

      ## Drop application user so that nobody can connect. Disabling per-session binlog beforehand
      #$orig_master_handler->disable_log_bin_local();
      #print current_time_us() . " Drpping app user on the orig master..\n";
      #FIXME_xxx_drop_app_user($orig_master_handler);

      ## Waiting for N * 100 milliseconds so that current connections can exit
      my $time_until_read_only = 15;
      $_tstart = [gettimeofday];
      my @threads = get_threads_util( $orig_master_handler->{dbh},
        $orig_master_handler->{connection_id} );
      while ( $time_until_read_only > 0 && $#threads >= 0 ) {
        if ( $time_until_read_only % 5 == 0 ) {
          printf
"%s Waiting all running %d threads are disconnected.. (max %d milliseconds)\n",
            current_time_us(), $#threads + 1, $time_until_read_only * 100;
          if ( $#threads < 5 ) {
            print Data::Dumper->new( [$_] )->Indent(0)->Terse(1)->Dump . "\n"
              foreach (@threads);
          }
        }
        sleep_until();
        $_tstart = [gettimeofday];
        $time_until_read_only--;
        @threads = get_threads_util( $orig_master_handler->{dbh},
          $orig_master_handler->{connection_id} );
      }

      ## Setting read_only=1 on the current master so that nobody(except SUPER) can write
      print current_time_us() . " Set read_only=1 on the orig master.. ";
      $orig_master_handler->enable_read_only();
      if ( $orig_master_handler->is_read_only() ) {
        print "ok.\n";
      }
      else {
        die "Failed!\n";
      }

      ## Waiting for M * 100 milliseconds so that current update queries can complete
      my $time_until_kill_threads = 5;
      @threads = get_threads_util( $orig_master_handler->{dbh},
        $orig_master_handler->{connection_id} );
      while ( $time_until_kill_threads > 0 && $#threads >= 0 ) {
        if ( $time_until_kill_threads % 5 == 0 ) {
          printf
"%s Waiting all running %d queries are disconnected.. (max %d milliseconds)\n",
            current_time_us(), $#threads + 1, $time_until_kill_threads * 100;
          if ( $#threads < 5 ) {
            print Data::Dumper->new( [$_] )->Indent(0)->Terse(1)->Dump . "\n"
              foreach (@threads);
          }
        }
        sleep_until();
        $_tstart = [gettimeofday];
        $time_until_kill_threads--;
        @threads = get_threads_util( $orig_master_handler->{dbh},
          $orig_master_handler->{connection_id} );
      }



                print "Disabling the VIP on old master: $orig_master_host \n";
                &stop_vip();     


      ## Terminating all threads
      print current_time_us() . " Killing all application threads..\n";
      $orig_master_handler->kill_threads(@threads) if ( $#threads >= 0 );
      print current_time_us() . " done.\n";
      #$orig_master_handler->enable_log_bin_local();
      $orig_master_handler->disconnect();

      ## After finishing the script, MHA executes FLUSH TABLES WITH READ LOCK
      $exit_code = 0;
    };
    if ($@) {
      warn "Got Error: $@\n";
      exit $exit_code;
    }
    exit $exit_code;
  }
  elsif ( $command eq "start" ) {
    ## Activating master ip on the new master
    # 1. Create app user with write privileges
    # 2. Moving backup script if needed
    # 3. Register new master's ip to the catalog database

# We don't return error even though activating updatable accounts/ip failed so that we don't interrupt slaves' recovery.
# If exit code is 0 or 10, MHA does not abort
    my $exit_code = 10;
    eval {
      my $new_master_handler = new MHA::DBHelper();

      # args: hostname, port, user, password, raise_error_or_not
      $new_master_handler->connect( $new_master_ip, $new_master_port,
        $new_master_user, $new_master_password, 1 );

      ## Set read_only=0 on the new master
      #$new_master_handler->disable_log_bin_local();
      print current_time_us() . " Set read_only=0 on the new master.\n";
      $new_master_handler->disable_read_only();

      ## Creating an app user on the new master
      #print current_time_us() . " Creating app user on the new master..\n";
      #FIXME_xxx_create_app_user($new_master_handler);
      #$new_master_handler->enable_log_bin_local();
      $new_master_handler->disconnect();

      ## Update master ip on the catalog database, etc
                print "Enabling the VIP - $vip on the new master - $new_master_host \n";
                &start_vip();
                $exit_code = 0;
    };
    if ($@) {
      warn "Got Error: $@\n";
      exit $exit_code;
    }
    exit $exit_code;
  }
  elsif ( $command eq "status" ) {

    # do nothing
    exit 0;
  }
  else {
    &usage();
    exit 1;
  }
}

# A simple system call that enable the VIP on the new master 
sub start_vip() {
    `ssh $new_master_ssh_user\@$new_master_host \" $ssh_start_vip \"`;
}

sub start_arp() {
    `ssh $new_master_ssh_user\@$new_master_host \" $ssh_Bcast_arp \"`;
}
# A simple system call that disable the VIP on the old_master
sub stop_vip() {
    `ssh $orig_master_ssh_user\@$orig_master_host \" $ssh_stop_vip \"`;
}


sub usage {
  print
"Usage: master_ip_online_change --command=start|stop|status --orig_master_host=host --orig_master_ip=ip --orig_master_port=port --orig_master_user=user --orig_master_password=password --orig_master_ssh_user=sshuser --new_master_host=host --new_master_ip=ip --new_master_port=port --new_master_user=user --new_master_password=password --new_master_ssh_user=sshuser \n";
  die;
}

（4）再次切换，观察vip是否迁移到mysqldb2上

# masterha_master_switch --conf=/etc/masterha/app1.cnf --master_state=alive --new_master_host=192.168.56.200 --orig_master_is_new_slave
Mon Oct 29 14:41:52 2018 - [info] MHA::MasterRotate version 0.57.
Mon Oct 29 14:41:52 2018 - [info] Starting online master switch..
Mon Oct 29 14:41:52 2018 - [info] 
Mon Oct 29 14:41:52 2018 - [info] * Phase 1: Configuration Check Phase..
Mon Oct 29 14:41:52 2018 - [info] 
Mon Oct 29 14:41:52 2018 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Mon Oct 29 14:41:52 2018 - [info] Reading application default configuration from /etc/masterha/app1.cnf..
Mon Oct 29 14:41:52 2018 - [info] Reading server configuration from /etc/masterha/app1.cnf..
Mon Oct 29 14:41:54 2018 - [info] GTID failover mode = 1
Mon Oct 29 14:41:54 2018 - [info] Current Alive Master: 192.168.56.100(192.168.56.100:3306)
Mon Oct 29 14:41:54 2018 - [info] Alive Slaves:
Mon Oct 29 14:41:54 2018 - [info]   192.168.56.200(192.168.56.200:3306)  Version=5.7.23-log (oldest major version between slaves) log-bin:enabled
Mon Oct 29 14:41:54 2018 - [info]     GTID ON
Mon Oct 29 14:41:54 2018 - [info]     Replicating from 192.168.56.100(192.168.56.100:3306)
Mon Oct 29 14:41:54 2018 - [info]     Primary candidate for the new Master (candidate_master is set)
Mon Oct 29 14:41:54 2018 - [info]   192.168.56.210(192.168.56.210:3306)  Version=5.7.23-log (oldest major version between slaves) log-bin:enabled
Mon Oct 29 14:41:54 2018 - [info]     GTID ON
Mon Oct 29 14:41:54 2018 - [info]     Replicating from 192.168.56.100(192.168.56.100:3306)
Mon Oct 29 14:41:54 2018 - [info]     Not candidate for the new Master (no_master is set)

It is better to execute FLUSH NO_WRITE_TO_BINLOG TABLES on the master before switching. Is it ok to execute on 192.168.56.100(192.168.56.100:3306)? (YES/no): yes
Mon Oct 29 14:41:55 2018 - [info] Executing FLUSH NO_WRITE_TO_BINLOG TABLES. This may take long time..
Mon Oct 29 14:41:55 2018 - [info]  ok.
Mon Oct 29 14:41:55 2018 - [info] Checking MHA is not monitoring or doing failover..
Mon Oct 29 14:41:55 2018 - [info] Checking replication health on 192.168.56.200..
Mon Oct 29 14:41:55 2018 - [info]  ok.
Mon Oct 29 14:41:55 2018 - [info] Checking replication health on 192.168.56.210..
Mon Oct 29 14:41:55 2018 - [info]  ok.
Mon Oct 29 14:41:55 2018 - [info] 192.168.56.200 can be new master.
Mon Oct 29 14:41:55 2018 - [info] 
From:
192.168.56.100(192.168.56.100:3306) (current master)
 +--192.168.56.200(192.168.56.200:3306)
 +--192.168.56.210(192.168.56.210:3306)

To:
192.168.56.200(192.168.56.200:3306) (new master)
 +--192.168.56.210(192.168.56.210:3306)
 +--192.168.56.100(192.168.56.100:3306)

Starting master switch from 192.168.56.100(192.168.56.100:3306) to 192.168.56.200(192.168.56.200:3306)? (yes/NO): yes
Mon Oct 29 14:41:57 2018 - [info] Checking whether 192.168.56.200(192.168.56.200:3306) is ok for the new master..
Mon Oct 29 14:41:57 2018 - [info]  ok.
Mon Oct 29 14:41:57 2018 - [info] 192.168.56.100(192.168.56.100:3306): SHOW SLAVE STATUS returned empty result. To check replication filtering rules, temporarily executing CHANGE MASTER to a dummy host.
Mon Oct 29 14:41:57 2018 - [info] 192.168.56.100(192.168.56.100:3306): Resetting slave pointing to the dummy host.
Mon Oct 29 14:41:57 2018 - [info] ** Phase 1: Configuration Check Phase completed.
Mon Oct 29 14:41:57 2018 - [info] 
Mon Oct 29 14:41:57 2018 - [info] * Phase 2: Rejecting updates Phase..
Mon Oct 29 14:41:57 2018 - [info] 
Mon Oct 29 14:41:57 2018 - [info] Executing master ip online change script to disable write on the current master:
Mon Oct 29 14:41:57 2018 - [info]   /usr/local/bin/master_ip_online_change --command=stop --orig_master_host=192.168.56.100 --orig_master_ip=192.168.56.100 --orig_master_port=3306 --orig_master_user='mha_rep' --new_master_host=192.168.56.200 --new_master_ip=192.168.56.200 --new_master_port=3306 --new_master_user='mha_rep' --orig_master_ssh_user=root --new_master_ssh_user=root   --orig_master_is_new_slave --orig_master_password=xxx --new_master_password=xxx
Mon Oct 29 14:41:57 2018 500045 Set read_only on the new master.. ok.
Mon Oct 29 14:41:57 2018 516171 Waiting all running 2 threads are disconnected.. (max 1500 milliseconds)
{'Time' => '41','db' => undef,'Id' => '56','User' => 'repl','State' => 'Master has sent all binlog to slave; waiting for more updates','Command' => 'Binlog Dump GTID','Info' => undef,'Host' => 'mysqldb3:47840'}
{'Time' => '40','db' => undef,'Id' => '57','User' => 'repl','State' => 'Master has sent all binlog to slave; waiting for more updates','Command' => 'Binlog Dump GTID','Info' => undef,'Host' => 'mysqldb2:54496'}
Mon Oct 29 14:41:58 2018 025934 Waiting all running 2 threads are disconnected.. (max 1000 milliseconds)
{'Time' => '42','db' => undef,'Id' => '56','User' => 'repl','State' => 'Master has sent all binlog to slave; waiting for more updates','Command' => 'Binlog Dump GTID','Info' => undef,'Host' => 'mysqldb3:47840'}
{'Time' => '41','db' => undef,'Id' => '57','User' => 'repl','State' => 'Master has sent all binlog to slave; waiting for more updates','Command' => 'Binlog Dump GTID','Info' => undef,'Host' => 'mysqldb2:54496'}
Mon Oct 29 14:41:58 2018 530397 Waiting all running 2 threads are disconnected.. (max 500 milliseconds)
{'Time' => '42','db' => undef,'Id' => '56','User' => 'repl','State' => 'Master has sent all binlog to slave; waiting for more updates','Command' => 'Binlog Dump GTID','Info' => undef,'Host' => 'mysqldb3:47840'}
{'Time' => '41','db' => undef,'Id' => '57','User' => 'repl','State' => 'Master has sent all binlog to slave; waiting for more updates','Command' => 'Binlog Dump GTID','Info' => undef,'Host' => 'mysqldb2:54496'}
Mon Oct 29 14:41:59 2018 032085 Set read_only=1 on the orig master.. ok.
Mon Oct 29 14:41:59 2018 041306 Waiting all running 2 queries are disconnected.. (max 500 milliseconds)
{'Time' => '43','db' => undef,'Id' => '56','User' => 'repl','State' => 'Master has sent all binlog to slave; waiting for more updates','Command' => 'Binlog Dump GTID','Info' => undef,'Host' => 'mysqldb3:47840'}
{'Time' => '42','db' => undef,'Id' => '57','User' => 'repl','State' => 'Master has sent all binlog to slave; waiting for more updates','Command' => 'Binlog Dump GTID','Info' => undef,'Host' => 'mysqldb2:54496'}
Disabling the VIP on old master: 192.168.56.100 
Mon Oct 29 14:41:59 2018 852808 Killing all application threads..
Mon Oct 29 14:41:59 2018 869546 done.
Mon Oct 29 14:41:59 2018 - [info]  ok.
Mon Oct 29 14:41:59 2018 - [info] Locking all tables on the orig master to reject updates from everybody (including root):
Mon Oct 29 14:41:59 2018 - [info] Executing FLUSH TABLES WITH READ LOCK..
Mon Oct 29 14:41:59 2018 - [info]  ok.
Mon Oct 29 14:41:59 2018 - [info] Orig master binlog:pos is my3306_binlog.000026:598.
Mon Oct 29 14:41:59 2018 - [info]  Waiting to execute all relay logs on 192.168.56.200(192.168.56.200:3306)..
Mon Oct 29 14:41:59 2018 - [info]  master_pos_wait(my3306_binlog.000026:598) completed on 192.168.56.200(192.168.56.200:3306). Executed 0 events.
Mon Oct 29 14:41:59 2018 - [info]   done.
Mon Oct 29 14:41:59 2018 - [info] Getting new master's binlog name and position..
Mon Oct 29 14:41:59 2018 - [info]  my3306_binlog.000016:250
Mon Oct 29 14:41:59 2018 - [info]  All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='192.168.56.200', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='xxx';
Mon Oct 29 14:41:59 2018 - [info] Executing master ip online change script to allow write on the new master:
Mon Oct 29 14:41:59 2018 - [info]   /usr/local/bin/master_ip_online_change --command=start --orig_master_host=192.168.56.100 --orig_master_ip=192.168.56.100 --orig_master_port=3306 --orig_master_user='mha_rep' --new_master_host=192.168.56.200 --new_master_ip=192.168.56.200 --new_master_port=3306 --new_master_user='mha_rep' --orig_master_ssh_user=root --new_master_ssh_user=root   --orig_master_is_new_slave --orig_master_password=xxx --new_master_password=xxx
Mon Oct 29 14:42:00 2018 046752 Set read_only=0 on the new master.
Enabling the VIP - 192.168.56.111/24 on the new master - 192.168.56.200 
Mon Oct 29 14:42:00 2018 - [info]  ok.
Mon Oct 29 14:42:00 2018 - [info] 
Mon Oct 29 14:42:00 2018 - [info] * Switching slaves in parallel..
Mon Oct 29 14:42:00 2018 - [info] 
Mon Oct 29 14:42:00 2018 - [info] -- Slave switch on host 192.168.56.210(192.168.56.210:3306) started, pid: 13737
Mon Oct 29 14:42:00 2018 - [info] 
Mon Oct 29 14:42:01 2018 - [info] Log messages from 192.168.56.210 ...
Mon Oct 29 14:42:01 2018 - [info] 
Mon Oct 29 14:42:00 2018 - [info]  Waiting to execute all relay logs on 192.168.56.210(192.168.56.210:3306)..
Mon Oct 29 14:42:00 2018 - [info]  master_pos_wait(my3306_binlog.000026:598) completed on 192.168.56.210(192.168.56.210:3306). Executed 0 events.
Mon Oct 29 14:42:00 2018 - [info]   done.
Mon Oct 29 14:42:00 2018 - [info]  Resetting slave 192.168.56.210(192.168.56.210:3306) and starting replication from the new master 192.168.56.200(192.168.56.200:3306)..
Mon Oct 29 14:42:00 2018 - [info]  Executed CHANGE MASTER.
Mon Oct 29 14:42:00 2018 - [info]  Slave started.
Mon Oct 29 14:42:01 2018 - [info] End of log messages from 192.168.56.210 ...
Mon Oct 29 14:42:01 2018 - [info] 
Mon Oct 29 14:42:01 2018 - [info] -- Slave switch on host 192.168.56.210(192.168.56.210:3306) succeeded.
Mon Oct 29 14:42:01 2018 - [info] Unlocking all tables on the orig master:
Mon Oct 29 14:42:01 2018 - [info] Executing UNLOCK TABLES..
Mon Oct 29 14:42:01 2018 - [info]  ok.
Mon Oct 29 14:42:01 2018 - [info] Starting orig master as a new slave..
Mon Oct 29 14:42:01 2018 - [info]  Resetting slave 192.168.56.100(192.168.56.100:3306) and starting replication from the new master 192.168.56.200(192.168.56.200:3306)..
Mon Oct 29 14:42:01 2018 - [info]  Executed CHANGE MASTER.
Mon Oct 29 14:42:01 2018 - [info]  Slave started.
Mon Oct 29 14:42:01 2018 - [info] All new slave servers switched successfully.
Mon Oct 29 14:42:01 2018 - [info] 
Mon Oct 29 14:42:01 2018 - [info] * Phase 5: New master cleanup phase..
Mon Oct 29 14:42:01 2018 - [info] 
Mon Oct 29 14:42:01 2018 - [info]  192.168.56.200: Resetting slave info succeeded.
Mon Oct 29 14:42:01 2018 - [info] Switching master to 192.168.56.200(192.168.56.200:3306) completed successfully.



# ifconfig
eth0: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 192.168.56.200  netmask 255.255.255.0  broadcast 192.168.56.255
...

eth0:0: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 192.168.56.111  netmask 255.255.255.0  broadcast 192.168.56.255
        ether 08:00:27:5b:8a:9a  txqueuelen 1000  (Ethernet)
..
DB-Engineer
关注
专栏目录
MHA安装包（manager+node+依赖包+整套mha系统安装文档）
07-12
MHA（Master High Availability）目前在MySQL高可用方面是一个相对成熟的解决方案，是一套优秀的作为MySQL高可用性环境下故障切换和主从提升的高可用软件。在MySQL故障切换过程中，MHA能做到在0~30秒之内自动完成数据库的故障切换操作，并且在进行故障切换的过程中，MHA能在最大程度上保证数据的一致性，以达到真正意义上的高可用。
MHA状况检查
cnktbkanb141909486的博客
11-20 258
环境： Master：192.168.100.50 Slave1：192.168.100.48 Slave2：192.168.100.141 （MHA管理节点） +++检查SSH的配置 [root@test11g ...
[医学影像知识] 医学体数据.mha文件，如何存取元信息?
最新发布
inter_peng的专栏
09-24 353
在之前的博文中，已经写过如果利用simpleITK, 来生成一个包含一个序列内全部像素信息的.mha文件。这里不再赘述。假设我们已经有一个之前生成好的.mha文件。那么我们首先读取这个.mha文件，看看它除了体数据，还有哪些信息。以下是一段, 基于SimpleITK读取一个已经存在.mha的文件，并且读取它已知的元信息的代码.然后读取这几个Key值时，输出的结果却是UNKNOWN_PRINT_CHARACTERISTICS.
mysql mha官网下载_mysql MHA 及多主复制
weixin_31080559的博客
01-19 1234
一.MHA(主节点高可用)二.多主复制：原理：多个msyql/mariadb之间可以实时同步，任意节点的操作可以立即同步到其他节点，底层采用galera插件同步，类似rsync，上层mysql相对于galera是透明的，可以实现多节点同时读写(无法实现读写分离)。NOTE:普通的msyql/mariadb无法集成galera，要想使用galera必须编译msyql/mariadb让其拥有调用gal...
MHA---Manager工具包工具
auo67284的博客
11-09 550
1、检查MHA Manager到所有MHA Node的SSH连接状态 [root@mha ~]# masterha_check_ssh --conf=/etc/masterha/app1.cnf Thu Nov 9 11:01:51 2017 - [info] Reading default configuration from /etc/masterha_default.c...
MySQL MHA源码解析
weixin_33775582的博客
10-12 209
MySQL的高可用方案很多，MHA算是其中最流行的一种方案之一。目前最新的版本是0.57，它分为两部分，一部分水himanager，另外一部分是node. 了解学习一个开源项目，阅读源码是一个很不错的开始，所以MHA就成为了我学习的一个重点内容。我们先从manager开始，当然因为这个开源项目是基于perl开...
mha依赖安装包.rar
06-22
MySQL高可用性解决方案MHA（Master-HA，MySQL High Availability）是一个开源工具，用于在MySQL主从复制环境中实现高可用性和故障切换。MHA由Yoshinori Natsumo开发，它可以在检测到主服务器故障时自动将一个从...
MHA-MySQL DBA运维部署手册包含离线安装文件
08-30
MySQL数据库是广泛应用于互联网行业的关系型数据库管理系统，而MHA（Master High Availability）则是针对MySQL主从复制架构的一种高可用性解决方案。MHA主要用于在主库出现故障时，能够快速无损地切换到备用服务器，...
mha4mysql安装包.zip
07-16
**MySQL高可用性解决方案——MHA(Master High Availability)** MySQL是全球最受欢迎的开源数据库系统之一，但在高可用性方面，单个实例的故障可能导致服务中断。为了应对这一挑战，MySQL社区开发了MHA（Master High...
mha-0.57.zip
05-20
【标题】"mha-0.57.zip" 提供的是 MySQL 高可用性解决方案 MHA（Master High Availability）的两个关键组件的软件包。这个压缩包包含的是 "mha4mysql-node-0.57-0.el7.noarch.rpm" 和 "mha4mysql-manager-0.57-0.el7...
MySQL MHA安装包
08-21
包括mha4mysql-manager-0.58.tar.gz， mha4mysql-manager-0.58-0.el7.centos.noarch.rpm, mha4mysql-node-0.58.tar.gz, mha4mysql-node-0.58-0.el7.centos.noarch.rpm, mha4mysql-manager-0.57.tar.gz, mha4mysql-...
mysql mha安装包
08-12
mha4mysql-manager-0.56-0.el6.noarch.rpm mha4mysql-node-0.56-0.el6.noarch.rpm perl-Config-Tiny-2.12-7.1.el6.noarch.rpm perl-Log-Dispatch-2.26-1.el6.rf.noarch.rpm perl-Mail-Sender-0.8.16-3.el6.noarch.rpm perl-Mail-Sendmail-0.79-1.2.el6.rf.noarch.rpm perl-Parallel-ForkManager-0.7.5-2.2.el6.rf.noarch.rpm perl-Parallel-ForkManager-0.7.9-1.el6.noarch.rpm perl-rrdtool-1.4.7-1.el5.rf.x86_64.rpm rrdtool-devel-1.3.8-7.el6.x86_64.rpm
MHA 0.57 安装包
05-14
MHA搭建所需要的rpm安装包, 包括 mha4mysql-node-0.57-0.el7.noarch.rpm与mha4mysql-manager-0.57-0.el7.noarch
mysql mha安装
01-26
Centos 安装配置MySql MHA
mha最新版0.57
09-29
MHA（Master High Availability）目前在MySQL高可用方面是一个相对成熟的解决方案，它由日本DeNA公司youshimaton（现就职于Facebook公司）开发，是一套优秀的作为MySQL高可用性环境下故障切换和主从提升的高可用软件。在MySQL故障切换过程中，MHA能做到在0~30秒之内自动完成数据库的故障切换操作，并且在进行故障切换的过程中，MHA能在最大程度上保证数据的一致性，以达到真正意义上的高可用。
MHA0.58安装包及安装文档
04-29
MHA安装包，版本0.58. 以及安装MHA0.58所需的RPM包。已经在Mysql5.7上通过测试。并附测试文档，及踩过的坑。请用7zip解压。
mha高可用资源包下载
qq_40722582的博客
08-01 808
下载个mha包几乎都是要C币的。。。不过总算找到好人提供的网站。。。 就是下面路径了， 至于以前的https://code.google.com 为什么不能够下载， 如下github提供的路径有讲。 https://github.com/yoshinorim/mha4mysql-manager/wiki/Downloads ...
mysql mha安装包下载_Mysql MHA软件下载并且获得perl包的方法 (mha4mysql-0.58)--el7相关软件...
weixin_39697660的博客
01-19 2702
perl 等包，可到 http://rpm.pbone.net/ 网站下载MHA 安装文件可以从 https://code.google.com/p/mysql-master-ha 下载centos7可在下面网站下载另外：软件：https://github.com/yoshinorim/mha4mysql-nodehttps://github.com/yoshinorim/mha4mysql...
MHA MySQL RPM包快速安装指南
资源摘要信息:"mha-mysql-rpm.zip" MySQL高可用性（MHA）是一个高可用性的软件套装，用于简化MySQL主从复制架构的管理，尤其是故障转移和故障恢复的过程。MHA可以自动检测到主节点的故障，然后迅速地将其中一个从...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值