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) , 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 tribute 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.