广告

扫码关注官方微信

扫码下载APP
返回顶部
首页 > 资讯 > 数据库 >【MySQL】【高可用】基于MHA架构的MySQL高可用故障自动切换架构
  • 460

0
分享到

【MySQL】【高可用】基于MHA架构的MySQL高可用故障自动切换架构

2024-04-02 19:04:59 460人浏览 八月长安
摘要

基于MHA架构的Mysql高可用切换架构 环境： ​ Centos7+mysql 5.7 + GTID 业务系统：mainBusiness ​ node1 : 192.16

基于MHA架构的Mysql高可用切换架构

环境：

​ Centos7+mysql 5.7 + GTID 业务系统：mainBusiness

​ node1 : 192.168.1.109 port:3109

​ node2 : 192.168.1.110 port:3110

​ VIP :192.168.1.88

​ manager:192.168.1.8

1.背景：

​ 除了galera cluster(Mariadb Cluster,GroupReplication,PXC)和KeepAlived之外，业界广泛使用的Mysql高可用就是MHA架构了。

​ MHA作者在离开DeNA加入facebook后就极少更新了这个工具了。

2.安装：

​ rpm包安装的方式最简单，但是作者在27天前增加了对从库上启用了super-read-only参数的优化，简而言之就是：当开启这个参数后，有可能会发生配置文件中的用户无法对差异事务进行应用的问题。于是增加了判断super-read-only参数是否开启的逻辑判断，若开启，则先关闭此参数，然后进行应用差异事务然后重新开启。

​ 所以这里我们采用编译GitHub上最新的代码的办法进行安装。地址为：

​ https://github.com/yoshinorim/mha4mysql-manager

​ Https://github.com/yoshinorim/mha4mysql-node

在node1和node2上：

#创建工作文件夹
mkdir -p /data/mha/
#下载源码包
wget  https://codeload.github.com/yoshinorim/mha4mysql-node/zip/master -O /usr/local/src/mha-node.zip
wget https://codeload.github.com/yoshinorim/mha4mysql-manager/zip/master -O /usr/local/src/mha-manager.zip
#解压
unzip /usr/local/src/mha-manager.zip -d /usr/local/src
unzip /usr/local/src/mha-node.zip -d /usr/local/src
#安装perl及其相关依赖
yum -y install perl perl-ExtUtils-MakeMaker perl-ExtUtils-CBuilder perl-Parallel-ForkManager  perl-Config-Tiny 
yum -y install 'perl(inc::Module::Install)'
yum -y install 'perl(Test::Without::Module)'
yum -y install 'perl(Log::Dispatch)'
#编译管理端
cd /usr/local/src/mha4mysql-manager-master/
perl Makefile.PL #提示时输入y
make &&make install
#编译节点端
cd /usr/local/src/mha4mysql-node-master/
perl Makefile.PL 
make &&make install
#更改配置文件
cp /usr/
#在数据库中创建用于MHA系统工作的管理员权限账号
#node1
mysql --login-path=3109 -e 'create user mha@'192.168.1.8' identified by 'sa123456''
mysql --login-path=3109 -e 'grant all privileges on *.* to mha@'192.168.1.8''
mysql --login-path=3109 -e 'flush privilges'
#node2
mysql --login-path=3110 -e 'create user mha@'192.168.1.8' identified by 'sa123456''
mysql --login-path=3110 -e 'grant all privileges on *.* to mha@'192.168.1.8''
mysql --login-path=3110 -e 'flush privilges'

在manager上

#创建工作目录
mkdir -p /data/mha/mainBusiness
#下载源码包
wget  https://codeload.github.com/yoshinorim/mha4mysql-node/zip/master -O /usr/local/src/mha-node.zip
wget https://codeload.github.com/yoshinorim/mha4mysql-manager/zip/master -O /usr/local/src/mha-manager.zip
#解压
unzip /usr/local/src/mha-manager.zip -d /usr/local/src
unzip /usr/local/src/mha-node.zip -d /usr/local/src
#安装perl及其相关依赖
yum -y install perl perl-ExtUtils-MakeMaker perl-ExtUtils-CBuilder perl-Parallel-ForkManager  perl-Config-Tiny 
yum -y install 'perl(inc::Module::Install)'
yum -y install 'perl(Test::Without::Module)'
yum -y install 'perl(Log::Dispatch)'
#编译管理端
cd /usr/local/src/mha4mysql-manager-master/
perl Makefile.PL #提示时输入y
make &&make install
#编译节点端
cd /usr/local/src/mha4mysql-node-master/
perl Makefile.PL 
make &&make install
#创建配置文件
cp /usr/local/src/mha4mysql-manager-master/samples/conf3 * * sh /etc/auto_clean_relay_log.sh 


修改故障切换VIP脚本：


vim /etc/mha/master_ip_failover
#!/usr/bin/env perl

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

use Getopt::Long;
use MHA::DBHelper;
#自定义该组机器的vip
my $vip = "192.168.1.100";
my $if = "eth0";
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
);

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,
);

sub add_vip {
    my $output1 = `ssh -o ConnectTimeout=15  -o ConnectionAttempts=3 $orig_master_host /sbin/ip addr del $vip/32 dev $if`;
    my $output2 = `ssh -o ConnectTimeout=15  -o ConnectionAttempts=3 $new_master_host /sbin/ip addr add $vip/32 dev $if`;

}
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 {

      # updating global catalog, etc
      $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();

      ## Update master ip on the catalog database, etc
      &add_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 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";
}


vim /etc/mha/master_ip_online_change

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;
#添加vip定义
my $vip = "192.168.1.100";
my $if = "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 drop_vip {
        my $output = `ssh -o ConnectTimeout=15  -o ConnectionAttempts=3 $orig_master_host /sbin/ip addr del $vip/32 dev $if`;
    #mysql里的连接全部干掉
    #FIXME
}
sub add_vip {
        my $output = `ssh -o ConnectTimeout=15  -o ConnectionAttempts=3 $new_master_host /sbin/ip addr add $vip/32 dev $if`;

}

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";
      print current_time_us() . " drop vip $vip..\n";
      #drop_app_user($orig_master_handler);
     &drop_vip();

      ## 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();

      ## 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";
      print current_time_us() . "Add vip $vip on $if..\n";
     # create_app_user($new_master_handler);
      &add_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;
}



​   至此：安装与配置已经完全结束，开始进入运行环节


运行：


masterha_manager --conf=/etc/mha/mainBusiness.cnf &


​   检查masterha_manager运行情况：


masterha_check_status --conf=/etc/mha/mainBusiness.cnf


故障转移：


​    我们将主库关机，进行观测整个故障转移的过程  


[g:\~]$ Connecting to 192.168.1.110:22...
Could not connect to '192.168.1.110' (port 22): Connection failed.

Type `help' to learn how to use Xshell prompt.


​   manager端输出如下：


Sun Feb  4 12:26:46 2018 - [info] Starting ping health check on 192.168.1.110(192.168.1.110:3110)..
Sun Feb  4 12:26:46 2018 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond..
Sun Feb  4 12:30:41 2018 - [warning] Got error on MySQL select ping: 2006 (MySQL Server has gone away)
Sun Feb  4 12:30:41 2018 - [info] Executing SSH check script: exit 0
Sun Feb  4 12:30:41 2018 - [warning] HealthCheck: SSH to 192.168.1.110 is NOT reachable.
Sun Feb  4 12:30:43 2018 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '192.168.1.110' (4))
Sun Feb  4 12:30:43 2018 - [warning] Connection failed 2 time(s)..
Sun Feb  4 12:30:44 2018 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '192.168.1.110' (4))
Sun Feb  4 12:30:44 2018 - [warning] Connection failed 3 time(s)..
Sun Feb  4 12:30:45 2018 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '192.168.1.110' (4))
Sun Feb  4 12:30:45 2018 - [warning] Connection failed 4 time(s)..
Sun Feb  4 12:30:45 2018 - [warning] Master is not reachable from health checker!
Sun Feb  4 12:30:45 2018 - [warning] Master 192.168.1.110(192.168.1.110:3110) is not reachable!
Sun Feb  4 12:30:45 2018 - [warning] SSH is NOT reachable.
Sun Feb  4 12:30:45 2018 - [info] Connecting to a master server failed. Reading configuration file /etc/masterha_default.cnf and /etc/mha/mainBusiness.cnf again, and trying to connect to all servers to check server status..
Sun Feb  4 12:30:45 2018 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sun Feb  4 12:30:45 2018 - [info] Reading application default configuration from /etc/mha/mainBusiness.cnf..
Sun Feb  4 12:30:45 2018 - [info] Reading server configuration from /etc/mha/mainBusiness.cnf..
Sun Feb  4 12:30:46 2018 - [info] GTID failover mode = 1
Sun Feb  4 12:30:46 2018 - [info] Dead Servers:
Sun Feb  4 12:30:46 2018 - [info]   192.168.1.110(192.168.1.110:3110)
Sun Feb  4 12:30:46 2018 - [info] Alive Servers:
Sun Feb  4 12:30:46 2018 - [info]   192.168.1.109(192.168.1.109:3109)
Sun Feb  4 12:30:46 2018 - [info] Alive Slaves:
Sun Feb  4 12:30:46 2018 - [info]   192.168.1.109(192.168.1.109:3109)  Version=5.7.19-log (oldest major version between slaves) log-bin:enabled
Sun Feb  4 12:30:46 2018 - [info]     GTID ON
Sun Feb  4 12:30:46 2018 - [info]     Replicating from 192.168.1.110(192.168.1.110:3110)
Sun Feb  4 12:30:46 2018 - [info]     Primary candidate for the new Master (candidate_master is set)
Sun Feb  4 12:30:46 2018 - [info] Checking slave configurations..
Sun Feb  4 12:30:46 2018 - [info] Checking replication filtering settings..
Sun Feb  4 12:30:46 2018 - [info]  Replication filtering check ok.
Sun Feb  4 12:30:46 2018 - [info] Master is down!
Sun Feb  4 12:30:46 2018 - [info] Terminating monitoring script.
Sun Feb  4 12:30:46 2018 - [info] Got exit code 20 (Master dead).
Sun Feb  4 12:30:46 2018 - [info] MHA::MasterFailover version 0.57.
Sun Feb  4 12:30:46 2018 - [info] Starting master failover.
Sun Feb  4 12:30:46 2018 - [info] 
Sun Feb  4 12:30:46 2018 - [info] * Phase 1: Configuration Check Phase..
Sun Feb  4 12:30:46 2018 - [info] 
Sun Feb  4 12:30:47 2018 - [info] GTID failover mode = 1
Sun Feb  4 12:30:47 2018 - [info] Dead Servers:
Sun Feb  4 12:30:47 2018 - [info]   192.168.1.110(192.168.1.110:3110)
Sun Feb  4 12:30:47 2018 - [info] Checking master reachability via MySQL(double check)...
Sun Feb  4 12:30:48 2018 - [info]  ok.
Sun Feb  4 12:30:48 2018 - [info] Alive Servers:
Sun Feb  4 12:30:48 2018 - [info]   192.168.1.109(192.168.1.109:3109)
Sun Feb  4 12:30:48 2018 - [info] Alive Slaves:
Sun Feb  4 12:30:48 2018 - [info]   192.168.1.109(192.168.1.109:3109)  Version=5.7.19-log (oldest major version between slaves) log-bin:enabled
Sun Feb  4 12:30:48 2018 - [info]     GTID ON
Sun Feb  4 12:30:48 2018 - [info]     Replicating from 192.168.1.110(192.168.1.110:3110)
Sun Feb  4 12:30:48 2018 - [info]     Primary candidate for the new Master (candidate_master is set)
Sun Feb  4 12:30:48 2018 - [info] Starting GTID based failover.
Sun Feb  4 12:30:48 2018 - [info] 
Sun Feb  4 12:30:48 2018 - [info] ** Phase 1: Configuration Check Phase completed.
Sun Feb  4 12:30:48 2018 - [info] 
Sun Feb  4 12:30:48 2018 - [info] * Phase 2: Dead Master Shutdown Phase..
Sun Feb  4 12:30:48 2018 - [info] 
Sun Feb  4 12:30:48 2018 - [info] Forcing shutdown so that applications never connect to the current master..
Sun Feb  4 12:30:48 2018 - [info] Executing master IP deactivation script:
Sun Feb  4 12:30:48 2018 - [info]   /etc/mha/master_ip_failover --orig_master_host=192.168.1.110 --orig_master_ip=192.168.1.110 --orig_master_port=3110 --command=stop 
Sun Feb  4 12:30:48 2018 - [info]  done.
Sun Feb  4 12:30:48 2018 - [warning] shutdown_script is not set. Skipping explicit shutting down of the dead master.
Sun Feb  4 12:30:48 2018 - [info] * Phase 2: Dead Master Shutdown Phase completed.
Sun Feb  4 12:30:48 2018 - [info] 
Sun Feb  4 12:30:48 2018 - [info] * Phase 3: Master Recovery Phase..
Sun Feb  4 12:30:48 2018 - [info] 
Sun Feb  4 12:30:48 2018 - [info] * Phase 3.1: Getting Latest Slaves Phase..
Sun Feb  4 12:30:48 2018 - [info] 
Sun Feb  4 12:30:48 2018 - [info] The latest binary log file/position on all slaves is 3110binlog.000073:234
Sun Feb  4 12:30:48 2018 - [info] Latest slaves (Slaves that received relay log files to the latest):
Sun Feb  4 12:30:48 2018 - [info]   192.168.1.109(192.168.1.109:3109)  Version=5.7.19-log (oldest major version between slaves) log-bin:enabled
Sun Feb  4 12:30:48 2018 - [info]     GTID ON
Sun Feb  4 12:30:48 2018 - [info]     Replicating from 192.168.1.110(192.168.1.110:3110)
Sun Feb  4 12:30:48 2018 - [info]     Primary candidate for the new Master (candidate_master is set)
Sun Feb  4 12:30:48 2018 - [info] The oldest binary log file/position on all slaves is 3110binlog.000073:234
Sun Feb  4 12:30:48 2018 - [info] Oldest slaves:
Sun Feb  4 12:30:48 2018 - [info]   192.168.1.109(192.168.1.109:3109)  Version=5.7.19-log (oldest major version between slaves) log-bin:enabled
Sun Feb  4 12:30:48 2018 - [info]     GTID ON
Sun Feb  4 12:30:48 2018 - [info]     Replicating from 192.168.1.110(192.168.1.110:3110)
Sun Feb  4 12:30:48 2018 - [info]     Primary candidate for the new Master (candidate_master is set)
Sun Feb  4 12:30:48 2018 - [info] 
Sun Feb  4 12:30:48 2018 - [info] * Phase 3.3: Determining New Master Phase..
Sun Feb  4 12:30:48 2018 - [info] 
Sun Feb  4 12:30:48 2018 - [info] Searching new master from slaves..
Sun Feb  4 12:30:48 2018 - [info]  Candidate masters from the configuration file:
Sun Feb  4 12:30:48 2018 - [info]   192.168.1.109(192.168.1.109:3109)  Version=5.7.19-log (oldest major version between slaves) log-bin:enabled
Sun Feb  4 12:30:48 2018 - [info]     GTID ON
Sun Feb  4 12:30:48 2018 - [info]     Replicating from 192.168.1.110(192.168.1.110:3110)
Sun Feb  4 12:30:48 2018 - [info]     Primary candidate for the new Master (candidate_master is set)
Sun Feb  4 12:30:48 2018 - [info]  Non-candidate masters:
Sun Feb  4 12:30:48 2018 - [info]  Searching from candidate_master slaves which have received the latest relay log events..
Sun Feb  4 12:30:48 2018 - [info] New master is 192.168.1.109(192.168.1.109:3109)
Sun Feb  4 12:30:48 2018 - [info] Starting master failover..
Sun Feb  4 12:30:48 2018 - [info] 
From:
192.168.1.110(192.168.1.110:3110) (current master)
 +--192.168.1.109(192.168.1.109:3109)

To:
192.168.1.109(192.168.1.109:3109) (new master)
Sun Feb  4 12:30:48 2018 - [info] 
Sun Feb  4 12:30:48 2018 - [info] * Phase 3.3: New Master Recovery Phase..
Sun Feb  4 12:30:48 2018 - [info] 
Sun Feb  4 12:30:48 2018 - [info]  Waiting all logs to be applied.. 
Sun Feb  4 12:30:48 2018 - [info]   done.
Sun Feb  4 12:30:48 2018 - [info] Getting new master's binlog name and position..
Sun Feb  4 12:30:48 2018 - [info]  3109binlog.000089:234
Sun Feb  4 12:30:48 2018 - [info]  All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='192.168.1.109', MASTER_PORT=3109, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='xxx';
Sun Feb  4 12:30:48 2018 - [info] Master Recovery succeeded. File:Pos:Exec_Gtid_Set: 3109binlog.000089, 234, 2285de8a-9bc2-11e7-8a15-000c298e9a6f:1-14,
28ea40ab-9bbd-11e7-8cd1-000c29c31069:1-15308048
Sun Feb  4 12:30:48 2018 - [info] Executing master IP activate script:
Sun Feb  4 12:30:48 2018 - [info]   /etc/mha/master_ip_failover --command=start --ssh_user=root --orig_master_host=192.168.1.110 --orig_master_ip=192.168.1.110 --orig_master_port=3110 --new_master_host=192.168.1.109 --new_master_ip=192.168.1.109 --new_master_port=3109 --new_master_user='mha'   --new_master_password=xxx
Set read_only=0 on the new master.
ssh: connect to host 192.168.1.110 port 22: Connection timed out
Sun Feb  4 12:31:35 2018 - [info]  OK.
Sun Feb  4 12:31:35 2018 - [info] ** Finished master recovery successfully.
Sun Feb  4 12:31:35 2018 - [info] * Phase 3: Master Recovery Phase completed.
Sun Feb  4 12:31:35 2018 - [info] 
Sun Feb  4 12:31:35 2018 - [info] * Phase 4: Slaves Recovery Phase..
Sun Feb  4 12:31:35 2018 - [info] 
Sun Feb  4 12:31:35 2018 - [info] 
Sun Feb  4 12:31:35 2018 - [info] * Phase 4.1: Starting Slaves in parallel..
Sun Feb  4 12:31:35 2018 - [info] 
Sun Feb  4 12:31:35 2018 - [info] All new slave servers recovered successfully.
Sun Feb  4 12:31:35 2018 - [info] 
Sun Feb  4 12:31:35 2018 - [info] * Phase 5: New master cleanup phase..
Sun Feb  4 12:31:35 2018 - [info] 
Sun Feb  4 12:31:35 2018 - [info] Resetting slave info on the new master..
Sun Feb  4 12:31:35 2018 - [info]  192.168.1.109: Resetting slave info succeeded.
Sun Feb  4 12:31:35 2018 - [info] Master failover to 192.168.1.109(192.168.1.109:3109) completed successfully.
Sun Feb  4 12:31:35 2018 - [info] 

----- Failover Report -----

mainBusiness: MySQL Master failover 192.168.1.110(192.168.1.110:3110) to 192.168.1.109(192.168.1.109:3109) succeeded

Master 192.168.1.110(192.168.1.110:3110) is down!

Check MHA Manager logs at Client-Cent7-IP008:/data/mha/mainBusiness/manager.log for details.

Started automated(non-interactive) failover.
Invalidated master IP address on 192.168.1.110(192.168.1.110:3110)
Selected 192.168.1.109(192.168.1.109:3109) as a new master.
192.168.1.109(192.168.1.109:3109): OK: Applying all logs succeeded.
192.168.1.109(192.168.1.109:3109): OK: Activated master IP address.
192.168.1.109(192.168.1.109:3109): Resetting slave info succeeded.
Master failover to 192.168.1.109(192.168.1.109:3109) completed successfully.
Sun Feb  4 12:31:35 2018 - [info] Sending mail..
Option new_slave_hosts requires an argument     #双节点，没有多余的从节点，所以会报错，下面也是。
Unknown option: conf



故障切换解析：


​       1.每秒检查MySQL，连续4次无法连上MySQL服务后，进入SSH检查阶段，SSH也不通后，确认实例故障。由于故障实例为主库，触发切换主库的操作。


​         2.再次读取配置文件信息，获取所有注册的实例，及其切换偏好。关闭manager节点，启用切换脚本进行切换操作。切换操作的逻辑与之前的【MySQL】【高可用】purge_relay_logs工具的使用文章中分析的相近。  


​        3.切换主库成功后，输出切换报告，同时在/data/mha中生成 mainBusiness.failover.complete文件。接着在新的主库上进行虚拟IP的挂载，发送故障报告邮件。


附：


​   两篇前言文章：
【MySQL】【高可用】purge_relay_logs工具的使用
【MySQL】【高可用】从masterha_master_switch工具简单分析MHA的切换逻辑


​   1.千万要注意定时清理relay log。我在搭建完之后，直接进行了压测数据的写入，大量relay log用尽了所有的硬盘空间。这时从库MySQL服务假死，mha所有的脚本也都会因为得不到从库的回应也同样卡住。


​   2.另一种简单的send_report的脚本：


vim /etc/mha/mha_error.sh
#!/bin/sh
SYSTEM=$1
echo $1 'mha error,please check it immediately!'|mail -s 'mha error' psyduck007@outlook.com


​   邮件系统安装脚本：


printf "安装mail相关软件和服务"
yum -y install sendmail* > /dev/null 2>&1
yum -y install mailx > /dev/null 2>&1
printf "\033[32;1m%20s\033[0m\n" "[ OK ]"

printf "创建邮件发送配置"
cat > /etc/mail.rc<<EOF
set from=zabbix@xxx.com
set smtp=smtp.xxxx.com
set smtp-auth-user=zabbix@xxx.com
set smtp-auth-password=123456
set smtp-auth=login
EOF
printf "\033[32;1m%20s\033[0m\n" "[ OK ]"

printf "设置邮件服务自启动"
systemctl enable sendmail > /dev/null 2>&1
printf "\033[32;1m%20s\033[0m\n" "[ OK ]"

printf "启动邮件服务"
systemctl restart sendmail > /dev/null 2>&1
printf "\033[32;1m%20s\033[0m\n" "[ OK ]"

printf "发送测试邮件"
hostname |mail -s "test" psyduck007@outlook.com
printf "\033[32;1m%20s\033[0m\n" "[ OK ]"


​   调用方式：


/etc/mha/mah_error.sh mainBusiness
您可能感兴趣的文档:
点击免费下载>>软考高级考试备考技巧/历年真题/备考精华资料

--结束END--

本文标题: 【MySQL】【高可用】基于MHA架构的MySQL高可用故障自动切换架构

本文链接: https://www.lsjlt.com/news/47593.html(转载时请注明来源链接)

有问题或投稿请发送至: 邮箱/279061341@qq.com    QQ/279061341

本篇文章演示代码以及资料文档资料下载

下载Word文档到电脑，方便收藏和打印～

下载Word文档
去做题
猜你喜欢
  • 【MySQL】【高可用】基于MHA架构的MySQL高可用故障自动切换架构
    基于MHA架构的MySQL高可用切换架构 环境： ​ CentOS7+MySQL 5.7 + GTID 业务系统：mainBusiness ​ node1 : 192.16...
    99+
    2022-10-18
  • MySQL高可用架构之MHA
    一、MHA介绍 　　MHA（Master High Availability）目前在MySQL高可用方面是一个相对成熟的解决方案，它由日本DeNA公司youshimaton（现就职于Facebook公司）开发，是一套优秀的作为MySQL高可...
    99+
    2019-01-11
    MySQL高可用架构之MHA
  • MySQL高可用架构之MHA架构全解
    目录一、介绍二、组成三、工作过程四、架构五、实例展示MHA（Master HA）是一款开源的 MySQL 的高可用程序，它为 MySQL 主从复制架构提供了 automating m...
    99+
    2022-11-12
  • mysql MHA 高可用架构部署
    MHA简介：即Master High Availability Manager and Tools for MySQL，是日本的一位MySQL专家采用Perl语言编写的一个脚本管理工具， 该工具仅...
    99+
    2022-10-18
  • MySQL高可用架构-MMM、MHA、MGR、PXC
    主从复制如何工作 在主库把数据记录到binlog（二进制日志）。 备库开IO线程把binlog复制到自己的relaylog（中继日志）。 备库读取中继日志，重放到备库上。 半同步复制 半同步复制可以确保备库拥有主库数据的拷...
    99+
    2015-08-21
    MySQL高可用架构-MMM MHA MGR PXC
  • MySQL高可用架构之MHA实践
    本文主要是描述MHA高可用快速部署步骤以及简单使用命令 1、下载安装包     mha 依赖包：        &n...
    99+
    2022-10-18
  • MHA实现MySQL高可用集群架构
    转转连接 https://www.cnblogs.com/tanxiaojun/p/10424699.html MHA简介 MHA（Master HighAvailability）目前在MySQL高可...
    99+
    2022-10-18
  • 【赵强老师】MySQL高可用架构：MHA
    MHA（Master HA）是一款开源的 MySQL 的高可用程序，它为 MySQL 主从复制架构提供了 automating master failover 功能。MHA 在监控到 master 节点故障时，会提升其中拥有最新...
    99+
    2019-11-27
    【赵强老师】MySQL高可用架构：MHA
  • 用MHA架构实现MySQL高可用方法
    下面一起来了解下用MHA架构实现MySQL高可用方法，相信大家看完肯定会受益匪浅，文字在精不在多，希望用MHA架构实现MySQL高可用方法这篇短内容是你想要的。MySQL复制是异步或者半同步的。当maste...
    99+
    2022-10-18
  • MySQL高可用之MHA架构企业实战
    📢📢📢📣📣📣 哈喽！大家好，我是【IT邦德】，江湖人称jeames007，10年DBA工作经验 一位上进心十足的【大数据领域博主】！😜😜😜 中国DBA联盟(ACDU)成员，目前从事DBA及程序编程 擅长主流数据Oracle、MySQL、...
    99+
    2023-08-17
    mysql 架构 数据库 MHA
  • MySQL高可用架构MHA的部署及测试
    不知道大家之前对类似MySQL高可用架构MHA的部署及测试的文章有无了解，今天我在这里给大家再简单的讲讲。感兴趣的话就一起来看看正文部分吧，相信看完MySQL高可用架构MHA的部署及测试你一定会有所收获的。...
    99+
    2022-10-18
  • MySQL高可用架构之MHA的原理分析
    这篇文章主要介绍了MySQL高可用架构之MHA的原理分析，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让小编带着大家一起了解一下。MHA角色部署MHA 服务有两种角色，...
    99+
    2022-10-18
  • 基于MHA搭建MySQL Replication集群高可用架
    MHA架构介绍 MHA是Master High Availability的缩写，它是目前MySQL高可用方面的一个相对成熟的解决方案，其核心是使用perl语言编写的一组脚本，是一套优秀的作为MySQL高可用...
    99+
    2022-10-18
  • 简单了解MySQL高可用MHA架构知识
    下面一起来了解下MySQL高可用MHA架构知识，相信大家看完肯定会受益匪浅，文字在精不在多，希望MySQL高可用MHA架构知识这篇短内容是你想要的。MHA软件介绍1.MHA软件是由MHA Manager（管...
    99+
    2022-10-18
  • MySQL高可用架构-MMM、MHA、MGR、PXC（补小结，laoqi）
    小结 MMM是 MHA是 MGR是 主从复制如何工作 在主库把数据记录到binlog（二进制日志）。 备库开IO线程把binlog复制到自己的relaylog（中继日志）。 备库读取中继日志，重放到备库上。 半同步复制 半...
    99+
    2019-01-17
    MySQL高可用架构-MMM MHA MGR PXC（补小结，laoqi）
  • Consul, ProxySQL and MySQL MHA架构高可用方案分享
    下文给大家带来关于Consul, ProxySQL and MySQL MHA架构高可用方案，感兴趣的话就一起来看看这篇文章吧，相信看完Consul, ProxySQL and MySQL MHA架构高可用...
    99+
    2022-10-18
  • 基于MySQL的高可用可扩展架构该如何探讨
    基于MySQL的高可用可扩展架构该如何探讨，相信很多没有经验的人对此束手无策，为此本文总结了问题出现的原因和解决方法，通过这篇文章希望你能解决这个问题。随着信息量飞涨，信息的存储成为了这个时代至关重要的一项...
    99+
    2022-10-19
  • mysql5.6基于GTID模式之高可用架构搭建-MHA（mha0.56）
    一、环境部署：mysql1:192.168.110.131   作为mastermysql2:192.168.110.132   作为slavemysql3:192....
    99+
    2022-10-18
  • MySQL数据库实现高可用架构之MHA的实战
    目录一、MySQLMHA介绍1.1什么是MHA1.2MHA的组成1.3MHA的特点二、MySQLMHA搭建1．MHA架构部分2．故障模拟部分3.实验环境三、实验步骤1、关闭防火墙和S...
    99+
    2022-11-13
  • 使用masterha_master_switch工具实现MySQL高可用MHA的自动切换
    本文主要给大家介绍使用masterha_master_switch工具实现MySQL高可用MHA的自动切换，文章内容都是笔者用心摘选和编辑的，具有一定的针对性，对大家的参考意义还是比较大的，下面跟笔者一起了...
    99+
    2022-10-18
软考高级职称资格查询
软考职称历年真题下载
软考职称资料下载
热门wiki
近期文章
推荐阅读
热门问答
热门标签
编程网，编程工程师的家园，是目前国内优秀的开源技术社区之一，形成了由开源软件库、代码分享、资讯、协作翻译、讨论区和博客等几大频道内容，为IT开发者提供了一个发现、使用、并交流开源技术的平台。

  • 官方手机版

  • 微信公众号

  • 商务合作