MHA的安装部署

66次阅读

共计 33759 个字符，预计需要花费 85 分钟才能阅读完成。

本篇内容主要讲解“MHA 的安装部署”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让丸趣 TV 小编来带大家学习“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 数据库
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.