用虚拟机搭键mha

最新推荐文章于 2022-07-04 02:08:59 发布
最新推荐文章于 2022-07-04 02:08:59 发布
阅读量1.5k 收藏 1
点赞数
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/jgmydsai/article/details/44993103
版权

基本环境

manager & binlog 192.168.1.41
master 192.168.1.42
slave1 192.168.1.43
slave2 192.168.1.44
binlog server 192.168.1.41

确认/etc/my.cnf中的参数

binlog_format=row
gtid_mode = on
enforce_gtid_consistency = on
log-slave-updates = 1
skip_slave_start = 1

并配制好主从关系

本例中复制用户名与密码为

grant replication slave on *.* to  rep@'192.168.1.%' identified by 'pwd4rep';


互信

配制ssh两两互通

ssh-keygen
cd ~/.ssh
cat id_rsa.pub > authorized_keys

该文件要包括所有机子的公匙，各节点均保留一份同样的文件

内容约如下

cat authorized_keys
ssh-rsa ....IwkGJcdjTkAashNWlVfD2cQ== root@mha41
ssh-rsa ....KGht29PlsTwCXm3BEGXUPvw== root@mha42
ssh-rsa ....+fb1fM71iGx42U5/CE3qH0Q== root@mha43
ssh-rsa ....uMdoOtmZ9wMqkg9vDwgW9KQ== root@mha44

安装mha

1、manager

41作为manager要安装

mha4mysql-node和mha4mysql-manage

yum install perl-DBD-MySQL

另外还有部分文件本地源中没有，需要下载或连线安装

yum install perl*


tar xzvf mha4mysql-node-0.56.tar.gz 
cd mha4mysql-node-0.56
perl Makefile.PL
make && make install

tar xzvf mha4mysql-manager-0.56.tar.gz 
cd mha4mysql-manager-0.56
perl Makefile.PL
make && make install

2、node

42、43、44作为node只需要安装node

yum install perl-DBD-MySQL

tar xzvf mha4mysql-node-0.56.tar.gz 
cd mha4mysql-node-0.56
perl Makefile.PL
make && make install

配制mha

41上执行

mkdir --p /etc/mha/{app1,scripts} 
cd /tmp/mha4mysql-manager-0.56
cp samples/conf/* /etc/mha/
cp samples/scripts/* /etc/mha/scripts/
mv /etc/mha/app1.cnf /etc/mha/app1/
mv /etc/mha/masterha_default.cnf /etc/


mysql增加用户供mha使用

grant all privileges on *.* to  mha@'192.168.1.%' identified by 'pwd4mha';

相关文件配制如下

app1.cnf

[root@oel64 ~]# cat /etc/mha/app1/app1.cnf 
[server default]
manager_workdir=/var/log/masterha/app1
manager_log=/var/log/masterha/app1/manager.log
remote_workdir=/var/log/masterha/app1

[server1]
hostname=192.168.1.42
master_binlog_dir=/data/mysql/user_3306/data/
candidate_master=1
check_repl_delay=0

[server2]
hostname=192.168.1.43
master_binlog_dir=/data/mysql/user_3306/data/
candidate_master=1
check_repl_delay=0

[server3]
hostname=192.168.1.44
master_binlog_dir=/data/mysql/user_3306/data/
ignore_fail=1
no_master=1

# [server4]
# hostname=host4
# no_master=1

[binlog1]
hostname=192.168.1.41
master_binlog_dir=/data/mysql/binlog/
ignore_fail=1
no_master=1


masterha_default.cnf

[root@oel64 ~]# cat /etc/masterha_default.cnf
[server default]
user= mha
password= pwd4mha

ssh_user=root
# master_binlog_dir= /var/lib/mysql,/var/log/mysql
# remote_workdir=/data/log/masterha

repl_user= rep
repl_password= pwd4rep

secondary_check_script= masterha_secondary_check -s mha42 -s mha43 -s mha44
ping_interval=1
master_ip_failover_script=/etc/mha/scripts/master_ip_failover 
# shutdown_script= /script/masterha/power_manager
# report_script= /script/masterha/send_report
master_ip_online_change_script= /etc/mha/scripts/master_ip_online_change

master_ip_online_change

[root@oel64 ~]# cat /etc/mha/scripts/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 (
  $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,
);

my $vip = '192.168.1.45'; # Virtual IP
my $gateway = '192.168.1.2';#Gateway IP
my $interface = 'eth0';
my $key = "1";
my $ssh_start_vip = "/sbin/ifconfig $interface:$key $vip;/sbin/arping -I $interface -c 3 -s $vip $gateway >/dev/null 2>&1";
my $ssh_stop_vip = "/sbin/ifconfig $interface:$key down";


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

      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" ) {
    ## 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 {
      print "Enabling the VIP - $vip on the new master - $new_master_host \n";
      &start_vip();
      ## 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
    &start_vip();   
     exit 0;
  }
  else {
    &usage();
    exit 1;
  }
}


# simple system call that enable the VIP on the new master
sub start_vip() {
    `ssh $new_master_ssh_user\@$new_master_ip \" $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_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;
}

master_ip_failover

[root@oel64 ~]# cat /etc/mha/scripts/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 = '192.168.1.45'; # Virtual IP
my $gateway = '192.168.1.1';#Gateway IP
my $interface = 'eth0';
my $key = "1";
my $ssh_start_vip = "/sbin/ifconfig $interface:$key $vip;/sbin/arping -I $interface -c 3 -s $vip $gateway";
my $ssh_stop_vip = "/sbin/ifconfig $interface:$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,
  'new_master_user=s'     => \$new_master_user,
  'new_master_password=s' => \$new_master_password,
);

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 $@;

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

    # do nothing
    print "Checking the Status of the script.. OK \n";
   `ssh $ssh_user\@$orig_master_ip \" $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_ip \" $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";
}


启动binlog server

mysqlbinlog -R --host=192.168.1.42 --user=mha --password='pwd4mha' --raw --stop-never mysql-bin.000001 &
可以看到42上的binlog已取到41上 
[root@oel64 binlog]# ls -lh
总用量 1.4M
-rw-r--r--. 1 root root  68K 4月  10 04:58 mysql-bin.000001
-rw-r--r--. 1 root root 1.2M 4月  10 04:58 mysql-bin.000002
-rw-r--r--. 1 root root  654 4月  10 04:58 mysql-bin.000003
-rw-r--r--. 1 root root  143 4月  10 04:58 mysql-bin.000004
-rw-r--r--. 1 root root  429 4月  10 04:58 mysql-bin.000005
-rw-r--r--. 1 root root  590 4月  10 04:58 mysql-bin.000006
-rw-r--r--. 1 root root  238 4月  10 04:58 mysql-bin.000007
-rw-r--r--. 1 root root 1.5K 4月  10 04:58 mysql-bin.000008

验正

1、验正ssh

masterha_check_ssh --conf=/etc/mha/app1/app1.cnf

部分结果如下：

Mon Dec 29 08:12:41 2014 -- [info] Reading default configuration from /etc/masterha_default.cnf..
Mon Dec 29 08:12:41 2014 -- [info] Reading application default configuration from /etc/mha/app1/app1.cnf..
Mon Dec 29 08:12:41 2014 -- [info] Reading server configuration from /etc/mha/app1/app1.cnf..
Mon Dec 29 08:12:41 2014 -- [info] Starting SSH connection tests..
Mon Dec 29 08:12:42 2014 -- [debug] 
Mon Dec 29 08:12:41 2014 -- [debug]  Connecting via SSH from root@192.168.1.31(192.168.1.31:22) to root@192.168.1.32(192.168.1.32:22)..
Warning: Permanently added '192.168.1.31' (RSA) to the list of known hosts.
Mon Dec 29 08:12:42 2014 -- [debug]   ok.
Mon Dec 29 08:12:42 2014 -- [debug] 
Mon Dec 29 08:12:41 2014 -- [debug]  Connecting via SSH from root@192.168.1.32(192.168.1.32:22) to root@192.168.1.31(192.168.1.31:22)..
Mon Dec 29 08:12:42 2014 -- [debug]   ok.
Mon Dec 29 08:12:42 2014 -- [info] All SSH connection tests passed successfully.

2、验正主从复制

masterha_check_repl --conf=/etc/mha/app1/app1.cnf

部分结果如下：


Fri Apr 10 05:22:53 2015 - [info]   Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/data/mysql/binlog/ --output_file=/var/log/masterha/app1/save_binary_logs_test --manager_version=0.56 --start_file=mysql-bin.000008 
Fri Apr 10 05:22:53 2015 - [info]   Connecting to root@192.168.1.41(192.168.1.41:22).. 
  Creating /var/log/masterha/app1 if not exists..    ok.
  Checking output directory is accessible or not..
   ok.
  Binlog found at /data/mysql/binlog/, up to mysql-bin.000008
Fri Apr 10 05:22:53 2015 - [info] Binlog setting check done.
Fri Apr 10 05:22:53 2015 - [info] Checking SSH publickey authentication settings on the current master..
Fri Apr 10 05:22:54 2015 - [info] HealthCheck: SSH to 192.168.1.42 is reachable.
Fri Apr 10 05:22:54 2015 - [info] 
192.168.1.42(192.168.1.42:3306) (current master)
 +--192.168.1.43(192.168.1.43:3306)
 +--192.168.1.44(192.168.1.44:3306)

Fri Apr 10 05:22:54 2015 - [info] Checking replication health on 192.168.1.43..
Fri Apr 10 05:22:54 2015 - [info]  ok.
Fri Apr 10 05:22:54 2015 - [info] Checking replication health on 192.168.1.44..
Fri Apr 10 05:22:54 2015 - [info]  ok.
Fri Apr 10 05:22:54 2015 - [info] Checking master_ip_failover_script status:
Fri Apr 10 05:22:54 2015 - [info]   /etc/mha/scripts/master_ip_failover --command=status --ssh_user=root --orig_master_host=192.168.1.42 --orig_master_ip=192.168.1.42 --orig_master_port=3306 


IN SCRIPT TEST====/sbin/ifconfig eth0:1 down==/sbin/ifconfig eth0:1 192.168.1.45;/sbin/arping -I eth0 -c 3 -s 192.168.1.45 192.168.1.1===

Checking the Status of the script.. OK 
Fri Apr 10 05:22:58 2015 - [info]  OK.
Fri Apr 10 05:22:58 2015 - [warning] shutdown_script is not defined.
Fri Apr 10 05:22:58 2015 - [info] Got exit code 0 (Not master dead).

MySQL Replication Health is OK.


启停

1、启动

nohup masterha_manager --conf=/etc/mha/app1/app1.cnf  > /tmp/mha_manager.log 2>&1  &

2、检测状态

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


app1 (pid:30679) is running(0:PING_OK), master:192.168.1.42

3、停止

masterha_stop --conf=/etc/mha/app1/app1.cnf
Stopped app1 successfully.
[2]+  Exit 1                  nohup masterha_manager --conf=/etc/mha/app1/app1.cnf > /tmp/mha_manager.log 2>&1


自动切换

正常启动mha后，停止42的mysql服务，可以观察到自动切换


tail /var/log/masterha/app1/manager.log 
Check MHA Manager logs at oel64:/var/log/masterha/app1/manager.log for details.

Started automated(non-interactive) failover.
Invalidated master IP address on 192.168.1.42(192.168.1.42:3306)
Selected 192.168.1.43(192.168.1.43:3306) as a new master.
192.168.1.43(192.168.1.43:3306): OK: Applying all logs succeeded.
192.168.1.43(192.168.1.43:3306): OK: Activated master IP address.
192.168.1.44(192.168.1.44:3306): OK: Slave started, replicating from 192.168.1.43(192.168.1.43:3306)
192.168.1.43(192.168.1.43:3306): Resetting slave info succeeded.
Master failover to 192.168.1.43(192.168.1.43:3306) completed successfully.

这时43变为新的主机

可以在41上用命令看

[root@oel64 binlog]# mysql -umha -ppwd4mha -h192.168.1.45 -e 'select @@hostname';
Warning: Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| mha43      |
+------------+

44上可以看到，指向了43 

mysql> select @@hostname;
+------------+
| @@hostname |
+------------+
| mha44      |
+------------+
1 row in set (0.00 sec)

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.43
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60

这期间mha调用了master_ip_failover 

[root@oel64 app1]# cat manager.log | grep "master_ip_failover"
Fri Apr 10 21:23:39 2015 - [info] Checking master_ip_failover_script status:
Fri Apr 10 21:23:39 2015 - [info]   /etc/mha/scripts/master_ip_failover --command=status --ssh_user=root --orig_master_host=192.168.1.42 --orig_master_ip=192.168.1.42 --orig_master_port=3306 
Fri Apr 10 21:24:12 2015 - [info]   /etc/mha/scripts/master_ip_failover --orig_master_host=192.168.1.42 --orig_master_ip=192.168.1.42 --orig_master_port=3306 --command=stopssh --ssh_user=root  
Fri Apr 10 21:24:13 2015 - [info]   /etc/mha/scripts/master_ip_failover --command=start --ssh_user=root --orig_master_host=192.168.1.42 --orig_master_ip=192.168.1.42 --orig_master_port=3306 --new_master_host=192.168.1.43 --new_master_ip=192.168.1.43 --new_master_port=3306 --new_master_user='mha' --new_master_password='pwd4mha'

分别是启动、关闭、切换 vip


手动切换

42修复后要重新设为master

1、同步42上的数据

cat manager.log | grep "change master" -i
Fri Apr 10 21:24:13 2015 - [info]  All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='192.168.1.43', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='rep', MASTER_PASSWORD='xxx';

更改上面的语句并执行 

CHANGE MASTER TO MASTER_HOST='192.168.1.43',
MASTER_PORT=3306,
MASTER_AUTO_POSITION=1,
MASTER_USER='rep',
MASTER_PASSWORD='pwd4rep';


2、切换
同步完成后再切换

masterha_master_switch --master_state=alive --conf=/etc/mha/app1/app1.cnf --new_master_host=192.168.1.42 --orig_master_is_new_slave --running_updates_limit=10000

上述语句会调用master_ip_online_change

/etc/mha/scripts/master_ip_online_change
 --command=stop
 --orig_master_host=192.168.1.43
 --orig_master_ip=192.168.1.43
 --orig_master_port=3306
 --orig_master_user='mha'
 --orig_master_password='pwd4mha'
 --new_master_host=192.168.1.42
 --new_master_ip=192.168.1.42
 --new_master_port=3306
 --new_master_user='mha'
 --new_master_password='pwd4mha'
 --orig_master_ssh_user=root
 --new_master_ssh_user=root  
 --orig_master_is_new_slave

/etc/mha/scripts/master_ip_online_change
 --command=start
 --orig_master_host=192.168.1.43
 --orig_master_ip=192.168.1.43
 --orig_master_port=3306
 --orig_master_user='mha'
 --orig_master_password='pwd4mha'
 --new_master_host=192.168.1.42
 --new_master_ip=192.168.1.42
 --new_master_port=3306
 --new_master_user='mha'
 --new_master_password='pwd4mha'
 --orig_master_ssh_user=root
 --new_master_ssh_user=root  
 --orig_master_is_new_slave

切换完后验正vip 


[root@oel64 binlog]# mysql -umha -ppwd4mha -h192.168.1.45 -e 'select @@hostname';
Warning: Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| mha42      |
+------------+




jgmydsai
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
vmware中mysqlMHA(mha)环境搭建
huoyuanshen的博客
03-30 456
vm环境搭建(学习环境，非正式环境): 1,先搭建主机。 1，安装好数据库，然后进行初始化（保持root用户免密码登陆，正式环境不可以这样！)。 下载：http://blog.csdn.net/huoyuanshen/article/details/50832432 安装：http://blog.csdn.net/huoyuanshen/article/details/51673449
MHA
风中清淡
10-29 1700
MHA配置互信 配置互信 1） 在三台机器上生成各自的key文件 [root@mysqldb1 11:10:53 /root] # ssh-keygen -t rsa [root@mysqldb2 11:10:56 /root] # ssh-keygen -t rsa [root@mysqldb3 11:10:58 /root] # ssh-keygen -t rsa 2）用ssh-copy-i...
mha配置参数详解：
无双-放飞的自我
04-29 794
mha配置参数详解： 参数名字 是否必须 参数作用域 默认值 示例 hostname Yes Local Only - hostname=mysql_server1, hostname=192.168.0.1, etc ip No Local Only gethostbyname($hostname) ip=1...
数据库中的MHA高可用配置及故障切换
Gengchenchen的博客
02-19 2436
文章目录一、MHA概述二、MHA的组成三、案例拓扑图四、搭建步骤实验思路： 一、MHA概述 MHA（MasterHigh Availability）是一套优秀的MySQL高可用环境下故障切换和主从复制的软件。 MHA 的出现就是解决MySQL 单点的问题。 MySQL故障切换过程中，MHA能做到0~30秒内自动完成故障切换操作。 MHA能在故障切换的过程中最大程度上保证数据的一致性，以达到真正意义上的高可用。 二、MHA的组成 1、MHA Node（数据节点） MHA Node 运行在每台 MySQL
mysql高可用mha部署
sdmei
02-06 1127
Mysql的高可用方案五花八门，本人人为最可靠、稳定、实用的还是mha。 mha方案涉及到5个节点：manager\secondary-check1\secondary-check2\db1\db2 备注： a.secondary-check1\secondary-check2虽是可选的，但强烈建议使用，防止因为网络原因误切换； b.官方推荐数据库3节点，但我人为业务量不是超大的情况下2节
mha依赖安装包.rar
06-22
10. **维护与优化**：定期检查MHA配置，优化网络连接，监控资源使用情况，都是保证MHA高效运行的关键。 总结起来，MHA是一个强大的工具，通过自动化故障检测和处理，提高了MySQL数据库的高可用性。正确安装和配置...
mha-0.57.zip
05-20
在 RHEL7 系统上，可以使用 rpm 包管理器（如 yum）来安装这两个软件包，然后按照 MHA 的官方文档进行配置。配置过程中，需要指定管理节点和节点之间的通信方式，设置监控参数，以及定义故障切换策略。 **总结：** ...
MHA 安装包.7z
04-27
MHA安装包，包含：mha4mysql-manager-0.56-0.el6.noarch.rpm，mha4mysql-node-0.56-0.el6.noarch.rpm，mha4mysql-manager-0.57.tar.gz
MHA0.58.zip
06-22
mha4mysql-manager-0.58.tar.gz和mha4mysql-manager-0.58-0.el7.centos.noarch.rpm文件分别提供了源码和预编译的RPM包，供用户在不同环境中安装和使用。Manager节点通过定期检查各个MySQL节点的健康状况，一旦发现主...
mha-0.58.zip
05-20
6. **RPM 安装与管理**：RPM 包使得在 CentOS 7 上安装 MHA 变得简单，只需使用 `yum` 或 `dnf` 命令即可完成安装、更新和卸载。同时，RPM 包自带依赖关系解决，确保了所有必需的库和组件都已安装。 7. **系统兼容...
MySQL MHA高可用+VIP 集群故障转移
12-20
MySQL MHA高可用+VIP 集群故障转移最佳实践
mysql 高可用架构 mha 之五 Undefined subroutine &main::FIXME_xxx called at power_manage
一名数据库爱好者的专栏
03-08 1589
os: ubuntu 16.04 db: mysql 5.7.25 mha: 0.58 做 masterha_check_repl 时,提示错误 # masterha_check_repl --conf=/etc/masterha/app1/app1.cnf Fri Mar 8 15:02:58 2019 - [info] Checking shutdown script status: Fr...
MHA-MySQL篇
Haibolun的博客
05-20 513
目录 1.部署 1.1所有节点设置软连接 1.2配置主节点与其他节点互信 1.3主节点创建 mha 专用管理用户 1.4下载mha软件：node+manager 1.5安装 1.6管理节点创建相关文件目录 1.7管理节点编辑mha配置文件 1.8管理节点互信检查与主从状态检查 1.9管理节点开启MHA 1.10查看MHA状态 2.MHA的 vip 功能 3.故障提醒 4.binlog server 5.故障处理流程 5.1监控到宕机，收到邮件 5.2选择新主，配置文件+脚本
MHA高可用配置及故障切换
稻香的博客
07-04 362
MHA :为了解决的是故障切换、数据尽可能的保存，以及所有节点日志的一致性MHA Manger管理多组主从复制。MHA工作原理总结如下:MHA manager节点服务器：192.168.119.130Master节点服务器：192.168.119.70Slave1节点服务器：192.168.119.20 Slave2节点服务器：192.168.119.30 2.5 master、slave1、slave2 节点上都创建两个软链接 2.6 登录数据库主从配置授权
数据库之MHA高可用集群部署及故障切换
kele_baba
07-04 192
1.什么是MHA MHA (MasterHigh Availability)是一套优秀的MySQL高可用环境下故障切换和主从复制的软件。MHA的出现就是解决MySQL单点的问题。 MySQL故障切换过程中，MHA能做到0-30秒内自动完成故障切换操作。 MHA能在故障切换的过程中最大程度上保证数据的一致性，以达到真正意义上的高可用。 2.MHA的组成 MHA Node（数据节点) MHA Node 运行在每台MySQL服务器上。 MHA Manager（管理节点） MHA Manager可以单独部署在
MHA初探（主从复制，GID，MHA高可用）
一只菜鸡的专栏
10-24 537
材料：一台mysql母鸡，glibc安装方式。yum源配置epel。 安装mysql请参考 https://blog.csdn.net/VegetandBird_s/article/details/102555932 1.利用母机克隆4台机器 利用母机克隆4台机器，一主二从一MHA服务器。 以本文环境为例 IP地址分别为： MHA（10.1.1.10） 主（10.1.1.13） 从...
MySQL数据库---MHA高可用群集架构
ZT云的博客
12-23 252
MHA概述 日本DeNA公司youshimaton (现就职于Facebook公司) 开发 一套优秀的作为MySQL高可用性环境下故障切换和主从提升的高可用软件 MHA的组成 MHA Manager (管理节点) MHA Node (数据节点) MHA特点 自动故障切换过程中，MHA试图从宕机的主服务器上保存二进制日志，最大程度的保证数据的不丢失 使用MySQL 5.5的半同步复制，可以大大降...
arping
hqmln的博客
07-28 539
arping 命令：说明：arping是用于发送arp请求到一个相邻主机的工具；arping使用arp数据包，通过ping命令检查设备上的硬件地址。语法：arping [-fqbDUAV] [-c count] [-w timeout] [-I device] [-s source] destination参数：-c count表示发送指定数量的 ARP 请求 数据包后就停止。如果制定了dea...
centos7.4搭建MySQL-MHA集群
布偶猫爸爸的博客
02-20 1328
本次测验实现的是centos7.4+MySQL8.0-mha，为啥是8.0，其实和5.7搭建没啥区别，之前用5.7也实现了，后来DBA老大哥说，用8.0来试试吧，8.0有聚合函数，所以我又重新搭成了8.0版本的mha，本身配置并无区别； 由于个人测试机有限，本次实现是在docker容器里搭建的，虽说是容器，但从centos7.4基础镜像开始搭建服务，并指定独立IP和端口，实现配置和物理机并无差异...
07_Mysql 5.7.22_mha_ver1.0.docx
09-20
本文主要描述了在Centos 7.4系统上通过源码安装Mysql 5.7.22，搭建一主多从的高可用集群，并使用MHA（0.57）进行配置。下面对该过程进行了详细总结。 首先，在Mysql 5.7.22 MHA高可用集群架构中，通过简易架构图...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值