澳门金沙vip 7

澳门金沙vip基于MGR+Atlas的读写分离尝试,以及MGR+Keepalived+Atlas自动故障转移+读写分离设想

 

rpm安装,瞬间完成

手动故障转移:

 

澳门金沙vip 1

[root@localhost .ssh]# cat id_rsa.pub >>authorized_keys

 

基于MGR+Keepalived+Atlas的高可用加读写分离

chmod 600 /root/.ssh/authorized_keys

keepalived负责的是故障转移,至于故障转以后的节点之间数据的一致性问题依赖于具体的复制模式。
不管是主从、一主多从还是双主、集群节点个数、主从具体的模式无关(常规复制,半同步复制,GTID复制,多线程复制,甚至可以是MGR)都没有直接的关系。
个人认为,MySQL高可用方向,MGR+自动故障转移中间件(keepalived),应该是是个趋势。怎么感觉MHA的配置又臭又长。

 

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

 

 

如果我们向关闭的话也非常简单

keepalive的安装

尚无进行分表测试。

以一台机器为例,34拷贝自己的公钥到其他机器:

1,参考

后续可以尝试,在MGR的基础上做一个基于keepalived的自动故障转移,写节点可以基于VIP做自动故障转移,
然后在此基础上,基于VIP+其他节点做读写分离,理论上可以完美地实现自动故障转移的高可用+读写分离。
这样YY起来的话,感觉这样子也略屌,自动故障转移有了,读写分离也有了,理论上,只要有一个存活的节点,都可以正常对外提供服务。

我这边包是已经下载好的,直接使用rpm安装了。至此算是安装完毕了

  1,主从(或者主主)服务器之间相互ping的同,如果不同,可能是防火墙的原因
  2,给予相关shell脚本的可执行权限,chmod +x mysql_check.sh
  3,测试之前先搭建好主从,单纯测试keepalived的故障转移功能的话,甚至主从就不用搭建,所以高可用的本质,还是跟复制的模式有很大的关系

澳门金沙vip 2

MHA算是业内比较成熟的MySQL高可用解决方案,在MySQL故障切换过程中,MHA能做到自动完成数据库的故障切换操作,并且在进行故障切换的过程中,MHA能在最大程度上保证数据的一致性,以达到真正意义上的高可用。软件主要有MHA
Manager(管理节点)和MHA
Node(数据节点)两部分组成,在MHA自动故障切换过程中,MHA试图从宕机的主服务器上保存二进制日志,最大程度的保证数据的不丢失,但这并不总是可行的。例如,如果主服务器硬件故障或无法通过ssh访问,MHA没法保存二进制日志,只进行故障转移而丢失了最新的数据。使用MySQL
5.5的半同步复制,可以大大降低数据丢失的风险。MHA可以与半同步复制结合起来。如果只有一个slave已经收到了最新的二进制日志,MHA可以将最新的二进制日志应用于其他所有的slave服务器上,因此可以保证所有节点的数据一致性。

 

这个节点需要注意的是,对应的用户名必须在每个是利用都存在且密码一样,否则会出现错误

最后,这文章大量参考了大师兄的MHA博客: 

 

目的是尝试altas的读写分离,现有一套搭建好做测试的MGR(单主),于是就腿搓绳,在MGR基础上搭建altas。

我们这样就可以手动清除掉relay log了,也可以加到定时人物里面定时执行。

 

altas详细配置参考:

[root@localhost .ssh]# masterha_stop --conf=/etc/mha/app1.cnf

2,Keepalived配置

读重定向到8003节点,写依旧是主节点,MGR状态也正常,如果尝试关闭所有的读节点,读将自动重定向到主(写)节点,说明从节点的错误都是可以兼容的。
这一点说明,从(读)节点的任何错误都是不影响atlas对外提供服务器的,如果做到主节点的高可用,atlas就可以完美地对外提供服务了。

可以看到已经有epel相关的资源了,所以我们就可以执行执行上面的yum语句安装MHA的依赖环境。

3,自行主从(主主)搭建,注意事项

复制环境准备

#!/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";
}

澳门金沙vip 3

启动atlas

我们假设一主两从的环境我们已经搭建好了。

1、官方下载
wget http://www.keepalived.org/software/keepalived-XXXX.tar.gz
2、解压,编译安装
tar zxvf keepalived-XXXX.tar.gz
./configure --prefix=/usr/local/keepalived --with-kernel-dir=/usr/src/kernels/2.6.XXXXXXXX/
make && make install
cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/rc.d/init.d/
cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
mkdir /etc/keepalived
cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/
cp /usr/local/keepalived/sbin/keepalived /usr/sbin/
service keepalived start

#用户名与其对应的加密过的MySQL密码,密码使用PREFIX/bin目录下的加密程序encrypt加密,下行的user1和user2为示例,将其替换为你的MySQL的用户名和加密密码!
pwds = username1:DAJnl8cVzy8=, username2:DAJnl8cVzy8=

发现已经自动切换了,接下来看一下日志:

整个搭建过程不算复杂,如果各个节点之间使用脚本标准化安装的话并不需要花很长时间,以下是节点的故障转移的效果,可以发现一个连接可以自动从一个节点转移到另外一个节点。

读写分离理论上讲,跟复制模式没有关系,atlas负责的是重定向读写,至于复制模式自己选择,这里是测试环境,之前测试MGR的单机多实例,MGR单主模式的复制模式,就顺便借助MGR做基于atlas的读写分离。

1:首先我们要安装MHA的安装包,安装MHA以前,要安装一些依赖环境

参考

MGR是一主二从,主节点Server_id是8001,从节点的Server_id分别是8002和8003
可以发现读信息重定向到8002节点,写信息重定向到8001节点,实现了读写分离

[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@%的最高权限给MHA来使用。由于我们假设一主两从是已经搭建好的,具体授权什么的也不在赘述了。相信大家配置MHA的话对于这些小问题都是小儿科了。

澳门金沙vip 4

(2)测试一下自动故障转移

测试读写分离

Manager工具包主要包括以下几个工具:

 

不过在此之前要设置我们的从库read_only=1;

强制关掉一个从节点,将读重定向到次级读节点。

然后执行授权语句:

澳门金沙vip 5

发现是失败的,我们这里需要把自己的公钥信息加入到认证(两台机器都要执行):

[mysql-proxy]

#带#号的为非必需的配置项目

#管理接口的用户名
admin-username = user

#管理接口的密码
admin-password = pwd

#Atlas后端连接的MySQL主库的IP和端口,可设置多项,用逗号分隔
proxy-backend-addresses = ***.***.***.***:8001

#Atlas后端连接的MySQL从库的IP和端口,@后面的数字代表权重,用来作负载均衡,若省略则默认为1,可设置多项,用逗号分隔
proxy-read-only-backend-addresses = proxy-read-only-backend-addresses =***.***.***.***:8002@1,***.***.***.***:8003@2

#用户名与其对应的加密过的MySQL密码,密码使用PREFIX/bin目录下的加密程序encrypt加密,下行的user1和user2为示例,将其替换为你的MySQL的用户名和加密密码!
pwds = user1:DAJnl8cVzy8=, user2:DAJnl8cVzy8=

#设置Atlas的运行方式,设为true时为守护进程方式,设为false时为前台方式,一般开发调试时设为false,线上运行时设为true,true后面不能有空格。
daemon = true

#设置Atlas的运行方式,设为true时Atlas会启动两个进程,一个为monitor,一个为worker,monitor在worker意外退出后会自动将其重启,设为false时只有worker,没有monitor,一般开发调试时设为false,线上运行时设为true,true后面不能有空格。
keepalive = true

#工作线程数,对Atlas的性能有很大影响,可根据情况适当设置
event-threads = 8

#日志级别,分为message、warning、critical、error、debug五个级别
log-level = message

#日志存放的路径
log-path = /usr/local/mysql-proxy/log

#SQL日志的开关,可设置为OFF、ON、REALTIME,OFF代表不记录SQL日志,ON代表记录SQL日志,REALTIME代表记录SQL日志且实时写入磁盘,默认为OFF
#sql-log = OFF

#慢日志输出设置。当设置了该参数时,则日志只输出执行时间超过sql-log-slow(单位:ms)的日志记录。不设置该参数则输出全部日志。
#sql-log-slow = 10

#实例名称,用于同一台机器上多个Atlas实例间的区分
#instance = test

#Atlas监听的工作接口IP和端口
proxy-address = ***.***.***.***:1234

#Atlas监听的管理接口IP和端口
admin-address = ***.***.***.***:2345

#分表设置,此例中person为库名,mt为表名,id为分表字段,3为子表数量,可设置多项,以逗号分隔,若不分表则不需要设置该项
#tables = person.mt.id.3

#默认字符集,设置该项后客户端不再需要执行SET NAMES语句
#charset = utf8

#允许连接Atlas的客户端的IP,可以是精确IP,也可以是IP段,以逗号分隔,若不设置该项则允许所有IP连接,否则只允许列表中的IP连接
#client-ips = 127.0.0.1, 192.168.1

#Atlas前面挂接的LVS的物理网卡的IP(注意不是虚IP),若有LVS且设置了client-ips则此项必须设置,否则可以不设置
#lvs-ips = 192.168.1.1

5:管理VIP:

澳门金沙vip 6

切换以后我们发现现在主从是OK的了,但是我们忘记了很重要的一个问题,那就是slave的relay
log,主从复制在缺省情况下从库的relay
logs会在SQL线程执行完毕后被自动删除,但是对于MHA场景下,对于某些滞后从库的恢复依赖于其他从库的relay
log,因此采取禁用自动删除功能以及定期清理的办法。对于清理过多过大的relay
log需要注意引起的复制延迟资源开销等。所以这里要将relay
log的自动清除设置为OFF,采用手动清除relay log的方式:

atlas安装

简单介绍一下MHA的Manager工具包和Node工具包

atlas的配置文件(默认/usr/local/mysql_proxy/conf/test.cnf),个人感觉是一个非常清爽的配置,基本上配置节点的备注都非常清楚。
澳门金沙vip,因为是单机多实例,这里仅仅通过不同的端口号来区分从库实例,可以是单从,也可以是多从,按照优先级来拆分。

[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

澳门金沙vip 7

/usr/bin/purge_relay_logs --user=root --password=123456 --host=172.16.16.35 --port=3307

 

yum install -y perl-DBD-MySQL

 

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)..