一、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 {
"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 a
###如果不在手动添加
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
