一、MHA

1、解压mysql包到/opt目录

[root@localhost opt\]# tar -zxvf mysql-5.7.26-linux-glibc2.12-x86\_64.tar.gz

2、创建关键程序软连接

ln -s /usr/local/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog
ln -s /usr/local/mysql/bin/mysql /usr/bin/mysql

3、节点间配置互信

###db1:

rm -rf /root/.ssh
ssh-keygen
cd /root/.ssh
mv id\_rsa.pub authorized\_keys
scp -r /root/.ssh root@192.168.1.70:/root
scp -r /root/.ssh root@192.168.1.71:/root

4、验证

###db1:

ssh 192.168.1.61 date
ssh 192.168.1.70 date
ssh 192.168.1.71 date

###db2:

ssh 192.168.1.61 date
ssh 192.168.1.70 date
ssh 192.168.1.71 date

###db3:

ssh 192.168.1.61 date
ssh 192.168.1.70 date
ssh 192.168.1.71 date

5、所有节点（数据库）安装node软件依赖

yum install perl-DBD-MySQL -y

上传并安装

rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm

6、在主库创建mha需要的用户

grant all privileges on \*.\* to mha@'%' identified by 'Mha@123';
flush privileges;

7、Manager安装

###db4单独的节点

yum install -y perl-Config-Tiny epel-release perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes

###上传并安装

rpm -ivh mha4mysql-manager-0.56-0.e16.noarch.rpm

8、Manager配置文件准备（db4）

###创建配置文件目录

[root@localhost etc\]# mkdir mha

###创建日志目录

[root@localhost log\]# pwd
###/data/mha/log

###编辑mha配置文件

vim /etc/mha/mha.cnf
###
[server default\] 
#工作日志 
manager_log=/data/mha/log/manager.log
manager_workdir=/data/mha/log
#主库的binlog日志位置 
master_binlog_dir=/data/mysql/3306/log 
#manger监控用户 
user=mha 
password=Mha@123 
#探测心跳的监控时间 
ping_interval=2
repl_password=123 
repl_user=repl
ssh_user=root 
#如果主库宕机，根据排列顺序切换新主
[server1] 
hostname=192.168.1.61 
port=3306
candidiate_master=1  强制选主 
check_repl_delay=0   不检查日志落后 
[server2]
hostname=192.168.1.70 
port=3306 
[server3] 
hostname=192.168.1.71 
port=3306

9、配置状态检查（db4）

masterha_check_ssh --conf=/etc/mha/mha.cnf
masterha_check_repl --conf=/etc/mha/mha.cnf

10、开启MHA-manager(db3)

nohup masterha_manager --conf=/etc/mha/mha.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null> /data/mha/log/manager.log 2>&1 &

11、查看MHA状态

masterha_check_status --conf=/etc/mha/mha.cnf

二、应用透明

1、配置参数（db4）

#添加以下参数：

vim /etc/mha/mha.cnf
###
[server default\]
master_ip_failover_script=/usr/local/bin/master_ip_failover

2、修改脚本(db4)

vim /usr/local/bin/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.74/24';
my $key = "1";
my $ssh_start_vip = "/sbin/ifconfig ens33:$key $vip";
my $ssh_stop_vip = "/sbin/ifconfig ens33:$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" ) {
# $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 "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" ) {
# 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 "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";
`ssh $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 $ssh_user\\@$new_master_host \\" $ssh_start_vip \\"\`;
}
# A simple system call that disable the VIP on the old_master
sub stop_vip() {
`ssh $ssh_user\\@$orig_master_host \\" $ssh\_stop_vip \\"\`;
}
sub usage {
print
"Usage: master_ip_failover --command=start|stop|stopssh|status --orig_master_host=host --orig_master_ip=ip --orig_master_port=port --new_master_host=host --new_master_ip=ip --new_master_port=port\\n";
}

3、修改权限

chmod +x /usr/local/bin/master_ip_failover

4、主库（db1）上手工生成第一个vip地址

ifconfig ens33:1 192.168.1.74/24

5、重启mha(db4)

masterha\_stop --conf=/etc/mha/mha.cnf
nohup masterha\_manager --conf=/etc/mha/mha.cnf --remove\_dead\_master\_conf --ignore\_last\_failover < /dev/null> /data/mha/log/manager.log 2>&1 &

6、检查状态

masterha_check_status --conf=/etc/mha/mha.cnf

三、通过binlog server（db3）实现数据补偿

1、修改配置文件

（db4）

vim /etc/mha/mha.cnf
###
[binlog1]
no_master=1   #不参与选主
hostname=192.168.1.72
master_binlog_dir=/data/mysql/binlog  ###不能与原来日志目录相同

2、创建目录并授权

[root@localhost mysql]# mkdir binlog
[root@localhost binlog\]# pwd
###/data/mysql/binlog
chown -R mysql.mysql binlog

3、拉取主库binlog日志

###db3

cd /data/mysql/binlog
mysqlbinlog -R --host=192.168.1.61 --user=mha --password=Mha@123 --raw --stop-never mysql-bin.000001 &

###注意：
拉取日志的起点，需要按照目前从库已经获得到的二进制日志为起点
可以在主库查看
mysql> show master status;

4、重启mha

masterha_stop --conf=/etc/mha/mha.cnf
nohup masterha_manager --conf=/etc/mha/mha.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null> /data/mha/log/manager.log 2>&1 &

5、检查状态

masterha_check_status --conf=/etc/mha/mha.cnf

四、邮件提醒(db4)

1、上传并解压sendEmail

[root@localhost opt\]# tar -zxvf sendEmail-v1.56.tar.gz

2、拷贝执行文件

mv sendEmail /usr/local/bin/

3、编辑发送邮件脚本

cd /usr/local/bin
vim send
###
#!/bin/bash
/usr/local/bin/sendEmail -o tls=no -f xxxxxxx@qq.com -t xxxxxx@qq.com -s smtp.qq.com:25 -xu xxxxxx@qq.com -xp mgsufeiukzvibach -u "MHA Waring" -m "YOUR MHA MAY BE FAILOVER" &>/tmp/sendmail.log

4、授权

chmod +x send

5、测试发送邮件

./send

6、修改manager配置文件

vim /etc/mha/mha.cnf
###
report_script=/usr/local/bin/send

7、重启mha

masterha_stop --conf=/etc/mha/mha.cnf
nohup masterha_manager --conf=/etc/mha/mha.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null> /data/mha/log/manager.log 2>&1 &

8、检查状态

masterha_check_status --conf=/etc/mha/mha.cnf

五、测试MHA功能

1、宕掉主库

查看vip飘逸

查看邮件

查看日志

故障库是否剔除

主从状态

六、修复思路

1、排查进程状态（db4）

ps -ef|grep manager
masterha\_check\_status --conf=/etc/mha/mha.cnf

2、检查配置文件

###查看故障节点是否存在
如果被移除说明切换过程大部分已经成功
如果节点还在，证明切换过程有问题

3、查看日志

vim /data/mha/manager.log

5、修复故障库

6、修复主从

将故障库修好后手工加入集群

###

mysql> change master to

   -> master_host='192.168.1.70',

   -> master_user='repl',

   -> master_password='Repl@123',

   -> master_auto_position=1;

Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

7、修复配置文件（db4）

vim /etc/mha/mha.cnf
###
恢复原样

8、检查ssh互信和rely的状态

masterha_check_ssh --conf=/etc/mha/mha.cnf
masterha_check_repl --conf=/etc/mha/mha.cnf

9、修复binlogserver

###db3

cd /data/mysql/binlog
rm -rf *

###拉取新主库的binlog

mysqlbinlog -R --host=192.168.1.70 --user=mha --password=Mha@123 --raw --stop-never mysql-bin.000001 &

10、检查vip是否在主节点上

IP addr

###如果不在手动添加

11、启动MHA（db4）

nohup masterha_manager --conf=/etc/mha/mha.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null> /data/mha/log/manager.log 2>&1 &

12、检查状态

masterha_check_status --conf=/etc/mha/mha.cnf