本文共 19052 字，大约阅读时间需要 63 分钟。

知识剖析:

• 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，另外一台充当从库

实验环境:

主机	IP	角色
server3	172.25.19.3	master （主）
server4	172.25.19.4	slave，（备master）
server5	172.25.19.5	slave
server6	172.25.19.6	MHA

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

• 首先配置好，基于GTID的主从复制（一主两从）

1. 编辑配置文件，然后再重启mysqld（server3，server4，server5一样）
   注意：此处省略了数据库的安装过程，想看详细搭建过程可看：

[root@server3 ~]# vim /etc/my.cnfserver_id=3gtid_mode=ONenforce_gtid_consistency=ONlog_bin=binloglog_slave_updates=ON[root@server3 ~]# systemctl restart mysqldserver4和server5一样[root@server4 ~]# vim /etc/my.cnfserver_id=4gtid_mode=ONenforce_gtid_consistency=ONlog_bin=binloglog_slave_updates=ON[root@server4 ~]# systemctl restart mysqld[root@server5 ~]# vim /etc/my.cnfserver_id=5gtid_mode=ONenforce_gtid_consistency=ONlog_bin=binloglog_slave_updates=ON[root@server5 ~]# systemctl restart mysqld

2. server3（master）

• 创建授权用户，安装插件

mysql> grant replication slave on *.* to hui@'172.25.19.%' identified by 'HUIyange+32'; Query OK, 0 rows affected, 1 warning (0.07 sec)mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';Query OK, 0 rows affected (0.07 sec)mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';Query OK, 0 rows affected (0.07 sec)mysql> set global rpl_semi_sync_master_enabled=1;Query OK, 0 rows affected (0.00 sec)mysql> set global rpl_semi_sync_master_timeout=10000000000000;Query OK, 0 rows affected (0.00 sec)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       |+-------------------------------------------+----------------+9 rows in set (0.09 sec)##创建库，表，并插入信息，用于测试主从复制mysql> create database westos;Query OK, 1 row affected (0.05 sec)mysql> use westos;Database changedmysql> create table userlist(    -> username varchar(10) not null,    -> password varchar(15) not null);Query OK, 0 rows affected (0.28 sec)mysql> insert into userlist values ('user1','123');Query OK, 1 row affected (0.07 sec)mysql> insert into userlist values ('user2','123');Query OK, 1 row affected (0.08 sec)mysql> insert into userlist values ('user3','123');Query OK, 1 row affected (0.08 sec)

3. server4（slave）

• 设定从设备，并开启从设备，安装相应插件

mysql> change master to master_host='172.25.19.3',master_user='hui',master_password='HUIyange+32',MASTER_AUTO_POSITION=1;Query OK, 0 rows affected, 2 warnings (0.36 sec)mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';Query OK, 0 rows affected (0.05 sec)mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';Query OK, 0 rows affected (0.04 sec)mysql> set global rpl_semi_sync_slave_enabled=1;Query OK, 0 rows affected (0.00 sec)mysql>  stop slave io_thread;Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> start slave io_thread;Query OK, 0 rows affected (0.00 sec)mysql> start slave;Query OK, 0 rows affected (0.04 sec)mysql> show slave status\G*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 172.25.19.3                  Master_User: hui                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: binlog.000004          Read_Master_Log_Pos: 194               Relay_Log_File: server4-relay-bin.000004                Relay_Log_Pos: 401        Relay_Master_Log_File: binlog.000004             Slave_IO_Running: Yes      ##两个YES表示同步成功            Slave_SQL_Running: Yes###查看同步成功mysql> use westos;Database changedmysql> select * from userlist;+----------+----------+| username | password |+----------+----------+| user1    | 123      || user2    | 123      || user3    | 123      |+----------+----------+

4. server5（slave）同server4的操作

mysql> change master to master_host='172.25.19.3',master_user='hui',master_password='HUIyange+32',MASTER_AUTO_POSITION=1;Query OK, 0 rows affected, 2 warnings (0.36 sec)mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';Query OK, 0 rows affected (0.05 sec)mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';Query OK, 0 rows affected (0.04 sec)mysql> set global rpl_semi_sync_slave_enabled=1;Query OK, 0 rows affected (0.00 sec)mysql>  stop slave io_thread;Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> start slave io_thread;Query OK, 0 rows affected (0.00 sec)mysql> start slave;Query OK, 0 rows affected (0.04 sec)mysql> show slave status\G*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 172.25.19.3                  Master_User: hui                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: binlog.000004          Read_Master_Log_Pos: 194               Relay_Log_File: server4-relay-bin.000004                Relay_Log_Pos: 401        Relay_Master_Log_File: binlog.000004             Slave_IO_Running: Yes      ##两个YES表示同步成功            Slave_SQL_Running: Yes###查看同步成功mysql> use westos;Database changedmysql> select * from userlist;+----------+----------+| username | password |+----------+----------+| user1    | 123      || user2    | 123      || user3    | 123      |+----------+----------+

• 配置MHA

1. 下载安装包

• 需要用到的安装包如下：

mha4mysql-manager-0.58.tar.gzmha4mysql-manager-0.58-0.el7.centos.noarch.rpmmha4mysql-node-0.58-0.el7.centos.noarch.rpmperl-Config-Tiny-2.14-7.el7.noarch.rpmperl-Email-Date-Format-1.002-15.el7.noarch.rpmperl-Log-Dispatch-2.41-1.el7.1.noarch.rpmperl-Mail-Sender-0.8.23-1.el7.noarch.rpmperl-Mail-Sendmail-0.79-21.el7.noarch.rpmperl-MIME-Lite-3.030-1.el7.noarch.rpmperl-MIME-Types-1.38-2.el7.noarch.rpmperl-Parallel-ForkManager-1.18-2.el7.noarch.rpm

• 安装，并将mha4mysql-node发送到另外三台主机

[root@server6 ~]# yum install -y mha4mysql-manager-0.58-0.el7.centos.noarch.rpm  mha4mysql-node-0.58-0.el7.centos.noarch.rpm  perl-*[root@server6 ~]# scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm 172.25.19.3:[root@server6 ~]# scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm 172.25.19.4:[root@server6 ~]# scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm 172.25.19.5:

2. server3，4，5都安装：

[root@server3 ~]# yum install -y mha4mysql-node-0.58-0.el7.centos.noarch.rpm[root@server4 ~]# yum install -y mha4mysql-node-0.58-0.el7.centos.noarch.rpm[root@server5 ~]# yum install -y mha4mysql-node-0.58-0.el7.centos.noarch.rpm

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

[root@server6 ~]# ssh-keygenGenerating public/private rsa key pair.Enter file in which to save the key (/root/.ssh/id_rsa): Enter passphrase (empty for no passphrase): Enter same passphrase again: Your identification has been saved in /root/.ssh/id_rsa.Your public key has been saved in /root/.ssh/id_rsa.pub.The key fingerprint is:13:ab:dc:2f:ae:8c:1b:09:dc:3a:70:01:25:28:1d:3f root@server6The key's randomart image is:+--[ RSA 2048]----+|+oo.             ||oo..             ||. . E   .        || . o .   o       ||. + .   S        || o o o o .       ||  o o o .        ||   . +  ..       ||    o.oo...      |+-----------------+[root@server6 ~]# ssh-copy-id server3   ##输入yes，密码[root@server6 ~]# ssh-copy-id server4[root@server6 ~]# ssh-copy-id server5 ##将公钥和私钥都发给三个服务器[root@server6 ~]# scp -r .ssh/ server3:known_hosts                                          100% 1026     1.0KB/s   00:00    id_rsa                                               100% 1675     1.6KB/s   00:00    id_rsa.pub                                           100%  394     0.4KB/s   00:00    [root@server6 ~]# scp -r .ssh/ server4:known_hosts                                          100% 1026     1.0KB/s   00:00    id_rsa                                               100% 1675     1.6KB/s   00:00    id_rsa.pub                                           100%  394     0.4KB/s   00:00    [root@server6 ~]# scp -r .ssh/ server5:known_hosts                                          100% 1026     1.0KB/s   00:00    id_rsa                                               100% 1675     1.6KB/s   00:00    id_rsa.pub

4. 编写配置文件

[root@server6 ~]# mkdir -p  /etc/masterha[root@server6 ~]# 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         ##设置监控用户rootpassword=HUIyange+32     ##设置root用户的登陆mysql的密码ping_interval=1     ##设置监控主库，发送ping包的时间间隔，默认是3秒，尝试三次没有回应的时候自动进行railoverremote_workdir=/tmp    ##设置远端mysql在发生切换时binlog的保存位置remote_workdir=/tmp     ##设置远端mysql在发生切换时binlog的保存位置repl_password=HUIyange+32    ##设置复制（slave）用户的密码repl_user=hui       ##设置复制环境中的复制用户名ssh_user=root      //设置ssh的登录用户名[server3]hostname=172.25.19.3port=3306[server4]hostname=172.25.19.4candidate_master=1     ##设置为候选master，如果设置该参数以后，发生主从切换以后将会将此从库提升为主库，即使这个主库不是集群中事件最新的slavecheck_repl_delay=0      ##默认情况下如果一个slave落后master 100M的relay logs的话，MHA将不会选择该slave作为一个新的master，因为对于这个slave的恢复需要花费很长时间，通过设置check_repl_delay=0,MHA触发切换在选择一个新的master的时候将会忽略复制延时，这个参数对于设置了candidate_master=1的主机非常有用，因为这个候选主在切换的过程中一定是新的master [server5]hostname=172.25.19.5port=3306no_master=1     ##设置server5不能成为master

5. 查看ssh登陆是否成功

[root@server6 ~]# masterha_check_ssh --conf=/etc/masterha/app1.cnfTue Feb 26 14:23:40 2019 - [info] All SSH connection tests passed successfully.   ##出现successfully则代表成功

6. 在master端添加授权，两个slave端设置只读

• server3：

mysql> grant all on *.* to root@'%' identified by 'HUIyange+32';

• server4和server5：

mysql> set global read_only=1;

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

[root@server6 masterha]# masterha_check_repl --conf=/etc/masterha/app1.cnf

测试1：手动同步

1. 手动关闭server3（master）

[root@server3 ~]# systemctl stop mysqld

2. 将master从server3手动同步到server4上

[root@server6 ~]# masterha_master_switch --master_state=dead --conf=/etc/masterha/app1.cnf --dead_master_ip=172.25.19.3 --dead_master_host=172.25.19.3 --dead_master_port=3306 --new_master_host=172.25.19.4 --new_master_port=3306

3. 测试在server4上查看slave状态为空，server5上的master显示是server4，说明手动转换成功
   
   
4. 然后打开server3的mysqld，在sever3上重新添加master

[root@server3 ~]# systemctl start mysqld

• 查看slave的状态，显示master是server4，切换成功
  
  测试2：两个主备master都开启

1. 首先删除/etc/masterha/app1.failover.complete，否则再次转换不能成功

[root@server6 ~]# cd /etc/masterha/[root@server6 masterha]# lsapp1.cnf  app1.failover.complete[root@server6 masterha]# rm -fr app1.failover.complete

2. 手动切换新的master——>server3

[root@server6 masterha]# masterha_master_switch  --conf=/etc/masterha/app1.cnf --master_state=alive --new_master_host=172.25.19.3 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=10000

3. 测试结果显示如下（这次server4不用重新添加master）
   

• server4上显示master为server3
  
• server5上显示master为server3
  

测试3：自动切换

1. 自动切换命令如下

[root@server6 masterha]# nohup masterha_manager --conf=/etc/masterha/app1.cnf &> /dev/null &     ##进程打入后台

2. 然后手动关掉master（现在是server3）

[root@server3 ~]# systemctl stop mysqld

3. 然后会发现master转到了server4上

• server4的slave状态为空
  
• server5显示master转为server4
  
• server3需要开启mysqld，然后添加master
  需要change，然后打开slave，显示master为server4

[root@server3 ~]# systemctl start mysqld

• 查看slave的状态，显示master是server4，切换成功
  

二、VIP

1. 编辑配置文件

[root@server6 ~]# vim /etc/masterha/app1.cnf##添加如下两行master_ip_failover_script=/usr/local/bin/master_ip_failovermaster_ip_online_change_script=/usr/local/bin/master_ip_online_change

2. 解压安装包

[root@server6 ~]# tar zxf mha4mysql-manager-0.58.tar.gz

3. 在 /usr/local/bin添加两个文件，并给两个文件添加执行权限

[root@server6 scripts]# cd /usr/local/bin[root@server6 bin]#  vim master_ip_online_change [root@server6 bin]#  vim master_ip_failover [root@server6 bin]# chmod +x *

• 两个文件内容如下

[root@server6 bin]#  vim master_ip_failover #!/usr/bin/env perluse 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.19.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 ($@) {
               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 \"`;}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";}

[root@server6 bin]#  vim master_ip_online_change #!/usr/bin/env perluse strict;  use warnings FATAL =>'all';    use Getopt::Long;  ##主要修改下面三行my $vip = '172.25.19.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 ($@) {
                 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 $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";  }

4. 在现在的master上（server4），添加VIP

[root@server4 ~]# ip addr add 172.25.19.100/24 dev eth0

测试1：手动转换

1. 手动转化master到server3

[root@server6 bin]# masterha_master_switch  --conf=/etc/masterha/app1.cnf --master_state=alive --new_master_host=172.25.19.3 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=10000

. 查看VIP漂移到server3上，则server3就是VIP 测试2：自动转换

1. 发起自动转换命令

[root@server6 bin]# nohup masterha_manager --conf=/etc/masterha/app1.cnf &> /dev/null &

2. 关掉server3（现在的master）

[root@server3 ~]# systemctl stop mysqld

3. 查看VIP漂移到server4上
   

• 我们还可以在物理机远程通过VIP登录到数据库
  mysql -h 172.25.19.100 -u root -p
  这里我就不做演示了，读者可自行查看

转载地址：http://rghrn.baihongyu.com/