MySQL-MHA 搭建文档

最新推荐文章于 2022-07-02 12:33:03 发布

原创最新推荐文章于 2022-07-02 12:33:03 发布 · 880 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库

MySQL 专栏收录该内容

10 篇文章

订阅专栏

MHA（Master High Availability）是一种用于MySQL的高可用性解决方案，能够在30秒内完成主服务器故障转移。本文详细介绍了MHA的搭建过程，包括环境配置、主从搭建、MHA安装、配置以及常用操作，如SSH检查、复制状态检查和主库在线切换。此外，还探讨了自动和手动Failover的场景，以及如何利用MHA日志修复宕机的主库。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

MHA软件介绍

MHA是由日本Mysql专家用Perl写的一套Mysql故障切换方案以保障数据库的高可用性，它的功能是能在0一30s之内实现主Mysql故障转移（failover)，凡MHA故障转移叮以很好的帮我们解决从库数据的一致性问题．同时最大化挽回故障发生后的数据．MHA里有两个角色一个是node节点一个是manager节点，要实现这个MHA，必须最少要二台数据库服务器，一主多备，即一台充当master，一台充当master的备份机，另外一台是从属机，这里实验为了实现更好的效果使用三台机器，需要说明的是一旦主服务器宕机．备份机即开始充当master提供服务，如果上服务器上线也不会再成为master了．因为如果这样数据库的一致性就被改变了。
MHA监控复制架构的主服务器，一旦检侧到主服务器故障．就会自动进行故障转移。即使有些从服务器没有收到最新的relay log自动从最新的从服务器上识别差异的relay log并把这些日志应用到其他从服务器上，因此所有的从服务器保持一致性了 , 期间通常在几秒内完成故障转移，9一12秒可以检测出主服务器故障，7一10秒内关闭故障的主服务器以避免脑裂，几秒中内应用差异的relay10到新的主上，整个过程在0-30s 内完成。

搭建环境

mha-master：4核 2G内存 Redhat 6.5 IP:192.168.86.148 主机名:mha-master 安装MySQL 5.6.36

mha-manager：4核 2G内存 Redhat 6.5 IP:192.168.86.149 主机名:mha-manager 安装MySQL 5.6.36

mha-bak：4核 2G内存 Redhat 6.5 IP:192.168.86.150 主机名:mha-bak 安装MySQL 5.6.36

搭建步骤

一、搭建主从（一主三从）

主库：mha-master

从库：mha-manager、mha-bak

注意：要在mha-master 端新建复制用户并授予复制权限

mysql> grant replication slave,replication client on *.* to 'repl'@'192.168.86.%' identified by '123456';

二、安装MHA及基本环境配置

三台机器都需执行1-2步骤

1、在所有的节点上安装MHA node所需的perl模块（DBD-mysql）以及MHA node节点

$ yum install perl-DBD-MySQL -y

2、在所有的节点上安装MHA的node数据节点

$ rpm -ivh mha4mysql-node-0.54-0.el6.noarch.rpm
Preparing...                ########################################### [100%]
   1:mha4mysql-node         ########################################### [100%]

安装完成后，看/usr/bin目录下是否生成以下脚本文件：

$ ll /usr/bin/ | grep logs
-rwxr-xr-x. 1 root root       15977 Dec  1  2012 apply_diff_relay_logs
-rwxr-xr-x. 1 root root        4807 Dec  1  2012 filter_mysqlbinlog
-rwxr-xr-x. 1 root root        7401 Dec  1  2012 purge_relay_logs
-rwxr-xr-x. 1 root root        7263 Dec  1  2012 save_binary_logs

3、在manager节点（mha-manager）上安装MHA manager以及依赖包

$ ls
perl-Config-Tiny-2.12-7.1.el6.noarch.rpm
perl-Email-Date-Format-1.002-5.el6.noarch.rpm
perl-Log-Dispatch-2.27-1.el6.noarch.rpm
perl-Mail-Sender-0.8.16-3.el6.noarch.rpm
perl-Mail-Sendmail-0.79-12.el6.noarch.rpm
perl-MailTools-2.04-4.el6.noarch.rpm
perl-MIME-Lite-3.027-2.el6.noarch.rpm
perl-MIME-Lite-HTML-1.23-2.el6.noarch.rpm
perl-MIME-Types-1.28-2.el6.noarch.rpm
perl-Parallel-ForkManager-0.7.9-1.el6.noarch.rpm
perl-Params-Validate-0.92-3.el6.x86_64.rpm
perl-TimeDate-1.16-13.el6.noarch.rpm

$ yum localinstall perl-* -y

$ rpm -ivh mha4mysql-manager-0.55-0.el6.noarch.rpm 
Preparing...                ########################################### [100%]
   1:mha4mysql-manager      ########################################### [100%]

安装成功后，生成以下脚本文件：

$ ls /usr/bin/ | grep "masterha_*"
masterha_check_repl
masterha_check_ssh
masterha_check_status
masterha_conf_host
masterha_manager
masterha_master_monitor
masterha_master_switch
masterha_secondary_check
masterha_stop

4、配置主机名字（三台机器都要配置）

$ vim /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.86.148 mha-master
192.168.86.149 mha-manager
192.168.86.150 mha-bak

5、配置SSH登录无密码验证

方法一：

在mha-master上：

①生成rsa秘钥

$ ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa): 
Created directory '/root/.ssh'.
Enter passphrase (empty for no passphrase): 
Enter same passphrase again: 
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
5a:26:2d:d2:63:48:52:d1:0f:54:b0:fc:8c:6e:93:9e root@mha-master
The key's randomart image is:
+--[ RSA 2048]----+
|    o+oo.        |
|   . .o.         |
|  . . oo         |
|   o o =.        |
|    o B S        |
|     + O         |
|      *          |
|     o o         |
|      E          |
+-----------------+

②将生成的公钥追加到认证文件中

$ cd /root/.ssh/
$ cat id_rsa.pub >> authorized_keys

③修改ssh文件夹和公钥的权限

chmod 700 /root/.ssh/

chmod 600 /root/.ssh/authorized_keys

④将公钥传输到mha-manager和mha-bak上

$ ssh-copy-id -i id_rsa.pub root@192.168.86.149
The authenticity of host '192.168.86.149 (192.168.84.149)' can't be established.
RSA key fingerprint is 22:00:37:a2:be:c4:98:12:72:57:d4:74:bb:07:02:50.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.86.149' (RSA) to the list of known hosts.
root@192.168.86.149's password: 
Now try logging into the machine, with "ssh 'root@192.168.86.149'", and check in:
  .ssh/authorized_keys
to make sure we haven't added extra keys that you weren't expecting.

 ssh-copy-id -i id_rsa.pub root@192.168.86.150
The authenticity of host '192.168.86.150 (192.168.86.150)' can't be established.
RSA key fingerprint is 22:00:37:a2:be:c4:98:12:72:57:d4:74:bb:07:02:50.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.86.150' (RSA) to the list of known hosts.
root@192.168.86.150's password: 
Now try logging into the machine, with "ssh 'root@192.168.86.150'", and check in:
  .ssh/authorized_keys
to make sure we haven't added extra keys that you weren't expecting.

在mha-manager和mha-bak上重复①-④步，至三台主机的authorized_keys文件中都包含了三台主机

方法二：

在mha-master上执行以下脚本，并按提示输入密码

#!/bin/bash
node=( 192.168.86.148 192.168.86.149 192.168.86.150)  # hostname for each node
username=root            # username to be interconnected
homename=$username        # home dir, i.e. home/zhangyang
if [ "$username" = "root" ];
then
        homename=root
else
        homename=home/$username
fi
for((i=0; i<${#node[*]}; i++))
do
        ssh $username@${node[i]} 'ssh-keygen -t rsa; chmod 755 ~/.ssh'
done
#cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys
echo "batch authorized_keys created..."
echo "start scp..."
#scp node003:/$homename/.ssh/authorized_keys /$homename/.ssh/node003.key
for((i=0; i<${#node[*]}; i++))
do
        scp ${node[i]}:/$homename/.ssh/id_rsa.pub /$homename/.ssh/${node[i]}.key
        echo "scp from ${node[i]} finished..."
done
echo "append key to authorized_keys..."
for((i=0; i<${#node[*]}; i++))
do
        cat /$homename/.ssh/${node[i]}.key >> /$homename/.ssh/authorized_keys
        echo "append ${node[i]}.key finished..."
done
echo "append all key finished..."
loop=${#node[*]}
let subloop=loop-1
echo "starting scp complete authorized_keys to ${node[0]}~${node[subloop]}"
for((i=0; i<${#node[*]}; i++))
do
        scp /$homename/.ssh/authorized_keys ${node[i]}:/$homename/.ssh/authorized_keys
        echo "scp to ${node[i]} finished..."
done
echo "scp all nodes finished..."
# delete intermediate files
rm -rf /$homename/.ssh/*.key
echo "all configuration finished..."

6、测试三台主机是否实现无密码相互登录

在mha-master上

$ ssh mha-manager
$ ssh mha-bak

在mha-manager上

$ ssh mha-master
$ ssh mha-manager

在mha-bak上

$ ssh mha-master
$ ssh mha-manager

7、在mha-master上创建监控用户，使得两个从库可以连接

mysql> grant all privileges on *.* to 'mha_monitor'@'192.168.86.%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.21 sec) 

8、再其他两台机器上测试是否可以登录

$ mysql -umha_monitor -p123456 -h192.168.86.148

三、配置MHA

在mha-manager上执行

1、创建MHA的工作目录

$ mkdir -p /etc/masterha 

2、修改相关配置文件/etc/masterha/app1.cnf，内容如下

[server default]
manager_log=/var/log/masterha/app1/manager.log
manager_workdir=/var/log/masterha/app1
master_binlog_dir=/data
remote_workdir=/tmp
ping_interval=1
repl_user=repl
repl_password=123456
repl_user=repl
ssh_user=root
user=mha_monitor
password=123456
[server1]
hostname=192.168.86.148
port=3306
[server2]
hostname=192.168.86.149
port=3306
[server3]
candidate_master=1
check_repl_delay=0
hostname=192.168.86.150
port=3306

3、在每个slave节点上设置relay_log的清除方式

mysql> set global relay_log_purge=0;（在 2 个 slave 上执行）
Query OK, 0 rows affected (0.00 sec)

注意：

MHA 在发生切换的过程中，从库的恢复过程中依赖于 relay log 的相关信息，所以这里要将 relay log 的自动清除设置为 OFF，采用手动清除 relay log 的方式。在默认情况下，从服务器上的中继日志会在 SQL 线程执行完毕后被自动删除。但是在 MHA 环境中，这些中继日志在恢复其他从服务器时可能会被用到，因此需要禁用中继日志的自动删除功能。定期清除中继日志需要考虑到复制延时的问题。在 ext3 的文件系统下，删除大的文件需要一定的时间，会导致严重的复制延时。为了避免复制延时，需要暂时为中继日志创建硬链接，因为在 linux 系统中通过硬链接删除大文件速度会很快。（在 mysql 数据库中，删除大表时，通常也采用建立硬链接的方式）
MHA 节点中包含了 pure_relay_logs 命令工具，它可以为中继日志创建硬链接，执行 SET GLOBAL relay_log_purge=1,等待几秒钟以便 SQL 线程切换到新的中继日志，再执行 SET GLOBAL relay_log_purge=0。

如下脚本可实现清空relay日志

vim /root/purge_relay_log.sh
#!/bin/bash
user=root
passwd=123456
port=3306
log_dir='/var/log/masterha/app1/log'
work_dir='/var/log/masterha/app1'
purge='/usr/local/bin/purge_relay_logs'
if [ ! -d $log_dir ]
then
mkdir $log_dir -p
fi
$purge --user=$user --password=$passwd --disable_relay_log_purge --port=$port
--workdir=$work_dir >> $log_dir/purge_relay_logs.log 2>&1

将脚本添加至crontab定期执行

$ crontab -l
0 4 * * * /bin/bash /root/purge_relay_log.sh

4、设置VIP（Virtual IP）

$ ifconfig eth0:1 192.168.86.155/24 

5. 编辑两个IP漂移脚本

failover脚本内容如下，注意修改脚本中的VIP（一处）

$ vim /usr/bin/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.86.155/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 $exit_code = 0;  
  
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 "\n\n\n***************************************************************\n";  
            print "Disabling the VIP - $vip on old master: $orig_master_host\n";  
            print "***************************************************************\n\n\n\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 "\n\n\n***************************************************************\n";  
            print "Enabling the VIP - $vip on new master: $new_master_host \n";  
            print "***************************************************************\n\n\n\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=po  
rt –new_master_host=host –new_master_ip=ip –new_master_port=port\n";  
}

online change脚本内容如下，注意修改脚本中的VIP（一处）

#/bin/bash
source /root/.bash_profile
vip=`echo '192.168.86.155/24'` # Virtual IP
key=`echo '1'`
command=`echo "$1" | awk -F = '{print $2}'`
orig_master_host=`echo "$2" | awk -F = '{print $2}'`
new_master_host=`echo "$7" | awk -F = '{print $2}'`
orig_master_ssh_user=`echo "${12}" | awk -F = '{print $2}'`
new_master_ssh_user=`echo "${13}" | awk -F = '{print $2}'`
stop_vip=`echo "ssh root@$orig_master_host /sbin/ifconfig eth0:$key down"`
start_vip=`echo "ssh root@$new_master_host /sbin/ifconfig eth0:$key $vip"`
if [ $command = 'stop' ]
then
echo -e "\n\n\n***************************************************************\n"
echo -e "Disabling the VIP - $vip on old master: $orig_master_host\n"
$stop_vip
if [ $? -eq 0 ]
then
echo "Disabled the VIP successfully"
else
echo "Disabled the VIP failed"
fi
echo -e "***************************************************************\n\n\n\n"
fi
if [ $command = 'start' -o $command = 'status' ]
then
echo -e "\n\n\n***************************************************************\n"
echo -e "Enabling the VIP - $vip on new master: $new_master_host \n"
$start_vip
if [ $? -eq 0 ]
then
echo "Enabled the VIP successfully"
else
echo "Enabled the VIP failed"
fi
echo -e "***************************************************************\n\n\n\n"
fi

对脚本授权

$ chmod +x /usr/bin/master_ip_failover /usr/bin/master_ip_online_change

四、MHA常用操作

1、执行SSH检查

$ masterha_check_ssh --conf=/etc/masterha/app1.cnf  
Fri Sep  1 01:31:11 2017 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Fri Sep  1 01:31:11 2017 - [info] Reading application default configuration from /etc/masterha/app1.cnf..
Fri Sep  1 01:31:11 2017 - [info] Reading server configuration from /etc/masterha/app1.cnf..
Fri Sep  1 01:31:11 2017 - [info] Starting SSH connection tests..
Fri Sep  1 01:31:12 2017 - [debug] 
Fri Sep  1 01:31:11 2017 - [debug]  Connecting via SSH from root@192.168.86.148(192.168.86.148:22) to root@192.168.86.149(192.168.86.149:22)..
Fri Sep  1 01:31:11 2017 - [debug]   ok.
Fri Sep  1 01:31:11 2017 - [debug]  Connecting via SSH from root@192.168.86.148(192.168.86.148:22) to root@192.168.86.150(192.168.86.150:22)..
Fri Sep  1 01:31:11 2017 - [debug]   ok.
Fri Sep  1 01:31:12 2017 - [debug] 
Fri Sep  1 01:31:11 2017 - [debug]  Connecting via SSH from root@192.168.86.149(192.168.86.149:22) to root@192.168.86.148(192.168.86.148:22)..
Fri Sep  1 01:31:11 2017 - [debug]   ok.
Fri Sep  1 01:31:11 2017 - [debug]  Connecting via SSH from root@192.168.86.149(192.168.86.149:22) to root@192.168.86.150(192.168.86.150:22)..
Fri Sep  1 01:31:12 2017 - [debug]   ok.
Fri Sep  1 01:31:12 2017 - [debug] 
Fri Sep  1 01:31:12 2017 - [debug]  Connecting via SSH from root@192.168.86.150(192.168.86.150:22) to root@192.168.86.148(192.168.86.148:22)..
Fri Sep  1 01:31:12 2017 - [debug]   ok.
Fri Sep  1 01:31:12 2017 - [debug]  Connecting via SSH from root@192.168.86.150(192.168.86.150:22) to root@192.168.86.149(192.168.86.149:22)..
Fri Sep  1 01:31:12 2017 - [debug]   ok.
Fri Sep  1 01:31:12 2017 - [info] All SSH connection tests passed successfully.

出现All SSH connection tests passed successfully说明ssh检查通过

2、执行复制状态检查

$ masterha_check_repl --conf=/etc/masterha/app1.cnf 
Fri Sep  1 01:36:29 2017 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Fri Sep  1 01:36:29 2017 - [info] Reading application default configuration from /etc/masterha/app1.cnf..
Fri Sep  1 01:36:29 2017 - [info] Reading server configuration from /etc/masterha/app1.cnf..
Fri Sep  1 01:36:29 2017 - [info] MHA::MasterMonitor version 0.56.
Fri Sep  1 01:36:29 2017 - [info] GTID failover mode = 0
Fri Sep  1 01:36:29 2017 - [info] Dead Servers:
Fri Sep  1 01:36:29 2017 - [info] Alive Servers:
Fri Sep  1 01:36:29 2017 - [info]   192.168.86.148(192.168.86.148:3306)
Fri Sep  1 01:36:29 2017 - [info]   192.168.86.149(192.168.86.149:3306)
Fri Sep  1 01:36:29 2017 - [info]   192.168.86.150(192.168.86.150:3306)
Fri Sep  1 01:36:29 2017 - [info] Alive Slaves:
Fri Sep  1 01:36:29 2017 - [info]   192.168.86.149(192.168.86.149:3306)  Version=5.6.20-log (oldest major version between slaves) log-bin:enabled
Fri Sep  1 01:36:29 2017 - [info]     Replicating from 192.168.86.148(192.168.86.148:3306)
Fri Sep  1 01:36:29 2017 - [info]   192.168.86.150(192.168.86.150:3306)  Version=5.6.20-log (oldest major version between slaves) log-bin:enabled
Fri Sep  1 01:36:29 2017 - [info]     Replicating from 192.168.86.148(192.168.86.148:3306)
Fri Sep  1 01:36:29 2017 - [info]     Primary candidate for the new Master (candidate_master is set)
Fri Sep  1 01:36:29 2017 - [info] Current Alive Master: 192.168.86.148(192.168.86.148:3306)
Fri Sep  1 01:36:29 2017 - [info] Checking slave configurations..
Fri Sep  1 01:36:29 2017 - [info]  read_only=1 is not set on slave 192.168.86.150(192.168.86.150:3306).
Fri Sep  1 01:36:29 2017 - [info] Checking replication filtering settings..
Fri Sep  1 01:36:29 2017 - [info]  binlog_do_db= , binlog_ignore_db= 
Fri Sep  1 01:36:29 2017 - [info]  Replication filtering check ok.
Fri Sep  1 01:36:29 2017 - [info] GTID (with auto-pos) is not supported
Fri Sep  1 01:36:29 2017 - [info] Starting SSH connection tests..
Fri Sep  1 01:36:31 2017 - [info] All SSH connection tests passed successfully.
Fri Sep  1 01:36:31 2017 - [info] Checking MHA Node version..
Fri Sep  1 01:36:31 2017 - [info]  Version check ok.
Fri Sep  1 01:36:31 2017 - [info] Checking SSH publickey authentication settings on the current master..
Fri Sep  1 01:36:31 2017 - [info] HealthCheck: SSH to 192.168.86.148 is reachable.
Fri Sep  1 01:36:32 2017 - [info] Master MHA Node version is 0.56.
Fri Sep  1 01:36:32 2017 - [info] Checking recovery script configurations on 192.168.86.148(192.168.86.148:3306)..
Fri Sep  1 01:36:32 2017 - [info]   Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/data/ --output_file=/tmp/save_binary_logs_test --manager_version=0.56 --start_file=mysql-server.000007 
Fri Sep  1 01:36:32 2017 - [info]   Connecting to root@192.168.86.148(192.168.86.148:22).. 
  Creating /tmp if not exists..    ok.
  Checking output directory is accessible or not..
   ok.
  Binlog found at /data/, up to mysql-server.000007
Fri Sep  1 01:36:32 2017 - [info] Binlog setting check done.
Fri Sep  1 01:36:32 2017 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..
Fri Sep  1 01:36:32 2017 - [info]   Executing command : apply_diff_relay_logs --command=test --slave_user='monitor' --slave_host=192.168.86.149 --slave_ip=192.168.86.149 --slave_port=3306 --workdir=/tmp --target_version=5.6.20-log --manager_version=0.56 --relay_log_info=/data/relay-log.info  --relay_dir=/data/  --slave_pass=xxx
Fri Sep  1 01:36:32 2017 - [info]   Connecting to root@192.168.86.149(192.168.86.149:22).. 
  Checking slave recovery environment settings..
    Opening /data/relay-log.info ... ok.
    Relay log found at /data, up to mysqld-relay-bin.000003
    Temporary relay log file is /data/mysqld-relay-bin.000003
    Testing mysql connection and privileges..Warning: Using a password on the command line interface can be insecure.
 done.
    Testing mysqlbinlog output.. done.
    Cleaning up test file(s).. done.
Fri Sep  1 01:36:32 2017 - [info]   Executing command : apply_diff_relay_logs --command=test --slave_user='monitor' --slave_host=192.168.86.150 --slave_ip=192.168.86.150 --slave_port=3306 --workdir=/tmp --target_version=5.6.20-log --manager_version=0.56 --relay_log_info=/data/relay-log.info  --relay_dir=/data/  --slave_pass=xxx
Fri Sep  1 01:36:32 2017 - [info]   Connecting to root@192.168.86.150(192.168.86.150:22).. 
  Checking slave recovery environment settings..
    Opening /data/relay-log.info ... ok.
    Relay log found at /data, up to mysqld-relay-bin.000003
    Temporary relay log file is /data/mysqld-relay-bin.000003
    Testing mysql connection and privileges..Warning: Using a password on the command line interface can be insecure.
 done.
    Testing mysqlbinlog output.. done.
    Cleaning up test file(s).. done.
Fri Sep  1 01:36:33 2017 - [info] Slaves settings check done.
Fri Sep  1 01:36:33 2017 - [info] 
192.168.86.148(192.168.86.148:3306) (current master)
 +--192.168.86.149(192.168.86.149:3306)
 +--192.168.86.150(192.168.86.150:3306)
Fri Sep  1 01:36:33 2017 - [info] Checking replication health on 192.168.86.149..
Fri Sep  1 01:36:33 2017 - [info]  ok.
Fri Sep  1 01:36:33 2017 - [info] Checking replication health on 192.168.86.150..
Fri Sep  1 01:36:33 2017 - [info]  ok.
Fri Sep  1 01:36:33 2017 - [info] Checking master_ip_failover_script status:
Fri Sep  1 01:36:33 2017 - [info]   /usr/bin/master_ip_failover --command=status --ssh_user=root --orig_master_host=192.168.86.148 --orig_master_ip=192.168.86.148 --orig_master_port=3306 
Checking the Status of the script.. OK 
Fri Sep  1 01:36:33 2017 - [info]  OK.
Fri Sep  1 01:36:33 2017 - [warning] shutdown_script is not defined.
Fri Sep  1 01:36:33 2017 - [info] Got exit code 0 (Not master dead).
MySQL Replication Health is OK.

出现MySQL Replication Health is OK说明复制状态检查通过

3、执行manager节点状态检查

执行检查时会出现如下三种情况

①manager服务未启动，会出现OT_RUNNING

$ masterha_check_status --conf=/etc/masterha/app1.cnf 
app1 is stopped(2:NOT_RUNNING).

②manager服务已启动，会出现master的ip地址

$ masterha_check_status --conf=/etc/masterha/app1.cnf 
app1 (pid:15469) is running(0:PING_OK), master:192.168.86.148

③manager服务正在启动

$ masterha_check_status --conf=/etc/masterha/app1.cnf 
app1 monitoring program is now on initialization phase(10:INITIALIZING_MONITOR). Wait for a while and try checking again.

4、启动MHA manager服务

$ nohup masterha_manager --conf=/etc/masterha/app1.cnf --remove_dead_master --ignore_last_failover </dev/null > /masterha/app1/manager.log 2>&1 &

5、关闭MHA manager服务

$ masterha_stop --conf=/etc/masterha/app1.cnf

6、在配置文件中添加参数

$ masterha_conf_host --command=add --conf=/etc/masterha/app1.cnf --hostname=192.168.68.102 --block=server1

7、执行主库在线切换

$ masterha_master_switch --conf=/etc/masterha/app1.cnf --master_state=alive --new_master_host=192.168.86.151 --orig_master_is_new_slave --running_updates_limit=10000;

8、借助MHA日志完成对原master的修复

在 mha-manager 监控机器上查看当时的 change master 信息如下，可以通过日志中给出的change master命令修复原master

$ cat /var/log/masterha/app1/manager.log |grep -i "All other slaves should start"
Fri Dec 30 18:09:35 2016 - [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER
TO MASTER_HOST='192.168.86.150', MASTER_PORT=3306, MASTER_LOG_FILE='mha-server.000001', MASTER_LOG_POS=154,
MASTER_USER='repl', MASTER_PASSWORD='xxx';

五、使用场景

1、自动Failover（需启动MHA manager）

a.启动manager服务

$ nohup masterha_manager --conf=/etc/masterha/app1.cnf --remove_dead_master --ignore_last_failover </dev/null > /masterha/app1/manager.log 2>&1 &
$ masterha_check_status --conf=/etc/masterha/app1.cnf 
app1 (pid:15469) is running(0:PING_OK), master:192.168.86.148

b.模拟主库故障，将主库的MySQL进程杀死，进行自动failover操作

在mha-master上执行：

$ pkill -9 mysqld

c.查看manager上的MHA切换日志，了解整个切换过程

$ cat /masterha/app1/manager.log

整个MHA的切换过程，共包括以下的步骤：

1.配置文件检查阶段，这个阶段会检查整个集群配置文件配置
2.宕机的master处理，这个阶段包括虚拟ip摘除操作，主机关机操作（这个我这里还没有实现，需要研究）
3.复制dead maste和最新slave相差的relay log，并保存到MHA Manger具体的目录下
4.识别含有最新更新的slave
5.应用从master保存的二进制日志事件（binlog events）
6.提升一个slave为新的master进行复制
7.使其他的slave连接新的master进行复制

d.启动MHA Manger服务，查看集群中现在的master（自动切换后manager服务会停止）

$ nohup masterha_manager --conf=/etc/masterha/app1.cnf --remove_dead_master --ignore_last_failover </dev/null > /masterha/app1/manager.log 2>&1 &
$ masterha_check_status --conf=/etc/masterha/app1.cnf 
app1 (pid:15469) is running(0:PING_OK), master:192.168.86.150

2、手动failover（MHA manager未启动）

当主服务器故障时，可以手动进行故障切换操作

a.执行手动在线切换命令

$ masterha_master_switch --master_state=dead --conf=/etc/masterha/app1.cnf --dead_master_host=192.168.86.148 --dead_master_port=3306 --new_master_host=192.168.86.150 --new_master_port=3306 --ignore_last_failover

b.根据提示输入进行切换

3、修复宕机的master（或在线切换master）

通常情况下自动切换以后，原master可能已经废弃掉，待原master主机修复后，如果数据完整的情况下，可能想把原来master重新作为
新主库的slave 这时我们可以借助当时自动切换时刻的MHA日志来完成对原master的修复。

a.在mha-manager监控机器上查看当时切换日志中的 change master 信息

可以通过日志中给出的change master命令使原master加入集群

$ cat /var/log/masterha/app1/manager.log |grep -i "All other slaves should start"
Fri Dec 30 18:09:35 2016 - [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER
TO MASTER_HOST='192.168.86.150', MASTER_PORT=3306, MASTER_LOG_FILE='mha-server.000001', MASTER_LOG_POS=154,
MASTER_USER='repl', MASTER_PASSWORD='xxx';

b.在宕机后修复的mha-master上执行change master命令

使原master重新加入集群

mysql> CHANGE MASTER
TO MASTER_HOST='192.168.86.150', MASTER_PORT=3306, MASTER_LOG_FILE='mha-server.000001', MASTER_LOG_POS=154,
MASTER_USER='repl', MASTER_PASSWORD='123456';

c.在mha-manager上在线切换master

使原master重新成为主库

$ masterha_master_switch --conf=/etc/masterha/app1.cnf --master_state=alive --new_master_host=192.168.86.148 --orig_master_is_new_slave --running_updates_limit=10000;