【mysql集群】二、MHA配置

ゝ一纸荒年。 2022-05-14 06:41 200阅读 0赞

# MHA 实现故障转移和主从切换 #

## 配置MHA ##

参考博客：[https://www.cnblogs.com/xuanzhi201111/p/4231412.html][https_www.cnblogs.com_xuanzhi201111_p_4231412.html]

监控节点 搭建在 3号从服务器上

*  在所有机器都要安装MHA node所需的perl模块（DBD:mysql），可以通过yum安装，如果没epel源,先安装epel源

192.168.2.128 [root ~]$ rpm -ivh http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
    Retrieving http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
    warning: /var/tmp/rpm-tmp.SAbcKl: Header V3 RSA/SHA256 Signature, key ID 0608b895: NOKEY
    Preparing...                ########################################### [100%]
       1:epel-release           ########################################### [100%]
    192.168.2.128 [root ~]$ yum install perl-DBD-MySQL -y

*  在所有的节点安装MHA node：（下面以server02为例，记得server03和server04也一样的操作），MHA node和MHA Manager都在要官网下载：

[https://code.google.com/archive/p/mysql-master-ha/downloads][https_code.google.com_archive_p_mysql-master-ha_downloads]

192.168.2.128 [root ~]$ tar xf mha4mysql-node-0.56.tar.gz 
    192.168.2.128 [root ~]$ cd mha4mysql-node-0.56
    192.168.2.128 [root mha4mysql-node-0.56]$ perl Makefile.PL
    Can't locate ExtUtils/MakeMaker.pm in @INC (@INC contains: inc /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at inc/Module/Install/Makefile.pm line 4.
    192.168.2.128 [root mha4mysql-node-0.56]$ yum install -y perl-devel
    192.168.2.128 [root mha4mysql-node-0.56]$ perl Makefile.PL          
    *** Module::AutoInstall version 1.03
    *** Checking for Perl dependencies...
    Can't locate CPAN.pm in @INC (@INC contains: inc /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at inc/Module/AutoInstall.pm line 279.
    192.168.2.128 [root mha4mysql-node-0.56]$ yum install -y perl-CPAN
    
    192.168.2.128 [root mha4mysql-node-0.56]$ perl Makefile.PL        
    *** Module::AutoInstall version 1.03
    *** Checking for Perl dependencies...
    [Core Features]
    - DBI        ...loaded. (1.609)
    - DBD::mysql ...loaded. (4.013)
    *** Module::AutoInstall configuration finished.
    Checking if your kit is complete...
    Looks good
    Writing Makefile for mha4mysql::node
    192.168.2.128 [root mha4mysql-node-0.56]$ make && make install

*  master 主机上安装  
    安装MHA Manager。首先安装MHA Manger依赖的perl模块（我这里使用yum安装）：

mha4mysql-manager-0.54.tar.gz

*  MHA-manager 配置  
    masterha/app1.cnf 文件

[server default]
    manager_workdir=/var/log/masterha/app1
    manager_log=/var/log/masterha/app1/manager.log
    
    master_binlog_dir=/data/mysql                       //设置master 保存binlog的位置，以便MHA可以找到master的日志，我这里的也就是mysql的数据目录
    #master_ip_failover_script= /usr/local/bin/master_ip_failover            //设置自动failover时候的切换脚本
    master_ip_online_change_script= /usr/local/bin/master_ip_online_change  //设置手动切换时候的切换脚本
    password=****                           //设置mysql中root用户的密码，这个密码是前文中创建监控用户的那个密码
    user=repl
    #ping_interval=1                           //设置监控主库，发送ping包的时间间隔，默认是3秒，尝试三次没有回应的时候自动进行railover
    remote_workdir=/tmp                       //设置远端mysql在发生切换时binlog的保存位置
    repl_password=*****                      //设置复制用户的密码
    repl_user=*****                            //设置复制环境中的复制用户名
    report_script=/usr/local/bin/send_report     //设置发生切换后发送的报警的脚本
    secondary_check_script= /usr/local/bin/masterha_secondary_check -s server03 -s server02 --user=root --master_host=server02 --master_ip=********* --master_port=3306                              //一旦MHA到server02的监控之间出现问题，MHA Manager将会尝试从server03登录到server02
    #shutdown_script=""                          //设置故障发生后关闭故障主机脚本（该脚本的主要作用是关闭主机放在发生脑裂,这里没有使用）
    #ssh_user=root                               //设置ssh的登录用户名
    
    
    [server1]
    hostname=*******
    port=3306
    
    [server2]
    hostname=******
    port=3306
    candidate_master=1
    check_repl_delay=0
    
    [server3]
    hostname=******
    port=3306

1.  检查SSH配置(server01 192.168.2.131 Monitor 监控节点上操作)，

masterha_check_ssh --conf=/etc/masterha/app1.cnf

1.  检查整个复制环境状况(server01 192.168.2.131 Monitor 监控节点上操作)，

masterha_check_repl --conf=/etc/masterha/app1.cnf

1.  检查MHA Manager的状态  
    通过master\_check\_status脚本查看Manager的状态：

masterha_check_status --conf=/etc/masterha/app1.cnf

1.  开启MHA Manager监控

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

## MHA 实现VIP切换用到脚本(实现故障转移) ##

参考博客：[https://blog.csdn.net/lichangzai/article/details/50503960][https_blog.csdn.net_lichangzai_article_details_50503960]

#!/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 = '10.1.5.21/24';  # Virtual IP
    my $key = "1";
    my $ssh_start_vip = "/sbin/ifconfig eth0:$key $vip";
    my $ssh_stop_vip = "/sbin/ifconfig eth0:$key down";
    my $exit_code = 0;
     
    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 "\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 $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=po
    rt –new_master_host=host –new_master_ip=ip –new_master_port=port\n";
    }

注意事项：(我躺过的坑)  
执行 masterha\_check\_repl --conf=/etc/masterha/app1.cnf 这个命令报错：

*  删除app1.cnf 中同一行的注释，否则报错
 *  master\_ip\_failover 必须放到 /usr/local/bin/ 这个目录下否则报错
 *  如果MHA Manager 和 MHA node 安装在同一台机器上，需要让本机也设置免密登录，否则报错

参考：  
MYSQL之MHA集群

> [https://www.cnblogs.com/chenglee/p/9052168.html][https_www.cnblogs.com_chenglee_p_9052168.html]

[https_www.cnblogs.com_xuanzhi201111_p_4231412.html]: https://www.cnblogs.com/xuanzhi201111/p/4231412.html
[https_code.google.com_archive_p_mysql-master-ha_downloads]: https://code.google.com/archive/p/mysql-master-ha/downloads
[https_blog.csdn.net_lichangzai_article_details_50503960]: https://blog.csdn.net/lichangzai/article/details/50503960
[https_www.cnblogs.com_chenglee_p_9052168.html]: https://www.cnblogs.com/chenglee/p/9052168.html