MHA搭建以及测试

环境信息

192.168.1.84 主库 manager节点（机器原因被迫在该机器上，应该单独部署的）
192.168.1.79 从库
192.168.1.101 从库
（主从环境自己可以搭建）
slave的配置文件：
log_bin=/home/birdteam/log/mysql-bin
read_only=1
relay_log_purge=0
一主一从不用此项，两从及以上建议打开此参数，防止切换为成主库的从库自动删除中继日志后，无法给其他从库应用这部分日志

master：https://github.com/yoshinorim/mha4mysql-manager/releases
node：https://github.com/yoshinorim/mha4mysql-node/releases

安装epel源
yum install -y epel-release
安装perl
yum install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager
yum install -y net-tools
在manager节点上安装管理包，其他所有节点上安装node包

配置免密

三台都需要
[root@master ~]# cd .ssh/
[root@master .ssh]# ll
total 8
-rw------- 1 root root 1675 May 1 16:45 id_rsa//私钥
-rw-r–r-- 1 root root 393 May 1 16:45 id_rsa.pub//公钥
将生成的公钥追加到认证文件中：
cat /root/.ssh/id_rsa.pub >> authorized_keys
修改ssh文件夹和公钥的权限：
chmod 700 /root/.ssh
chmod 600 authorized_key
将公钥传输到其他2个机器上：
ssh-copy-id -i id_rsa.pub root@slave
ssh-copy-id -i id_rsa.pub root@bakmas

配置环境

mkdir /etc/masterha
vim /etc/masterha/app1.conf
[root@localhost app1]# cat app1.conf

[server default]
manager_workdir = /data/mha/app1
manager_log = /etc/masterha/app1/manager.log
remote_workdir = /etc/masterha/app1
master_ip_failover_script=/etc/masterha/conf/master_ip_failover
#master failover时执行
report_script=/etc/masterha/conf/send_report
#master failover时执行，发送邮件使用
master_ip_online_change_script=/etc/masterha/conf/master_ip_online_change
#master_switchover时执行（手动切换）
user=mha
password=123456
ping_interval=1
ping_type=CONNECT
repl_password=123456
repl_user=repl
ssh_port=22
ssh_user=root
[server1]
hostname = 192.168.1.84
port=3306
master_binlog_dir = /data/mha/log/
candidate_master = 1
#这个服务器有较高的优先级提升为新的master（还要具备：开启binlog使复制没有延迟）
[server2]
hostname = 192.168.1.101
port=3306
master_binlog_dir = /data/mha/log/
candidate_master =1
ignore_fail=1
[server3]
hostname = 192.168.1.79
port=3306
master_binlog_dir = /data/mysql/log/
candidate_master =1
ignore_fail=1
#如slave存在故障，在主库出现问题时默认情况下mha不会进行故障切换，该参数即设定MHA会在所有的机器有问题的时间也会进行故障切换
no_master=1
#不将这台主机转换为master

然后到conf目录下
chmod +x master*
分别执行
masterha_check_ssh --conf=/etc/masterha/app1/app1.conf （测试互信）
masterha_check_repl --conf=/etc/masterha/app1/app1.conf （测试主从）

然后给master节点添加一个vip
ifconfig eth0:0 192.168.1.102

然后启动
nohup masterha_manager --conf=/etc/masterha/app1/app1.conf --ignore_last_failover > /tmp/mha_manager.log < /dev/null 2>&1 &

之后
[root@localhost app1]# ps -ef|grep master
root 9519 29060 5 17:41 pts/0 00:00:00 perl /usr/bin/masterha_manager --conf=/etc/masterha/app1/app1.conf --ignore_last_failover --remove_dead_master_conf
root 9621 29060 0 17:42 pts/0 00:00:00 grep --color=auto master

检测状态
[root@localhost app1]# masterha_check_status --conf=/etc/masterha/app1/app1.conf
app1 (pid:9519) is running(0:PING_OK), master:192.168.1.84

手工停掉mha监控
masterha_stop --conf=/etc/masterha/app1/app1.conf

切换主

MHA failover测试 直接kill掉主库线程观察日志即可
或者
MHA 手动切换
masterha_master_switch --master_state=alive --conf=/etc/masterha/app1/app1.conf --orig_master_is_new_slave --interactive=0 --new_master_host=192.168.1.101 --new_master_port=3306
1、参数–running_updates_limit如果现在master执行写操作的执行时间大于这个参数，或任何一台slave的Seconds_Behind_Master大于这个参数，那么master switch将自动放弃，默认参数为1s；
2、参数–interactive=0非交互切换，建议加上，可以大大加快切换速度，加上后库不忙时大概3秒内切换完成。
3、–orig_master_is_new_slave 切换时加上此参数是将原 master 变为 slave
节点，如果不加此参数，原来的 master 将不启动。

在manager的manage.log中可以查看change master的信息
**

# cat /var/log/masterha/app1/manager.log |grep -i "All other slaves should start"

**
获取到change master to 对应的信息，在旧主上执行，重新设置为新主的从库
然后启动冲库，查看同步状态

对于修复好的旧主，并没有加入到mha监控环境中，需要手工加入
masterha_conf_host --command=add --conf=/etc/masterha/app1/app1.conf --hostname=192.168.1.84 --block=server1 --params=“no_master=1;ignore_fail=1”
或者是直接手工编辑app1.cnf配置文件添加该信息，再次检查复制环境
masterha_check_repl --conf=/etc/masterha/app1/app1.conf

遇到的问题汇总

1、每次故障切换完成之后，如果想继续测试，需要删除remote_workdir下边的complete文件
2、每次切换之后需要重新拉起manager
3、mysqlbinlog跟mysql命令需要做软连接到/usr/bin下边
4、切换过程中定时器需要临时关闭，不然会报错。待切换完成再开启
5、线上在用的my.cnf配置文件的[client]段有配置default-character-set=utf8。在用masterha_check_repl --conf=/etc/masterha_default.cnf检测 复制状态的时候会报错：
Wed Jan 20 03:03:43 2016 - [info] Connecting to mha@192.168.1.101(192.168.1.101:22)…
mysqlbinlog: unknown variable ‘default-character-set=utf8’
mysqlbinlog version command failed with rc 7:0, please verify PATH, LD_LIBRARY_PATH, and client options
at /usr/bin/apply_diff_relay_logs line 493
解决方法：
因为mysqlbinlog工具查看二进制日志时会重新读取的mysql的配置文件my.cnf，而不是服务器已经加载进内存的配置文件，所以可以把my.cnf的default-character-set=utf8这个参数注释掉，但是可以不重启，这样既不会对线上业务有影响，也可以避免复制状态检测报错
6、如果root用户不允许做ssh免密码登陆，用非root用户部署mha的时候要把这个用户加入到mysql组，让其有操作binlog和relaylog的权限（没有实践）

附录

[root@localhost conf]# ls
master_ip_failover  master_ip_online_change  send_report
[root@localhost conf]# cat 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 = '192.168.1.102/24';
my $key = '0';
my $ssh_start_vip = "/usr/sbin/ifconfig ens192:$key $vip";
my $ssh_stop_vip = "/usr/sbin/ifconfig ens192:$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";
}
[root@localhost conf]#
[root@localhost conf]#
[root@localhost conf]# cat master_ip_online_change
#!/usr/bin/env perl
use strict;
use warnings FATAL =>'all';
use Getopt::Long;
my $vip = '192.168.1.102/24'; # Virtual IP
my $key = "0";
my $ssh_start_vip = "/usr/sbin/ifconfig ens192:$key $vip";
my $ssh_stop_vip = "/usr/sbin/ifconfig ens192:$key down";
my $exit_code = 0;
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,
);
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 main {
#print "\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n";
if ( $command eq "stop" || $command eq "stopssh" ) {
# $orig_master_host, $orig_master_ip, $orig_master_port are passed.
# If you manage master ip address at global catalog database,
# invalidate orig_master_ip here.
my $exit_code = 1;
eval {
print "\n\n\n***************************************************************\n";
print "Disabling the VIP - $vip on old master: $orig_master_host\n";
print "***************************************************************\n\n\n\n";
&stop_vip();
$exit_code = 0;
};
if ($@) {
warn "Got Error: $@\n";
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "start" ) {
# all arguments are passed.
# If you manage master ip address at global catalog database,
# activate new_master_ip here.
# You can also grant write access (create user, set read_only=0, etc) here.
my $exit_code = 10;
eval {
print "\n\n\n***************************************************************\n";
print "Enabling the VIP - $vip on new master: $new_master_host \n";
print "***************************************************************\n\n\n\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";
`ssh $orig_master_ssh_user\@$orig_master_host \" $ssh_start_vip \"`;
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 \"`;
}
# 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_failover -command=start|stop|stopssh|status -orig_master_host=host -orig_master_ip=ip -
orig_master_port=po
rt -new_master_host=host -new_master_ip=ip -new_master_port=port\n";
}
[root@localhost conf]#
[root@localhost conf]#
[root@localhost conf]# cat send_report
#!/bin/bash

source /root/.bash_profile
orig_master_host=`echo "$1" | awk -F = '{print $2}'`
new_master_host=`echo "$2" | awk -F = '{print $2}'`
new_slave_hosts=`echo "$3" | awk -F = '{print $2}'`
subject=`echo "$4" | awk -F = '{print $2}'`
body=`echo "$5" | awk -F = '{print $2}'`

#判断日志结尾是否有successfully，有则表示切换成功，成功与否都发邮件
tac /etc/masterha/app1/manager.log | sed -n 2p | grep 'successfully' > /dev/null
if [ $? -eq 0 ]
  then
  echo -e "MHA $subject 主从切换成功\n master:$orig_master_host --> $new_master_host \n $body \n 当前从库:$new_slave_hosts" | mailx -s "MySQL实例宕掉,MHA $subject 切换成功" 123456@xx.com
else
  echo -e "MHA $subject 主从切换失败\n master:$orig_master_host --> $new_master_host \n $body" | mailx -s "MySQL实例宕掉,MHA $subject 切换失败" 123456@xx.com
fi

[root@localhost conf]#