MySQL MHA高可用架构搭建

最新推荐文章于 2023-06-28 23:10:45 发布

Liu三变

最新推荐文章于 2023-06-28 23:10:45 发布

阅读量177

点赞数

分类专栏： MySQL

本文链接：https://blog.csdn.net/justaiiit/article/details/108783784

版权

MySQL 专栏收录该内容

13 篇文章 1 订阅

订阅专栏

一. 环境信息

角色	IP	主机名	网卡
MHA node	10.210.99.33	liupc3	ens192
master	10.210.99.31	liupc1	ens192
slave	10.210.99.32	liupc2	ens192

VIP:10.210.99.30

二. 配置MySQL node主从复制（该部分不叙述了，详情可以自行百度）

三. 配置好相关依赖
所有节点安装好依赖（我这边是以非rootOS用户mysql执行的）
sudo yum install perl-DBD-MySQL -y

四. 配置MHA节点，master，slave三者之间服务器互信
注意：多台服务器配置互信需要将各自的id_rsa.pub都放到统一的authorized_keys文件中（注意本主机的）

五. 部署MHA
a.下载MHA node和MHA manager，下载地址：https://github.com/yoshinorim/mha4mysql-manager/wiki/Downloads

b.安装MHA node
在MySQL节点上下载MHA node，并安装

sudo rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm

安装完成之后检测：

[mysql@liupc1 ~]$ ls -lrt /usr/bin/ |grep -iE 'apply|filter|purge|save_binary'
-rwxr-xr-x    1 root root       7525 Apr  1  2014 save_binary_logs
-rwxr-xr-x    1 root root       8261 Apr  1  2014 purge_relay_logs
-rwxr-xr-x    1 root root       4807 Apr  1  2014 filter_mysqlbinlog
-rwxr-xr-x    1 root root      16367 Apr  1  2014 apply_diff_relay_logs
-rwxr-xr-x.   1 root root      28224 Apr  1 10:43 msgfilter

c.安装MHA manager

sudo rpm -ivh mha4mysql-manager-0.56-0.el6.noarch.rpm

安装完成之后检测：

[mysql@liupc3 ~]$ ls -lrt /usr/bin/ |grep -iE 'masterha|apply|purge|save_binary'
-rwxr-xr-x  1 root root       7525 Apr  1  2014 save_binary_logs
-rwxr-xr-x  1 root root       8261 Apr  1  2014 purge_relay_logs
-rwxr-xr-x  1 root root      16367 Apr  1  2014 apply_diff_relay_logs
-rwxr-xr-x  1 root root       1739 Apr  1  2014 masterha_stop
-rwxr-xr-x  1 root root       5171 Apr  1  2014 masterha_secondary_check
-rwxr-xr-x  1 root root       2373 Apr  1  2014 masterha_master_switch
-rwxr-xr-x  1 root root       2165 Apr  1  2014 masterha_master_monitor
-rwxr-xr-x  1 root root       2517 Apr  1  2014 masterha_manager
-rwxr-xr-x  1 root root       3201 Apr  1  2014 masterha_conf_host
-rwxr-xr-x  1 root root       1865 Apr  1  2014 masterha_check_status
-rwxr-xr-x  1 root root       1779 Apr  1  2014 masterha_check_ssh
-rwxr-xr-x  1 root root       1995 Apr  1  2014 masterha_check_repl

d.配置MHA

mkdir -p /home/mysql/MHA/masterha
cd /home/mysql/MHA/masterha
vi app1.cnf
[server default]
manager_log=/home/mysql/MHA/masterha/log/manager.log
manager_workdir=/home/mysql/MHA/masterha/log/app1.log
master_binlog_dir=/u01/my3308/log
master_ip_failover_script=/home/mysql/MHA/masterha/scripts/master_ip_failover
master_ip_online_change_script=/home/mysql/MHA/masterha/scripts/master_ip_online_change
password=Bnqc@2020
ping_interval=1
remote_workdir=/tmp
repl_password=Bnqc@2020
repl_user=dbsync
secondary_check_script=/usr/bin/masterha_secondary_check -s liupc1 -s liupc2 --user=root --master_host=liupc1 --master_ip=10.210.99.31 --master_port=3308
shutdown_script=""
ssh_user=mysql
user=mha

[server1]
hostname=10.210.99.31
port=3308

[server2]
candidate_master=1
check_repl_delay=0
hostname=10.210.99.32
port=3308

主要参数说明：

manager_log：设置manager的日志文件。
manager_workdir：设置manager的工作目录。
master_binlog_dir：设置master保存binlog的位置，以便MHA可以找到master的日志，这里的也就是mysql的数据目录。
master_ip_failover_script：设置自动failover时候的切换脚本。
master_ip_online_change_script：设置手动切换时候的切换脚本。
password：设置mysql中root用户的密码。
ping_interval：设置监控主库，发送ping包的时间间隔，默认是3秒，尝试三次没有回应的时候自动进行railover。
remote_workdir：设置远端mysql在发生切换时binlog的保存位置。
repl_password：设置复制用户的密码。
repl_user：设置复制环境中的复制用户名。
secondary_check_script：一旦MHA到hdp4的监控之间出现问题，MHA Manager将会尝试从hdp3登录到hdp4。
shutdown_script：设置故障发生后关闭故障主机脚本。该脚本的主要作用是关闭主机放在发生脑裂，这里没有使用。
ssh_user：设置ssh的登录用户名。
user：设置监控用户为root。
candidate_master：设置为候选master。设置该参数以后，发生主从切换以后将会将此从库提升为主库，即使这个主库不是集群中事件最新的slave。
check_repl_delay：默认情况下如果一个slave落后master 100M的relay logs的话，MHA将不会选择该slave作为一个新的master，因为对于这个slave的恢复需要花费很长时间，通过设置check_repl_delay=0，MHA触发切换在选择一个新的master的时候将会忽略复制延时，这个参数对于设置了candidate_master=1的主机非常有用，因为这个候选主在切换的过程中一定是新的master。

e.创建相关脚本
(1)创建failover脚本

mkdir -p /home/mysql/MHA/masterha/scripts
vi 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 = '10.210.99.30/24';  # Virtual IP 
my $key = "1"; 
my $ssh_start_vip = "sudo /sbin/ifconfig ens192:$key $vip";
my $ssh_stop_vip =  "sudo /sbin/ifconfig ens192:$key down";
 
GetOptions(
    'command=s'          => \$command,
    'ssh_user=s'         => \$ssh_user,
    'orig_master_host=s' => \$orig_master_host,
    'orig_master_ip=s'   => \$orig_master_ip,
    'orig_master_port=i' => \$orig_master_port,
    'new_master_host=s'  => \$new_master_host,
    'new_master_ip=s'    => \$new_master_ip,
    'new_master_port=i'  => \$new_master_port,
);
 
exit &main();
 
sub main {
 
    print "\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n"; 
 
    if ( $command eq "stop" || $command eq "stopssh" ) {
 
        # $orig_master_host, $orig_master_ip, $orig_master_port are passed.
        # If you manage master ip address at global catalog database,
        # invalidate orig_master_ip here.
        my $exit_code = 1;
        eval {
            print "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" ) {
 
        # 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 "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"; 
        `ssh $ssh_user\@$orig_master_host \" $ssh_start_vip \"`;
        exit 0;
    }
    else {
        &usage();
        exit 1;
    }
}
 
# A simple system call that enable the VIP on the new master 
sub start_vip() {
    `ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`;
}
# A simple system call that disable the VIP on the old_master
sub stop_vip() {
    `ssh $ssh_user\@$orig_master_host \" $ssh_stop_vip \"`;
}
 
sub usage {
    print
    "Usage: master_ip_failover --command=start|stop|stopssh|status --orig_master_host=host --orig_master_ip=ip --orig_master_port=port --new_master_host=host --new_master_ip=ip --new_master_port=port\n";
}

注意点：
1.挂载卸载VIP时需要注意调整VIP和网卡，设置成实际情况的信息。
2.如果是非root用户需要配置sudo，同时配置免密

(2)创建master_ip_online_change

cd /home/mysql/MHA/masterha/scripts
vi master_ip_online_change
#!/usr/bin/env perl
  
## Note: This is a sample script and is notcomplete. 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 gettimeofdaytv_interval );
use Time::HiRes qw(sleep gettimeofday tv_interval);
use Data::Dumper;
  
my $_tstart;
my $_running_interval = 0.1;
my (
 $command,         $orig_master_host, $orig_master_ip,
 $orig_master_port, $orig_master_user,
 $new_master_host, $new_master_ip,   $new_master_port,
 $new_master_user, 
);
  
my $vip = '10.210.99.30/24';  # Virtual IP 
my $key = "1"; 
my $ssh_start_vip = "sudo sbin/ifconfig ens192:$key $vip";
my $ssh_stop_vip = "sudo /sbin/ifconfig ens192:$key down";
my $ssh_user = "root";
my $new_master_password = "123456";
my $orig_master_password = "123456";
  
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,
 'orig_master_user=s'     =>\$orig_master_user,
 #'orig_master_password=s' => \$orig_master_password,
 '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 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" );
      nextif ( 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 nobodycan connect. Disabling per-session binlog beforehand
     #$orig_master_handler->disable_log_bin_local();
     #print current_time_us() . " Drpping app user on the origmaster..\n";
     #FIXME_xxx_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 aredisconnected.. (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(exceptSUPER) 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 cancomplete
     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 aredisconnected.. (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} );
     }
  
                print "Disabling the VIPon old master: $orig_master_host \n";
                &stop_vip();    
  
     ## Terminating all threads
     print current_time_us() . " Killing all applicationthreads..\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 thoughactivating updatable accounts/ip failed so that we don't interrupt slaves'recovery.
# If exit code is 0 or 10, MHA does notabort
    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 newmaster.\n";
     $new_master_handler->disable_read_only();
  
     ## Creating an app user on the new master
     #print current_time_us() . " Creating app user on the newmaster..\n";
     #FIXME_xxx_create_app_user($new_master_handler);
     #$new_master_handler->enable_log_bin_local();
     $new_master_handler->disconnect();
  
     ## Update master ip on the catalog database, etc
                print "Enabling the VIP -$vip on the new master - $new_master_host \n";
                &start_vip();
                $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;
  }
}
  
# A simple system call that enable the VIPon the new master
sub start_vip() {
   `ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`;
}
# A simple system call that disable the VIPon the old_master
sub stop_vip() {
   `ssh $ssh_user\@$orig_master_host \" $ssh_stop_vip \"`;
}
  
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;
}

注意点：与failover脚本一样

f.验证配置
(1)检查ssh配置：

[mysql@liupc3 scripts]$ /usr/bin/masterha_check_ssh  --conf=/home/mysql/MHA/masterha/app1.cnf
Fri Sep 25 09:29:15 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Fri Sep 25 09:29:15 2020 - [info] Reading application default configuration from /home/mysql/MHA/masterha/app1.cnf..
Fri Sep 25 09:29:15 2020 - [info] Reading server configuration from /home/mysql/MHA/masterha/app1.cnf..
Fri Sep 25 09:29:15 2020 - [info] Starting SSH connection tests..
Fri Sep 25 09:29:16 2020 - [debug] 
Fri Sep 25 09:29:16 2020 - [info] All SSH connection tests passed successfully.

(2)检查复制状态

[mysql@liupc3 masterha]$ /usr/bin/masterha_check_repl --conf=/home/mysql/MHA/masterha/app1.cnf
Fri Sep 25 10:09:47 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Fri Sep 25 10:09:47 2020 - [info] Reading application default configuration from /home/mysql/MHA/masterha/app1.cnf..
Fri Sep 25 10:09:47 2020 - [info] Reading server configuration from /home/mysql/MHA/masterha/app1.cnf..
Fri Sep 25 10:09:47 2020 - [info] MHA::MasterMonitor version 0.56.
Fri Sep 25 10:09:48 2020 - [info] Multi-master configuration is detected. Current primary(writable) master is 10.210.99.31(10.210.99.31:3308)
Fri Sep 25 10:09:48 2020 - [info] Master configurations are as below: 
Master 10.210.99.31(10.210.99.31:3308), replicating from 10.210.99.32(10.210.99.32:3308)
Master 10.210.99.32(10.210.99.32:3308), replicating from 10.210.99.31(10.210.99.31:3308), read-only

Fri Sep 25 10:09:48 2020 - [info] GTID failover mode = 1
Fri Sep 25 10:09:48 2020 - [info] Dead Servers:
Fri Sep 25 10:09:48 2020 - [info] Alive Servers:
Fri Sep 25 10:09:48 2020 - [info]   10.210.99.31(10.210.99.31:3308)
Fri Sep 25 10:09:48 2020 - [info]   10.210.99.32(10.210.99.32:3308)
Fri Sep 25 10:09:48 2020 - [info] Alive Slaves:
Fri Sep 25 10:09:48 2020 - [info]   10.210.99.32(10.210.99.32:3308)  Version=8.0.21 (oldest major version between slaves) log-bin:enabled
Fri Sep 25 10:09:48 2020 - [info]     GTID ON
Fri Sep 25 10:09:48 2020 - [info]     Replicating from 10.210.99.31(10.210.99.31:3308)
Fri Sep 25 10:09:48 2020 - [info]     Primary candidate for the new Master (candidate_master is set)
Fri Sep 25 10:09:48 2020 - [info] Current Alive Master: 10.210.99.31(10.210.99.31:3308)
Fri Sep 25 10:09:48 2020 - [info] Checking slave configurations..
Fri Sep 25 10:09:48 2020 - [info] Checking replication filtering settings..
Fri Sep 25 10:09:48 2020 - [info]  binlog_do_db= , binlog_ignore_db= 
Fri Sep 25 10:09:48 2020 - [info]  Replication filtering check ok.
Fri Sep 25 10:09:48 2020 - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.
Fri Sep 25 10:09:48 2020 - [info] Checking SSH publickey authentication settings on the current master..
Fri Sep 25 10:09:48 2020 - [info] HealthCheck: SSH to 10.210.99.31 is reachable.
Fri Sep 25 10:09:48 2020 - [info] 
10.210.99.31(10.210.99.31:3308) (current master)
 +--10.210.99.32(10.210.99.32:3308)

Fri Sep 25 10:09:48 2020 - [info] Checking replication health on 10.210.99.32..
Fri Sep 25 10:09:48 2020 - [info]  ok.
Fri Sep 25 10:09:48 2020 - [info] Checking master_ip_failover_script status:
Fri Sep 25 10:09:48 2020 - [info]   /home/mysql/MHA/masterha/scripts/master_ip_failover --command=status --ssh_user=mysql --orig_master_host=10.210.99.31 --orig_master_ip=10.210.99.31 --orig_master_port=3308 


IN SCRIPT TEST====sudo /sbin/ifconfig ens192:1 down==sudo /sbin/ifconfig ens192:1 10.210.99.30/24===

Checking the Status of the script.. OK 
Fri Sep 25 10:09:49 2020 - [info]  OK.
Fri Sep 25 10:09:49 2020 - [warning] shutdown_script is not defined.
Fri Sep 25 10:09:49 2020 - [info] Got exit code 0 (Not master dead).

MySQL Replication Health is OK.

(3)检查MHA manager状态

[mysql@liupc3 masterha]$ /usr/bin/masterha_check_status --conf=/home/mysql/MHA/masterha/app1.cnf
app1 is stopped(2:NOT_RUNNING).

原因：未启动manager
启动manager并检查

[mysql@liupc3 masterha]$nohup /usr/bin/masterha_manager --conf=/home/mysql/MHA/masterha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /home/mysql/MHA/masterha/log/app1.log/manager.log 2>&1 &
[mysql@liupc3 masterha]$ /usr/bin/masterha_check_status --conf=/home/mysql/MHA/masterha/app1.cnf
app1 (pid:39251) is running(0:PING_OK), master:10.210.99.31

启动参数说明：

remove_dead_master_conf：该参数代表当发生主从切换后，老的主库的ip将会从配置文件中移除。
manger_log：日志存放位置。
ignore_last_failover：在缺省情况下，如果MHA检测到连续发生宕机，且两次宕机间隔不足8小时的话，则不会进行Failover，之所以这样限制是为了避免ping-pong效应。该参数代表忽略上次MHA触发切换产生的文件，默认情况下，MHA发生切换后会在日志目录，也就是上面设置的/data产生app1.failover.complete文件，下次再次切换的时候如果发现该目录下存在该文件将不允许触发切换，除非在第一次切换后收到删除该文件。为了方便，这里设置为–ignore_last_failover。

g.挂载VIP

liupc1:pudge :master > sudo /sbin/ifconfig ens192:1 10.210.99.30/24
liupc1:pudge :master > ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
2: ens192: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP group default qlen 1000
    link/ether 00:50:56:97:00:fd brd ff:ff:ff:ff:ff:ff
    inet 10.210.99.31/24 brd 10.210.99.255 scope global noprefixroute ens192
       valid_lft forever preferred_lft forever
    inet 10.210.99.30/24 brd 10.210.99.255 scope global secondary ens192:1
       valid_lft forever preferred_lft forever

h.验证

#1.检查各个节点读写和复制情况
#node1
liupc1:pudge :master > mysql-l -e "show variables like 'read_only'";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | OFF   |
+---------------+-------+

liupc1:pudge :master > mysql-l -e "show slave status \G"|grep -iE 'slave_io_running|slave_sql_running';
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

#node2
liupc2:pudge :slave > mysql-l -e "show variables like 'read_only';"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | ON    |
+---------------+-------+
liupc2:pudge :slave > mysql-l -e "show slave status \G"|grep -iE 'slave_io_running|slave_sql_running';
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

#2.启动MHA
[mysql@liupc3 masterha]$ nohup /usr/bin/masterha_manager --conf=/home/mysql/MHA/masterha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /home/mysql/MHA/masterha/log/app1.log/manager.log 2>&1 &
[1] 44878

#检查日志信息
app1: MySQL Master failover 10.210.99.31(10.210.99.31:3308) to 10.210.99.32(10.210.99.32:3308) succeeded

Master 10.210.99.31(10.210.99.31:3308) is down!

Check MHA Manager logs at liupc3:/home/mysql/MHA/masterha/log/manager.log for details.

Started automated(non-interactive) failover.
Invalidated master IP address on 10.210.99.31(10.210.99.31:3308)
Selected 10.210.99.32(10.210.99.32:3308) as a new master.
10.210.99.32(10.210.99.32:3308): OK: Applying all logs succeeded.
10.210.99.32(10.210.99.32:3308): OK: Activated master IP address.
10.210.99.32(10.210.99.32:3308): Resetting slave info succeeded.
Master failover to 10.210.99.32(10.210.99.32:3308) completed successfully.
Fri Sep 25 16:13:52 2020 - [info] MHA::MasterMonitor version 0.56.
Fri Sep 25 16:13:53 2020 - [error][/usr/share/perl5/vendor_perl/MHA/ServerManager.pm, ln671] Master 10.210.99.31:3308 from which slave 10.210.99.32(
10.210.99.32:3308) replicates is not defined in the configuration file!
Fri Sep 25 16:13:53 2020 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln424] Error happened on checking configurations.  at /usr/sha
re/perl5/vendor_perl/MHA/MasterMonitor.pm line 326.
Fri Sep 25 16:13:53 2020 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln523] Error happened on monitoring servers.
Fri Sep 25 16:13:53 2020 - [info] Got exit code 1 (Not master dead).
Fri Sep 25 16:14:59 2020 - [info] MHA::MasterMonitor version 0.56.
Fri Sep 25 16:15:00 2020 - [info] Multi-master configuration is detected. Current primary(writable) master is liupc1(10.210.99.31:3308)
Fri Sep 25 16:15:00 2020 - [info] Master configurations are as below: 
Master liupc1(10.210.99.31:3308), replicating from 10.210.99.32(10.210.99.32:3308)
Master 10.210.99.32(10.210.99.32:3308), replicating from 10.210.99.31(10.210.99.31:3308), read-only

Fri Sep 25 16:15:00 2020 - [info] GTID failover mode = 1
Fri Sep 25 16:15:00 2020 - [info] Dead Servers:
Fri Sep 25 16:15:00 2020 - [info] Alive Servers:
Fri Sep 25 16:15:00 2020 - [info]   liupc1(10.210.99.31:3308)
Fri Sep 25 16:15:00 2020 - [info]   10.210.99.32(10.210.99.32:3308)
Fri Sep 25 16:15:00 2020 - [info] Alive Slaves:
Fri Sep 25 16:15:00 2020 - [info]   10.210.99.32(10.210.99.32:3308)  Version=8.0.21 (oldest major version between slaves) log-bin:enabled
Fri Sep 25 16:15:00 2020 - [info]     GTID ON
Fri Sep 25 16:15:00 2020 - [info]     Replicating from 10.210.99.31(10.210.99.31:3308)
Fri Sep 25 16:15:00 2020 - [info]     Primary candidate for the new Master (candidate_master is set)
Fri Sep 25 16:15:00 2020 - [info] Current Alive Master: liupc1(10.210.99.31:3308)
Fri Sep 25 16:15:00 2020 - [info] Checking slave configurations..
Fri Sep 25 16:15:00 2020 - [info] Checking replication filtering settings..
Fri Sep 25 16:15:00 2020 - [info]  binlog_do_db= , binlog_ignore_db= 
Fri Sep 25 16:15:00 2020 - [info]  Replication filtering check ok.
Fri Sep 25 16:15:00 2020 - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.
Fri Sep 25 16:15:00 2020 - [info] Checking SSH publickey authentication settings on the current master..
Fri Sep 25 16:15:00 2020 - [info] HealthCheck: SSH to liupc1 is reachable.
Fri Sep 25 16:15:00 2020 - [info] 
liupc1(10.210.99.31:3308) (current master)
 +--10.210.99.32(10.210.99.32:3308)

Fri Sep 25 16:15:00 2020 - [info] Checking master_ip_failover_script status:
Fri Sep 25 16:15:00 2020 - [info]   /home/mysql/MHA/masterha/scripts/master_ip_failover --command=status --ssh_user=mysql --orig_master_host=liupc1 
--orig_master_ip=10.210.99.31 --orig_master_port=3308 


IN SCRIPT TEST====sudo /sbin/ifconfig ens192:1 down==sudo /sbin/ifconfig ens192:1 10.210.99.30/24===

Checking the Status of the script.. OK 
Fri Sep 25 16:15:00 2020 - [info]  OK.
Fri Sep 25 16:15:00 2020 - [warning] shutdown_script is not defined.
Fri Sep 25 16:15:00 2020 - [info] Set master ping interval 1 seconds.
Fri Sep 25 16:15:00 2020 - [info] Set secondary check script: /usr/bin/masterha_secondary_check -s 10.210.99.31 -s 10.210.99.32
Fri Sep 25 16:15:00 2020 - [info] Starting ping health check on liupc1(10.210.99.31:3308)..
Fri Sep 25 16:15:00 2020 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond..


3.检查VIP信息
liupc1:pudge :master > ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
2: ens192: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP group default qlen 1000
    link/ether 00:50:56:97:00:fd brd ff:ff:ff:ff:ff:ff
    inet 10.210.99.31/24 brd 10.210.99.255 scope global noprefixroute ens192
       valid_lft forever preferred_lft forever
    inet 10.210.99.30/24 brd 10.210.99.255 scope global secondary ens192:1
       valid_lft forever preferred_lft forever

#已成功挂载

4.切换
#停master节点的MySQL服务
liupc1:pudge :master > mysql-l 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 35
Server version: 8.0.21 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> shutdown ;
Query OK, 0 rows affected (0.00 sec)

#查看slave节点信息
liupc2:pudge :slave > ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
2: ens192: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP group default qlen 1000
    link/ether 00:50:56:97:a7:f6 brd ff:ff:ff:ff:ff:ff
    inet 10.210.99.32/24 brd 10.210.99.255 scope global noprefixroute ens192
       valid_lft forever preferred_lft forever
    inet 10.210.99.30/24 brd 10.210.99.255 scope global secondary ens192:1
       valid_lft forever preferred_lft forever
liupc2:pudge :slave > mysql-l
liupc2:pudge :slave > mysql-l -e "show variables like 'read_only';"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | OFF   |
+---------------+-------+

#VIP挂到原来slave节点，读写模式也打开了，验证正常！

Liu三变

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
2
评论
MySQL MHA高可用架构搭建

一. 环境信息角色IP主机名网卡MHA node10.210.99.33liupc3ens192master10.210.99.31liupc1ens192slave10.210.99.32liupc2ens192VIP:10.210.99.30二. 配置MySQL node主从复制（该部分不叙述了，详情可以自行百度）.三. 配置好相关依赖所有节点安装好依赖（我这边是以非rootOS用户mysql执行的）sudo yum install
复制链接

扫一扫