Mysql - 高可用方案之MHA

 

一、概述

本文将介绍mysql的MHA(Master High Availability)方案，官方文档地址：https://github.com/yoshinorim/mha4mysql-manager/wiki/Installation
MHA架构由三台mysql服务器(一主两从)和一台manager节点组成，当主库发生故障时，manager能自动从众多从库中选择一台slave log最新的从库转变成主库，然后将其它所有节点重新指向新的主库。将丢失数据的概率降至最低。

写库故障发生前：

写库故障发生后：

二、节点介绍

本次实验采用4台虚拟机，操作系统版本Centos6.10，mysql版本5.7.25
manager  10.40.16.60  管理  管理集群
node1    10.40.16.61  主库  提供写服务
node2    10.40.16.62  从库  提供读服务
node3    10.40.16.63  从库  提供读服务

还须预留1个vip，现在不用配置，这里先提一下，后面的安装步骤用得到
10.40.16.71  写vip

三、安装

1. 配置一主二从

其中node1是主库，node2和node3是从库。具体的复制搭建这里就省略，要是这都不会，那么该文章对你就没意思了。顺便安利一个自己写的mysql一键安装脚本https://www.cnblogs.com/ddzj01/p/10678296.html
注明：集群中使用的复制账号为repl，密码是'123456'

node1(10.40.16.61)
(root@localhost)[test1]> show master status\G
*************************** 1. row ***************************
              File: mysql-bin.000003
          Position: 1067
      Binlog_Do_DB:
  Binlog_Ignore_DB:
Executed_Gtid_Set:

node2(10.40.16.62)
(root@localhost)[test1]> show slave status\G
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 10.40.16.61
                   Master_User: repl
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000003
           Read_Master_Log_Pos: 1067
                Relay_Log_File: slave-relay-bin.000002
                 Relay_Log_Pos: 1233
         Relay_Master_Log_File: mysql-bin.000003
              Slave_IO_Running: Yes
             Slave_SQL_Running: Yes

node3(10.40.16.63)
(root@localhost)[test1]> show slave status\G
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 10.40.16.61
                   Master_User: repl
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000003
           Read_Master_Log_Pos: 1067
                Relay_Log_File: slave-relay-bin.000002
                 Relay_Log_Pos: 1233
         Relay_Master_Log_File: mysql-bin.000003
              Slave_IO_Running: Yes
             Slave_SQL_Running: Yes

2. 下载MHA包

MHA Manager和MHA Node的rpm下载地址
https://github.com/yoshinorim/mha4mysql-manager/wiki/Downloads

3. 下载扩展包(node1&node2&node3&manager)

wget https://dl.fedoraproject.org/pub/epel/epel-release-latest-6.noarch.rpm
rpm -ivh epel-release-latest-6.noarch.rpm

编辑文件/etc/yum.repos.d/epel.repo

将该文件的第三行取消注释，第四行添加注释，如下所示

4. 安装MHA包

每个数据库节点(node1&node2&node3)

yum install -y perl-DBD-MySQL
rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm

node包在/usr/bin下面生成4个文件
save_binary_logs：保存和复制master的二进制日志
apply_diff_relay_logs：识别差异的relay log并将差异的event应用到其它slave中
filter_mysqlbinlog：去除不必要的ROLLBACK事件（MHA已不再使用这个工具）
purge_relay_logs：消除中继日志（不会堵塞SQL线程）

 

管理节点(manager)

yum install -y perl-DBD-MySQL
yum install -y perl-Config-Tiny
yum install -y perl-Log-Dispatch
yum install -y perl-Parallel-ForkManager
rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm
rpm -ivh mha4mysql-manager-0.56-0.el6.noarch.rpm

manager包在/usr/bin下面生成9个文件
masterha_check_repl：检查MySQL的复制状况
masterha_check_ssh：检查MHA的SSH配置状况
masterha_check_status：检测当前MHA运行状态
masterha_conf_host：添加或删除配置的server信息
masterha_manager：启动MHA
masterha_master_monitor：检测master是否宕机
masterha_master_switch：控制故障转移（自动或手动）
masterha_secondary_check：二次检查主库是否真有问题
masterha_stop：关闭MHA

5. 配置互信

在所有节点把下面的命令都执行一遍(node1&node2&node3&manager)
ssh-keygen
ssh-copy-id -i /root/.ssh/id_rsa.pub root@10.40.16.60
ssh-copy-id -i /root/.ssh/id_rsa.pub root@10.40.16.61
ssh-copy-id -i /root/.ssh/id_rsa.pub root@10.40.16.62
ssh-copy-id -i /root/.ssh/id_rsa.pub root@10.40.16.63

四、配置MHA

1. 主节点上(node1)添加集群监控账号

(root@localhost)[(none)]> grant all privileges on *.* to 'monitor'@'%' identified by '123456';

2. 每个从节点上(node2&node3)

set global read_only = 1;
set global relay_log_purge = 0;

之所以关闭从库的relay_log_purge，是因为 在默认情况下，从服务器上的中继日志会在SQL线程执行完后被自动删除。但是在MHA环境中，这些中继日志在恢复其它从服务器时可能会被用到，因此需要禁用中继日志的自动清除。改为定期手动清除SQL线程应用完的中继日志。
每个从节点添加定时任务，最好时间节点错开
crontab -e
0 4 * * * /usr/bin/purge_relay_logs --user=monitor --password=123456 -disable_relay_log_purge --workdir=/tmp/ >> /tmp/purge_relay_logs.log 2>&1

workdir：为relay logs创建硬链接的目录，purge_relay_logs脚本执行的时候会现在该目录下面创建relay log的硬链接，然后短时间开启relay_log_purge这个参数，当relay log删除完毕后，再关闭relay_log_purge这个参数，最后删除硬链接。

 

3. 所有数据库节点建立两个软连接(node1&node2&node3)

mha运行时需要用到/usr/bin下的两个mysql命令
ln -s /usr/local/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog
ln -s /usr/local/mysql/bin/mysql /usr/bin/mysql

4. 在manager节点上准备自定义脚本

master_ip_failover：自动切换脚本(当主库发生故障时)

#!/usr/bin/env perl

#  Copyright (C) 2011 DeNA Co.,Ltd.
#
#  This program is free software; you can redistribute it and/or modify
#  it under the terms of the GNU General Public License as published by
#  the Free Software Foundation; either version 2 of the License, or
#  (at your option) any later version.
#
#  This program is distributed in the hope that it will be useful,
#  but WITHOUT ANY WARRANTY; without even the implied warranty of
#  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#  GNU General Public License for more details.
#
#  You should have received a copy of the GNU General Public License
#   along with this program; if not, write to the Free Software
#  Foundation, Inc.,
#  51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA

## Note: This is a sample script and is not complete. Modify the script based on your environment.

use strict;
use warnings FATAL => 'all';

use Getopt::Long;
use MHA::DBHelper;
my (
  $command,        $ssh_user,         $orig_master_host,
  $orig_master_ip, $orig_master_port, $new_master_host,
  $new_master_ip,  $new_master_port,  $new_master_user,
  $new_master_password
);

my $vip = '10.40.16.71';
my $key = "2";
my $ssh_start_vip = "/sbin/ifconfig eth0:$key $vip/24";
my $ssh_stop_vip = "/sbin/ifconfig eth0:$key down";
my $ssh_send_garp = "/sbin/arping -U $vip -I eth0 -c 1";


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,
  'new_master_user=s'     => \$new_master_user,
  'new_master_password=s' => \$new_master_password,
);

exit &main();

sub main {
  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 an 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 {

      my $new_master_handler = new MHA::DBHelper();

      # args: hostname, port, user, password, raise_error_or_not
      $new_master_handler->connect( $new_master_ip, $new_master_port,
        $new_master_user, $new_master_password, 1 );

      ## Set read_only=0 on the new master
      $new_master_handler->disable_log_bin_local();
      print "Set read_only=0 on the new master.\n";
      $new_master_handler->disable_read_only();

      ## Creating an app user on the new master
      # print "Creating app user on the new master..\n";
      # FIXME_xxx_create_user( $new_master_handler->{dbh} );
      $new_master_handler->enable_log_bin_local();
      $new_master_handler->disconnect();

      print "Enabling the VIP $vip on the new master: $new_master_host \n";
      &start_vip();
      $exit_code = 0;
    };
    if ($@) {
      warn $@;

      # If you want to continue failover, exit 10.
      exit $exit_code;
    }
    exit $exit_code;
  }
  elsif ( $command eq "status" ) {

    # do nothing
    exit 0;
  }
  else {
    &usage();
    exit 1;
  }
}

sub start_vip(){
    `ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`;
    `ssh $ssh_user\@$new_master_host \" $ssh_send_garp \"`;
}

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";
}

需要修改的地方，其它都是固定的

master_ip_online_change：手动切换脚本

#!/usr/bin/env perl

#  Copyright (C) 2011 DeNA Co.,Ltd.
#
#  This program is free software; you can redistribute it and/or modify
#  it under the terms of the GNU General Public License as published by
#  the Free Software Foundation; either version 2 of the License, or
#  (at your option) any later version.
#
#  This program is distributed in the hope that it will be useful,
#  but WITHOUT ANY WARRANTY; without even the implied warranty of
#  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#  GNU General Public License for more details.
#
#  You should have received a copy of the GNU General Public License
#   along with this program; if not, write to the Free Software
#  Foundation, Inc.,
#  51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA

## Note: This is a sample script and is not complete. Modify the script based on your environment.

use strict;
use warnings FATAL => 'all';

use Getopt::Long;
use MHA::DBHelper;
use MHA::NodeUtil;
use Time::HiRes qw( sleep gettimeofday tv_interval );
use Data::Dumper;

my $_tstart;
my $_running_interval = 0.1;

my $vip = '10.40.16.71';
my $key = "2";
my $ssh_start_vip = "/sbin/ifconfig eth0:$key $vip/24";
my $ssh_stop_vip = "/sbin/ifconfig eth0:$key down";
my $ssh_send_garp = "/sbin/arping -U $vip -I eth0 -c 1";

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 start_vip(){
    `ssh $new_master_ssh_user\@$new_master_host \" $ssh_start_vip \"`;
    `ssh $new_master_ssh_user\@$new_master_host \" $ssh_send_garp \"`;
}

sub stop_vip(){
    `ssh $orig_master_ssh_user\@$orig_master_host \" $ssh_stop_vip \"`;
}


sub current_time_us {
  my ( $sec, $microsec ) = gettimeofday();
  my $curdate = localtime($sec);
  return $curdate . "" . sprintf( "%06d", $microsec );
}

sub sleep_until {
  my $elapsed = tv_interval($_tstart);
  if ( $_running_interval > $elapsed ) {
    sleep( $_running_interval - $elapsed );
  }
}

sub get_threads_util {
  my $dbh                    = shift;
  my $my_connection_id       = shift;
  my $running_time_threshold = shift;
  my $type                   = shift;
  $running_time_threshold = 0 unless ($running_time_threshold);
  $type                   = 0 unless ($type);
  my @threads;

  my $sth = $dbh->prepare("SHOW PROCESSLIST");
  $sth->execute();

  while ( my $ref = $sth->fetchrow_hashref() ) {
    my $id         = $ref->{Id};
    my $user       = $ref->{User};
    my $host       = $ref->{Host};
    my $command    = $ref->{Command};
    my $state      = $ref->{State};
    my $query_time = $ref->{Time};
    my $info       = $ref->{Info};
    $info =~ s/^\s*(.*?)\s*$/$1/ if defined($info);
    next if ( $my_connection_id == $id );
    next if ( defined($query_time) && $query_time < $running_time_threshold );
    next if ( defined($command)    && $command eq "Binlog Dump" );
    next if ( defined($user)       && $user eq "system user" );
    next
      if ( defined($command)
      && $command eq "Sleep"
      && defined($query_time)
      && $query_time >= 1 );

    if ( $type >= 1 ) {
      next if ( defined($command) && $command eq "Sleep" );
      next if ( defined($command) && $command eq "Connect" );
    }

    if ( $type >= 2 ) {
      next if ( defined($info) && $info =~ m/^select/i );
      next if ( defined($info) && $info =~ m/^show/i );
    }

    push @threads, $ref;
  }
  return @threads;
}

sub main {
  if ( $command eq "stop" ) {
    ## Gracefully killing connections on the current master
    # 1. Set read_only= 1 on the new master
    # 2. DROP USER so that no app user can establish new connections
    # 3. Set read_only= 1 on the current master
    # 4. Kill current queries
    # * Any database access failure will result in script die.
    my $exit_code = 1;
    eval {
      ## Setting read_only=1 on the new master (to avoid accident)
      my $new_master_handler = new MHA::DBHelper();

      # args: hostname, port, user, password, raise_error(die_on_error)_or_not
      $new_master_handler->connect( $new_master_ip, $new_master_port,
        $new_master_user, $new_master_password, 1 );
      print current_time_us() . " Set read_only on the new master.. ";
      $new_master_handler->enable_read_only();
      if ( $new_master_handler->is_read_only() ) {
        print "ok.\n";
      }
      else {
        die "Failed!\n";
      }
      $new_master_handler->disconnect();

      # Connecting to the orig master, die if any database error happens
      my $orig_master_handler = new MHA::DBHelper();
      $orig_master_handler->connect( $orig_master_ip, $orig_master_port,
        $orig_master_user, $orig_master_password, 1 );

      ## Drop application user so that nobody can connect. Disabling per-session binlog beforehand
      $orig_master_handler->disable_log_bin_local();
      # print current_time_us() . " Drpping app user on the orig master..\n";
      #drop_app_user($orig_master_handler);

      ## Waiting for N * 100 milliseconds so that current connections can exit
      my $time_until_read_only = 15;
      $_tstart = [gettimeofday];
      my @threads = get_threads_util( $orig_master_handler->{dbh},
        $orig_master_handler->{connection_id} );
      while ( $time_until_read_only > 0 && $#threads >= 0 ) {
        if ( $time_until_read_only % 5 == 0 ) {
          printf
"%s Waiting all running %d threads are disconnected.. (max %d milliseconds)\n",
            current_time_us(), $#threads + 1, $time_until_read_only * 100;
          if ( $#threads < 5 ) {
            print Data::Dumper->new( [$_] )->Indent(0)->Terse(1)->Dump . "\n"
              foreach (@threads);
          }
        }
        sleep_until();
        $_tstart = [gettimeofday];
        $time_until_read_only--;
        @threads = get_threads_util( $orig_master_handler->{dbh},
          $orig_master_handler->{connection_id} );
      }

      ## Setting read_only=1 on the current master so that nobody(except SUPER) can write
      print current_time_us() . " Set read_only=1 on the orig master.. ";
      $orig_master_handler->enable_read_only();
      if ( $orig_master_handler->is_read_only() ) {
        print "ok.\n";
      }
      else {
        die "Failed!\n";
      }

      ## Waiting for M * 100 milliseconds so that current update queries can complete
      my $time_until_kill_threads = 5;
      @threads = get_threads_util( $orig_master_handler->{dbh},
        $orig_master_handler->{connection_id} );
      while ( $time_until_kill_threads > 0 && $#threads >= 0 ) {
        if ( $time_until_kill_threads % 5 == 0 ) {
          printf
"%s Waiting all running %d queries are disconnected.. (max %d milliseconds)\n",
            current_time_us(), $#threads + 1, $time_until_kill_threads * 100;
          if ( $#threads < 5 ) {
            print Data::Dumper->new( [$_] )->Indent(0)->Terse(1)->Dump . "\n"
              foreach (@threads);
          }
        }
        sleep_until();
        $_tstart = [gettimeofday];
        $time_until_kill_threads--;
        @threads = get_threads_util( $orig_master_handler->{dbh},
          $orig_master_handler->{connection_id} );
      }

      ## Terminating all threads
      print current_time_us() . " Killing all application threads..\n";
      $orig_master_handler->kill_threads(@threads) if ( $#threads >= 0 );
      print current_time_us() . " done.\n";
      $orig_master_handler->enable_log_bin_local();
      $orig_master_handler->disconnect();

      ## Droping the VIP
      print "Disabling the VIP an old master: $orig_master_host \n";
      &stop_vip();

      ## After finishing the script, MHA executes FLUSH TABLES WITH READ LOCK
      $exit_code = 0;
    };
    if ($@) {
      warn "Got Error: $@\n";
      exit $exit_code;
    }
    exit $exit_code;
  }
  elsif ( $command eq "start" ) {
    ## Activating master ip on the new master
    # 1. Create app user with write privileges
    # 2. Moving backup script if needed
    # 3. Register new master's ip to the catalog database

# We don't return error even though activating updatable accounts/ip failed so that we don't interrupt slaves' recovery.
# If exit code is 0 or 10, MHA does not abort
    my $exit_code = 10;
    eval {
      my $new_master_handler = new MHA::DBHelper();

      # args: hostname, port, user, password, raise_error_or_not
      $new_master_handler->connect( $new_master_ip, $new_master_port,
        $new_master_user, $new_master_password, 1 );

      ## Set read_only=0 on the new master
      $new_master_handler->disable_log_bin_local();
      print current_time_us() . " Set read_only=0 on the new master.\n";
      $new_master_handler->disable_read_only();

      ## Creating an app user on the new master
      #print current_time_us() . " Creating app user on the new master..\n";
      # create_app_user($new_master_handler);
      print "Enabling the VIP $vip on the new master: $new_master_host \n";
      &start_vip();
      $new_master_handler->enable_log_bin_local();
      $new_master_handler->disconnect();

      ## Update master ip on the catalog database, etc
      $exit_code = 0;
    };
    if ($@) {
      warn "Got Error: $@\n";
      exit $exit_code;
    }
    exit $exit_code;
  }
  elsif ( $command eq "status" ) {

    # do nothing
    exit 0;
  }
  else {
    &usage();
    exit 1;
  }
}

sub usage {
  print
"Usage: master_ip_online_change --command=start|stop|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";
  die;
}

需要修改的地方，其它都是固定的

send_master_failover_mail：当发生故障切换时，可通过该脚本发送告警邮件

#!/usr/bin/perl

#  Copyright (C) 2011 DeNA Co.,Ltd.
#
#  This program is free software; you can redistribute it and/or modify
#  it under the terms of the GNU General Public License as published by
#  the Free Software Foundation; either version 2 of the License, or
#  (at your option) any later version.
#
#  This program is distributed in the hope that it will be useful,
#  but WITHOUT ANY WARRANTY; without even the implied warranty of
#  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#  GNU General Public License for more details.
#
#  You should have received a copy of the GNU General Public License
#   along with this program; if not, write to the Free Software
#  Foundation, Inc.,
#  51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA

## Note: This is a sample script and is not complete. Modify the script based on your environment.

use strict;
use warnings FATAL => 'all';
use Mail::Sender;
use Getopt::Long;

#new_master_host and new_slave_hosts are set only when recovering master succeeded
my ( $dead_master_host, $new_master_host, $new_slave_hosts, $subject, $body );
my $smtp='smtp.163.com';
my $mail_from='xxxx@163.com';
my $mail_user='xxxx@163.com';
my $mail_pass='xxxx';
my $mail_to=['xxxx@163.com'];
GetOptions(
  'orig_master_host=s' => \$dead_master_host,
  'new_master_host=s'  => \$new_master_host,
  'new_slave_hosts=s'  => \$new_slave_hosts,
  'subject=s'          => \$subject,
  'body=s'             => \$body,
);
mailToContacts($smtp,$mail_from,$mail_user,$mail_pass,$mail_to,$subject,$body);

sub mailToContacts {
    my ( $smtp, $mail_from, $user, $passwd, $mail_to, $subject, $msg ) = @_;
    open my $DEBUG, "> /tmp/monitormail.log"
        or die "Can't open the debug      file:$!\n";
    my $sender = new Mail::Sender {
        ctype       => 'text/plain; charset=utf-8',
        encoding    => 'utf-8',
        smtp        => $smtp,
        from        => $mail_from,
        auth        => 'LOGIN',
        TLS_allowed => '0',
        authid      => $user,
        authpwd     => $passwd,
        to          => $mail_to,
        subject     => $subject,
        debug       => $DEBUG
    };

    $sender->MailMsg(
        {   msg   => $msg,
            debug => $DEBUG
        }
    ) or print $Mail::Sender::Error;
    return 1;
}


# Do whatever you want here
exit 0;

需要修改的地方，其它都是固定的

将上面的文件放入/usr/local/bin/
chmod +x master_ip_failover master_ip_online_change send_master_failover_mail

mkdir -p /etc/masterha/app1
vi /etc/masterha/app1/app1.cnf

[server default]
manager_log=/etc/masterha/app1/manager.log      //设置manager的日志
manager_workdir=/etc/masterha/app1              //设置manager的工作目录
master_binlog_dir=/opt/mydata/log/binlog        //设置master默认保存binlog的位置，以便MHA可以找到master的日志          
master_ip_failover_script=/usr/local/bin/master_ip_failover    //设置自动failover时候的切换脚本
master_ip_online_change_script=/usr/local/bin/master_ip_online_change   //设置手动切换时候的切换脚本
user=monitor                                    //设置监控用户
password=123456                                 //设置监控用户的密码
ping_interval=3                                 //设置监控主库，发送ping包的时间间隔，默认是3秒，尝试三次没有回应的时候进行自动failover
remote_workdir=/tmp                             //设置master在发生切换时，提取binlog的内容所保存的位置，该目录是在master上
repl_user=repl                                  //设置复制环境中的复制用户名
repl_password=123456                            //设置复制用户的密码
report_script=/usr/local/bin/send_master_failover_mail        //设置发生切换后发送的报警的脚本
secondary_check_script=/usr/bin/masterha_secondary_check -s 10.40.16.62 -s 10.40.16.63 --user=root --master_host=10.40.16.61 --master_ip=10.40.16.61 --master_port=3306                              //一旦MHA到master的监控之间出现问题，MHA Manager将会判断其它两个slave是否能建立到master连接，通过多条网络路由检测master的可用性
shutdown_script=""                              //设置故障发生后关闭故障主机脚本（该脚本的主要作用是关闭主机防止发生脑裂）
ssh_user=root                                   //设置ssh的登录用户名

[server1]
hostname=10.40.16.61
port=3306

[server2]
hostname=10.40.16.62
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=10.40.16.63
port=3306
no_master=1                                     //设置这个节点永远不会选为master

五、MHA检查

在manager节点上执行下列检查
检查SSH的配置
masterha_check_ssh --conf=/etc/masterha/app1/app1.cnf

查看整个集群的状态(从库需启动slave进程)
masterha_check_repl --conf=/etc/masterha/app1/app1.cnf

检查MHA Manager的状态
masterha_check_status --conf=/etc/masterha/app1/app1.cnf

开启MHA Manager监控
nohup masterha_manager --conf=/etc/masterha/app1/app1.cnf --remove_dead_master_conf --ignore_last_failover >> /etc/masterha/app1/manager.log 2>&1 &
remove_dead_master_conf：该参数代表当发生主从切换后，老的主库的IP将会从配置文件中移除。
ignore_last_failover：在默认情况下，MHA发生切换后将会在/etc/masterha/app1下产生app1.failover.complete文件，下次再次切换的时候如果发现该目录下存在该文件且两次切换的时间间隔不足8小时的话，将不允许触发切换。除非在第一次切换后手动rm -rf /etc/masterha/app1/app1.failover.complete。该参数代表忽略上次MHA触发切换产生的文件。

六、切换

1. 自动failover(主库down了)

关闭主库节点
[root@mysqla ~]# service mysql stop

查看manager日志
[root@monitor ~]# less /etc/masterha/app1/manager.log

查看manager状态
[root@monitor ~]# masterha_check_status --conf=/etc/masterha/app1/app1.cnf
app1 is stopped(2:NOT_RUNNING).
可以看到切换完成后manager状态就变成NOT_RUNNING了

在node3看从库状态

可以看到集群的主库目前是node2

在node2看主库状态
(root@localhost)[(none)]> show variables like 'read_only';

可以看到node2的read_only状态已经由ON变成OFF了

可以看到vip已经出现在node2上了
[root@mysqlb ~]# ifconfig

vip搭建完成之后并没有vip，只有第一次切换之后才会有，所以所以刚刚配置完mha的时候，如果想用vip，需要在主库手工创建一个vip
ifconfig eth0:2 10.40.16.71 netmask 255.255.255.0 up         
          
最后看看自己的邮箱，看有没有收到一封邮件，呵呵:-)

2. 手工在线切换

注意这个实验是接着上一个实验来的

手工切换需要关闭MHA监控，如果MHA监控正在运行是不允许手工切换的
masterha_stop --conf=/etc/masterha/app1/app1.cnf

开启node1的mysql服务
[root@mysqla ~]# service mysql start

查看node2的master log position
(root@localhost)[(none)]> show master status;

在node1开启同步
(root@localhost)[(none)]> change master to master_host='10.40.16.62',master_port=3306,master_user='repl',master_password='123456',master_log_file='mysql-bin.000002',master_log_pos=1914;
(root@localhost)[(none)]> start slave;

重新编辑app1.cnf，因为在前面failover过程中，mha已经将server1删除了，故需要重新添加进来
vi /etc/masterha/app1/app1.cnf

[server default]
manager_log=/etc/masterha/app1/manager.log
manager_workdir=/etc/masterha/app1
master_binlog_dir=/opt/mydata/log/binlog
master_ip_failover_script=/usr/local/bin/master_ip_failover
master_ip_online_change_script=/usr/local/bin/master_ip_online_change
password=123456
ping_interval=3
remote_workdir=/tmp
repl_password=123456
repl_user=repl
report_script=/usr/local/bin/send_master_failover_mail
secondary_check_script=/usr/bin/masterha_secondary_check -s 10.40.16.61 -s 10.40.16.63 --user=root --master_host=10.40.16.62 --master_ip=10.40.16.62 --master_port=3306
shutdown_script=""
ssh_user=root
user=monitor

[server1]
hostname=10.40.16.61
candidate_master=1
check_repl_delay=0
port=3306

[server2]
hostname=10.40.16.62
port=3306

[server3]
hostname=10.40.16.63
no_master=1
port=3306

masterha_master_switch --conf=/etc/masterha/app1/app1.cnf --master_state=alive --new_master_host=10.40.16.61 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=10000
orig_master_is_new_slave是将原master切换为新主的slave，默认情况下，是不添加的。
running_updates_limit默认为1s，即如果主从延迟时间（Seconds_Behind_Master），或master show processlist中dml操作大于1s，则不会执行切换。

会出现两次提示，需要填yes/no，填yes即可。

查看集群情况
(root@localhost)[(none)]> show master status\G
*************************** 1. row ***************************
              File: mysql-bin.000004
          Position: 154
      Binlog_Do_DB:
  Binlog_Ignore_DB:
Executed_Gtid_Set:

(root@localhost)[(none)]> show slave status\G
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 10.40.16.61
                   Master_User: repl
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000004
           Read_Master_Log_Pos: 154
          
(root@localhost)[(none)]> show slave status\G
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 10.40.16.61
                   Master_User: repl
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000004
           Read_Master_Log_Pos: 154         

可以看到手工切换也ok了。

 

3. 手工failover

还有种特殊情况是mha监控没有开，但是主库挂掉了该怎么手工failover
[root@monitor ~]# masterha_check_status --conf=/etc/masterha/app1/app1.cnf
app1 is stopped(2:NOT_RUNNING).

在manager节点进行failover切换
masterha_master_switch --conf=/etc/masterha/app1/app1.cnf --master_state=dead --dead_master_host=10.40.16.61 --dead_master_port=3306 --new_master_host=10.40.16.62 --new_master_port=3306 --ignore_last_failover

提示报错，主库并没有挂掉，所以不能failover

在node1关闭主库
[root@mysqla ~]# service mysql stop

在manager节点再进行failover切换
masterha_master_switch --conf=/etc/masterha/app1/app1.cnf --master_state=dead --dead_master_host=10.40.16.61 --dead_master_port=3306 --new_master_host=10.40.16.62 --new_master_port=3306 --ignore_last_failover
有两次提示，都输入yes

查看manager日志
[root@monitor ~]# less /etc/masterha/app1/manager.log

这样就完成了手工failover

 

七、MHA的优缺点

优点：
1.可以基于gtid的复制模式
2.mha在进行故障转移时更不易产生数据丢失，因为主库一旦发生故障了，如果主库还能ping通，MHA会在主库的/tmp(由参数remote_workdir设置)保存主库最后的binlog，同时会将该binlog传到manager节点的/etc/masterha/app1(由参数manager_workdir设置)中进行保存。mha配置mysql的半同步复制更能降低数据丢失的概率。
3.同一个监控节点可以监控多个集群

缺点：
1.mha只对主数据库进行监控，也只有主服务器有vip
2.基于ssh免认证配置，存在一定的安全隐患
3.没有提供从服务器的负载均衡
4.配置过程比较复杂
  

八、总结

MHA就介绍到这了，有空可以再多做做实验，感受MHA的魅力。
文章参考：https://www.cnblogs.com/ivictor/p/5686275.html