MHA的安裝部署

本篇內容主要講解“MHA的安裝部署”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓小編來帶大家學習“MHA的安裝部署”吧!

MHA 0.56 is now available +2 Vote Up -0Vote Down
posted by Yoshinori Matsunobu on Tue 01 Apr 2014 04:50 UTC 
Tags: (edit) mysql, MHA
I released MHA version 0.56 today. Downloads are available here. MHA 0.56 includes below features.

Supporting MySQL 5.6 GTID. If GTID and auto position is enabled, MHA automatically does failover with GTID SQL syntax, not using traditional relay log based failover. You don't need any explicit configuration within MHA to use GTID based failover.
Supporting MySQL 5.6 Multi-Threaded slave
Supporting MySQL 5.6 binlog checksum
MHA …

一、.環境準備
1、修改每臺主機名
192.168.2.52    virtdb52.gewara.cn   #manager  
192.168.2.54    virtdb54.gewara.cn      #node master
192.168.2.55    virtdb55.gewara.cn      #node slave1  
192.168.2.56    virtdb56.gewara.cn      #node slave2

2.配置root信任：
#主機:master執行命令
ssh-keygen -t rsa
ssh-copy-id -i ~/.ssh/id_rsa.pub root@manager
ssh-copy-id -i ~/.ssh/id_rsa.pub root@slave01
ssh-copy-id -i ~/.ssh/id_rsa.pub root@slave02
#主機:slave01執行命令
ssh-keygen -t rsa
ssh-copy-id -i ~/.ssh/id_rsa.pub root@manager
ssh-copy-id -i ~/.ssh/id_rsa.pub root@master
ssh-copy-id -i ~/.ssh/id_rsa.pub root@slave02


3.配置主從
創建復制賬號
grant replication slave on *.* to repl@'%' identified by '123456';
flush privileges;
stop slave;

配置復制
change master to MASTER_HOST='192.168.2.54', MASTER_PORT=3306,MASTER_USER='repl', MASTER_PASSWORD='123456',master_log_file='mysql-bin.000005', master_log_pos=120;
start slave;
show slave status\G;

創建mha監控賬戶
grant all on *.* to mha@'192.168.%' identified by '123456';
flush privileges;

egrep "log-bin|server_id" /opt/mysql3306/etc/my.cnf 

二.安裝部署MHA

2.1安裝MHA node(在所有Mysql服務器上安裝)

1）安裝依賴包
rpm -Uvh http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
rpm --import /etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-6
yum -y install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Config-IniFiles perl-Time-HiRes  perl-Time-HiRes  perl-CPAN

2）在所有的節點上安裝mha node:
下載：https://downloads.mariadb.com/files/MHA
wget https://downloads.mariadb.com/files/MHA/mha4mysql-node-0.56.tar.gz

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


Installing /usr/local/bin/apply_diff_relay_logs
Installing /usr/local/bin/save_binary_logs
Installing /usr/local/bin/purge_relay_logs
Installing /usr/local/bin/filter_mysqlbinlog

3)在manager上安裝mha4mysql-manager和mha4mysql-node包
wget https://downloads.mariadb.com/files/MHA/mha4mysql-manager-0.56.tar.gz


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

Installing /usr/local/bin/masterha_stop
Installing /usr/local/bin/masterha_master_monitor
Installing /usr/local/bin/masterha_check_status
Installing /usr/local/bin/masterha_conf_host
Installing /usr/local/bin/masterha_secondary_check
Installing /usr/local/bin/masterha_master_switch
Installing /usr/local/bin/masterha_manager
Installing /usr/local/bin/masterha_check_repl
Installing /usr/local/bin/masterha_check_ssh

mkdir -p /usr/local/mha/scripts

cp samples/scripts/* /usr/local/mha/scripts/

[root@virtdb52 mha]# vi /usr/local/mha/mha_app1.cnf
[server default]
manager_workdir=/usr/local/mha
manager_log=/usr/local/mha/manager.log
ssh_user=root
repl_user=repl
repl_password=123456
ping_interval=1


[server1]
hostname=virtdb54.gewara.cn
ssh_port=22
master_binlog_dir=/opt/mysql3306/data/
candidate_master=1


[server2]
hostname=virtdb55.gewara.cn
ssh_port=22
master_binlog_dir=/opt/mysql3306/data/
candidate_master=1


[server3]
hostname=virtdb56.gewara.cn
ssh_port=22
master_binlog_dir=/opt/mysql3306/data/
candidate_master=1

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


2.2驗證ssh通訊
masterha_check_ssh --conf=/usr/local/mha/mha_app1.cnf 


2.3驗證mysql主從復制
masterha_check_repl --conf=/usr/local/mha/mha_app1.cnf 

Can't exec "mysqlbinlog": No such file or directory at /usr/local/share/perl5/MHA/BinlogManager.pm line 106.
mysqlbinlog version command failed with rc 1:0, please verify PATH, LD_LIBRARY_PATH, and client options

解決：
which mysqlbinlog
type mysqlbinlog
ln -s /opt/mysql3306/bin/mysqlbinlog /usr/bin/mysqlbinlog

mysqlbinlog: unknown variable 'default-character-set=utf8'
解決：
vi my.cnf
#default-character-set=utf8

Testing mysql connection and privileges..sh: mysql: command not found
解決：   
ln -s /opt/mysql3306/bin/mysql /usr/bin/mysql

2.4.檢查啟動的狀態
masterha_check_status --conf=/usr/local/mha/mha_app1.cnf 

2.4啟動mha
1)在每次做mha實驗的時候，我們都最好先執行如下命令做檢測
masterha_check_ssh --conf=/usr/local/mha/mha_app1.cnf 
masterha_check_repl --conf=/usr/local/mha/mha_app1.cnf 


2)在manager端啟動mha服務并時刻監控日志文件的輸出變化
nohup masterha_manager --conf=/usr/local/mha/mha_app1.cnf  > /tmp/mha_manager.log 2>&1 &
ps -ef |grep masterha |grep -v 'grep'

2.5.停止mha
masterha_stop masterha_check_status --conf=/usr/local/mha/mha_app1.cnf

2.5測試master宕機后，時候會自動切換
#查看slave01,slave02的主從同步情況
#slave01
測試前查看slave01,slave02的主從同步情況
mysql -umha -p123456 -h292.168.2.55  -e 'show slave status\G' |egrep 'Slave_IO_Running:|Slave_SQL_Running|Master_Host'
mysql -umha -p123456 -h292.168.2.56  -e 'show slave status\G' |egrep 'Slave_IO_Running:|Slave_SQL_Running|Master_Host'
mysql -umha -p123456 -h292.168.2.54  -e 'show slave status\G' |egrep 'Slave_IO_Running:|Slave_SQL_Running|Master_Host'

#停止master的mysql服務
service mysqld stop
檢查從庫的配置
mysql -umha -p123456 -h292.168.2.55  -e 'show slave status\G' |egrep 'Slave_IO_Running:|Slave_SQL_Running|Master_Host'
mysql -umha -p123456 -h292.168.2.56  -e 'show slave status\G' |egrep 'Slave_IO_Running:|Slave_SQL_Running|Master_Host'

#關閉master數據庫
service mysqld stop
隨著master的關閉，slave2從庫會從新指向新的master
原先的slave1變成master后，slave配置信息會reset slave;
MHA服務會關閉，但VIP還是會自動切到新master上，需要重新啟動MHA
發生主從切換后，MHAmanager服務會自動停掉，且在manager_workdir目錄下面生成文件app1.failover.complete，若要啟動MHA，必須先確保無此文件)
當有slave 節點宕掉時，默認是啟動不了的，加上 --ignore_fail_on_start 即使有節點宕掉也能啟動MHA，如下：
# nohup masterha_manager --conf=/etc/masterha/app1/app1.cnf --ignore_fail_on_start >/etc/masterha/app1/mha_manager.log 2>&1 &

定期刪除中繼日志

由于在第一步中，每個slave上設置了參數relay_log_purge=0，所以slave節點需要定期刪除中繼日志，建議每個slave節點刪除中繼日志的時間錯開。
corntab -e
0 5 * * *  /usr/bin/purge_relay_logs --user=root--password=123456 --port=3306 --disable_relay_log_purge >> /var/lib/mysql/purge_relay.log  2>&1

2.6 恢復原master服務
#刪除故障轉移文件
[root@manager mha]# rm -rf /usr/local/mha/mha_app1.failover.complete

-rw-r--r-- 1 root root     0 May 17 16:09 mha_app1.failover.complete
-rw-r--r-- 1 root root   143 May 17 16:09 saved_master_binlog_from_virtdb54.gewara.cn_3306_20160517160908.binlog 

#重啟原master的mysql服務
service mysqld start

#在manager的日子文件中找到主從同步的sql語句
grep MASTER_HOST /usr/local/mha/manager.log

Tue May 17 16:09:11 2016 - [info]  All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='virtdb55.gewara.cn or 192.168.2.55', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=120, MASTER_USER='repl', MASTER_PASSWORD='xxx';

重新配置從庫：
CHANGE MASTER TO MASTER_HOST='192.168.2.55', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=120, MASTER_USER='repl', MASTER_PASSWORD='123456';
start slave;

五、通過vip實現mysql的高可用
1、修改/usr/local/mha/mha_app1.cnf 
vi /usr/local/mha/mha_app1.cnf 
master_ip_failover_script=/usr/local/mha/scripts/master_ip_failover    #添加管理vip的腳本


2、修改腳本/usr/local/mha/scripts/master_ip_failover


[root@virtdb52 scripts]# more 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 = '192.168.2.220'; # Virtual IP
my $gateway = '192.168.2.11'; #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,
'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";
}

chmod 755 master_ip_failover

測試網卡綁定
/sbin/ifconfig eth0:1 192.168.2.220/24
/sbin/ifconfig eth0:1 down


恢復操作
1.db1啟動mysql
 service mysqld start
 
--切換后：重新加入該節點
grep MASTER_HOST /usr/local/mha/manager.log
CHANGE MASTER TO MASTER_HOST='192.168.2.54', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=120, MASTER_USER='repl', MASTER_PASSWORD='123456';


start slave;
show slave status\G;

4，啟動manager的管理
1)在每次做mha實驗的時候，我們都最好先執行如下命令做檢測
masterha_check_ssh --conf=/usr/local/mha/mha_app1.cnf 
masterha_check_repl --conf=/usr/local/mha/mha_app1.cnf 


2)在manager端啟動mha服務并時刻監控日志文件的輸出變化
nohup masterha_manager --conf=/usr/local/mha/mha_app1.cnf  > /tmp/mha_manager.log 2>&1 &
ps -ef |grep masterha |grep -v 'grep'

Scheduled(Online) Master Switch(手動在線主庫切換)
應用場景1：master和slave正常，MHA正常開啟，維護操作時(例如更換新主機硬件、添加/刪除列或主鍵)手動在線切換master到其他主機。
1. 如果MHA在運行，需先停止MHA
masterha_stop --conf=/usr/local/mha/mha_app1.cnf 
2. 檢查MHA當前置
masterha_check_repl --conf=/usr/local/mha/mha_app1.cnf 
3. 手動切換
masterha_master_switch --master_state=alive --conf=/usr/local/mha/mha_app1.cnf  --orig_master_is_new_slave --running_updates_limit=3600 --interactive=0
注意：執行masterha_master_switch調用的不是master_ip_failover_script腳本，而是master_ip_online_change_script腳本，可把啟動和停止VIP放到這個腳本中，如果沒有配置VIP，則需要手動執行VIP切換，如下：
ssh  root@$orig_master_ip   /sbin/ifconfig  eth0:1 down
ssh  root@$new_master_ip   /sbin/ifconfig  eth0:1 10.1.5.21/24

附腳本：
[root@virtdb52 mha]# more mha_app1.cnf
[server default]
user=mha
password=123456
manager_workdir=/usr/local/mha
manager_log=/usr/local/mha/manager.log
master_ip_failover_script=/usr/local/mha/scripts/master_ip_failover 
master_ip_online_change_script=/usr/local/mha/scripts/master_ip_online_change
report_script=/usr/local/mha/scripts/send_report
ssh_user=root
repl_user=repl
repl_password=123456
ping_interval=1


[server1]
hostname=virtdb54.gewara.cn
ssh_port=22
master_binlog_dir=/opt/mysql3306/data/
candidate_master=1


[server2]
hostname=virtdb55.gewara.cn
ssh_port=22
master_binlog_dir=/opt/mysql3306/data/
candidate_master=1


[server3]
hostname=virtdb56.gewara.cn
ssh_port=22
master_binlog_dir=/opt/mysql3306/data/
candidate_master=1


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

[root@virtdb52 scripts]# more 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 = '192.168.2.220'; # Virtual IP
my $gateway = '192.168.2.11'; #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,
'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";
}

[root@virtdb52 scripts]# more 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_host, $orig_master_ip,
  $orig_master_port, $orig_master_user, 
  $new_master_host,  $new_master_ip,    $new_master_port,
  $new_master_user,  
);
 
 
my $vip = '192.168.2.220/24';  # Virtual IP 
my $key = "1"; 
my $ssh_start_vip = "/sbin/ifconfig eth0:$key $vip";
my $ssh_stop_vip = "/sbin/ifconfig eth0:$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" );
      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} );
      }
 
 
 
                print "Disabling the VIP on old master: $orig_master_host \n";
                &stop_vip();     
 
 
      ## 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";
      #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 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_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=p
ort\n";
  die;
}

vi send_report 

#!/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';
my $mail_user='xxxxx';
my $mail_pass='xxxxx';
my $mail_to=['xxxx','xxxx'];
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;
-----------------
mysql 5.6 GTID 
percona server 5.6.25

master邊設置：
server_id=1
log_bin=mysql-bin
binlog_format=row
gtid_mode=on
enforce_gtid_consistency
log_slave_updates


slave設置：
server_id=2
log_bin=mysql-bin
binlog_format=row
skip_slave_start
gtid_mode=on
enforce_gtid_consistency
log_slave_updates


 CHANGE MASTER TO
 MASTER_HOST='192.168.2.54',
 MASTER_PORT=3306,
 MASTER_USER='repl',
 MASTER_PASSWORD='123456',
 MASTER_AUTO_POSITION=1;
 
MHA GTID
改成MHA GITD后，在切換后：
grep -i "CHANGE" manager.log |tail


CHANGE MASTER TO MASTER_HOST='virtdb55.gewara.cn or 192.168.2.55', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='xxx';

MHA GTID切換日志

發現master無法訪問
Thu May 19 10:04:16 2016 - [warning] Got error on MySQL select ping: 2006 (MySQL server has gone away)
Thu May 19 10:04:16 2016 - [info] Executing SSH check script: exit 0
Thu May 19 10:04:16 2016 - [info] HealthCheck: SSH to virtdb54.gewara.cn is reachable.
Thu May 19 10:04:17 2016 - [warning] Got error on MySQL connect: 2013 (Lost connection to MySQL server at 'reading initial communication packet', system error: 111)
Thu May 19 10:04:17 2016 - [warning] Connection failed 1 time(s)..
Thu May 19 10:04:18 2016 - [warning] Got error on MySQL connect: 2013 (Lost connection to MySQL server at 'reading initial communication packet', system error: 111)
Thu May 19 10:04:18 2016 - [warning] Connection failed 2 time(s)..
Thu May 19 10:04:19 2016 - [warning] Got error on MySQL connect: 2013 (Lost connection to MySQL server at 'reading initial communication packet', system error: 111)
Thu May 19 10:04:19 2016 - [warning] Connection failed 3 time(s)..
Thu May 19 10:04:19 2016 - [warning] Master is not reachable from health checker!
Thu May 19 10:04:19 2016 - [warning] Master virtdb54.gewara.cn(192.168.2.54:3306) is not reachable!
Thu May 19 10:04:19 2016 - [warning] SSH is reachable.
Thu May 19 10:04:19 2016 - [info] Connecting to a master server failed. Reading configuration file /etc/masterha_default.cnf and /usr/local/mha/mha_app1.cnf again, and trying to connect to all servers to check server status..

通過配置文件檢查所有master-slave server狀態
Thu May 19 10:04:19 2016 - [warning] SQL Thread is stopped(no error) on virtdb56.gewara.cn(192.168.2.56:3306)
Thu May 19 10:04:19 2016 - [info] Dead Servers:
Thu May 19 10:04:19 2016 - [info]   virtdb54.gewara.cn(192.168.2.54:3306)
Thu May 19 10:04:19 2016 - [info] Alive Servers:
Thu May 19 10:04:19 2016 - [info]   virtdb55.gewara.cn(192.168.2.55:3306)
Thu May 19 10:04:19 2016 - [info]   virtdb56.gewara.cn(192.168.2.56:3306)
Thu May 19 10:04:19 2016 - [info] Alive Slaves:
Thu May 19 10:04:19 2016 - [info]   virtdb55.gewara.cn(192.168.2.55:3306)  Version=5.6.25-73.1-log (oldest major version between slaves) log-bin:enabled
Thu May 19 10:04:19 2016 - [info]     GTID ON
Thu May 19 10:04:19 2016 - [info]     Replicating from 192.168.2.54(192.168.2.54:3306)
Thu May 19 10:04:19 2016 - [info]     Primary candidate for the new Master (candidate_master is set)
Thu May 19 10:04:19 2016 - [info]   virtdb56.gewara.cn(192.168.2.56:3306)  Version=5.6.25-73.1-log (oldest major version between slaves) log-bin:enabled
Thu May 19 10:04:19 2016 - [info]     GTID ON
Thu May 19 10:04:19 2016 - [info]     Replicating from 192.168.2.54(192.168.2.54:3306)
Thu May 19 10:04:19 2016 - [info]     Primary candidate for the new Master (candidate_master is set)

確定master down啟動切換動作
Thu May 19 10:04:19 2016 - [info] Master is down!
Thu May 19 10:04:19 2016 - [info] Terminating monitoring script.
Thu May 19 10:04:19 2016 - [info] Got exit code 20 (Master dead).
Thu May 19 10:04:19 2016 - [info] MHA::MasterFailover version 0.56.
Thu May 19 10:04:19 2016 - [info] Starting master failover.

第1階段：檢查master -salve server配置角色、線程狀態
Thu May 19 10:04:19 2016 - [info] * Phase 1: Configuration Check Phase..
Thu May 19 10:04:19 2016 - [info] 
Thu May 19 10:04:19 2016 - [warning] SQL Thread is stopped(no error) on virtdb56.gewara.cn(192.168.2.56:3306)
Thu May 19 10:04:19 2016 - [info] Dead Servers:
Thu May 19 10:04:19 2016 - [info]   virtdb54.gewara.cn(192.168.2.54:3306)
Thu May 19 10:04:19 2016 - [info] Checking master reachability via mysql(double check)..
Thu May 19 10:04:19 2016 - [info]  ok.
Thu May 19 10:04:19 2016 - [info] Alive Servers:
Thu May 19 10:04:19 2016 - [info]   virtdb55.gewara.cn(192.168.2.55:3306)
Thu May 19 10:04:19 2016 - [info]   virtdb56.gewara.cn(192.168.2.56:3306)
Thu May 19 10:04:19 2016 - [info] Alive Slaves:
Thu May 19 10:04:19 2016 - [info]   virtdb55.gewara.cn(192.168.2.55:3306)  Version=5.6.25-73.1-log (oldest major version between slaves) log-bin:enabled
Thu May 19 10:04:19 2016 - [info]     GTID ON
Thu May 19 10:04:19 2016 - [info]     Replicating from 192.168.2.54(192.168.2.54:3306)
Thu May 19 10:04:19 2016 - [info]     Primary candidate for the new Master (candidate_master is set)
Thu May 19 10:04:19 2016 - [info]   virtdb56.gewara.cn(192.168.2.56:3306)  Version=5.6.25-73.1-log (oldest major version between slaves) log-bin:enabled
Thu May 19 10:04:19 2016 - [info]     GTID ON
Thu May 19 10:04:19 2016 - [info]     Replicating from 192.168.2.54(192.168.2.54:3306)
Thu May 19 10:04:19 2016 - [info]     Primary candidate for the new Master (candidate_master is set)
Thu May 19 10:04:19 2016 - [info]  Starting SQL thread on virtdb56.gewara.cn(192.168.2.56:3306) ..
Thu May 19 10:04:19 2016 - [info]   done.
Thu May 19 10:04:19 2016 - [info] ** Phase 1: Configuration Check Phase completed.

第2階段：將master shutdown,VIP關閉，使其無法訪問master
Thu May 19 10:04:19 2016 - [info] * Phase 2: Dead Master Shutdown Phase..
Thu May 19 10:04:19 2016 - [info] 
Thu May 19 10:04:19 2016 - [info] Forcing shutdown so that applications never connect to the current master..
Thu May 19 10:04:19 2016 - [info] Executing master IP deactivatation script:
Thu May 19 10:04:19 2016 - [info]   /usr/local/mha/scripts/master_ip_failover --orig_master_host=virtdb54.gewara.cn --orig_master_ip=192.168.2.54 --orig_master_port=3306 --command=stopssh --ssh_user=root  


IN SCRIPT TEST====/sbin/ifconfig eth0:1 down==/sbin/ifconfig eth0:1 192.168.2.220;/sbin/arping -I eth0 -c 3 -s 192.168.2.220 192.168.2.11 >/dev/null 2>&1===


Disabling the VIP on old master: virtdb54.gewara.cn 
Thu May 19 10:04:19 2016 - [info]  done.
Thu May 19 10:04:19 2016 - [warning] shutdown_script is not set. Skipping explicit shutting down of the dead master.
Thu May 19 10:04:19 2016 - [info] * Phase 2: Dead Master Shutdown Phase completed.

第3階段：發現得到GITD EVENT的Slave，并確定該slave為master
Thu May 19 10:04:19 2016 - [info] * Phase 3.1: Getting Latest Slaves Phase..
Thu May 19 10:04:19 2016 - [info] 
Thu May 19 10:04:19 2016 - [info] The latest binary log file/position on all slaves is mysql-bin.000010:3006905
Thu May 19 10:04:19 2016 - [info] Retrieved Gtid Set: 8b8cad8e-053c-11e6-b500-5254006f0b84:2-11304
Thu May 19 10:04:19 2016 - [info] Latest slaves (Slaves that received relay log files to the latest):
Thu May 19 10:04:19 2016 - [info]   virtdb55.gewara.cn(192.168.2.55:3306)  Version=5.6.25-73.1-log (oldest major version between slaves) log-bin:enabled
Thu May 19 10:04:19 2016 - [info]     GTID ON
Thu May 19 10:04:19 2016 - [info]     Replicating from 192.168.2.54(192.168.2.54:3306)
Thu May 19 10:04:19 2016 - [info]     Primary candidate for the new Master (candidate_master is set)
Thu May 19 10:04:19 2016 - [info] The oldest binary log file/position on all slaves is mysql-bin.000010:1616340
Thu May 19 10:04:19 2016 - [info] Retrieved Gtid Set: 8b8cad8e-053c-11e6-b500-5254006f0b84:5-6082
Thu May 19 10:04:19 2016 - [info] Oldest slaves:
Thu May 19 10:04:19 2016 - [info]   virtdb56.gewara.cn(192.168.2.56:3306)  Version=5.6.25-73.1-log (oldest major version between slaves) log-bin:enabled
Thu May 19 10:04:19 2016 - [info]     GTID ON
Thu May 19 10:04:19 2016 - [info]     Replicating from 192.168.2.54(192.168.2.54:3306)
Thu May 19 10:04:19 2016 - [info]     Primary candidate for the new Master (candidate_master is set)
Thu May 19 10:04:19 2016 - [info] 
Thu May 19 10:04:19 2016 - [info] * Phase 3.3: Determining New Master Phase..
Thu May 19 10:04:19 2016 - [info] 
Thu May 19 10:04:19 2016 - [info] Searching new master from slaves..
Thu May 19 10:04:19 2016 - [info]  Candidate masters from the configuration file:
Thu May 19 10:04:19 2016 - [info]   virtdb55.gewara.cn(192.168.2.55:3306)  Version=5.6.25-73.1-log (oldest major version between slaves) log-bin:enabled
Thu May 19 10:04:19 2016 - [info]     GTID ON
Thu May 19 10:04:19 2016 - [info]     Replicating from 192.168.2.54(192.168.2.54:3306)
Thu May 19 10:04:19 2016 - [info]     Primary candidate for the new Master (candidate_master is set)
Thu May 19 10:04:19 2016 - [info]   virtdb56.gewara.cn(192.168.2.56:3306)  Version=5.6.25-73.1-log (oldest major version between slaves) log-bin:enabled
Thu May 19 10:04:19 2016 - [info]     GTID ON
Thu May 19 10:04:19 2016 - [info]     Replicating from 192.168.2.54(192.168.2.54:3306)
Thu May 19 10:04:19 2016 - [info]     Primary candidate for the new Master (candidate_master is set)
Thu May 19 10:04:19 2016 - [info]  Non-candidate masters:
Thu May 19 10:04:19 2016 - [info]  Searching from candidate_master slaves which have received the latest relay log events..
Thu May 19 10:04:19 2016 - [info] New master is virtdb55.gewara.cn(192.168.2.55:3306)
Thu May 19 10:04:19 2016 - [info] Starting master failover..

準備master的slave要進行一次應用并切換
To:
virtdb55.gewara.cn (new master)
 +--virtdb56.gewara.cn
Thu May 19 10:04:19 2016 - [info] 
Thu May 19 10:04:19 2016 - [info] * Phase 3.3: New Master Recovery Phase..
Thu May 19 10:04:19 2016 - [info] 
Thu May 19 10:04:19 2016 - [info]  Waiting all logs to be applied.. 
Thu May 19 10:04:19 2016 - [info]   done.
Thu May 19 10:04:19 2016 - [info] Getting new master's binlog name and position..
Thu May 19 10:04:19 2016 - [info]  mysql-bin.000009:2815604
Thu May 19 10:04:19 2016 - [info]  All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='virtdb55.gewara.cn or 192.168.2.55', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='xxx';
Thu May 19 10:04:19 2016 - [info] Master Recovery succeeded. File:Pos:Exec_Gtid_Set: mysql-bin.000009, 2815604, 8b3861e6-053c-11e6-b500-525400691d52:1-2,
8b8cad8e-053c-11e6-b500-5254006f0b84:1-11304
Thu May 19 10:04:19 2016 - [info] Executing master IP activate script:
Thu May 19 10:04:19 2016 - [info]   /usr/local/mha/scripts/master_ip_failover --command=start --ssh_user=root --orig_master_host=virtdb54.gewara.cn --orig_master_ip=192.168.2.54 --orig_master_port=3306 --new_master_host=virtdb55.gewara.cn --new_master_ip=192.168.2.55 --new_master_port=3306 --new_master_user='mha' --new_master_password='123456'  
Unknown option: new_master_user
Unknown option: new_master_password

IN SCRIPT TEST====/sbin/ifconfig eth0:1 down==/sbin/ifconfig eth0:1 192.168.2.220;/sbin/arping -I eth0 -c 3 -s 192.168.2.220 192.168.2.11 >/dev/null 2>&1===


Enabling the VIP - 192.168.2.220 on the new master - virtdb55.gewara.cn 
Thu May 19 10:04:23 2016 - [info]  OK.
Thu May 19 10:04:23 2016 - [info] Setting read_only=0 on virtdb55.gewara.cn(192.168.2.55:3306)..
Thu May 19 10:04:23 2016 - [info]  ok.
Thu May 19 10:04:23 2016 - [info] ** Finished master recovery successfully.
Thu May 19 10:04:23 2016 - [info] * Phase 3: Master Recovery Phase completed.
Thu May 19 10:04:23 2016 - [info] 
Thu May 19 10:04:23 2016 - [info] * Phase 4: Slaves Recovery Phase..
Thu May 19 10:04:23 2016 - [info] 
Thu May 19 10:04:23 2016 - [info] 
Thu May 19 10:04:23 2016 - [info] * Phase 4.1: Starting Slaves in parallel..
Thu May 19 10:04:23 2016 - [info] 
Thu May 19 10:04:23 2016 - [info] -- Slave recovery on host virtdb56.gewara.cn(192.168.2.56:3306) started, pid: 29244. Check tmp log /usr/local/mha/virtdb56.gewara.cn_3306_20160519100419.log if it takes time..
Thu May 19 10:04:24 2016 - [info] 
Thu May 19 10:04:24 2016 - [info] Log messages from virtdb56.gewara.cn ...
Thu May 19 10:04:24 2016 - [info] 
Thu May 19 10:04:23 2016 - [info]  Resetting slave virtdb56.gewara.cn(192.168.2.56:3306) and starting replication from the new master virtdb55.gewara.cn(192.168.2.55:3306)..
Thu May 19 10:04:24 2016 - [info]  Executed CHANGE MASTER.
Thu May 19 10:04:24 2016 - [info]  Slave started.
Thu May 19 10:04:24 2016 - [info] End of log messages from virtdb56.gewara.cn.
Thu May 19 10:04:24 2016 - [info] -- Slave on host virtdb56.gewara.cn(192.168.2.56:3306) started.
Thu May 19 10:04:24 2016 - [info] All new slave servers recovered successfully.

到此，相信大家對“MHA的安裝部署”有了更深的了解，不妨來實際操作一番吧！這里是億速云網站，更多相關內容可以進入相關頻道進行查詢，關注我們，繼續學習！