基于MHA的MySQL高可用架构搭建

知识剖析:

• MHA（Master High Availability）目前在MySQL高可用方面是一个相对成熟的解决方案，它由日本DeNA公司youshimaton（现就职于 Facebook公司）开发，是一套优秀的作为MySQL高可用性环境下故障切换和主从提升的高可用软件。

• 在MySQL故障切换过程中，MHA能做到在 0~30秒之内自动完成数据库的故障切换操作，并且在进行故障切换的过程中，MHA能在最大程度上保证数据的一致性，以达到真正意义上的高可用。

• 该软件由两部分组成：MHA Manager（管理节点）和MHA Node（数据节点）。MHA Manager可以单独部署在一*立的机器上管理多个master-slave集群，也可以部署在一台slave节点上。MHA Node运行在每台MySQL服务器上，MHA Manager会定时探测集群中的master节点，当master出现故障时，它可以自动将最新数据的slave提升为新的master，然后将所有其 他的slave重新指向新的master。整个故障转移过程对应用程序完全透明。

• 目前MHA主要支持一主多从的架构，要搭建MHA,要求一个复制集群中必须最少有三台数据库服务器，一主二从，即一台充当master，一台充当备用master，另外一台充当从库
  
  （1）从宕机崩溃的master保存二进制日志事件（binlog events）;

（2）识别含有最新更新的slave；

（3）应用差异的中继日志（relay log）到其他的slave；

（4）应用从master保存的二进制日志事件（binlog events）；

（5）提升一个slave为新的master；

（6）使其他的slave连接新的master进行复制；

实验环境：

主机	ip
server1	172.25.5.1(master)
server2(slave,(备用master))	172.25.5.2
server3（slave）	172.25.5.3
server4（MHA）	172.25.5.4

一、基于MHA的MySQL高可用架构搭建

1.首先配置好，基于GTID的主从半同步复制（一主两从）
server1：

[[email protected] ~]# tar xf mysql-5.7.24-1.el7.x86_64.rpm-bundle.tar 
[[email protected] ~]# scp mysql-community-client-5.7.24-1.el7.x86_64.rpm mysql-community-common-5.7.24-1.el7.x86_64.rpm mysql-community-libs-5.7.24-1.el7.x86_64.rpm mysql-community-libs-compat-5.7.24-1.el7.x86_64.rpm mysql-community-server-5.7.24-1.el7.x86_64.rpm [email protected]:
[[email protected] ~]# scp mysql-community-client-5.7.24-1.el7.x86_64.rpm mysql-community-common-5.7.24-1.el7.x86_64.rpm mysql-community-libs-5.7.24-1.el7.x86_64.rpm mysql-community-libs-compat-5.7.24-1.el7.x86_64.rpm mysql-community-server-5.7.24-1.el7.x86_64.rpm [email protected]:
##给server2和server3送去mysqld安装包

安装：

[[email protected] ~]# yum install -y mysql-community-client-5.7.24-1.el7.x86_64.rpm mysql-community-common-5.7.24-1.el7.x86_64.rpm mysql-community-libs-5.7.24-1.el7.x86_64.rpm mysql-community-libs-compat-5.7.24-1.el7.x86_64.rpm mysql-community-server-5.7.24-1.el7.x86_64.rpm
[[email protected] ~]# vim /etc/my.cnf
server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON
log_bin=binlog
log_slave_updates=ON
[[email protected] ~]# systemctl start mysqld
[[email protected] ~]# cat /var/log/mysqld.log | grep password
[[email protected] ~]# mysql_secure_installation ##数据库初始化
[[email protected] ~]# mysql -p
mysql> grant replication slave on *.* to [email protected]'172.25.5.%' identified by 'Asimov+123';
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';  ##以下为半同步模块
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
mysql> set global rpl_semi_sync_master_enabled=1;
mysql> set global rpl_semi_sync_master_timeout=10000000000000;
mysql> show variables like '%rpl%';
+-------------------------------------------+----------------+
| Variable_name                             | Value          |
+-------------------------------------------+----------------+
| rpl_semi_sync_master_enabled              | ON             |
| rpl_semi_sync_master_timeout              | 10000000000000 |
| rpl_semi_sync_master_trace_level          | 32             |
| rpl_semi_sync_master_wait_for_slave_count | 1              |
| rpl_semi_sync_master_wait_no_slave        | ON             |
| rpl_semi_sync_master_wait_point           | AFTER_SYNC     |
| rpl_semi_sync_slave_enabled               | OFF            |
| rpl_semi_sync_slave_trace_level           | 32             |
| rpl_stop_slave_timeout                    | 31536000       |
+-------------------------------------------+----------------+

server2：

[[email protected] ~]# ls
mysql-community-client-5.7.24-1.el7.x86_64.rpm
mysql-community-common-5.7.24-1.el7.x86_64.rpm
mysql-community-libs-5.7.24-1.el7.x86_64.rpm
mysql-community-libs-compat-5.7.24-1.el7.x86_64.rpm
mysql-community-server-5.7.24-1.el7.x86_64.rpm
[[email protected] ~]# yum install * -y
[[email protected] ~]# vim /etc/my.cnf
server_id=2
gtid_mode=ON
enforce_gtid_consistency=ON
log_bin=binlog
log_slave_updates=ON
[[email protected] ~]# systemctl start mysqld
[[email protected] ~]# cat /var/log/mysqld.log | grep password
[[email protected] ~]# mysql -p
mysql> change master to master_host='172.25.5.1',master_user='westos',master_password='Asimov+123',master_auto_position=1;
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
mysql> set global rpl_semi_sync_slave_enabled=1;
mysql>  stop slave io_thread;
mysql> start slave io_thread;
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.5.1
                  Master_User: westos
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000002
          Read_Master_Log_Pos: 1004
               Relay_Log_File: server2-relay-bin.000002
                Relay_Log_Pos: 1211
        Relay_Master_Log_File: binlog.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes   ##两个yes表示同步成功

server3和server2的配置一致

2.配置MHA服务器（server4）
安装，并将mha4mysql-node发送到另外三台主机

[[email protected] MHA]# ls
mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
mha4mysql-manager-0.58.tar.gz
mha4mysql-node-0.58-0.el7.centos.noarch.rpm
perl-Config-Tiny-2.14-7.el7.noarch.rpm
perl-Email-Date-Format-1.002-15.el7.noarch.rpm
perl-Log-Dispatch-2.41-1.el7.1.noarch.rpm
perl-Mail-Sender-0.8.23-1.el7.noarch.rpm
perl-Mail-Sendmail-0.79-21.el7.noarch.rpm
perl-MIME-Lite-3.030-1.el7.noarch.rpm
perl-MIME-Types-1.38-2.el7.noarch.rpm
perl-Parallel-ForkManager-1.18-2.el7.noarch.rpm
[[email protected] MHA]# yum install * -y
[[email protected] MHA]# scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm server1:
[[email protected] MHA]# scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm server2:

server1，2，3都安装node：

[[email protected] ~]# yum install mha4mysql-node-0.58-0.el7.centos.noarch.rpm 
[[email protected] ~]# yum insatll mha4mysql-node-0.58-0.el7.centos.noarch.rpm -y
[[email protected] ~]# yum install mha4mysql-node-0.58-0.el7.centos.noarch.rpm -y

生成公钥和私钥发送到server3，4，5上，使之互相之间可以进行免密登录

[[email protected] MHA]# ssh-****** 
[[email protected] MHA]# ssh-copy-id server1
[[email protected] MHA]# ssh-copy-id server2
[[email protected] MHA]# ssh-copy-id server3
[[email protected] ~]# scp -r .ssh/ server1:
[[email protected] ~]# scp -r .ssh/ server2:
[[email protected] ~]# scp -r .ssh/ server3:

编写配置文件

[[email protected] ~]# mkdir -p  /etc/masterha
[[email protected] ~]# vim /etc/masterha/app1.cnf
[server default]
manager_workdir=/etc/masterha      ##设置manager的工作目录
manager_log=/var/log/masterha.log    ##设置manager的日志
master_binlog_dir=/var/lib/mysql    ##设置master 保存binlog的位置，以便MHA可以找到master的日志，我这里的也就是mysql的数据目录
user=root         ##设置监控用户root
password=Asimov+123     ##设置root用户的登陆mysql的密码
ping_interval=1     ##设置监控主库，发送ping包的时间间隔，默认是3秒，尝试三次没有回应的时候自动进行railover
remote_workdir=/tmp    ##设置远端mysql在发生切换时binlog的保存位置
remote_workdir=/tmp     ##设置远端mysql在发生切换时binlog的保存位置
repl_password=Asimov+123  ##设置复制（slave）用户的密码
repl_user=westos       ##设置复制环境中的复制用户名
ssh_user=root      //设置ssh的登录用户名

[server1]
hostname=172.25.5.1
port=3306

[server2]
hostname=172.25.5.2
port=3306
candidate_master=1     ##设置为候选master，如果设置该参数以后，发生主从切换以后将会将此从库提升为主库，即使这个主库不是集群中事件最新的slave
check_repl_delay=0      ##默认情况下如果一个slave落后master 100M的relay logs的话，MHA将不会选择该slave作为一个新的master，因为对于这个slave的恢复需要花费很长时间，通过设置check_repl_delay=0,MHA触发切换在选择一个新的master的时候将会忽略复制延时，这个参数对于设置了candidate_master=1的主机非常有用，因为这个候选主在切换的过程中一定是新的master

[server3]
hostname=172.25.5.3
port=3306
no_master=1     ##设置server5不能成为master

查看ssh登陆是否成功

[[email protected] ~]# masterha_check_ssh --conf=/etc/masterha/app1.cnf

在master端添加授权，两个slave端设置只读
server1:

mysql> grant all on *.* to [email protected]'%' identified by 'Asimov+123';

server2,server3

mysql> set global read_only=1;

检查整个复制环境状况(健康检查)

[[email protected] masterha]# masterha_check_repl --conf=/etc/masterha/app1.cnf

3.测试
测试1：手动切换

1. 手动关闭server1（master）

[[email protected] ~]# systemctl stop mysqld

2.将master从server1手动同步到server2上

[[email protected] masterha]# masterha_master_switch --master_state=dead --conf=/etc/masterha/app1.cnf --dead_master_ip=172.25.5.1 --dead_master_host=172.25.5.1 --dead_master_port=3306 --new_master_host=172.25.5.2 --new_master_port=3306

3.查看server3的master

4.当开启server1的nysql时，他不会自动同步server2，需要手动添加master
server1：

[[email protected] ~]# systemctl start mysqld
mysql> change master to master_host='172.25.5.2',master_user='westos',master_password='Asimov+123',master_auto_position=1;
mysql> start slave;

切换成功

测试2：热切换
1.首先删除/etc/masterha/app1.failover.complete，否则再次转换不能成功

[[email protected] masterha]# ls
app1.cnf  app1.failover.complete
[[email protected] masterha]# rm -fr app1.failover.complete 

2.手动切换master从server2到server1

[[email protected] masterha]# masterha_master_switch --conf=/etc/masterha/app1.cnf --master_state=alive --new_master_host=172.25.5.1 --new_master_port=3306 --orig_master_is_new_slave

3.看server2和server3的mastaer是不是指向server1
server2:

server3:

测试3：自动切换
自动切换命令如下

[[email protected] masterha]# nohup masterha_manager --conf=/etc/masterha/app1.cnf &> /dev/null &

然后手动关掉master（现在是server1）

[[email protected] ~]# systemctl stop mysqld

在server3上看现在的master指向server2，切换成功

若把server1的mysql打开，则必须手动添加master指向server2

[[email protected] ~]# systemctl start mysqld
mysql> change master to master_host='172.25.5.2',master_user='westos',master_password='Asimov+123',master_auto_position=1;
mysql> start slave;

二、VIP

1.编辑配置文件

[[email protected] ~]# vim /etc/masterha/app1.cnf 
添加
master_ip_failover_script=/usr/local/bin/master_ip_failover
master_ip_online_change_script=/usr/local/bin/master_ip_online_change

2.编写脚本

[[email protected] ~]# cd /usr/local/bin/
[[email protected] bin]# ls
[[email protected] bin]# cp /root/master_ip_* .
[[email protected] bin]# ls
master_ip_failover  master_ip_online_change
[[email protected] bin]# vim master_ip_failover 
[[email protected] bin]# vim master_ip_online_change 
[[email protected] bin]# chmod +x *   ##给这两个脚本添加执行权限

两个脚本的内容为；

[[email protected] bin]#  vim 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 = '172.25.5.100/24';
my $ssh_start_vip = "/sbin/ip addr add $vip dev eth0";
my $ssh_stop_vip = "/sbin/ip addr add $vip dev eth0";

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 ([email protected]) {
            warn "Got Error: [email protected]\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 ([email protected]) {
            warn [email protected];
            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";
}

#!/usr/bin/env perl
use strict;  
use warnings FATAL =>'all';  
  
use Getopt::Long;  

##主要修改下面三行
my $vip = '172.25.5.100/24';
my $ssh_start_vip = "/sbin/ip addr add $vip dev eth0";
my $ssh_stop_vip = "/sbin/ip addr add $vip dev eth0";  
  
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 ([email protected]) {  
            warn "Got Error: [email protected]\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 ([email protected]) {  
            warn [email protected];  
            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=port --new_master_host=host --new_master_ip=ip --new_master_port=port\n";  
}

给master添加vip
[[email protected] ~]# ip addr add 172.25.5.100/24 dev eth0

测试：
手动切换master从server2到server1：

[[email protected] masterha]# masterha_master_switch  --conf=/etc/masterha/app1.cnf --master_state=alive --new_master_host=172.25.5.1 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=10000

此时vip也从server2飘逸到server1了
server2：vip消失

server1：

自动切换master

[[email protected] masterha]# nohup masterha_manager --conf=/etc/masterha/app1.cnf &> /dev/null &

关掉master（server1）的mysqld

[[email protected] ~]# systemctl stop mysqld

此时server2成为master，server1的vip飘逸到server2上
server1：

server2（此时master）：

我们还可以在物理机远程通过VIP登录到数据库
mysql -h 172.25.5.100 -u root -p