Mysql MHA搭建以及故障切换测试

最新推荐文章于 2024-09-10 15:37:32 发布
最新推荐文章于 2024-09-10 15:37:32 发布
阅读量3.2k 收藏 7
点赞数
分类专栏： MySQL学习笔记
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/shiyu1157758655/article/details/70242093
版权
本文详细介绍MHA（MySQL High Availability）的环境搭建、工作原理及如何实现MySQL Master故障自动切换。MHA可监控Master状态，自动修复多个Slave间的差异日志，确保数据一致性。
摘要由CSDN通过智能技术生成
测试环境信息：
OS:Centos6.8
192.168.180.55  manager
192.168.180.56  master
192.168.180.57  slave1
192.168.180.58  slave2

1.架构
从架构上来说，MHA分为如下两大部分：
（1） Node
我们知道，MHA是基于MySQL Replication环境的，在该环境中，不管是Master角色，还是Slave角色，都称为Node，是被监控管理的对象节点。
Node服务器上需要安装MHA Node包。
（2） Manager
Manager为MHA架构中的管理者，建议部署在一台独立的服务器上，当然也可部署在某个Slave上，但该Slave永远不要被选择成为新的Master，否则故障切换后的MHA架构就失去了高可用性。
Manager服务器需要安装MHA Manager包，并完善一个主配置文件。
一个Manager可管理多套MySQL Replication环境。

2.工作原理：

相较于其它HA软件，MHA的目的在于维持MySQL Replication中Master库的高可用性，其最大特点是可以修复多个Slave之间的差异日志，最终使所有Slave保持数据一致，然后从中选择一个充当新的Master，并将其它Slave指向它。
基本工作流程大致如下：
（1） Manager定期监控Master，监控时间间隔由参数ping_interval决定，缺省为3秒钟一次；可利用其自身的监控功能，也可调用第三方软件来监控；MHA自身提供了两种监控方式：SELECT（执行SELECT 1）和CONNECT（创建连接/断开连接），由于参数ping_type决定，缺省为SELECT方式。
（2） 当监测到Master故障时，调用SSH脚本对所有Node执行一次检查，包括如下几个方面：
――MySQL实例是否可以连接；
――Master服务器是否可以SSH连通；
――检查SQL Thread的状态；
――检查哪些Server死掉了，哪些Server是活动的，以及活动的Slave实例；
――检查Slave实例的配置及复制过滤规则；
――最后退出监控脚本并返回代表特殊意义代码。
（3） 开始Master故障切换，包括如下几个子阶段：
――Phase 1: Configuration Check Phase
在这个阶段，若某个Slave实例的SQL Thread停止了，则会自动启动它；并再次确认活动的Servers及Slaves。
――Phase 2: Dead Master Shutdown Phase
在这个阶段，首先调用master_ip_failover_script，若HA是基于VIP实现的，则关闭VIP，若是基于目录数据库实现的，则修改映射记录。
然后调用shutdown_script脚本强制关闭主机，以避免服务重启时，发生脑裂。
――Phase 3: Master Recovery Phase
又包括如下3个子阶段：
Phase 3.1: Getting Latest Slaves Phase
检查各个Slave，获取最近的和最旧的binary log file和position，并检查各个Slave成为Master的优先级，依赖于candidate_master、no_master、[server_xxx]顺序、binary log差异量等因素。
Phase 3.2: Saving Dead Master's Binlog Phase
若dead master所在服务器依然可以通过SSH连通，则提取dead master的binary log，提取日志的起点就是上一步获取的最新的binary log file和position，直到最后一条事件日志，并在dead master本地的工作目录（由参数remote_workdir决定）中创建文件保存这些提取到的日志，然后将该文件拷贝到Manager服务器的工作目录下（由参数manager_workdir决定）。
当然，若dead master系统就无法连接，也就不存在差异的binary log了。
另外，MHA还要对各个Slave节点进行健康检查，主要是SSH连通性。
Phase 3.3: Determining New Master Phase
接下来调用apply_diff_relay_logs命令恢复Slave的差异日志，这个差异日志指的是各个Slave之间的relay log。
恢复完成后，所有的Slave数据是一致的，此时就可以根据优先级选择New Master了。
Phase 3.3: New Master Diff Log Generation Phase
这里是生成dead master和new master之间的差异日志，即将Phase 3.2保存的binary log拷贝到New Master的工作目录中（remote_workdir）。
Phase 3.4: Master Log Apply Phase
将上一步拷贝的差异日志恢复到New Master上，若发生错误，也可手动恢复。
然后获取New Master的binlog name和position，以便其它Slave从这个新的binlog name和position开始复制。
最后会开启New Master的写权限，即将read_only参数设置为0。
――Phase 4: Slaves Recovery Phase
Phase 4.1: Starting Parallel Slave Diff Log Generation Phase
生成Slave与New Slave之间的差异日志，并将该日志拷贝到各Slave的工作目录下，这部分日志dead master和new master之间差异的那部分日志，因为各个Slave在Phase 3.3阶段已经同步了。
Phase 4.2: Starting Parallel Slave Log Apply Phase
在各个Slave上应用这部分差异日志，然后通过CHANGE MASTER TO命令将这些Slave指向新的New Master，最后开始复制(start slave)。
――Phase 5: New master cleanup phase
清理New Master其实就是重置slave info，即取消原来的Slave信息。
至此整个Master故障切换过程完成。
3.功能
从官方网站的介绍来看，MHA具有如下几个功能：
（1） Master自动监控和故障转移
基于现有的MySQL主从复制环境，MHA可以监控Master，当发现其故障时，自动进行切换。
在多个Slave环境中，如果个别Slave没有接受到最新的relay log events，MHA则会自动从最新的那个Slave上查找差异的relay log events，并将这些差异事件应用到有延迟的Slave上，最终保持所有的Slave数据一致。通常情况下，MHA可在9-12秒内监测到Master故障，7-10秒内关闭主机以避免脑裂，然后花费几秒时间应用差异的relay log，整个过程通常只需10－30秒即可完成。
既然MHA可以自动修复多个Slaves之间的差异日志，所以不用担心数据一致性问题。当Master故障时，MHA会从多个Slave中随机选择一个充当新的Master；当然，也可在配置文件中指定某一个Slave优先成为Master。
（2） 互动（手动）Master故障转移
可以只使用MHA的故障转移功能，而不监控Master，当其故障时，手动调用MHA来进行故障切换。
（3）非交互式自动故障转移
MHA还支持非交互式的Master故障切换（不监控Master，但实现自动故障切换），这个特性其实是将Master的监控和VIP接管交给第三方工具来做，比如Heartbeat，MHA只负责Master故障转移和Slave之间的数据同步。
（4） 在线切换Master到不同的主机

在有些情况下，比如更换Raid控制器，升级主机硬件等，则需要将其上的Master实例迁移到其它主机上，MHA支持快速的Master切换和短暂的写操作阻塞，通常只需0.5－2秒的downtime，这是可以接受的，也方便了DBA的操作


4.搭建环境：
系统环境：
os：centos6.8
192.168.180.55  manager
192.168.180.56  master
192.168.180.57  slave1
192.168.180.58  slave2


a）安装mysql


这里将不再赘述，详细请参考 http://blog.csdn.net/shiyu1157758655/article/details/70226036


b）配置SSH等效连接


这里也不再赘述，详细请参考 http://blog.csdn.net/shiyu1157758655/article/details/56838603


c）安装MHA


HA的安装包也包括Manager和Node两部分，其中Node包不但要在所有的Node节点上安装，而且还需在Manager节点上安装，因为Manager模块内部依赖Node模块，
Manager包则只需在Manager节点安装即可。
MHA是采用perl语言编写的一个脚本管理工具，所以需要安装一系列perl依赖包。
（1）在所有节点上安装perl语言包
   rpm -ivh MySQL-shared-compat-5.6.14-1.rhel5.x86_64.rpm 
   rpm -ivh perl-DBI-1.609-4.el6.x86_64.rpm
   rpm -ivh perl-DBD-MySQL-4.013-3.el6.x86_64.rpm 


（2）以下依赖包为manager 节点需要
   rpm -ivh perl-Params-Validate-0.95-1.el6.rfx.x86_64.rpm 
   rpm -ivh perl-Log-Dispatch-2.26-1.el6.rf.noarch.rpm
   rpm -ivh perl-Config-Tiny-2.12-7.1.el6.noarch.rpm
   rpm -ivh perl-Parallel-ForkManager-0.7.5-2.2.el6.rf.noarch.rpm
（3）在所有节点以及manager上安装node包
   rpm  -ivh mha4mysql-node-0.54-0.el6.noarch.rpm
（4）在manager节点上安装manager包
   rpm  -ivh mha4mysql-manager-0.55-0.el6.noarch.rpm
  成功安装后，会在/usr/bin 目录下生成如下一系列命令工具
 [root@manager bin]# cd /usr/bin/
[root@manager bin]# ll masterha_*
-rwxr-xr-x 1 root root 1995 Dec 13  2012 masterha_check_repl
-rwxr-xr-x 1 root root 1779 Dec 13  2012 masterha_check_ssh
-rwxr-xr-x 1 root root 1865 Dec 13  2012 masterha_check_status
-rwxr-xr-x 1 root root 3201 Dec 13  2012 masterha_conf_host
-rwxr-xr-x 1 root root 2517 Dec 13  2012 masterha_manager
-rwxr-xr-x 1 root root 2165 Dec 13  2012 masterha_master_monitor
-rwxr-xr-x 1 root root 2373 Dec 13  2012 masterha_master_switch
-rwxr-xr-x 1 root root 3879 Dec 13  2012 masterha_secondary_check
-rwxr-xr-x 1 root root 1739 Dec 13  2012 masterha_stop
d）配置MHA
下来就可以配置MHA配置文件了，只需在Manager服务器上操作；
RPM包安装时，缺省不会生成该配置文件，可手动生成，也可从MHA Manager源码安装包中查找配置文件模板，及一系列调用脚本
这里使用manager源码安装包的配置文件模板
[root@manager ~]# mkdir /etc/masterha/
[root@manager master]# unzip mha4mysql-manager-master.zip 
解压后查看
[root@manager conf]# pwd
/data/src/master/mha4mysql-manager-master/samples/conf
[root@manager conf]# ll
total 8
-rw-r--r-- 1 root root 257 Nov  6  2014 app1.cnf
-rw-r--r-- 1 root root 475 Nov  6  2014 masterha_default.cnf
[root@manager conf]# 
将app1.cnf复制到/etc/masterha/下
[root@manager conf]# cp app1.cnf /etc/masterha/
编辑app1.cnf
[root@manager conf]# vi /etc/masterha/app1.cnf 
内容如下：
[server default]
user=mha         #mha管理的用户名
password=mysql 
manager_workdir=/data/mha
manager_log=/data/mha/manager.log
remote_workdir=/data/mha
ssh_user=root    #上面双机互相的用户
repl_user=mysql      #mysql复制（同步）的用户
repl_password=mysql
ping_interval=1
secondary_check_script= masterha_secondary_check -s 192.168.180.56 -s 192.168.180.57 -s 192.168.180.58
#report_script=/data/mha/scripts/send_report      #设置发生切换后发送的报警的脚本
#master_ip_online_change_script=/data/mha/scripts/master_ip_online_change #设置手动切换时候的切换脚本
注意：这里的send_report，master_ip_online_change 可以在刚才解压的文件里找到，可以copy到相应的位置
[server1]
hostname=192.168.180.56    #写ip或主机名都可以
ssh_port=22
master_binlog_dir=/data/mysql  #设置master 保存binlog的位置，以便MHA可以找到master的日志
candidate_master=1
[server2]
hostname=192.168.180.57
ssh_port=22
master_binlog_dir=/data/mysql
candidate_master=1


[server3]
hostname=192.168.180.58
ssh_port=22
master_binlog_dir=/data/mysql
no_master=1
~             
e）验证ssh通讯


root@manager bin]# masterha_check_ssh --conf=/etc/masterha/app1.cnf 
Wed Apr 19 13:06:39 2017 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Wed Apr 19 13:06:39 2017 - [info] Reading application default configurations from /etc/masterha/app1.cnf..
Wed Apr 19 13:06:39 2017 - [info] Reading server configurations from /etc/masterha/app1.cnf..
Wed Apr 19 13:06:39 2017 - [info] Starting SSH connection tests..
Wed Apr 19 13:06:40 2017 - [debug] 
Wed Apr 19 13:06:39 2017 - [debug]  Connecting via SSH from root@192.168.180.56(192.168.180.56:22) to root@192.168.180.57(192.168.180.57:22)..
Wed Apr 19 13:06:39 2017 - [debug]   ok.
Wed Apr 19 13:06:39 2017 - [debug]  Connecting via SSH from root@192.168.180.56(192.168.180.56:22) to root@192.168.180.58(192.168.180.58:22)..
Wed Apr 19 13:06:39 2017 - [debug]   ok.
Wed Apr 19 13:06:40 2017 - [debug] 
Wed Apr 19 13:06:40 2017 - [debug]  Connecting via SSH from root@192.168.180.57(192.168.180.57:22) to root@192.168.180.56(192.168.180.56:22)..
Wed Apr 19 13:06:40 2017 - [debug]   ok.
Wed Apr 19 13:06:40 2017 - [debug]  Connecting via SSH from root@192.168.180.57(192.168.180.57:22) to root@192.168.180.58(192.168.180.58:22)..
Wed Apr 19 13:06:40 2017 - [debug]   ok.
Wed Apr 19 13:06:41 2017 - [debug] 
Wed Apr 19 13:06:40 2017 - [debug]  Connecting via SSH from root@192.168.180.58(192.168.180.58:22) to root@192.168.180.56(192.168.180.56:22)..
Wed Apr 19 13:06:40 2017 - [debug]   ok.
Wed Apr 19 13:06:40 2017 - [debug]  Connecting via SSH from root@192.168.180.58(192.168.180.58:22) to root@192.168.180.57(192.168.180.57:22)..
Wed Apr 19 13:06:40 2017 - [debug]   ok.
Wed Apr 19 13:06:41 2017 - [info] All SSH connection tests passed successfully

f）验证mysql复制，mysql必须都启动


[root@manager ~]# masterha_check_repl --conf=/etc/masterha/app1.cnf 
Wed Apr 19 15:21:53 2017 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Wed Apr 19 15:21:53 2017 - [info] Reading application default configurations from /etc/masterha/app1.cnf..
Wed Apr 19 15:21:53 2017 - [info] Reading server configurations from /etc/masterha/app1.cnf..
Wed Apr 19 15:21:53 2017 - [info] MHA::MasterMonitor version 0.55.
Wed Apr 19 15:21:53 2017 - [info] Dead Servers:
Wed Apr 19 15:21:53 2017 - [info] Alive Servers:
Wed Apr 19 15:21:53 2017 - [info]   192.168.180.56(192.168.180.56:3306)
Wed Apr 19 15:21:53 2017 - [info]   192.168.180.57(192.168.180.57:3306)
Wed Apr 19 15:21:53 2017 - [info]   192.168.180.58(192.168.180.58:3306)
Wed Apr 19 15:21:53 2017 - [info] Alive Slaves:
Wed Apr 19 15:21:53 2017 - [info]   192.168.180.57(192.168.180.57:3306)  Version=5.7.15-log (oldest major version between slaves) log-bin:enabled
Wed Apr 19 15:21:53 2017 - [info]     Replicating from 192.168.180.56(192.168.180.56:3306)
Wed Apr 19 15:21:53 2017 - [info]     Primary candidate for the new Master (candidate_master is set)
Wed Apr 19 15:21:53 2017 - [info]   192.168.180.58(192.168.180.58:3306)  Version=5.7.15-log (oldest major version between slaves) log-bin:enabled
Wed Apr 19 15:21:53 2017 - [info]     Replicating from 192.168.180.56(192.168.180.56:3306)
Wed Apr 19 15:21:53 2017 - [info]     Not candidate for the new Master (no_master is set)
Wed Apr 19 15:21:53 2017 - [info] Current Alive Master: 192.168.180.56(192.168.180.56:3306)
Wed Apr 19 15:21:53 2017 - [info] Checking slave configurations..
Wed Apr 19 15:21:53 2017 - [info]  read_only=1 is not set on slave 192.168.180.57(192.168.180.57:3306).
Wed Apr 19 15:21:53 2017 - [info] Checking replication filtering settings..
Wed Apr 19 15:21:53 2017 - [info]  binlog_do_db= , binlog_ignore_db= 
Wed Apr 19 15:21:53 2017 - [info]  Replication filtering check ok.
Wed Apr 19 15:21:53 2017 - [info] Starting SSH connection tests..
Wed Apr 19 15:21:55 2017 - [info] All SSH connection tests passed successfully.
Wed Apr 19 15:21:55 2017 - [info] Checking MHA Node version..
Wed Apr 19 15:21:55 2017 - [info]  Version check ok.
Wed Apr 19 15:21:55 2017 - [info] Checking SSH publickey authentication settings on the current master..
Wed Apr 19 15:21:55 2017 - [info] HealthCheck: SSH to 192.168.180.56 is reachable.
Wed Apr 19 15:21:55 2017 - [info] Master MHA Node version is 0.54.
Wed Apr 19 15:21:55 2017 - [info] Checking recovery script configurations on the current master..
Wed Apr 19 15:21:55 2017 - [info]   Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/data/mysql --output_file=/data/mha/save_binary_logs_test --manager_version=0.55 --start_file=mysql-bin.000001 
Wed Apr 19 15:21:55 2017 - [info]   Connecting to root@192.168.180.56(192.168.180.56).. 
  Creating /data/mha if not exists..    ok.
  Checking output directory is accessible or not..
   ok.
  Binlog found at /data/mysql, up to mysql-bin.000001
Wed Apr 19 15:21:55 2017 - [info] Master setting check done.
Wed Apr 19 15:21:55 2017 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..
Wed Apr 19 15:21:55 2017 - [info]   Executing command : apply_diff_relay_logs --command=test --slave_user='mha' --slave_host=192.168.180.57 --slave_ip=192.168.180.57 --slave_port=3306 --workdir=/data/mha --target_version=5.7.15-log --manager_version=0.55 --relay_log_info=/data/mysql/relay-log.info  --relay_dir=/data/mysql/  --slave_pass=xxx
Wed Apr 19 15:21:55 2017 - [info]   Connecting to root@192.168.180.57(192.168.180.57:22).. 
  Checking slave recovery environment settings..
    Opening /data/mysql/relay-log.info ... ok.
    Relay log found at /data/mysql, up to slave1-relay-bin.000002
    Temporary relay log file is /data/mysql/slave1-relay-bin.000002
    Testing mysql connection and privileges..mysql: [Warning] Using a password on the command line interface can be insecure.
 done.
    Testing mysqlbinlog output.. done.
    Cleaning up test file(s).. done.
Wed Apr 19 15:21:56 2017 - [info]   Executing command : apply_diff_relay_logs --command=test --slave_user='mha' --slave_host=192.168.180.58 --slave_ip=192.168.180.58 --slave_port=3306 --workdir=/data/mha --target_version=5.7.15-log --manager_version=0.55 --relay_log_info=/data/mysql/relay-log.info  --relay_dir=/data/mysql/  --slave_pass=xxx
Wed Apr 19 15:21:56 2017 - [info]   Connecting to root@192.168.180.58(192.168.180.58:22).. 
  Checking slave recovery environment settings..
    Opening /data/mysql/relay-log.info ... ok.
    Relay log found at /data/mysql, up to mysql-relay-bin.000002
    Temporary relay log file is /data/mysql/mysql-relay-bin.000002
    Testing mysql connection and privileges..mysql: [Warning] Using a password on the command line interface can be insecure.
 done.
    Testing mysqlbinlog output.. done.
    Cleaning up test file(s).. done.
Wed Apr 19 15:21:56 2017 - [info] Slaves settings check done.
Wed Apr 19 15:21:56 2017 - [info] 
192.168.180.56 (current master)
 +--192.168.180.57
 +--192.168.180.58


Wed Apr 19 15:21:56 2017 - [info] Checking replication health on 192.168.180.57..
Wed Apr 19 15:21:56 2017 - [info]  ok.
Wed Apr 19 15:21:56 2017 - [info] Checking replication health on 192.168.180.58..
Wed Apr 19 15:21:56 2017 - [info]  ok.
Wed Apr 19 15:21:56 2017 - [warning] master_ip_failover_script is not defined.
Wed Apr 19 15:21:56 2017 - [warning] shutdown_script is not defined.
Wed Apr 19 15:21:56 2017 - [info] Got exit code 0 (Not master dead).


MySQL Replication Health is OK.

如果有报错，请根据上面的报错排查


g）启动MHA
[root@manager ~]# nohup masterha_manager --conf=/etc/masterha/app1.cnf </dev/null >/data/mha/mag.log 2>&1 &
查看：
[root@manager ~]# masterha_check_status --conf=/etc/masterha/app1.cnf 
app1 (pid:5181) is running(0:PING_OK), master:192.168.180.56
[root@manager ~]# 


[root@manager scripts]# tail -f /data/mha/manager.log 
192.168.180.56 (current master)
 +--192.168.180.57
 +--192.168.180.58


Wed Apr 19 14:12:45 2017 - [warning] master_ip_failover_script is not defined.
Wed Apr 19 14:12:45 2017 - [warning] shutdown_script is not defined.
Wed Apr 19 14:12:45 2017 - [info] Set master ping interval 1 seconds.
Wed Apr 19 14:12:45 2017 - [info] Set secondary check script: masterha_secondary_check -s 192.168.180.56 -s 192.168.180.57 -s 192.168.180.58
Wed Apr 19 14:12:45 2017 - [info] Starting ping health check on 192.168.180.56(192.168.180.56:3306)..
Wed Apr 19 14:12:45 2017 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond..


5.测试master(56)宕机后，是否会自动切换
[root@master src]# /etc/init.d/mysql status
MySQL running (3629)                                       [  OK  ]
[root@master src]# /etc/init.d/mysql stop
Shutting down MySQL............                            [  OK  ]
[root@master src]# 


查看manager日志


[root@manager scripts]# tail -f /data/mha/manager.log 
192.168.180.56 (current master)
 +--192.168.180.57
 +--192.168.180.58


Wed Apr 19 14:12:45 2017 - [warning] master_ip_failover_script is not defined.
Wed Apr 19 14:12:45 2017 - [warning] shutdown_script is not defined.
Wed Apr 19 14:12:45 2017 - [info] Set master ping interval 1 seconds.
Wed Apr 19 14:12:45 2017 - [info] Set secondary check script: masterha_secondary_check -s 192.168.180.56 -s 192.168.180.57 -s 192.168.180.58
Wed Apr 19 14:12:45 2017 - [info] Starting ping health check on 192.168.180.56(192.168.180.56:3306)..
Wed Apr 19 14:12:45 2017 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond..
Wed Apr 19 15:45:30 2017 - [warning] Got error on MySQL select ping: 2006 (MySQL server has gone away)
Wed Apr 19 15:45:30 2017 - [info] Executing seconary network check script: masterha_secondary_check -s 192.168.180.56 -s 192.168.180.57 -s 192.168.180.58  --user=root  --master_host=192.168.180.56  --master_ip=192.168.180.56  --master_port=3306
Wed Apr 19 15:45:30 2017 - [info] Executing SSH check script: save_binary_logs --command=test --start_pos=4 --binlog_dir=/data/mysql --output_file=/data/mha/save_binary_logs_test --manager_version=0.55 --binlog_prefix=mysql-bin
Wed Apr 19 15:45:30 2017 - [info] HealthCheck: SSH to 192.168.180.56 is reachable.
Monitoring server 192.168.180.56 is reachable, Master is not reachable from 192.168.180.56. OK.
Monitoring server 192.168.180.57 is reachable, Master is not reachable from 192.168.180.57. OK.
Monitoring server 192.168.180.58 is reachable, Master is not reachable from 192.168.180.58. OK.
Wed Apr 19 15:45:31 2017 - [info] Master is not reachable from all other monitoring servers. Failover should start.
Wed Apr 19 15:45:31 2017 - [warning] Got error on MySQL connect: 2013 (Lost connection to MySQL server at 'reading initial communication packet', system error: 111)
Wed Apr 19 15:45:31 2017 - [warning] Connection failed 1 time(s)..
Wed Apr 19 15:45:32 2017 - [warning] Got error on MySQL connect: 2013 (Lost connection to MySQL server at 'reading initial communication packet', system error: 111)
Wed Apr 19 15:45:32 2017 - [warning] Connection failed 2 time(s)..
Wed Apr 19 15:45:33 2017 - [warning] Got error on MySQL connect: 2013 (Lost connection to MySQL server at 'reading initial communication packet', system error: 111)
Wed Apr 19 15:45:33 2017 - [warning] Connection failed 3 time(s)..
Wed Apr 19 15:45:33 2017 - [warning] Master is not reachable from health checker!
Wed Apr 19 15:45:33 2017 - [warning] Master 192.168.180.56(192.168.180.56:3306) is not reachable!
Wed Apr 19 15:45:33 2017 - [warning] SSH is reachable.
Wed Apr 19 15:45:33 2017 - [info] Connecting to a master server failed. Reading configuration file /etc/masterha_default.cnf and /etc/masterha/app1.cnf again, and trying to connect to all servers to check server status..
Wed Apr 19 15:45:33 2017 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Wed Apr 19 15:45:33 2017 - [info] Reading application default configurations from /etc/masterha/app1.cnf..
Wed Apr 19 15:45:33 2017 - [info] Reading server configurations from /etc/masterha/app1.cnf..
Wed Apr 19 15:45:33 2017 - [info] Dead Servers:
Wed Apr 19 15:45:33 2017 - [info]   192.168.180.56(192.168.180.56:3306)
Wed Apr 19 15:45:33 2017 - [info] Alive Servers:
Wed Apr 19 15:45:33 2017 - [info]   192.168.180.57(192.168.180.57:3306)
Wed Apr 19 15:45:33 2017 - [info]   192.168.180.58(192.168.180.58:3306)
Wed Apr 19 15:45:33 2017 - [info] Alive Slaves:
Wed Apr 19 15:45:33 2017 - [info]   192.168.180.57(192.168.180.57:3306)  Version=5.7.15-log (oldest major version between slaves) log-bin:enabled
Wed Apr 19 15:45:33 2017 - [info]     Replicating from 192.168.180.56(192.168.180.56:3306)
Wed Apr 19 15:45:33 2017 - [info]     Primary candidate for the new Master (candidate_master is set)
Wed Apr 19 15:45:33 2017 - [info]   192.168.180.58(192.168.180.58:3306)  Version=5.7.15-log (oldest major version between slaves) log-bin:enabled
Wed Apr 19 15:45:33 2017 - [info]     Replicating from 192.168.180.56(192.168.180.56:3306)
Wed Apr 19 15:45:33 2017 - [info]     Not candidate for the new Master (no_master is set)
Wed Apr 19 15:45:33 2017 - [info] Checking slave configurations..
Wed Apr 19 15:45:33 2017 - [info]  read_only=1 is not set on slave 192.168.180.57(192.168.180.57:3306).
Wed Apr 19 15:45:33 2017 - [info] Checking replication filtering settings..
Wed Apr 19 15:45:33 2017 - [info]  Replication filtering check ok.
Wed Apr 19 15:45:33 2017 - [info] Master is down!
Wed Apr 19 15:45:33 2017 - [info] Terminating monitoring script.
Wed Apr 19 15:45:33 2017 - [info] Got exit code 20 (Master dead).
Wed Apr 19 15:45:33 2017 - [info] MHA::MasterFailover version 0.55.
Wed Apr 19 15:45:33 2017 - [info] Starting master failover.
Wed Apr 19 15:45:33 2017 - [info] 
Wed Apr 19 15:45:33 2017 - [info] * Phase 1: Configuration Check Phase..
Wed Apr 19 15:45:33 2017 - [info] 
Wed Apr 19 15:45:33 2017 - [info] Dead Servers:
Wed Apr 19 15:45:33 2017 - [info]   192.168.180.56(192.168.180.56:3306)
Wed Apr 19 15:45:33 2017 - [info] Checking master reachability via mysql(double check)..
Wed Apr 19 15:45:33 2017 - [info]  ok.
Wed Apr 19 15:45:33 2017 - [info] Alive Servers:
Wed Apr 19 15:45:33 2017 - [info]   192.168.180.57(192.168.180.57:3306)
Wed Apr 19 15:45:33 2017 - [info]   192.168.180.58(192.168.180.58:3306)
Wed Apr 19 15:45:33 2017 - [info] Alive Slaves:
Wed Apr 19 15:45:33 2017 - [info]   192.168.180.57(192.168.180.57:3306)  Version=5.7.15-log (oldest major version between slaves) log-bin:enabled
Wed Apr 19 15:45:33 2017 - [info]     Replicating from 192.168.180.56(192.168.180.56:3306)
Wed Apr 19 15:45:33 2017 - [info]     Primary candidate for the new Master (candidate_master is set)
Wed Apr 19 15:45:33 2017 - [info]   192.168.180.58(192.168.180.58:3306)  Version=5.7.15-log (oldest major version between slaves) log-bin:enabled
Wed Apr 19 15:45:33 2017 - [info]     Replicating from 192.168.180.56(192.168.180.56:3306)
Wed Apr 19 15:45:33 2017 - [info]     Not candidate for the new Master (no_master is set)
Wed Apr 19 15:45:33 2017 - [info] ** Phase 1: Configuration Check Phase completed.
Wed Apr 19 15:45:33 2017 - [info] 
Wed Apr 19 15:45:33 2017 - [info] * Phase 2: Dead Master Shutdown Phase..
Wed Apr 19 15:45:33 2017 - [info] 
Wed Apr 19 15:45:33 2017 - [info] Forcing shutdown so that applications never connect to the current master..
Wed Apr 19 15:45:33 2017 - [warning] master_ip_failover_script is not set. Skipping invalidating dead master ip address.
Wed Apr 19 15:45:33 2017 - [warning] shutdown_script is not set. Skipping explicit shutting down of the dead master.
Wed Apr 19 15:45:33 2017 - [info] * Phase 2: Dead Master Shutdown Phase completed.
Wed Apr 19 15:45:33 2017 - [info] 
Wed Apr 19 15:45:33 2017 - [info] * Phase 3: Master Recovery Phase..
Wed Apr 19 15:45:33 2017 - [info] 
Wed Apr 19 15:45:33 2017 - [info] * Phase 3.1: Getting Latest Slaves Phase..
Wed Apr 19 15:45:33 2017 - [info] 
Wed Apr 19 15:45:33 2017 - [info] The latest binary log file/position on all slaves is mysql-bin.000001:1299
Wed Apr 19 15:45:33 2017 - [info] Latest slaves (Slaves that received relay log files to the latest):
Wed Apr 19 15:45:33 2017 - [info]   192.168.180.57(192.168.180.57:3306)  Version=5.7.15-log (oldest major version between slaves) log-bin:enabled
Wed Apr 19 15:45:33 2017 - [info]     Replicating from 192.168.180.56(192.168.180.56:3306)
Wed Apr 19 15:45:33 2017 - [info]     Primary candidate for the new Master (candidate_master is set)
Wed Apr 19 15:45:33 2017 - [info]   192.168.180.58(192.168.180.58:3306)  Version=5.7.15-log (oldest major version between slaves) log-bin:enabled
Wed Apr 19 15:45:33 2017 - [info]     Replicating from 192.168.180.56(192.168.180.56:3306)
Wed Apr 19 15:45:33 2017 - [info]     Not candidate for the new Master (no_master is set)
Wed Apr 19 15:45:33 2017 - [info] The oldest binary log file/position on all slaves is mysql-bin.000001:1299
Wed Apr 19 15:45:33 2017 - [info] Oldest slaves:
Wed Apr 19 15:45:33 2017 - [info]   192.168.180.57(192.168.180.57:3306)  Version=5.7.15-log (oldest major version between slaves) log-bin:enabled
Wed Apr 19 15:45:33 2017 - [info]     Replicating from 192.168.180.56(192.168.180.56:3306)
Wed Apr 19 15:45:33 2017 - [info]     Primary candidate for the new Master (candidate_master is set)
Wed Apr 19 15:45:33 2017 - [info]   192.168.180.58(192.168.180.58:3306)  Version=5.7.15-log (oldest major version between slaves) log-bin:enabled
Wed Apr 19 15:45:33 2017 - [info]     Replicating from 192.168.180.56(192.168.180.56:3306)
Wed Apr 19 15:45:33 2017 - [info]     Not candidate for the new Master (no_master is set)
Wed Apr 19 15:45:33 2017 - [info] 
Wed Apr 19 15:45:33 2017 - [info] * Phase 3.2: Saving Dead Master's Binlog Phase..
Wed Apr 19 15:45:33 2017 - [info] 
Wed Apr 19 15:45:33 2017 - [info] Fetching dead master's binary logs..
Wed Apr 19 15:45:33 2017 - [info] Executing command on the dead master 192.168.180.56(192.168.180.56:3306): save_binary_logs --command=save --start_file=mysql-bin.000001  --start_pos=1299 --binlog_dir=/data/mysql --output_file=/data/mha/saved_master_binlog_from_192.168.180.56_3306_20170419154533.binlog --handle_raw_binlog=1 --disable_log_bin=0 --manager_version=0.55
  Creating /data/mha if not exists..    ok.
 Concat binary/relay logs from mysql-bin.000001 pos 1299 to mysql-bin.000001 EOF into /data/mha/saved_master_binlog_from_192.168.180.56_3306_20170419154533.binlog ..
  Dumping binlog format description event, from position 0 to 123.. ok.
  No need to dump effective binlog data from /data/mysql/mysql-bin.000001 (pos starts 1299, filesize 1299). Skipping.
 /data/mha/saved_master_binlog_from_192.168.180.56_3306_20170419154533.binlog has no effective data events.
Event not exists.
Wed Apr 19 15:45:34 2017 - [info] Additional events were not found from the orig master. No need to save.
Wed Apr 19 15:45:34 2017 - [info] 
Wed Apr 19 15:45:34 2017 - [info] * Phase 3.3: Determining New Master Phase..
Wed Apr 19 15:45:34 2017 - [info] 
Wed Apr 19 15:45:34 2017 - [info] Finding the latest slave that has all relay logs for recovering other slaves..
Wed Apr 19 15:45:34 2017 - [info] All slaves received relay logs to the same position. No need to resync each other.
Wed Apr 19 15:45:34 2017 - [info] Searching new master from slaves..
Wed Apr 19 15:45:34 2017 - [info]  Candidate masters from the configuration file:
Wed Apr 19 15:45:34 2017 - [info]   192.168.180.57(192.168.180.57:3306)  Version=5.7.15-log (oldest major version between slaves) log-bin:enabled
Wed Apr 19 15:45:34 2017 - [info]     Replicating from 192.168.180.56(192.168.180.56:3306)
Wed Apr 19 15:45:34 2017 - [info]     Primary candidate for the new Master (candidate_master is set)
Wed Apr 19 15:45:34 2017 - [info]  Non-candidate masters:
Wed Apr 19 15:45:34 2017 - [info]   192.168.180.58(192.168.180.58:3306)  Version=5.7.15-log (oldest major version between slaves) log-bin:enabled
Wed Apr 19 15:45:34 2017 - [info]     Replicating from 192.168.180.56(192.168.180.56:3306)
Wed Apr 19 15:45:34 2017 - [info]     Not candidate for the new Master (no_master is set)
Wed Apr 19 15:45:34 2017 - [info]  Searching from candidate_master slaves which have received the latest relay log events..
Wed Apr 19 15:45:34 2017 - [info] New master is 192.168.180.57(192.168.180.57:3306)
Wed Apr 19 15:45:34 2017 - [info] Starting master failover..
Wed Apr 19 15:45:34 2017 - [info] 
From:
192.168.180.56 (current master)
 +--192.168.180.57
 +--192.168.180.58


To:
192.168.180.57 (new master)
 +--192.168.180.58
Wed Apr 19 15:45:34 2017 - [info] 
Wed Apr 19 15:45:34 2017 - [info] * Phase 3.3: New Master Diff Log Generation Phase..
Wed Apr 19 15:45:34 2017 - [info] 
Wed Apr 19 15:45:34 2017 - [info]  This server has all relay logs. No need to generate diff files from the latest slave.
Wed Apr 19 15:45:34 2017 - [info] 
Wed Apr 19 15:45:34 2017 - [info] * Phase 3.4: Master Log Apply Phase..
Wed Apr 19 15:45:34 2017 - [info] 
Wed Apr 19 15:45:34 2017 - [info] *NOTICE: If any error happens from this phase, manual recovery is needed.
Wed Apr 19 15:45:34 2017 - [info] Starting recovery on 192.168.180.57(192.168.180.57:3306)..
Wed Apr 19 15:45:34 2017 - [info]  This server has all relay logs. Waiting all logs to be applied.. 
Wed Apr 19 15:45:34 2017 - [info]   done.
Wed Apr 19 15:45:34 2017 - [info]  All relay logs were successfully applied.
Wed Apr 19 15:45:34 2017 - [info] Getting new master's binlog name and position..
Wed Apr 19 15:45:34 2017 - [info]  mysql-bin.000002:893
Wed Apr 19 15:45:34 2017 - [info]  All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='192.168.180.57', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=893, MASTER_USER='mysql', MASTER_PASSWORD='xxx';
Wed Apr 19 15:45:34 2017 - [warning] master_ip_failover_script is not set. Skipping taking over new master ip address.
Wed Apr 19 15:45:34 2017 - [info] ** Finished master recovery successfully.
Wed Apr 19 15:45:34 2017 - [info] * Phase 3: Master Recovery Phase completed.
Wed Apr 19 15:45:34 2017 - [info] 
Wed Apr 19 15:45:34 2017 - [info] * Phase 4: Slaves Recovery Phase..
Wed Apr 19 15:45:34 2017 - [info] 
Wed Apr 19 15:45:34 2017 - [info] * Phase 4.1: Starting Parallel Slave Diff Log Generation Phase..
Wed Apr 19 15:45:34 2017 - [info] 
Wed Apr 19 15:45:34 2017 - [info] -- Slave diff file generation on host 192.168.180.58(192.168.180.58:3306) started, pid: 11789. Check tmp log /data/mha/192.168.180.58_3306_20170419154533.log if it takes time..
Wed Apr 19 15:45:34 2017 - [info] 
Wed Apr 19 15:45:34 2017 - [info] Log messages from 192.168.180.58 ...
Wed Apr 19 15:45:34 2017 - [info] 
Wed Apr 19 15:45:34 2017 - [info]  This server has all relay logs. No need to generate diff files from the latest slave.
Wed Apr 19 15:45:34 2017 - [info] End of log messages from 192.168.180.58.
Wed Apr 19 15:45:34 2017 - [info] -- 192.168.180.58(192.168.180.58:3306) has the latest relay log events.
Wed Apr 19 15:45:34 2017 - [info] Generating relay diff files from the latest slave succeeded.
Wed Apr 19 15:45:34 2017 - [info] 
Wed Apr 19 15:45:34 2017 - [info] * Phase 4.2: Starting Parallel Slave Log Apply Phase..
Wed Apr 19 15:45:34 2017 - [info] 
Wed Apr 19 15:45:34 2017 - [info] -- Slave recovery on host 192.168.180.58(192.168.180.58:3306) started, pid: 11791. Check tmp log /data/mha/192.168.180.58_3306_20170419154533.log if it takes time..
Wed Apr 19 15:45:34 2017 - [info] 
Wed Apr 19 15:45:34 2017 - [info] Log messages from 192.168.180.58 ...
Wed Apr 19 15:45:34 2017 - [info] 
Wed Apr 19 15:45:34 2017 - [info] Starting recovery on 192.168.180.58(192.168.180.58:3306)..
Wed Apr 19 15:45:34 2017 - [info]  This server has all relay logs. Waiting all logs to be applied.. 
Wed Apr 19 15:45:34 2017 - [info]   done.
Wed Apr 19 15:45:34 2017 - [info]  All relay logs were successfully applied.
Wed Apr 19 15:45:34 2017 - [info]  Resetting slave 192.168.180.58(192.168.180.58:3306) and starting replication from the new master 192.168.180.57(192.168.180.57:3306)..
Wed Apr 19 15:45:34 2017 - [info]  Executed CHANGE MASTER.
Wed Apr 19 15:45:34 2017 - [info]  Slave started.
Wed Apr 19 15:45:34 2017 - [info] End of log messages from 192.168.180.58.
Wed Apr 19 15:45:34 2017 - [info] -- Slave recovery on host 192.168.180.58(192.168.180.58:3306) succeeded.
Wed Apr 19 15:45:34 2017 - [info] All new slave servers recovered successfully.
Wed Apr 19 15:45:34 2017 - [info] 
Wed Apr 19 15:45:34 2017 - [info] * Phase 5: New master cleanup phase..
Wed Apr 19 15:45:34 2017 - [info] 
Wed Apr 19 15:45:34 2017 - [info] Resetting slave info on the new master..
Wed Apr 19 15:45:34 2017 - [info]  192.168.180.57: Resetting slave info succeeded.
Wed Apr 19 15:45:34 2017 - [info] Master failover to 192.168.180.57(192.168.180.57:3306) completed successfully.
Wed Apr 19 15:45:34 2017 - [info] 


----- Failover Report -----


app1: MySQL Master failover 192.168.180.56 to 192.168.180.57 succeeded


Master 192.168.180.56 is down!


Check MHA Manager logs at manager:/data/mha/manager.log for details.


Started automated(non-interactive) failover.
The latest slave 192.168.180.57(192.168.180.57:3306) has all relay logs for recovery.
Selected 192.168.180.57 as a new master.
192.168.180.57: OK: Applying all logs succeeded.
192.168.180.58: This host has the latest relay log events.
Generating relay diff files from the latest slave succeeded.
192.168.180.58: OK: Applying all logs succeeded. Slave started, replicating from 192.168.180.57.
192.168.180.57: Resetting slave info succeeded.

Master failover to 192.168.180.57(192.168.180.57:3306) completed successfully.


切换主机后，检查replication状态


[root@manager ~]# masterha_check_repl --conf /etc/masterha/app1.cnf 
Wed Apr 19 16:07:29 2017 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Wed Apr 19 16:07:29 2017 - [info] Reading application default configurations from /etc/masterha/app1.cnf..
Wed Apr 19 16:07:29 2017 - [info] Reading server configurations from /etc/masterha/app1.cnf..
Wed Apr 19 16:07:29 2017 - [info] MHA::MasterMonitor version 0.55.
Wed Apr 19 16:07:29 2017 - [error][/usr/share/perl5/vendor_perl/MHA/ServerManager.pm, ln604] There are 2 non-slave servers! MHA manages at most one non-slave server. Check configurations.
Wed Apr 19 16:07:29 2017 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln386] Error happend on checking configurations.  at /usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm line 300
Wed Apr 19 16:07:29 2017 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln482] Error happened on monitoring servers.
Wed Apr 19 16:07:29 2017 - [info] Got exit code 1 (Not master dead).


MySQL Replication Health is NOT OK!
[root@manager ~]# masterha_check_repl --conf /etc/masterha/app1.cnf 
Wed Apr 19 16:08:41 2017 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Wed Apr 19 16:08:41 2017 - [info] Reading application default configurations from /etc/masterha/app1.cnf..
Wed Apr 19 16:08:41 2017 - [info] Reading server configurations from /etc/masterha/app1.cnf..
Wed Apr 19 16:08:41 2017 - [info] MHA::MasterMonitor version 0.55.
Wed Apr 19 16:08:42 2017 - [error][/usr/share/perl5/vendor_perl/MHA/ServerManager.pm, ln604] There are 2 non-slave servers! MHA manages at most one non-slave server. Check configurations.
Wed Apr 19 16:08:42 2017 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln386] Error happend on checking configurations.  at /usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm line 300
Wed Apr 19 16:08:42 2017 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln482] Error happened on monitoring servers.
Wed Apr 19 16:08:42 2017 - [info] Got exit code 1 (Not master dead).


MySQL Replication Health is NOT OK!
[root@manager ~]# masterha_check_repl --conf /etc/masterha/app1.cnf 
Wed Apr 19 16:14:17 2017 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Wed Apr 19 16:14:17 2017 - [info] Reading application default configurations from /etc/masterha/app1.cnf..
Wed Apr 19 16:14:17 2017 - [info] Reading server configurations from /etc/masterha/app1.cnf..
Wed Apr 19 16:14:17 2017 - [info] MHA::MasterMonitor version 0.55.
Wed Apr 19 16:14:18 2017 - [error][/usr/share/perl5/vendor_perl/MHA/ServerManager.pm, ln604] There are 2 non-slave servers! MHA manages at most one non-slave server. Check configurations.
Wed Apr 19 16:14:18 2017 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln386] Error happend on checking configurations.  at /usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm line 300
Wed Apr 19 16:14:18 2017 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln482] Error happened on monitoring servers.
Wed Apr 19 16:14:18 2017 - [info] Got exit code 1 (Not master dead).


MySQL Replication Health is NOT OK!


关于这个报错相信请参考http://blog.csdn.net/shiyu1157758655/article/details/70242249


解决之后再次执行
[root@manager ~]# masterha_check_repl --conf /etc/masterha/app1.cnf 
Wed Apr 19 16:22:37 2017 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Wed Apr 19 16:22:37 2017 - [info] Reading application default configurations from /etc/masterha/app1.cnf..
Wed Apr 19 16:22:37 2017 - [info] Reading server configurations from /etc/masterha/app1.cnf..
Wed Apr 19 16:22:37 2017 - [info] MHA::MasterMonitor version 0.55.
Wed Apr 19 16:22:37 2017 - [info] Dead Servers:
Wed Apr 19 16:22:37 2017 - [info] Alive Servers:
Wed Apr 19 16:22:37 2017 - [info]   192.168.180.56(192.168.180.56:3306)
Wed Apr 19 16:22:37 2017 - [info]   192.168.180.57(192.168.180.57:3306)
Wed Apr 19 16:22:37 2017 - [info]   192.168.180.58(192.168.180.58:3306)
Wed Apr 19 16:22:37 2017 - [info] Alive Slaves:
Wed Apr 19 16:22:37 2017 - [info]   192.168.180.56(192.168.180.56:3306)  Version=5.7.15-log (oldest major version between slaves) log-bin:enabled
Wed Apr 19 16:22:37 2017 - [info]     Replicating from 192.168.180.57(192.168.180.57:3306)
Wed Apr 19 16:22:37 2017 - [info]     Primary candidate for the new Master (candidate_master is set)
Wed Apr 19 16:22:37 2017 - [info]   192.168.180.58(192.168.180.58:3306)  Version=5.7.15-log (oldest major version between slaves) log-bin:enabled
Wed Apr 19 16:22:37 2017 - [info]     Replicating from 192.168.180.57(192.168.180.57:3306)
Wed Apr 19 16:22:37 2017 - [info]     Not candidate for the new Master (no_master is set)
Wed Apr 19 16:22:37 2017 - [info] Current Alive Master: 192.168.180.57(192.168.180.57:3306)
Wed Apr 19 16:22:37 2017 - [info] Checking slave configurations..
Wed Apr 19 16:22:37 2017 - [info]  read_only=1 is not set on slave 192.168.180.56(192.168.180.56:3306).
Wed Apr 19 16:22:37 2017 - [warning]  relay_log_purge=0 is not set on slave 192.168.180.56(192.168.180.56:3306).
Wed Apr 19 16:22:37 2017 - [info] Checking replication filtering settings..
Wed Apr 19 16:22:37 2017 - [info]  binlog_do_db= , binlog_ignore_db= 
Wed Apr 19 16:22:37 2017 - [info]  Replication filtering check ok.
Wed Apr 19 16:22:37 2017 - [info] Starting SSH connection tests..
Wed Apr 19 16:22:38 2017 - [info] All SSH connection tests passed successfully.
Wed Apr 19 16:22:38 2017 - [info] Checking MHA Node version..
Wed Apr 19 16:22:39 2017 - [info]  Version check ok.
Wed Apr 19 16:22:39 2017 - [info] Checking SSH publickey authentication settings on the current master..
Wed Apr 19 16:22:39 2017 - [info] HealthCheck: SSH to 192.168.180.57 is reachable.
Wed Apr 19 16:22:39 2017 - [info] Master MHA Node version is 0.54.
Wed Apr 19 16:22:39 2017 - [info] Checking recovery script configurations on the current master..
Wed Apr 19 16:22:39 2017 - [info]   Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/data/mysql --output_file=/data/mha/save_binary_logs_test --manager_version=0.55 --start_file=mysql-bin.000002 
Wed Apr 19 16:22:39 2017 - [info]   Connecting to root@192.168.180.57(192.168.180.57).. 
  Creating /data/mha if not exists..    ok.
  Checking output directory is accessible or not..
   ok.
  Binlog found at /data/mysql, up to mysql-bin.000002
Wed Apr 19 16:22:39 2017 - [info] Master setting check done.
Wed Apr 19 16:22:39 2017 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..
Wed Apr 19 16:22:39 2017 - [info]   Executing command : apply_diff_relay_logs --command=test --slave_user='mha' --slave_host=192.168.180.56 --slave_ip=192.168.180.56 --slave_port=3306 --workdir=/data/mha --target_version=5.7.15-log --manager_version=0.55 --relay_log_info=/data/mysql/relay-log.info  --relay_dir=/data/mysql/  --slave_pass=xxx
Wed Apr 19 16:22:39 2017 - [info]   Connecting to root@192.168.180.56(192.168.180.56:22).. 
  Checking slave recovery environment settings..
    Opening /data/mysql/relay-log.info ... ok.
    Relay log found at /data/mysql, up to master-relay-bin.000003
    Temporary relay log file is /data/mysql/master-relay-bin.000003
    Testing mysql connection and privileges..mysql: [Warning] Using a password on the command line interface can be insecure.
 done.
    Testing mysqlbinlog output.. done.
    Cleaning up test file(s).. done.
Wed Apr 19 16:22:40 2017 - [info]   Executing command : apply_diff_relay_logs --command=test --slave_user='mha' --slave_host=192.168.180.58 --slave_ip=192.168.180.58 --slave_port=3306 --workdir=/data/mha --target_version=5.7.15-log --manager_version=0.55 --relay_log_info=/data/mysql/relay-log.info  --relay_dir=/data/mysql/  --slave_pass=xxx
Wed Apr 19 16:22:40 2017 - [info]   Connecting to root@192.168.180.58(192.168.180.58:22).. 
  Checking slave recovery environment settings..
    Opening /data/mysql/relay-log.info ... ok.
    Relay log found at /data/mysql, up to mysql-relay-bin.000002
    Temporary relay log file is /data/mysql/mysql-relay-bin.000002
    Testing mysql connection and privileges..mysql: [Warning] Using a password on the command line interface can be insecure.
 done.
    Testing mysqlbinlog output.. done.
    Cleaning up test file(s).. done.
Wed Apr 19 16:22:40 2017 - [info] Slaves settings check done.
Wed Apr 19 16:22:40 2017 - [info] 
192.168.180.57 (current master)
 +--192.168.180.56
 +--192.168.180.58


Wed Apr 19 16:22:40 2017 - [info] Checking replication health on 192.168.180.56..
Wed Apr 19 16:22:40 2017 - [info]  ok.
Wed Apr 19 16:22:40 2017 - [info] Checking replication health on 192.168.180.58..
Wed Apr 19 16:22:40 2017 - [info]  ok.
Wed Apr 19 16:22:40 2017 - [warning] master_ip_failover_script is not defined.
Wed Apr 19 16:22:40 2017 - [warning] shutdown_script is not defined.
Wed Apr 19 16:22:40 2017 - [info] Got exit code 0 (Not master dead).


MySQL Replication Health is OK.






至此Mysql MHA 搭建以及故障切换已经完成












雨花石~
关注
专栏目录
MySQL MHA
Drw_Dcm的博客
10-02 1723
MySQL MHA
Mysql数据库MHA故障切换
勤能补拙，知行合一
02-21 709
文章目录前言：MHA能在故障切换的过程中最大程度上保证数据的一致性，以达到真正意义上的高可用搭建Mysql MHA先关闭防火墙 文件防护（四个机器上都要操作）配置hosts文件（四个机器上都要操作）配置主从同步切换到主服务器切换到从服务器1切换到从服务器2设置命令软链接（一主两从都要设置）在所有数据库节点进行mysql授权切换到主服务器查看位置点在slave1 ，slave2执行同步操作在所有服务器上安装 MHA依赖的环境，首先安装epel源在 MHA manager 节点上安装 manager 组件在所有
mysql检查所作的修正_MySQL故障检测和修正的一般过程_MySQL
weixin_42548961的博客
01-20 265
表的故障检测和修正的一般过程如下：◆ 检查出错的表。如果该表检查通过，则完成任务，否则必须修复出错的数据库表。◆ 在开始修复之前对表文件进行拷贝，以保证数据的安全。◆ 开始修复数据库表。◆ 如果修复失败，从数据库的备份或更新日志中恢复数据。在使用myisamchk或isamchk检查或修复表之前，应该首先注意：◆ 建立数据库备份和使用更新日志，以防修复失败，丢失数据。◆ 仔细阅读本章内容以后再进行...
MySQL数据库——MHA高可用
最新发布
A6985HG的博客
09-10 1138
通过设置check_repl_delay=0，MHA触发切换在选择一个新的master的时候将会忽略复制延时，这个参数对于设置了candidate_master=1的主机非常有用，因为这个候选主在切换的过程中一定是新的master。当 master 出现故障时，它可以自动将最新数据的 slave 提升为新的 master，然后将所有其他的 slave 重新指向新的 master。：一旦确认主节点故障，MHA 会自动执行主从切换操作，选择一个健康的从节点，将其提升为新的主节点，以继续提供数据库服务。
MySQL高可用群集----MHA
weixin_45726050的博客
03-02 335
文章目录前言：一、MHA概述1.1 MHA简介1.2 MHA特点1.3 MHA作用二、MHA实验2.1 实验环境2.2 拓扑图2.3 实验目的2.4 案例配置思路2.5 实验配置2.5.1 定义节点服务器名称2.5.2 安装编译依赖环境2.5.3 手工编译安装MySQL5.62.5.4 修改MySQL配置文件2.2.5 新增数据库授权2.5.6 配置主从同步2.5.7 安装MHA2.5.8 配置无...
Mysql-MHA
guowei的博客
06-09 594
Mysql分布式系统： CAP标准：分布式系统最多可以实现其中的两点，常用的有AP和AC； C：一致性 A：可用性 P：网络分区（分裂）容错性 mysql的切分： 垂直切分：将一个数据库中的多张表分散到多个mysql服务器中（尽量让那些需要做联合查询的表位于同一...
MYSQL MHA
qq_34457768的博客
03-15 443
MySQL高可用之MHA的搭建  MySQL MHA架构介绍：MHA（Master High Availability）目前在MySQL高可用方面是一个相对成熟的解决方案，它由日本DeNA公司youshimaton（现就职于Facebook公司）开发，是一套优秀的作为MySQL高可用性环境下故障切换和主从提升的高可用软件。在MySQL故障切换过程中，MHA能做到在0~30秒之内自动完成数据库的...
mysql mha搭建以及故障切换.
07-07
mysql mha搭建以及故障切换.
mysql+MHA 主从复制 故障切换
mwx17630337353的博客
05-26 1045
超详细的奶妈级mysql高可用教程 无脑复制 即可！！！！
Mysql MHA搭建
u010648194的博客
04-06 2704
MHA是一种方便简单可靠的MySQL高可用架构 该软件由两部分组成：MHA Manager（管理节点）和MHA Node（数据节点） MHA Manager可以单独部署在一台独立的机器上管理多个master-slave集群，也可以部署在一台slave节点上。MHA Node运行在每台MySQL服务器上，MHA Manager会定时探测集群中的master节点，当master出现故障时，它可以自动将最新数据的slave提升为新的master，然后将所有其他的slave重新指向新的master。整个故障转移
Mysql MHA高可用配置及故障切换
weixin_48271370的博客
11-08 525
Mysql MHA高可用配置及故障切换
Mysql MHA
Buster_ZR的博客
08-13 759
拓扑： Server1 : master Server2 : slave Server3 : slave 首先进行主从复制的搭建 基于GTID的主从复制 进行主库的建立 配置文件的编辑： 启动mysql后查看主库的状态 mysql&amp;gt; show master status; +---------------+----------+--------------+...
mysql MHA
jc_benben的专栏
10-20 794
MHA（Master High Availability）目前在MySQL高可用方面是一个相对成熟的解决方案，它由日本DeNA公司youshimaton（现就职于Facebook公司）开发，是一套优秀的作为MySQL高可用性环境下故障切换和主从提升的高可用软件。在MySQL故障切换过程中，MHA能做到在0~30秒之内自动完成数据库的故障切换操作，并且在进行故障切换的过程中，MHA能在最大程度上保证...
MySQL之MHA
zmac111的博客
07-04 329
MySQL之MHA一、MHA概述1.定义2.MHA 的组成3.MHA 的特点二、 一、MHA概述 1.定义 MHA（MasterHigh Availability）是一套优秀的MySQL高可用环境下故障切换和主从复制的软件。 MHA 的出现就是解决MySQL 单点的问题。 MySQL故障切换过程中，MHA能做到0-30秒内自动完成故障切换操作。 MHA能在故障切换的过程中最大程度上保证数据的一致性，以达到真正意义上的高可用。 2.MHA 的组成 MHA Node（数据节点） MHA Node 运行在每台 M
MySQL-MHA搭建、故障测试
qq_41270538的博客
02-29 1020
MHA一主一从+vip架构搭建，MHA故障测试
MySQL MHA故障切换
2301_77081516的博客
03-15 847
目前 MySQL 已经成为市场上主流数据库之一，考虑到业务的重要性，MySQL 数据库单点问题已成为企业网站架构中最大的隐患。随着技术的发展，MHA 的出现就是解决 MySQL 单点的问题。另外随着企业数据量越来越庞大，数据库的压力又成为企业的另一个 瓶颈，MySQL 多主多从架构的出现可以减轻 MySQL 本身的压力。本章将主要介绍 MHA 的搭建和模拟 MySQL 故障自动切换的过程。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

雨花石~

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值