MHA搭建高可用MySQL集群(仅用于测试环境)

1. MHA的作用

监控数据库服务器是否可用，当主db不可用时，从多个从服务器中选举出新的数据库服务器，提供了主从切换和故障转移功能

2. 主从切换过程

尝试从出现故障的主数据库保存二进制日志，从多个备选从服务器选举出新的备选主服务器，在备选主服务器和其他从服务器之间同步差异二进制数据，从原主db服务器上保存的二进制日志，提升备选主db服务器为新的主db服务器，迁移集群中的其他从db作为新的主db的从服务器

3. 实验环境

Liunx发行版:centos-release-7-7.1908.0.el7.centos.x86_64

服务器	IP	说明
A	192.168.127.128	主库，mha-node
B	192.168.127.129	从库，mha-node
C	192.168.127.130	从库，mha-node,mha-manager

4. 搭建MySQL复制

复制可以看这篇博客
https://blog.csdn.net/weixin_40132589/article/details/103343776

实验环境为了方便关闭防火墙

 service firewalld stop

5. MHA安装

所有主机的ssh免认证登录

ssh-keygen
ssh-copy-id -i /root/.ssh/id_rsa -p 22 root@192.168.127.128;
ssh-copy-id -i /root/.ssh/id_rsa -p 22 root@192.168.127.129;
ssh-copy-id -i /root/.ssh/id_rsa -p 22 root@192.168.127.130;

下载MHA的node和manager包，https://github.com/yoshinorim/mha4mysql-manager/wiki/Downloads

安装MHA的node包的依赖

 yum install perl-DBD-MySQL

安装MHA的node包

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

node包安装成功如下图

安装MHA的mamager包
安装依赖包

yum install perl-DBD-MySQL
yum install perl-Config-Tiny
yum install perl-Log-Dispatch
yum install perl-Parallel-ForkManager
rpm -ivh mha4mysql-manager-X.Y-0.noarch.rpm

如果yum找不到相应的包，执行下面

 yum install https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
 rpm -ivh mha4mysql-manager-0.56-0.el6.noarch.rpm

manager包安装成功如下图

建mha的配置文件
mkdir -p /etc/mha
mkdir -p /home/mysql_mha

vi /etc/mha/mysql_mha.cnf

[server default]
	user=mha
	password=123456
	manager_workdir=/home/mysql_mha
	manager_log=/home/mysql_mha/manager.log
	remote_workdir=/home/mysql_mha
	ssh_user=root
	# 这里填MySQL复制的账号和密码
	repl_user=repl
	repl_password=123456
	ping_interval=1
	master_binlog_dir=/var/lib/mysql/mysql-bin
	master_ip_failover_script= /etc/mha/master_ip_failover_script
[server1]
	hostname=192.168.127.128
	# 参与主库选举
	candidate_master=1
[server2]
	hostname=192.168.127.129
	candidate_master=1
[server3]
	hostname=192.168.127.130
	# 不参与主库选举
	no_master=1

虚拟IP漂移perl脚本

vi /etc/mha/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
);
# 虚拟Ip
my $vip = '192.168.127.131'; 
# 通过ip addr可以查到
my $brdc = '192.168.127.255';
# 网卡名称
my $ifdev = 'ens33';
my $key = '1';
my $ssh_start_vip = "/usr/sbin/ip addr add $vip/24 brd $brdc dev $ifdev label $ifdev:$key;/usr/sbin/arping -q -A -c 1 -I $ifdev:$key $vip;iptables -F;";
my $ssh_stop_vip = "/usr/sbin/ip addr del $vip/24 dev $ifdev label $ifdev:$key";

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 \"`;
}
# 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";
}

创建mha的账号

grant all privileges on *.* to mha@'192.168.127.*' identified by '123456';

检测集群内服务器的ssh是否能免密登录

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

检测成功如下图

检测mysql复制是否正常

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

检测成功如下图

给A服务器绑定虚拟IP

# 安装net-tools，为了使用ifconfig命令
yum install net-tools.x86_64 -y
ifconfig ens33:1 192.168.127.131 broadcast 192.168.127.255 netmask 255.255.255.0

后台启动mha

nohup masterha_manager --conf=/etc/mha/mysql_mha.cnf

6. 测试

把A服务器的MySQL停止

service mysql stop

查看虚拟IP是否漂移到B服务器，在A服务器和B服务器都执行

ip addr

A服务器绑定的192.168.127.131已经转换到B服务器

查看C服务器的主服务器是否转移到B

show slave status \G;

C服务器的Master_Host从192.168.127.128变位192.168.127.129，主库切换成功