共计 51382 个字符，预计需要花费 129 分钟才能阅读完成。
MHA 算是业内比较成熟的 MySQL 高可用解决方案，在 MySQL 故障切换过程中，MHA 能做到自动完成数据库的故障切换操作，并且在进行故障切换的过程中，MHA 能在最大程度上保证数据的一致性，以达到真正意义上的高可用。软件主要有 MHA Manager（管理节点）和 MHA Node（数据节点）两部分组成，在 MHA 自动故障切换过程中，MHA 试图从宕机的主服务器上保存二进制日志，最大程度的保证数据的不丢失，但这并不总是可行的。例如，如果主服务器硬件故障或无法通过 ssh 访问，MHA 没法保存二进制日志，只进行故障转移而丢失了最新的数据。使用 MySQL 5.5 的半同步复制，可以大大降低数据丢失的风险。MHA 可以与半同步复制结合起来。如果只有一个 slave 已经收到了最新的二进制日志，MHA 可以将最新的二进制日志应用于其他所有的 slave 服务器上，因此可以保证所有节点的数据一致性。
目前 MHA 主要支持一主多从的架构，要搭建 MHA, 要求一个复制集群中必须最少有三台数据库服务器，一主二从，即一台充当 master，一台充当备用 master，另外一台充当从库，因为至少需要三台服务器。
下面我们就开始着手配置我们的 MHA 高可用，因为本人只有两台虚拟机，所以就只能按照两台来搞了，中间也踩了点坑，下面看一下我们的基本环境：
MySQL1（master）：172.16.16.34:3306 +MHA Manager+MHA Node
MySQL2（slave1）：172.16.16.35:3306+MHA Node
MySQL3（slave2）：172.16.16.35:3307+MHA Node我们假设一主两从的环境我们已经搭建好了。
只有两台机器，所以说凑合着用起来吧
1：首先我们要安装 MHA 的安装包，安装 MHA 以前，要安装一些依赖环境
NODE 节点：
yum install -y perl-DBD-MySQLManager 节点：
yum install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes -y 但是这些包我们系统是没有的，需要我们安装相应的 epel 第三方资源库，再安装，我们可以先去
https://fedoraproject.org/wiki/EPEL 这个网站下载我们需要的包，然后安装：
[root@localhost yum.repos.d]# rpm -ivh epel-release-6-8.noarch.rpm安装完以后执行以下语句查看一下源：
[root@localhost yum.repos.d]# yum repolist
Loaded plugins: fastestmirror, security
Loading mirror speeds from cached hostfile
* epel: mirror.lzu.edu.cn
repo id repo name status
base CentOS-6 - Base - 163.com 6,706
*epel Extra Packages for Enterprise Linux 6 - x86_64 12,305
extras CentOS-6 - Extras - 163.com 45
updates CentOS-6 - Updates - 163.com 318
yum yum 6,367
repolist: 25,741可以看到已经有 epel 相关的资源了，所以我们就可以执行执行上面的 yum 语句安装 MHA 的依赖环境。
安装完成以后在两台机器安装 NODE 节点在 master 机器安装 Manage：
[root@localhost sa]# rpm -ivh mha4mysql-node-0.57-0.el7.noarch.rpm
[root@localhost sa]# rpm -ivh mha4mysql-manager-0.57-0.el7.noarch.rpm我这边包是已经下载好的，直接使用 rpm 安装了。至此算是安装完毕了
简单介绍一下 MHA 的 Manager 工具包和 Node 工具包
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 工具包（这些工具通常由 MHA Manager 的脚本触发，无需人为操作）主要包括以下几个工具：
save_binary_logs 保存和复制 master 的二进制日志
apply_diff_relay_logs 识别差异的中继日志事件并将其差异的事件应用于其他的 slave
filter_mysqlbinlog 去除不必要的 ROLLBACK 事件（MHA 已不再使用这个工具）purge_relay_logs 清除中继日志（不会阻塞 SQL 线程）2：配置主机 SSH 免密登录
由于我这两台测试机是从运维手里申请的，折腾过来配置 SSH 浪费了不少时间，而且我这边还是两台 server 代替 MHA 的一主两从一管理的四台机器，中间还是有点问题的
两台机器生成自己的公钥信息：ssh-keygen -t rsa
以一台机器为例，34 拷贝自己的公钥到其他机器：
scp ~/.ssh/id_rsa.pub root@172.16.16.35:/root/.ssh/authorized_keys然后执行授权语句：
chmod 600 /root/.ssh/authorized_keys按说是 OK 了，我们验证一下：
[root@localhost .ssh]# masterha_check_ssh --conf=/etc/mha/app1.cnf[root@localhost .ssh]# masterha_check_ssh --conf=/etc/mha/app1.cnf发现是失败的，我们这里需要把自己的公钥信息加入到认证（两台机器都要执行）：
[root@localhost .ssh]# cat id_rsa.pub >>authorized_keys再次执行就 OK 了
[root@localhost .ssh]# masterha_check_ssh --conf=/etc/mha/app1.cnf这里使用到了 MHA 的配置文件，我们贴一下：
[root@localhost .ssh]# cat /etc/mha/app1.cnf
[server default]
manager_log=/var/log/mha/app1/manager.log
manager_workdir=/var/log/mha/app1.log
master_binlog_dir=/home/mysql/db3306/log/
master_ip_failover_script=/usr/local/bin/master_ip_failover
master_ip_online_change_script=/usr/local/bin/master_ip_online_change
password=123456
ping_interval=1
remote_workdir=/tmp
repl_password=123456
repl_user=root
report_script=/usr/local/bin/send_report
shutdown_script=""ssh_user=root
user=root
[server1]
hostname=172.16.16.34
port=3306
[server2]
hostname=172.16.16.35
port=3306
candidate_master=1
check_repl_delay=0
[server3]
hostname=172.16.16.35
port=3307我这里创建了一个 root@% 的最高权限给 MHA 来使用。由于我们假设一主两从是已经搭建好的，具体授权什么的也不在赘述了。相信大家配置 MHA 的话对于这些小问题都是小儿科了。
3：我们也可以检测一下复制的问题。
不过在此之前要设置我们的从库 read_only=1；
mysql -h172.16.16.35 -P3306 -uroot -p123456 -e'set global read_only=1'
mysql -h172.16.16.35 -P3307 -uroot -p123456 -e'set global read_only=1'然后执行检查：
[root@localhost .ssh]# masterha_check_repl --conf=/etc/mha/app1.cnf
Sat May 27 15:01:57 2017 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sat May 27 15:01:57 2017 - [info] Reading application default configuration from /etc/mha/app1.cnf..
Sat May 27 15:01:57 2017 - [info] Reading server configuration from /etc/mha/app1.cnf..
Sat May 27 15:01:57 2017 - [info] MHA::MasterMonitor version 0.57.
Sat May 27 15:01:57 2017 - [info] GTID failover mode = 1
Sat May 27 15:01:57 2017 - [info] Dead Servers:
Sat May 27 15:01:57 2017 - [info] Alive Servers:
Sat May 27 15:01:57 2017 - [info] 172.16.16.34(172.16.16.34:3306)
Sat May 27 15:01:57 2017 - [info] 172.16.16.35(172.16.16.35:3306)
Sat May 27 15:01:57 2017 - [info] 172.16.16.35(172.16.16.35:3307)
Sat May 27 15:01:57 2017 - [info] Alive Slaves:
Sat May 27 15:01:57 2017 - [info] 172.16.16.35(172.16.16.35:3306) Version=5.7.14-log (oldest major version between slaves) log-bin:enabled
Sat May 27 15:01:57 2017 - [info] GTID ON
Sat May 27 15:01:57 2017 - [info] Replicating from 172.16.16.34(172.16.16.34:3306)
Sat May 27 15:01:57 2017 - [info] Primary candidate for the new Master (candidate_master is set)
Sat May 27 15:01:57 2017 - [info] 172.16.16.35(172.16.16.35:3307) Version=5.7.14-log (oldest major version between slaves) log-bin:enabled
Sat May 27 15:01:57 2017 - [info] GTID ON
Sat May 27 15:01:57 2017 - [info] Replicating from 172.16.16.34(172.16.16.34:3306)
Sat May 27 15:01:57 2017 - [info] Current Alive Master: 172.16.16.34(172.16.16.34:3306)
Sat May 27 15:01:57 2017 - [info] Checking slave configurations..
Sat May 27 15:01:57 2017 - [info] Checking replication filtering settings..
Sat May 27 15:01:57 2017 - [info] binlog_do_db= , binlog_ignore_db=
Sat May 27 15:01:57 2017 - [info] Replication filtering check ok.
Sat May 27 15:01:57 2017 - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.
Sat May 27 15:01:57 2017 - [info] Checking SSH publickey authentication settings on the current master..
Sat May 27 15:01:57 2017 - [info] HealthCheck: SSH to 172.16.16.34 is reachable.
Sat May 27 15:01:57 2017 - [info]
172.16.16.34(172.16.16.34:3306) (current master)
+--172.16.16.35(172.16.16.35:3306)
+--172.16.16.35(172.16.16.35:3307)
Sat May 27 15:01:57 2017 - [info] Checking replication health on 172.16.16.35..
Sat May 27 15:01:57 2017 - [info] ok.
Sat May 27 15:01:57 2017 - [info] Checking replication health on 172.16.16.35..
Sat May 27 15:01:57 2017 - [info] ok.
Sat May 27 15:01:57 2017 - [warning] master_ip_failover_script is not defined.
Sat May 27 15:01:57 2017 - [warning] shutdown_script is not defined.
Sat May 27 15:01:57 2017 - [info] Got exit code 0 (Not master dead).
MySQL Replication Health is OK.我们看到复制是 OK 的，这里我们注释掉了 #master_ip_failover_script，根据我看大师兄的博客里面所说 MHA 的 Failover 有两种方式：一种是虚拟 IP 地址，一种是全局配置文件。MHA 并没有限定使用哪一种方式，而是让用户自己选择，虚拟 IP 地址的方式会牵扯到其它的软件, 比如 keepalive 软件，而且还要修改脚本 master_ip_failover。所以说我们这里先注释掉这块。
虽然已经成功了，但是有两个 warning，因为这两个脚本我们还没有定义，后面补上，先不管他
4：提起 MHA
[root@localhost .ssh]#nohup masterha_manager --conf=/etc/masterha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 &
[1] 8195检查一下 MHA 的运行状态：
[root@localhost .ssh]# masterha_check_status --conf=/etc/masterha/app1.cnf
app1 (pid:8469) is running(0:PING_OK), master:172.16.16.34发现是运行状态，证明启动是成功的，我们去看一下日志：
[root@localhost masterha]# cat /var/log/mha/app1/manager.log
Sat May 27 15:50:47 2017 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sat May 27 15:50:47 2017 - [info] Reading application default configuration from /etc/masterha/app1.cnf..
Sat May 27 15:50:47 2017 - [info] Reading server configuration from /etc/masterha/app1.cnf..
Sat May 27 15:50:47 2017 - [info] MHA::MasterMonitor version 0.57.
Sat May 27 15:50:47 2017 - [warning] /var/log/mha/app1.log/app1.master_status.health already exists. You might have killed manager with SIGKILL(-9), may run two or more monitoring process for the same application, or use the same working directory. Check for details, and consider setting --workdir separately.
Sat May 27 15:50:48 2017 - [info] GTID failover mode = 1
Sat May 27 15:50:48 2017 - [info] Dead Servers:
Sat May 27 15:50:48 2017 - [info] Alive Servers:
Sat May 27 15:50:48 2017 - [info] 172.16.16.34(172.16.16.34:3306)
Sat May 27 15:50:48 2017 - [info] 172.16.16.35(172.16.16.35:3306)
Sat May 27 15:50:48 2017 - [info] 172.16.16.35(172.16.16.35:3307)
Sat May 27 15:50:48 2017 - [info] Alive Slaves:
Sat May 27 15:50:48 2017 - [info] 172.16.16.35(172.16.16.35:3306) Version=5.7.14-log (oldest major version between slaves) log-bin:enabled
Sat May 27 15:50:48 2017 - [info] GTID ON
Sat May 27 15:50:48 2017 - [info] Replicating from 172.16.16.34(172.16.16.34:3306)
Sat May 27 15:50:48 2017 - [info] Primary candidate for the new Master (candidate_master is set)
Sat May 27 15:50:48 2017 - [info] 172.16.16.35(172.16.16.35:3307) Version=5.7.14-log (oldest major version between slaves) log-bin:enabled
Sat May 27 15:50:48 2017 - [info] GTID ON
Sat May 27 15:50:48 2017 - [info] Replicating from 172.16.16.34(172.16.16.34:3306)
Sat May 27 15:50:48 2017 - [info] Current Alive Master: 172.16.16.34(172.16.16.34:3306)
Sat May 27 15:50:48 2017 - [info] Checking slave configurations..
Sat May 27 15:50:48 2017 - [info] Checking replication filtering settings..
Sat May 27 15:50:48 2017 - [info] binlog_do_db= , binlog_ignore_db=
Sat May 27 15:50:48 2017 - [info] Replication filtering check ok.
Sat May 27 15:50:48 2017 - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.
Sat May 27 15:50:48 2017 - [info] Checking SSH publickey authentication settings on the current master..
Sat May 27 15:50:48 2017 - [info] HealthCheck: SSH to 172.16.16.34 is reachable.
Sat May 27 15:50:48 2017 - [info]
172.16.16.34(172.16.16.34:3306) (current master)
+--172.16.16.35(172.16.16.35:3306)
+--172.16.16.35(172.16.16.35:3307)
Sat May 27 15:50:48 2017 - [warning] master_ip_failover_script is not defined.
Sat May 27 15:50:48 2017 - [warning] shutdown_script is not defined.
Sat May 27 15:50:48 2017 - [info] Set master ping interval 1 seconds.
Sat May 27 15:50:48 2017 - [info] Set secondary check script: /usr/bin/masterha_secondary_check -s server03 -s server02
Sat May 27 15:50:48 2017 - [info] Starting ping health check on 172.16.16.34(172.16.16.34:3306)..
Sat May 27 15:50:48 2017 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond.. 如果我们向关闭的话也非常简单
[root@localhost .ssh]# masterha_stop --conf=/etc/mha/app1.cnf5: 管理 VIP：
我们上面已经说过了，有两种 VIP 的管理方式，一种是 keepalived，一种是脚本的方式管理 VIP，keepalived 的管理方式比较简单就是主节点和备用节点两台机器，监控 MySQL 进程就好了，这个和 keepalived+MySQL 双主并没有太大区别在配置方面，关于这个配置可以看下我的上篇博客，博客地址：keepalived+MySQL 双主搭建
下面我们主要使用脚本的方式管理 VIP，定义 master_ip_failover，我们这里直接使用大师兄的博客里面的脚本：
#!/usr/bin/env perl
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 = '172.16.16.20/24';
my $key = '1';
my $ssh_start_vip = "/sbin/ifconfig eth0:$key $vip";
my $ssh_stop_vip = "/sbin/ifconfig eth0:$key down";
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();
$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 stop_vip() {return 0 unless ($ssh_user);
`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";
}然后我们手动在 server1 上添加虚拟 IP
/sbin/ifconfig eth0:1 172.16.16.20/24重新提起来 MHA manager：
[root@localhost masterha]# masterha_stop --conf=/etc/masterha/app1.cnf
[root@localhost masterha]# nohup masterha_manager --conf=/etc/masterha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 &
[root@localhost masterha]# masterha_check_status --conf=/etc/masterha/app1.cnf
app1 (pid:3953) is running(0:PING_OK), master:172.16.16.34更多详情见请继续阅读下一页的精彩内容 ：http://www.linuxidc.com/Linux/2017-06/144615p2.htm
现在来说，我们的 MHA 已经完全搭建起来了，下面测试一下故障转移看看有没有问题：
（1）下面开始测试手动的故障转移：
手动故障转移：
masterha_master_switch --master_state=dead --conf=/etc/masterha/app1.cnf --dead_master_host=172.16.16.34 --dead_master_port=3306 --new_master_host=172.16.16.35 --new_master_port=3306 --ignore_last_failover执行完成后就能够看到主库的地址已经是 172.16.16.35:3306,VIP：172.16.16.20 也已经转移到了 172.16.16.35 上了 。
（2）测试一下自动故障转移
现在我们重新做主从
VIP：172.16.16.20 在 server2:172.16.16.35 上,MySQL master 是：172.16.16.35:3306
从库：172.16.16.34:3306 和 172.16.16.35:3307
手动 kill 掉主库
发现已经自动切换了，接下来看一下日志：
Mon Jun 5 14:23:13 2017 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Mon Jun 5 14:23:13 2017 - [info] Reading application default configuration from /etc/masterha/app1.cnf..
Mon Jun 5 14:23:13 2017 - [info] Reading server configuration from /etc/masterha/app1.cnf..
Mon Jun 5 14:23:13 2017 - [info] MHA::MasterMonitor version 0.57.
Mon Jun 5 14:23:14 2017 - [info] GTID failover mode = 1
Mon Jun 5 14:23:14 2017 - [info] Dead Servers:
Mon Jun 5 14:23:14 2017 - [info] Alive Servers:
Mon Jun 5 14:23:14 2017 - [info] 172.16.16.35(172.16.16.35:3306)
Mon Jun 5 14:23:14 2017 - [info] 172.16.16.34(172.16.16.34:3306)
Mon Jun 5 14:23:14 2017 - [info] 172.16.16.35(172.16.16.35:3307)
Mon Jun 5 14:23:14 2017 - [info] Alive Slaves:
Mon Jun 5 14:23:14 2017 - [info] 172.16.16.34(172.16.16.34:3306) Version=5.7.14-log (oldest major version between slaves) log-bin:enabled
Mon Jun 5 14:23:14 2017 - [info] GTID ON
Mon Jun 5 14:23:14 2017 - [info] Replicating from 172.16.16.35(172.16.16.35:3306)
Mon Jun 5 14:23:14 2017 - [info] Primary candidate for the new Master (candidate_master is set)
Mon Jun 5 14:23:14 2017 - [info] 172.16.16.35(172.16.16.35:3307) Version=5.7.14-log (oldest major version between slaves) log-bin:enabled
Mon Jun 5 14:23:14 2017 - [info] GTID ON
Mon Jun 5 14:23:14 2017 - [info] Replicating from 172.16.16.35(172.16.16.35:3306)
Mon Jun 5 14:23:14 2017 - [info] Current Alive Master: 172.16.16.35(172.16.16.35:3306)
Mon Jun 5 14:23:14 2017 - [info] Checking slave configurations..
Mon Jun 5 14:23:14 2017 - [info] Checking replication filtering settings..
Mon Jun 5 14:23:14 2017 - [info] binlog_do_db= , binlog_ignore_db=
Mon Jun 5 14:23:14 2017 - [info] Replication filtering check ok.
Mon Jun 5 14:23:14 2017 - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.
Mon Jun 5 14:23:14 2017 - [info] Checking SSH publickey authentication settings on the current master..
Mon Jun 5 14:23:14 2017 - [info] HealthCheck: SSH to 172.16.16.35 is reachable.
Mon Jun 5 14:23:14 2017 - [info]
172.16.16.35(172.16.16.35:3306) (current master)
+--172.16.16.34(172.16.16.34:3306)
+--172.16.16.35(172.16.16.35:3307)
Mon Jun 5 14:23:14 2017 - [info] Checking master_ip_failover_script status:
Mon Jun 5 14:23:14 2017 - [info] /usr/local/bin/master_ip_failover --command=status --ssh_user=root --orig_master_host=172.16.16.35 --orig_master_ip=172.16.16.35 --orig_master_port=3306
IN SCRIPT TEST====/sbin/ifconfig eth0:1 down==/sbin/ifconfig eth0:1 172.16.16.20/24===
Checking the Status of the script.. OK
Mon Jun 5 14:23:14 2017 - [info] OK.
Mon Jun 5 14:23:14 2017 - [warning] shutdown_script is not defined.
Mon Jun 5 14:23:14 2017 - [info] Set master ping interval 1 seconds.
Mon Jun 5 14:23:14 2017 - [warning] secondary_check_script is not defined. It is highly recommended setting it to check master reachability from two or more routes.
Mon Jun 5 14:23:14 2017 - [info] Starting ping health check on 172.16.16.35(172.16.16.35:3306)..
Mon Jun 5 14:23:14 2017 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond..
Mon Jun 5 14:23:46 2017 - [warning] Got error on MySQL select ping: 2006 (MySQL server has gone away)
Mon Jun 5 14:23:46 2017 - [info] Executing SSH check script: exit 0
Mon Jun 5 14:23:46 2017 - [info] HealthCheck: SSH to 172.16.16.35 is reachable.
Mon Jun 5 14:23:47 2017 - [warning] Got error on MySQL connect: 2013 (Lost connection to MySQL server at 'reading initial communication packet', system error: 111)
Mon Jun 5 14:23:47 2017 - [warning] Connection failed 2 time(s)..
Mon Jun 5 14:23:48 2017 - [warning] Got error on MySQL connect: 2013 (Lost connection to MySQL server at 'reading initial communication packet', system error: 111)
Mon Jun 5 14:23:48 2017 - [warning] Connection failed 3 time(s)..
Mon Jun 5 14:23:49 2017 - [warning] Got error on MySQL connect: 2013 (Lost connection to MySQL server at 'reading initial communication packet', system error: 111)
Mon Jun 5 14:23:49 2017 - [warning] Connection failed 4 time(s)..
Mon Jun 5 14:23:49 2017 - [warning] Master is not reachable from health checker!
Mon Jun 5 14:23:49 2017 - [warning] Master 172.16.16.35(172.16.16.35:3306) is not reachable!
Mon Jun 5 14:23:49 2017 - [warning] SSH is reachable.
Mon Jun 5 14:23:49 2017 - [info] Connecting to a master server failed. Reading configuration file /etc/masterha_default.cnf and /etc/masterha/app1.cnf again, and trying to connect to all servers to check server status..
Mon Jun 5 14:23:49 2017 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Mon Jun 5 14:23:49 2017 - [info] Reading application default configuration from /etc/masterha/app1.cnf..
Mon Jun 5 14:23:49 2017 - [info] Reading server configuration from /etc/masterha/app1.cnf..
Mon Jun 5 14:23:49 2017 - [info] GTID failover mode = 1
Mon Jun 5 14:23:49 2017 - [info] Dead Servers:
Mon Jun 5 14:23:49 2017 - [info] 172.16.16.35(172.16.16.35:3306)
Mon Jun 5 14:23:49 2017 - [info] Alive Servers:
Mon Jun 5 14:23:49 2017 - [info] 172.16.16.34(172.16.16.34:3306)
Mon Jun 5 14:23:49 2017 - [info] 172.16.16.35(172.16.16.35:3307)
Mon Jun 5 14:23:49 2017 - [info] Alive Slaves:
Mon Jun 5 14:23:49 2017 - [info] 172.16.16.34(172.16.16.34:3306) Version=5.7.14-log (oldest major version between slaves) log-bin:enabled
Mon Jun 5 14:23:49 2017 - [info] GTID ON
Mon Jun 5 14:23:49 2017 - [info] Replicating from 172.16.16.35(172.16.16.35:3306)
Mon Jun 5 14:23:49 2017 - [info] Primary candidate for the new Master (candidate_master is set)
Mon Jun 5 14:23:49 2017 - [info] 172.16.16.35(172.16.16.35:3307) Version=5.7.14-log (oldest major version between slaves) log-bin:enabled
Mon Jun 5 14:23:49 2017 - [info] GTID ON
Mon Jun 5 14:23:49 2017 - [info] Replicating from 172.16.16.35(172.16.16.35:3306)
Mon Jun 5 14:23:49 2017 - [info] Checking slave configurations..
Mon Jun 5 14:23:49 2017 - [info] Checking replication filtering settings..
Mon Jun 5 14:23:49 2017 - [info] Replication filtering check ok.
Mon Jun 5 14:23:49 2017 - [info] Master is down!
Mon Jun 5 14:23:49 2017 - [info] Terminating monitoring script.
Mon Jun 5 14:23:49 2017 - [info] Got exit code 20 (Master dead).
Mon Jun 5 14:23:49 2017 - [info] MHA::MasterFailover version 0.57.
Mon Jun 5 14:23:49 2017 - [info] Starting master failover.
Mon Jun 5 14:23:49 2017 - [info]
Mon Jun 5 14:23:49 2017 - [info] * Phase 1: Configuration Check Phase..
Mon Jun 5 14:23:49 2017 - [info]
Mon Jun 5 14:23:49 2017 - [info] GTID failover mode = 1
Mon Jun 5 14:23:49 2017 - [info] Dead Servers:
Mon Jun 5 14:23:49 2017 - [info] 172.16.16.35(172.16.16.35:3306)
Mon Jun 5 14:23:49 2017 - [info] Checking master reachability via MySQL(double check)...
Mon Jun 5 14:23:49 2017 - [info] ok.
Mon Jun 5 14:23:49 2017 - [info] Alive Servers:
Mon Jun 5 14:23:49 2017 - [info] 172.16.16.34(172.16.16.34:3306)
Mon Jun 5 14:23:49 2017 - [info] 172.16.16.35(172.16.16.35:3307)
Mon Jun 5 14:23:49 2017 - [info] Alive Slaves:
Mon Jun 5 14:23:49 2017 - [info] 172.16.16.34(172.16.16.34:3306) Version=5.7.14-log (oldest major version between slaves) log-bin:enabled
Mon Jun 5 14:23:49 2017 - [info] GTID ON
Mon Jun 5 14:23:49 2017 - [info] Replicating from 172.16.16.35(172.16.16.35:3306)
Mon Jun 5 14:23:49 2017 - [info] Primary candidate for the new Master (candidate_master is set)
Mon Jun 5 14:23:49 2017 - [info] 172.16.16.35(172.16.16.35:3307) Version=5.7.14-log (oldest major version between slaves) log-bin:enabled
Mon Jun 5 14:23:49 2017 - [info] GTID ON
Mon Jun 5 14:23:49 2017 - [info] Replicating from 172.16.16.35(172.16.16.35:3306)
Mon Jun 5 14:23:49 2017 - [info] Starting GTID based failover.
Mon Jun 5 14:23:49 2017 - [info]
Mon Jun 5 14:23:49 2017 - [info] ** Phase 1: Configuration Check Phase completed.
Mon Jun 5 14:23:49 2017 - [info]
Mon Jun 5 14:23:49 2017 - [info] * Phase 2: Dead Master Shutdown Phase..
Mon Jun 5 14:23:49 2017 - [info]
Mon Jun 5 14:23:49 2017 - [info] Forcing shutdown so that applications never connect to the current master..
Mon Jun 5 14:23:49 2017 - [info] Executing master IP deactivation script:
Mon Jun 5 14:23:49 2017 - [info] /usr/local/bin/master_ip_failover --orig_master_host=172.16.16.35 --orig_master_ip=172.16.16.35 --orig_master_port=3306 --command=stopssh --ssh_user=root
IN SCRIPT TEST====/sbin/ifconfig eth0:1 down==/sbin/ifconfig eth0:1 172.16.16.20/24===
Disabling the VIP on old master: 172.16.16.35
Mon Jun 5 14:23:49 2017 - [info] done.
Mon Jun 5 14:23:49 2017 - [warning] shutdown_script is not set. Skipping explicit shutting down of the dead master.
Mon Jun 5 14:23:49 2017 - [info] * Phase 2: Dead Master Shutdown Phase completed.
Mon Jun 5 14:23:49 2017 - [info]
Mon Jun 5 14:23:49 2017 - [info] * Phase 3: Master Recovery Phase..
Mon Jun 5 14:23:49 2017 - [info]
Mon Jun 5 14:23:49 2017 - [info] * Phase 3.1: Getting Latest Slaves Phase..
Mon Jun 5 14:23:49 2017 - [info]
Mon Jun 5 14:23:49 2017 - [info] The latest binary log file/position on all slaves is mysql-bin.000003:194
Mon Jun 5 14:23:49 2017 - [info] Retrieved Gtid Set: 806ede0c-357e-11e7-9719-00505693235d:1
Mon Jun 5 14:23:49 2017 - [info] Latest slaves (Slaves that received relay log files to the latest):
Mon Jun 5 14:23:49 2017 - [info] 172.16.16.34(172.16.16.34:3306) Version=5.7.14-log (oldest major version between slaves) log-bin:enabled
Mon Jun 5 14:23:49 2017 - [info] GTID ON
Mon Jun 5 14:23:49 2017 - [info] Replicating from 172.16.16.35(172.16.16.35:3306)
Mon Jun 5 14:23:49 2017 - [info] Primary candidate for the new Master (candidate_master is set)
Mon Jun 5 14:23:49 2017 - [info] 172.16.16.35(172.16.16.35:3307) Version=5.7.14-log (oldest major version between slaves) log-bin:enabled
Mon Jun 5 14:23:49 2017 - [info] GTID ON
Mon Jun 5 14:23:49 2017 - [info] Replicating from 172.16.16.35(172.16.16.35:3306)
Mon Jun 5 14:23:49 2017 - [info] The oldest binary log file/position on all slaves is mysql-bin.000003:194
Mon Jun 5 14:23:49 2017 - [info] Retrieved Gtid Set: 806ede0c-357e-11e7-9719-00505693235d:1
Mon Jun 5 14:23:49 2017 - [info] Oldest slaves:
Mon Jun 5 14:23:49 2017 - [info] 172.16.16.34(172.16.16.34:3306) Version=5.7.14-log (oldest major version between slaves) log-bin:enabled
Mon Jun 5 14:23:49 2017 - [info] GTID ON
Mon Jun 5 14:23:49 2017 - [info] Replicating from 172.16.16.35(172.16.16.35:3306)
Mon Jun 5 14:23:49 2017 - [info] Primary candidate for the new Master (candidate_master is set)
Mon Jun 5 14:23:49 2017 - [info] 172.16.16.35(172.16.16.35:3307) Version=5.7.14-log (oldest major version between slaves) log-bin:enabled
Mon Jun 5 14:23:49 2017 - [info] GTID ON
Mon Jun 5 14:23:49 2017 - [info] Replicating from 172.16.16.35(172.16.16.35:3306)
Mon Jun 5 14:23:49 2017 - [info]
Mon Jun 5 14:23:49 2017 - [info] * Phase 3.3: Determining New Master Phase..
Mon Jun 5 14:23:49 2017 - [info]
Mon Jun 5 14:23:49 2017 - [info] Searching new master from slaves..
Mon Jun 5 14:23:49 2017 - [info] Candidate masters from the configuration file:
Mon Jun 5 14:23:49 2017 - [info] 172.16.16.34(172.16.16.34:3306) Version=5.7.14-log (oldest major version between slaves) log-bin:enabled
Mon Jun 5 14:23:49 2017 - [info] GTID ON
Mon Jun 5 14:23:49 2017 - [info] Replicating from 172.16.16.35(172.16.16.35:3306)
Mon Jun 5 14:23:49 2017 - [info] Primary candidate for the new Master (candidate_master is set)
Mon Jun 5 14:23:49 2017 - [info] Non-candidate masters:
Mon Jun 5 14:23:49 2017 - [info] Searching from candidate_master slaves which have received the latest relay log events..
Mon Jun 5 14:23:49 2017 - [info] New master is 172.16.16.34(172.16.16.34:3306)
Mon Jun 5 14:23:49 2017 - [info] Starting master failover..
Mon Jun 5 14:23:49 2017 - [info]
From:
172.16.16.35(172.16.16.35:3306) (current master)
+--172.16.16.34(172.16.16.34:3306)
+--172.16.16.35(172.16.16.35:3307)
To:
172.16.16.34(172.16.16.34:3306) (new master)
+--172.16.16.35(172.16.16.35:3307)
Mon Jun 5 14:23:49 2017 - [info]
Mon Jun 5 14:23:49 2017 - [info] * Phase 3.3: New Master Recovery Phase..
Mon Jun 5 14:23:49 2017 - [info]
Mon Jun 5 14:23:49 2017 - [info] Waiting all logs to be applied..
Mon Jun 5 14:23:49 2017 - [info] done.
Mon Jun 5 14:23:49 2017 - [info] Getting new master's binlog name and position..
Mon Jun 5 14:23:49 2017 - [info] mysql-bin.000001:427
Mon Jun 5 14:23:49 2017 - [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='172.16.16.34', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='root', MASTER_PASSWORD='xxx';
Mon Jun 5 14:23:49 2017 - [info] Master Recovery succeeded. File:Pos:Exec_Gtid_Set: mysql-bin.000001, 427, 806ede0c-357e-11e7-9719-00505693235d:1
Mon Jun 5 14:23:49 2017 - [info] Executing master IP activate script:
Mon Jun 5 14:23:49 2017 - [info] /usr/local/bin/master_ip_failover --command=start --ssh_user=root --orig_master_host=172.16.16.35 --orig_master_ip=172.16.16.35 --orig_master_port=3306 --new_master_host=172.16.16.34 --new_master_ip=172.16.16.34 --new_master_port=3306 --new_master_user='root' --new_master_password=xxx
Unknown option: new_master_user
Unknown option: new_master_password
IN SCRIPT TEST====/sbin/ifconfig eth0:1 down==/sbin/ifconfig eth0:1 172.16.16.20/24===
Enabling the VIP - 172.16.16.20/24 on the new master - 172.16.16.34
Mon Jun 5 14:23:50 2017 - [info] OK.
Mon Jun 5 14:23:50 2017 - [info] Setting read_only=0 on 172.16.16.34(172.16.16.34:3306)..
Mon Jun 5 14:23:50 2017 - [info] ok.
Mon Jun 5 14:23:50 2017 - [info] ** Finished master recovery successfully.
Mon Jun 5 14:23:50 2017 - [info] * Phase 3: Master Recovery Phase completed.
Mon Jun 5 14:23:50 2017 - [info]
Mon Jun 5 14:23:50 2017 - [info] * Phase 4: Slaves Recovery Phase..
Mon Jun 5 14:23:50 2017 - [info]
Mon Jun 5 14:23:50 2017 - [info]
Mon Jun 5 14:23:50 2017 - [info] * Phase 4.1: Starting Slaves in parallel..
Mon Jun 5 14:23:50 2017 - [info]
Mon Jun 5 14:23:50 2017 - [info] -- Slave recovery on host 172.16.16.35(172.16.16.35:3307) started, pid: 636. Check tmp log /var/log/mha/app1.log/172.16.16.35_3307_20170605142349.log if it takes time..
Mon Jun 5 14:23:50 2017 - [info]
Mon Jun 5 14:23:50 2017 - [info] Log messages from 172.16.16.35 ...
Mon Jun 5 14:23:50 2017 - [info]
Mon Jun 5 14:23:50 2017 - [info] Resetting slave 172.16.16.35(172.16.16.35:3307) and starting replication from the new master 172.16.16.34(172.16.16.34:3306)..
Mon Jun 5 14:23:50 2017 - [info] Executed CHANGE MASTER.
Mon Jun 5 14:23:50 2017 - [info] Slave started.
Mon Jun 5 14:23:50 2017 - [info] gtid_wait(806ede0c-357e-11e7-9719-00505693235d:1) completed on 172.16.16.35(172.16.16.35:3307). Executed 0 events.
Mon Jun 5 14:23:50 2017 - [info] End of log messages from 172.16.16.35.
Mon Jun 5 14:23:50 2017 - [info] -- Slave on host 172.16.16.35(172.16.16.35:3307) started.
Mon Jun 5 14:23:50 2017 - [info] All new slave servers recovered successfully.
Mon Jun 5 14:23:50 2017 - [info]
Mon Jun 5 14:23:50 2017 - [info] * Phase 5: New master cleanup phase..
Mon Jun 5 14:23:50 2017 - [info]
Mon Jun 5 14:23:50 2017 - [info] Resetting slave info on the new master..
Mon Jun 5 14:23:50 2017 - [info] 172.16.16.34: Resetting slave info succeeded.
Mon Jun 5 14:23:50 2017 - [info] Master failover to 172.16.16.34(172.16.16.34:3306) completed successfully.
Mon Jun 5 14:23:50 2017 - [info] Deleted server1 entry from /etc/masterha/app1.cnf .
Mon Jun 5 14:23:50 2017 - [info]
----- Failover Report -----
app1: MySQL Master failover 172.16.16.35(172.16.16.35:3306) to 172.16.16.34(172.16.16.34:3306) succeeded
Master 172.16.16.35(172.16.16.35:3306) is down!
Check MHA Manager logs at localhost.localdomain:/var/log/mha/app1/manager.log for details.
Started automated(non-interactive) failover.
Invalidated master IP address on 172.16.16.35(172.16.16.35:3306)
Selected 172.16.16.34(172.16.16.34:3306) as a new master.
172.16.16.34(172.16.16.34:3306): OK: Applying all logs succeeded.
172.16.16.34(172.16.16.34:3306): OK: Activated master IP address.
172.16.16.35(172.16.16.35:3307): OK: Slave started, replicating from 172.16.16.34(172.16.16.34:3306)
172.16.16.34(172.16.16.34:3306): Resetting slave info succeeded.
Master failover to 172.16.16.34(172.16.16.34:3306) completed successfully.
Mon Jun 5 14:23:50 2017 - [info] Sending mail..
sh: /usr/local/bin/send_report: No such file or directory
Mon Jun 5 14:23:50 2017 - [error][/usr/share/perl5/vendor_perl/MHA/MasterFailover.pm, ln2066] Failed to send mail with return code 127:0
tail: /var/log/mha/app1/manager.log: file truncated
Mon Jun 5 14:48:26 2017 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Mon Jun 5 14:48:26 2017 - [info] Reading application default configuration from /etc/masterha/app1.cnf..
Mon Jun 5 14:48:26 2017 - [info] Reading server configuration from /etc/masterha/app1.cnf..
Mon Jun 5 14:48:26 2017 - [info] MHA::MasterMonitor version 0.57.
Mon Jun 5 14:48:26 2017 - [info] GTID failover mode = 1
Mon Jun 5 14:48:26 2017 - [info] Dead Servers:
Mon Jun 5 14:48:26 2017 - [info] Alive Servers:
Mon Jun 5 14:48:26 2017 - [info] 172.16.16.34(172.16.16.34:3306)
Mon Jun 5 14:48:26 2017 - [info] 172.16.16.35(172.16.16.35:3306)
Mon Jun 5 14:48:26 2017 - [info] 172.16.16.35(172.16.16.35:3307)
Mon Jun 5 14:48:26 2017 - [info] Alive Slaves:
Mon Jun 5 14:48:26 2017 - [info] 172.16.16.35(172.16.16.35:3306) Version=5.7.14-log (oldest major version between slaves) log-bin:enabled
Mon Jun 5 14:48:26 2017 - [info] GTID ON
Mon Jun 5 14:48:26 2017 - [info] Replicating from 172.16.16.34(172.16.16.34:3306)
Mon Jun 5 14:48:26 2017 - [info] Primary candidate for the new Master (candidate_master is set)
Mon Jun 5 14:48:26 2017 - [info] 172.16.16.35(172.16.16.35:3307) Version=5.7.14-log (oldest major version between slaves) log-bin:enabled
Mon Jun 5 14:48:26 2017 - [info] GTID ON
Mon Jun 5 14:48:26 2017 - [info] Replicating from 172.16.16.34(172.16.16.34:3306)
Mon Jun 5 14:48:26 2017 - [info] Current Alive Master: 172.16.16.34(172.16.16.34:3306)
Mon Jun 5 14:48:26 2017 - [info] Checking slave configurations..
Mon Jun 5 14:48:26 2017 - [info] Checking replication filtering settings..
Mon Jun 5 14:48:26 2017 - [info] binlog_do_db= , binlog_ignore_db=
Mon Jun 5 14:48:26 2017 - [info] Replication filtering check ok.
Mon Jun 5 14:48:26 2017 - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.
Mon Jun 5 14:48:26 2017 - [info] Checking SSH publickey authentication settings on the current master..
Mon Jun 5 14:48:26 2017 - [info] HealthCheck: SSH to 172.16.16.34 is reachable.
Mon Jun 5 14:48:26 2017 - [info]
172.16.16.34(172.16.16.34:3306) (current master)
+--172.16.16.35(172.16.16.35:3306)
+--172.16.16.35(172.16.16.35:3307)
Mon Jun 5 14:48:26 2017 - [info] Checking master_ip_failover_script status:
Mon Jun 5 14:48:26 2017 - [info] /usr/local/bin/master_ip_failover --command=status --ssh_user=root --orig_master_host=172.16.16.34 --orig_master_ip=172.16.16.34 --orig_master_port=3306
IN SCRIPT TEST====/sbin/ifconfig eth0:1 down==/sbin/ifconfig eth0:1 172.16.16.20/24===
Checking the Status of the script.. OK
Mon Jun 5 14:48:26 2017 - [info] OK.
Mon Jun 5 14:48:26 2017 - [warning] shutdown_script is not defined.
Mon Jun 5 14:48:26 2017 - [info] Set master ping interval 1 seconds.
Mon Jun 5 14:48:26 2017 - [warning] secondary_check_script is not defined. It is highly recommended setting it to check master reachability from two or more routes.
Mon Jun 5 14:48:26 2017 - [info] Starting ping health check on 172.16.16.34(172.16.16.34:3306)..
Mon Jun 5 14:48:26 2017 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond..
^C
[root@localhost ~]# tail -100 /var/log/mha/app1/manager.log
Mon Jun 5 14:48:26 2017 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Mon Jun 5 14:48:26 2017 - [info] Reading application default configuration from /etc/masterha/app1.cnf..
Mon Jun 5 14:48:26 2017 - [info] Reading server configuration from /etc/masterha/app1.cnf..
Mon Jun 5 14:48:26 2017 - [info] MHA::MasterMonitor version 0.57.
Mon Jun 5 14:48:26 2017 - [info] GTID failover mode = 1
Mon Jun 5 14:48:26 2017 - [info] Dead Servers:
Mon Jun 5 14:48:26 2017 - [info] Alive Servers:
Mon Jun 5 14:48:26 2017 - [info] 172.16.16.34(172.16.16.34:3306)
Mon Jun 5 14:48:26 2017 - [info] 172.16.16.35(172.16.16.35:3306)
Mon Jun 5 14:48:26 2017 - [info] 172.16.16.35(172.16.16.35:3307)
Mon Jun 5 14:48:26 2017 - [info] Alive Slaves:
Mon Jun 5 14:48:26 2017 - [info] 172.16.16.35(172.16.16.35:3306) Version=5.7.14-log (oldest major version between slaves) log-bin:enabled
Mon Jun 5 14:48:26 2017 - [info] GTID ON
Mon Jun 5 14:48:26 2017 - [info] Replicating from 172.16.16.34(172.16.16.34:3306)
Mon Jun 5 14:48:26 2017 - [info] Primary candidate for the new Master (candidate_master is set)
Mon Jun 5 14:48:26 2017 - [info] 172.16.16.35(172.16.16.35:3307) Version=5.7.14-log (oldest major version between slaves) log-bin:enabled
Mon Jun 5 14:48:26 2017 - [info] GTID ON
Mon Jun 5 14:48:26 2017 - [info] Replicating from 172.16.16.34(172.16.16.34:3306)
Mon Jun 5 14:48:26 2017 - [info] Current Alive Master: 172.16.16.34(172.16.16.34:3306)
Mon Jun 5 14:48:26 2017 - [info] Checking slave configurations..
Mon Jun 5 14:48:26 2017 - [info] Checking replication filtering settings..
Mon Jun 5 14:48:26 2017 - [info] binlog_do_db= , binlog_ignore_db=
Mon Jun 5 14:48:26 2017 - [info] Replication filtering check ok.
Mon Jun 5 14:48:26 2017 - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.
Mon Jun 5 14:48:26 2017 - [info] Checking SSH publickey authentication settings on the current master..
Mon Jun 5 14:48:26 2017 - [info] HealthCheck: SSH to 172.16.16.34 is reachable.
Mon Jun 5 14:48:26 2017 - [info]
172.16.16.34(172.16.16.34:3306) (current master)
+--172.16.16.35(172.16.16.35:3306)
+--172.16.16.35(172.16.16.35:3307)
Mon Jun 5 14:48:26 2017 - [info] Checking master_ip_failover_script status:
Mon Jun 5 14:48:26 2017 - [info] /usr/local/bin/master_ip_failover --command=status --ssh_user=root --orig_master_host=172.16.16.34 --orig_master_ip=172.16.16.34 --orig_master_port=3306
IN SCRIPT TEST====/sbin/ifconfig eth0:1 down==/sbin/ifconfig eth0:1 172.16.16.20/24===
Checking the Status of the script.. OK
Mon Jun 5 14:48:26 2017 - [info] OK.
Mon Jun 5 14:48:26 2017 - [warning] shutdown_script is not defined.
Mon Jun 5 14:48:26 2017 - [info] Set master ping interval 1 seconds.
Mon Jun 5 14:48:26 2017 - [warning] secondary_check_script is not defined. It is highly recommended setting it to check master reachability from two or more routes.
Mon Jun 5 14:48:26 2017 - [info] Starting ping health check on 172.16.16.34(172.16.16.34:3306).. 切换以后我们发现现在主从是 OK 的了，但是我们忘记了很重要的一个问题，那就是 slave 的 relay log, 主从复制在缺省情况下从库的 relay logs 会在 SQL 线程执行完毕后被自动删除，但是对于 MHA 场景下，对于某些滞后从库的恢复依赖于其他从库的 relay log，因此采取禁用自动删除功能以及定期清理的办法。对于清理过多过大的 relay log 需要注意引起的复制延迟资源开销等。所以这里要将 relay log 的自动清除设置为 OFF，采用手动清除 relay log 的方式：
mysql -uroot -h172.16.16.35 -P3306 -p123456 -e'set global relay_log_purge=OFF;'
mysql -uroot -h172.16.16.35 -P3306 -p123456 -e'set global relay_log_purge=OFF;'默认设置为 OFF，这样 relay lay 每次 SQL 执行线程完毕后并不会被自动删除了，所以说我们需要手动删除掉 relay log，在 mha node 的工具包里面有个 purge_relay_logs 工具来直接处理这个事情，
[root@mxqmongodb2 data]# purge_relay_logs --user=root --password=123456 --host=172.16.16.35 --port=3306
2017-06-06 09:11:23: purge_relay_logs script started.
Opening /home/mysql/db3306/data/mxqmongodb2-relay-bin.000001 ..
Opening /home/mysql/db3306/data/mxqmongodb2-relay-bin.000002 ..
Opening /home/mysql/db3306/data/mxqmongodb2-relay-bin.000003 ..
Executing SET GLOBAL relay_log_purge=1; FLUSH LOGS; sleeping a few seconds so that SQL thread can delete older relay log files (if it keeps up); SET GLOBAL relay_log_purge=0; .. ok.
2017-06-06 09:11:26: All relay log purging operations succeeded.
[root@mxqmongodb2 data]# purge_relay_logs --user=root --password=123456 --host=172.16.16.35 --port=3307
2017-06-06 09:11:41: purge_relay_logs script started.
Opening /home/mysql/db3307/data/mxqmongodb2-relay-bin.000001 ..
Opening /home/mysql/db3307/data/mxqmongodb2-relay-bin.000002 ..
Opening /home/mysql/db3307/data/mxqmongodb2-relay-bin.000003 ..
Executing SET GLOBAL relay_log_purge=1; FLUSH LOGS; sleeping a few seconds so that SQL thread can delete older relay log files (if it keeps up); SET GLOBAL relay_log_purge=0; .. ok.
2017-06-06 09:11:44: All relay log purging operations succeeded.我们这样就可以手动清除掉 relay log 了，也可以加到定时人物里面定时执行。
/usr/bin/purge_relay_logs --user=root --password=123456 --host=172.16.16.35 --port=3307这样算是搭建完了。
本文永久更新链接地址 ：http://www.linuxidc.com/Linux/2017-06/144615.htm
MHA 算是业内比较成熟的 MySQL 高可用解决方案，在 MySQL 故障切换过程中，MHA 能做到自动完成数据库的故障切换操作，并且在进行故障切换的过程中，MHA 能在最大程度上保证数据的一致性，以达到真正意义上的高可用。软件主要有 MHA Manager（管理节点）和 MHA Node（数据节点）两部分组成，在 MHA 自动故障切换过程中，MHA 试图从宕机的主服务器上保存二进制日志，最大程度的保证数据的不丢失，但这并不总是可行的。例如，如果主服务器硬件故障或无法通过 ssh 访问，MHA 没法保存二进制日志，只进行故障转移而丢失了最新的数据。使用 MySQL 5.5 的半同步复制，可以大大降低数据丢失的风险。MHA 可以与半同步复制结合起来。如果只有一个 slave 已经收到了最新的二进制日志，MHA 可以将最新的二进制日志应用于其他所有的 slave 服务器上，因此可以保证所有节点的数据一致性。
目前 MHA 主要支持一主多从的架构，要搭建 MHA, 要求一个复制集群中必须最少有三台数据库服务器，一主二从，即一台充当 master，一台充当备用 master，另外一台充当从库，因为至少需要三台服务器。
下面我们就开始着手配置我们的 MHA 高可用，因为本人只有两台虚拟机，所以就只能按照两台来搞了，中间也踩了点坑，下面看一下我们的基本环境：
MySQL1（master）：172.16.16.34:3306 +MHA Manager+MHA Node
MySQL2（slave1）：172.16.16.35:3306+MHA Node
MySQL3（slave2）：172.16.16.35:3307+MHA Node我们假设一主两从的环境我们已经搭建好了。
只有两台机器，所以说凑合着用起来吧
1：首先我们要安装 MHA 的安装包，安装 MHA 以前，要安装一些依赖环境
NODE 节点：
yum install -y perl-DBD-MySQLManager 节点：
yum install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes -y 但是这些包我们系统是没有的，需要我们安装相应的 epel 第三方资源库，再安装，我们可以先去
https://fedoraproject.org/wiki/EPEL 这个网站下载我们需要的包，然后安装：
[root@localhost yum.repos.d]# rpm -ivh epel-release-6-8.noarch.rpm安装完以后执行以下语句查看一下源：
[root@localhost yum.repos.d]# yum repolist
Loaded plugins: fastestmirror, security
Loading mirror speeds from cached hostfile
* epel: mirror.lzu.edu.cn
repo id repo name status
base CentOS-6 - Base - 163.com 6,706
*epel Extra Packages for Enterprise Linux 6 - x86_64 12,305
extras CentOS-6 - Extras - 163.com 45
updates CentOS-6 - Updates - 163.com 318
yum yum 6,367
repolist: 25,741可以看到已经有 epel 相关的资源了，所以我们就可以执行执行上面的 yum 语句安装 MHA 的依赖环境。
安装完成以后在两台机器安装 NODE 节点在 master 机器安装 Manage：
[root@localhost sa]# rpm -ivh mha4mysql-node-0.57-0.el7.noarch.rpm
[root@localhost sa]# rpm -ivh mha4mysql-manager-0.57-0.el7.noarch.rpm我这边包是已经下载好的，直接使用 rpm 安装了。至此算是安装完毕了
简单介绍一下 MHA 的 Manager 工具包和 Node 工具包
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 工具包（这些工具通常由 MHA Manager 的脚本触发，无需人为操作）主要包括以下几个工具：
save_binary_logs 保存和复制 master 的二进制日志
apply_diff_relay_logs 识别差异的中继日志事件并将其差异的事件应用于其他的 slave
filter_mysqlbinlog 去除不必要的 ROLLBACK 事件（MHA 已不再使用这个工具）purge_relay_logs 清除中继日志（不会阻塞 SQL 线程）2：配置主机 SSH 免密登录
由于我这两台测试机是从运维手里申请的，折腾过来配置 SSH 浪费了不少时间，而且我这边还是两台 server 代替 MHA 的一主两从一管理的四台机器，中间还是有点问题的
两台机器生成自己的公钥信息：ssh-keygen -t rsa
以一台机器为例，34 拷贝自己的公钥到其他机器：
scp ~/.ssh/id_rsa.pub root@172.16.16.35:/root/.ssh/authorized_keys然后执行授权语句：
chmod 600 /root/.ssh/authorized_keys按说是 OK 了，我们验证一下：
[root@localhost .ssh]# masterha_check_ssh --conf=/etc/mha/app1.cnf[root@localhost .ssh]# masterha_check_ssh --conf=/etc/mha/app1.cnf发现是失败的，我们这里需要把自己的公钥信息加入到认证（两台机器都要执行）：
[root@localhost .ssh]# cat id_rsa.pub >>authorized_keys再次执行就 OK 了
[root@localhost .ssh]# masterha_check_ssh --conf=/etc/mha/app1.cnf这里使用到了 MHA 的配置文件，我们贴一下：
[root@localhost .ssh]# cat /etc/mha/app1.cnf
[server default]
manager_log=/var/log/mha/app1/manager.log
manager_workdir=/var/log/mha/app1.log
master_binlog_dir=/home/mysql/db3306/log/
master_ip_failover_script=/usr/local/bin/master_ip_failover
master_ip_online_change_script=/usr/local/bin/master_ip_online_change
password=123456
ping_interval=1
remote_workdir=/tmp
repl_password=123456
repl_user=root
report_script=/usr/local/bin/send_report
shutdown_script=""ssh_user=root
user=root
[server1]
hostname=172.16.16.34
port=3306
[server2]
hostname=172.16.16.35
port=3306
candidate_master=1
check_repl_delay=0
[server3]
hostname=172.16.16.35
port=3307我这里创建了一个 root@% 的最高权限给 MHA 来使用。由于我们假设一主两从是已经搭建好的，具体授权什么的也不在赘述了。相信大家配置 MHA 的话对于这些小问题都是小儿科了。
3：我们也可以检测一下复制的问题。
不过在此之前要设置我们的从库 read_only=1；
mysql -h172.16.16.35 -P3306 -uroot -p123456 -e'set global read_only=1'
mysql -h172.16.16.35 -P3307 -uroot -p123456 -e'set global read_only=1'然后执行检查：
[root@localhost .ssh]# masterha_check_repl --conf=/etc/mha/app1.cnf
Sat May 27 15:01:57 2017 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sat May 27 15:01:57 2017 - [info] Reading application default configuration from /etc/mha/app1.cnf..
Sat May 27 15:01:57 2017 - [info] Reading server configuration from /etc/mha/app1.cnf..
Sat May 27 15:01:57 2017 - [info] MHA::MasterMonitor version 0.57.
Sat May 27 15:01:57 2017 - [info] GTID failover mode = 1
Sat May 27 15:01:57 2017 - [info] Dead Servers:
Sat May 27 15:01:57 2017 - [info] Alive Servers:
Sat May 27 15:01:57 2017 - [info] 172.16.16.34(172.16.16.34:3306)
Sat May 27 15:01:57 2017 - [info] 172.16.16.35(172.16.16.35:3306)
Sat May 27 15:01:57 2017 - [info] 172.16.16.35(172.16.16.35:3307)
Sat May 27 15:01:57 2017 - [info] Alive Slaves:
Sat May 27 15:01:57 2017 - [info] 172.16.16.35(172.16.16.35:3306) Version=5.7.14-log (oldest major version between slaves) log-bin:enabled
Sat May 27 15:01:57 2017 - [info] GTID ON
Sat May 27 15:01:57 2017 - [info] Replicating from 172.16.16.34(172.16.16.34:3306)
Sat May 27 15:01:57 2017 - [info] Primary candidate for the new Master (candidate_master is set)
Sat May 27 15:01:57 2017 - [info] 172.16.16.35(172.16.16.35:3307) Version=5.7.14-log (oldest major version between slaves) log-bin:enabled
Sat May 27 15:01:57 2017 - [info] GTID ON
Sat May 27 15:01:57 2017 - [info] Replicating from 172.16.16.34(172.16.16.34:3306)
Sat May 27 15:01:57 2017 - [info] Current Alive Master: 172.16.16.34(172.16.16.34:3306)
Sat May 27 15:01:57 2017 - [info] Checking slave configurations..
Sat May 27 15:01:57 2017 - [info] Checking replication filtering settings..
Sat May 27 15:01:57 2017 - [info] binlog_do_db= , binlog_ignore_db=
Sat May 27 15:01:57 2017 - [info] Replication filtering check ok.
Sat May 27 15:01:57 2017 - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.
Sat May 27 15:01:57 2017 - [info] Checking SSH publickey authentication settings on the current master..
Sat May 27 15:01:57 2017 - [info] HealthCheck: SSH to 172.16.16.34 is reachable.
Sat May 27 15:01:57 2017 - [info]
172.16.16.34(172.16.16.34:3306) (current master)
+--172.16.16.35(172.16.16.35:3306)
+--172.16.16.35(172.16.16.35:3307)
Sat May 27 15:01:57 2017 - [info] Checking replication health on 172.16.16.35..
Sat May 27 15:01:57 2017 - [info] ok.
Sat May 27 15:01:57 2017 - [info] Checking replication health on 172.16.16.35..
Sat May 27 15:01:57 2017 - [info] ok.
Sat May 27 15:01:57 2017 - [warning] master_ip_failover_script is not defined.
Sat May 27 15:01:57 2017 - [warning] shutdown_script is not defined.
Sat May 27 15:01:57 2017 - [info] Got exit code 0 (Not master dead).
MySQL Replication Health is OK.我们看到复制是 OK 的，这里我们注释掉了 #master_ip_failover_script，根据我看大师兄的博客里面所说 MHA 的 Failover 有两种方式：一种是虚拟 IP 地址，一种是全局配置文件。MHA 并没有限定使用哪一种方式，而是让用户自己选择，虚拟 IP 地址的方式会牵扯到其它的软件, 比如 keepalive 软件，而且还要修改脚本 master_ip_failover。所以说我们这里先注释掉这块。
虽然已经成功了，但是有两个 warning，因为这两个脚本我们还没有定义，后面补上，先不管他
4：提起 MHA
[root@localhost .ssh]#nohup masterha_manager --conf=/etc/masterha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 &
[1] 8195检查一下 MHA 的运行状态：
[root@localhost .ssh]# masterha_check_status --conf=/etc/masterha/app1.cnf
app1 (pid:8469) is running(0:PING_OK), master:172.16.16.34发现是运行状态，证明启动是成功的，我们去看一下日志：
[root@localhost masterha]# cat /var/log/mha/app1/manager.log
Sat May 27 15:50:47 2017 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sat May 27 15:50:47 2017 - [info] Reading application default configuration from /etc/masterha/app1.cnf..
Sat May 27 15:50:47 2017 - [info] Reading server configuration from /etc/masterha/app1.cnf..
Sat May 27 15:50:47 2017 - [info] MHA::MasterMonitor version 0.57.
Sat May 27 15:50:47 2017 - [warning] /var/log/mha/app1.log/app1.master_status.health already exists. You might have killed manager with SIGKILL(-9), may run two or more monitoring process for the same application, or use the same working directory. Check for details, and consider setting --workdir separately.
Sat May 27 15:50:48 2017 - [info] GTID failover mode = 1
Sat May 27 15:50:48 2017 - [info] Dead Servers:
Sat May 27 15:50:48 2017 - [info] Alive Servers:
Sat May 27 15:50:48 2017 - [info] 172.16.16.34(172.16.16.34:3306)
Sat May 27 15:50:48 2017 - [info] 172.16.16.35(172.16.16.35:3306)
Sat May 27 15:50:48 2017 - [info] 172.16.16.35(172.16.16.35:3307)
Sat May 27 15:50:48 2017 - [info] Alive Slaves:
Sat May 27 15:50:48 2017 - [info] 172.16.16.35(172.16.16.35:3306) Version=5.7.14-log (oldest major version between slaves) log-bin:enabled
Sat May 27 15:50:48 2017 - [info] GTID ON
Sat May 27 15:50:48 2017 - [info] Replicating from 172.16.16.34(172.16.16.34:3306)
Sat May 27 15:50:48 2017 - [info] Primary candidate for the new Master (candidate_master is set)
Sat May 27 15:50:48 2017 - [info] 172.16.16.35(172.16.16.35:3307) Version=5.7.14-log (oldest major version between slaves) log-bin:enabled
Sat May 27 15:50:48 2017 - [info] GTID ON
Sat May 27 15:50:48 2017 - [info] Replicating from 172.16.16.34(172.16.16.34:3306)
Sat May 27 15:50:48 2017 - [info] Current Alive Master: 172.16.16.34(172.16.16.34:3306)
Sat May 27 15:50:48 2017 - [info] Checking slave configurations..
Sat May 27 15:50:48 2017 - [info] Checking replication filtering settings..
Sat May 27 15:50:48 2017 - [info] binlog_do_db= , binlog_ignore_db=
Sat May 27 15:50:48 2017 - [info] Replication filtering check ok.
Sat May 27 15:50:48 2017 - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.
Sat May 27 15:50:48 2017 - [info] Checking SSH publickey authentication settings on the current master..
Sat May 27 15:50:48 2017 - [info] HealthCheck: SSH to 172.16.16.34 is reachable.
Sat May 27 15:50:48 2017 - [info]
172.16.16.34(172.16.16.34:3306) (current master)
+--172.16.16.35(172.16.16.35:3306)
+--172.16.16.35(172.16.16.35:3307)
Sat May 27 15:50:48 2017 - [warning] master_ip_failover_script is not defined.
Sat May 27 15:50:48 2017 - [warning] shutdown_script is not defined.
Sat May 27 15:50:48 2017 - [info] Set master ping interval 1 seconds.
Sat May 27 15:50:48 2017 - [info] Set secondary check script: /usr/bin/masterha_secondary_check -s server03 -s server02
Sat May 27 15:50:48 2017 - [info] Starting ping health check on 172.16.16.34(172.16.16.34:3306)..
Sat May 27 15:50:48 2017 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond.. 如果我们向关闭的话也非常简单
[root@localhost .ssh]# masterha_stop --conf=/etc/mha/app1.cnf5: 管理 VIP：
我们上面已经说过了，有两种 VIP 的管理方式，一种是 keepalived，一种是脚本的方式管理 VIP，keepalived 的管理方式比较简单就是主节点和备用节点两台机器，监控 MySQL 进程就好了，这个和 keepalived+MySQL 双主并没有太大区别在配置方面，关于这个配置可以看下我的上篇博客，博客地址：keepalived+MySQL 双主搭建
下面我们主要使用脚本的方式管理 VIP，定义 master_ip_failover，我们这里直接使用大师兄的博客里面的脚本：
#!/usr/bin/env perl
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 = '172.16.16.20/24';
my $key = '1';
my $ssh_start_vip = "/sbin/ifconfig eth0:$key $vip";
my $ssh_stop_vip = "/sbin/ifconfig eth0:$key down";
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();
$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 stop_vip() {return 0 unless ($ssh_user);
`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";
}然后我们手动在 server1 上添加虚拟 IP
/sbin/ifconfig eth0:1 172.16.16.20/24重新提起来 MHA manager：
[root@localhost masterha]# masterha_stop --conf=/etc/masterha/app1.cnf
[root@localhost masterha]# nohup masterha_manager --conf=/etc/masterha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 &
[root@localhost masterha]# masterha_check_status --conf=/etc/masterha/app1.cnf
app1 (pid:3953) is running(0:PING_OK), master:172.16.16.34更多详情见请继续阅读下一页的精彩内容 ：http://www.linuxidc.com/Linux/2017-06/144615p2.htm
正文完
星哥玩云-微信公众号
