MySQL高可用之MHA的搭建

235次阅读

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

MySQL MHA 架构介绍：

MHA（Master High Availability）目前在 MySQL 高可用方面是一个相对成熟的解决方案，它由日本 DeNA 公司 youshimaton（现就职于 Facebook 公司）开发，是一套优秀的作为 MySQL 高可用性环境下故障切换和主从提升的高可用软件。在 MySQL 故障切换过程中，MHA 能做到在 0~30 秒之内自动完成数据库的故障切换操作，并且在进行故障切换的过程中，MHA 能在最大程度上保证数据的一致性，以达到真正意义上的高可用。

该软件由两部分组成：MHA Manager（管理节点）和 MHA Node（数据节点）。MHA Manager 可以单独部署在一台独立的机器上管理多个 master-slave 集群，也可以部署在一台 slave 节点上。MHA Node 运行在每台 MySQL 服务器上，MHA Manager 会定时探测集群中的 master 节点，当 master 出现故障时，它可以自动将最新数据的 slave 提升为新的 master，然后将所有其他的 slave 重新指向新的 master。整个故障转移过程对应用程序完全透明。

在 MHA 自动故障切换过程中，MHA 试图从宕机的主服务器上保存二进制日志，最大程度的保证数据的不丢失，但这并不总是可行的。例如，如果主服务器硬件故障或无法通过 ssh 访问，MHA 没法保存二进制日志，只进行故障转移而丢失了最新的数据。使用 MySQL 5.5 的半同步复制，可以大大降低数据丢失的风险。MHA 可以与半同步复制结合起来。如果只有一个 slave 已经收到了最新的二进制日志，MHA 可以将最新的二进制日志应用于其他所有的 slave 服务器上，因此可以保证所有节点的数据一致性。

目前 MHA 主要支持一主多从的架构，要搭建 MHA, 要求一个复制集群中必须最少有三台数据库服务器，一主二从，即一台充当 master，一台充当备用 master，另外一台充当从库，因为至少需要三台服务器，出于机器成本的考虑，淘宝也在该基础上进行了改造，目前淘宝 TMHA 已经支持一主一从。（出自：《深入浅出 MySQL(第二版)》）http://www.linuxidc.com/Linux/2016-05/130922.htm

架构图：

MySQL 高可用之 MHA 的搭建

MHA 工作原理总结为以下几条：

（1）从宕机崩溃的 master 保存二进制日志事件（binlog events）;

（2）识别含有最新更新的 slave;

（3）应用差异的中继日志(relay log) 到其他 slave;

（4）应用从 master 保存的二进制日志事件(binlog events);

（5）提升一个 slave 为新 master;

（6）使用其他的 slave 连接新的 master 进行复制。

官方介绍：https://code.google.com/p/mysql-master-ha/

实验环境：(CentOS6.2 MySQL 版本 5.5)

角色 ip 地址主机名 server_id 类型
Monitor host 192.168.2.131 server01 – 监控复制组
Master 192.168.2.128 server02 1 写入
Candicate master 192.168.2.129 server03 2 读
Slave 192.168.2.130 server04 3 读 server03 和 server04 是 server02 的 slave，复制环境搭建后面会简单演示, 其中 master 对外提供写服务，备选 master（实际的 slave，主机名 server03）提供读服务，slave 也提供相关的读服务，一旦 master 宕机，将会把备选 master 提升为新的 master，slave 指向新的 master

1、部署 MHA 过程：

方法一

在所有节点都要安装 MHA node 所需的 perl 模块（DBD:mysql），可以通过 yum 安装，如果没 epel 源, 先安装 epel 源，在如下：（温馨提示：系统时间一定要是最新的，否则安装时会出各种奇葩问题）

在 server02(192.168.2.128)操作：

192.168.2.128 [root ~]$ rpm -ivh http://dl.Fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
Retrieving http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
warning: /var/tmp/rpm-tmp.SAbcKl: Header V3 RSA/SHA256 Signature, key ID 0608b895: NOKEY
Preparing… ########################################### [100%]
1:epel-release ########################################### [100%]
192.168.2.128 [root ~]$ yum install perl-DBD-MySQL -y 在 server03(192.168.2.129)操作：

192.168.2.129 [root ~]$ rpm -ivh http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
Retrieving http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
warning: /var/tmp/rpm-tmp.gsdYwg: Header V3 RSA/SHA256 Signature, key ID 0608b895: NOKEY
Preparing… ########################################### [100%]
1:epel-release ########################################### [100%]
192.168.2.129 [root ~]$ yum install perl-DBD-MySQL -y 在 server04(192.168.2.130)操作：

192.168.2.130 [root ~]$ rpm -ivh http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
Retrieving http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
warning: /var/tmp/rpm-tmp.TUeiym: Header V3 RSA/SHA256 Signature, key ID 0608b895: NOKEY
Preparing… ########################################### [100%]
1:epel-release ########################################### [100%]
192.168.2.130 [root ~]$ yum install perl-DBD-MySQL -y（2）在所有的节点安装 MHA node：（下面以 server02 为例，记得 server03 和 server04 也一样的操作），MHA node 和 MHA Manager 都在要官网下载，

下载地址：https://code.google.com/p/mysql-master-ha/wiki/Downloads?tm=2（自备梯子）

192.168.2.128 [root ~]$ tar xf mha4mysql-node-0.56.tar.gz
192.168.2.128 [root ~]$ cd mha4mysql-node-0.56
192.168.2.128 [root mha4mysql-node-0.56]$ perl Makefile.PL
Can’t locate ExtUtils/MakeMaker.pm in @INC (@INC contains: inc /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at inc/Module/Install/Makefile.pm line 4.
192.168.2.128 [root mha4mysql-node-0.56]$ yum install -y perl-devel
192.168.2.128 [root mha4mysql-node-0.56]$ perl Makefile.PL
*** Module::AutoInstall version 1.03
*** Checking for Perl dependencies…
Can’t locate CPAN.pm in @INC (@INC contains: inc /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at inc/Module/AutoInstall.pm line 279.
192.168.2.128 [root mha4mysql-node-0.56]$ yum install -y perl-CPAN

192.168.2.128 [root mha4mysql-node-0.56]$ perl Makefile.PL
*** Module::AutoInstall version 1.03
*** Checking for Perl dependencies…
[Core Features]
– DBI …loaded. (1.609)
– DBD::mysql …loaded. (4.013)
*** Module::AutoInstall configuration finished.
Checking if your kit is complete…
Looks good
Writing Makefile for mha4mysql::node
192.168.2.128 [root mha4mysql-node-0.56]$ make && make install
方法二、

安装 MHA node 所需的 perl 模块（DBD:mysql）也可以通过脚本安装，安装脚本如下：（个人不建议用这样的方法安装，安装时间比较长，本人试过，蛋碎一地，太多问题了，要花时间去找资料，而且我们不能确定这些依赖带来的问题是否影响后面的使用）

192.168.2.128 [root ~]$ cat install.sh
#!/bin/bash
wget http://xrl.us/cpanm –no-check-certificate
mv cpanm /usr/bin
chmod 755 /usr/bin/cpanm
cat > /root/list << EOF
install DBD::mysql
EOF
for package in `cat /root/list`
do
cpanm $package
done
192.168.2.128 [root ~]$
再安装 MHA node 节点：

192.168.2.128 [root ~]$ tar xf mha4mysql-node-0.56.tar.gz
192.168.2.128 [root ~]$ cd mha4mysql-node-0.56
192.168.2.128 [root mha4mysql-node-0.56]$ perl Makefile.PL
192.168.2.128 [root mha4mysql-node-0.56]$ make && make install 安装完成后会在 /usr/local/bin 目录下生成以下脚本文件：

192.168.2.128 [root mha4mysql-node-0.56]$ cd /usr/local/bin/
192.168.2.128 [root bin]$ pwd
/usr/local/bin
192.168.2.128 [root bin]$ ll
总用量 40576
-r-xr-xr-x 1 root root 15498 1 月 18 11:02 apply_diff_relay_logs
-r-xr-xr-x 1 root root 4807 1 月 18 11:02 filter_mysqlbinlog
-r-xr-xr-x 1 root root 7401 1 月 18 11:02 purge_relay_logs
-r-xr-xr-x 1 root root 7263 1 月 18 11:02 save_binary_logs
192.168.2.128 [root bin]$
Node 脚本说明：(这些工具通常由 MHA Manager 的脚本触发，无需人为操作)

save_binary_logs // 保存和复制 master 的二进制日志
apply_diff_relay_logs // 识别差异的中继日志事件并将其差异的事件应用于其他的 slave
filter_mysqlbinlog // 去除不必要的 ROLLBACK 事件（MHA 已不再使用这个工具）
purge_relay_logs // 清除中继日志（不会阻塞 SQL 线程）

2. 安装 MHA Manager, 在 MHA Manager 的主机也是需要安装 MHA Node,MHA Manger 也依赖于 perl 模块

（1）在 MHA Manager 的主机也是需要安装 MHA Node, 所以以下的步骤和上面的操作一样，如下（在 server01 192.168.2.131 操作）：

192.168.2.131 [root ~]$ rpm -ivh http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
Retrieving http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
Preparing… ########################################### [100%]
package epel-release-6-8.noarch is already installed
192.168.2.131 [root ~]$ yum install perl-DBD-MySQL -y
192.168.2.131 [root ~]$ yum install -y perl-devel perl-CPAN
192.168.2.131 [root ~]$ tar xf mha4mysql-node-0.56.tar.gz
192.168.2.131 [root ~]$ cd mha4mysql-node-0.56
192.168.2.131 [root mha4mysql-node-0.56]$ perl Makefile.PL
192.168.2.131 [root mha4mysql-node-0.56]$ make && make install
（2）安装 MHA Manager。首先安装 MHA Manger 依赖的 perl 模块（我这里使用 yum 安装）：

192.168.2.131 [root mha4mysql-node-0.56]$ yum install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes - y 安装 MHA Manager 软件包：

192.168.2.131 [root mha4mysql-node-0.56]$ cd
192.168.2.131 [root ~]$ tar xf mha4mysql-manager-0.56.tar.gz
192.168.2.131 [root ~]$ cd mha4mysql-manager-0.56
192.168.2.131 [root mha4mysql-manager-0.56]$ perl Makefile.PL
*** Module::AutoInstall version 1.03
*** Checking for Perl dependencies…
[Core Features]
– DBI …loaded. (1.609)
– DBD::mysql …loaded. (4.013)
– Time::HiRes …loaded. (1.9721)
– Config::Tiny …loaded. (2.12)
– Log::Dispatch …loaded. (2.26)
– Parallel::ForkManager …loaded. (0.7.9)
– MHA::NodeConst …loaded. (0.56)
*** Module::AutoInstall configuration finished.
Writing Makefile for mha4mysql::manager
192.168.2.131 [root mha4mysql-manager-0.56]$ make && make install
安装完成后，在 /usr/local/bin 会产生相关的脚本：

192.168.2.131 [root bin]$ pwd
/usr/local/bin
192.168.2.131 [root bin]$ ll
总用量 37364
-r-xr-xr-x. 1 root root 15498 1 月 11 22:55 apply_diff_relay_logs
-r-xr-xr-x. 1 root root 4807 1 月 11 22:55 filter_mysqlbinlog
-r-xr-xr-x. 1 root root 1995 1 月 11 22:55 masterha_check_repl
-r-xr-xr-x. 1 root root 1779 1 月 11 22:55 masterha_check_ssh
-r-xr-xr-x. 1 root root 1865 1 月 11 22:55 masterha_check_status
-r-xr-xr-x. 1 root root 3201 1 月 11 22:55 masterha_conf_host
-r-xr-xr-x. 1 root root 2517 1 月 11 22:55 masterha_manager
-r-xr-xr-x. 1 root root 2165 1 月 11 22:55 masterha_master_monitor
-r-xr-xr-x. 1 root root 2373 1 月 11 22:55 masterha_master_switch
-r-xr-xr-x. 1 root root 3749 1 月 11 22:55 masterha_secondary_check
-r-xr-xr-x. 1 root root 1739 1 月 11 22:55 masterha_stop
-r-xr-xr-x. 1 root root 7401 1 月 11 22:55 purge_relay_logs
-r-xr-xr-x. 1 root root 7263 1 月 11 22:55 save_binary_logd
复制相关脚本到 /usr/local/bin 目录(软件包解压缩后就有了，不是必须，因为这些脚本不完整，需要自己修改，这是软件开发着留给我们自己发挥的, 如果开启下面的任何一个脚本对应的参数，而对应这里的脚本又没有修改，则会抛错，自己被坑的很惨)

192.168.2.131 [root scripts]$ pwd
/root/mha4mysql-manager-0.56/samples/scripts
192.168.2.131 [root scripts]$ ll
总用量 32
-rwxr-xr-x. 1 root root 3443 1 月 8 2012 master_ip_failover // 自动切换时 vip 管理的脚本，不是必须，如果我们使用 keepalived 的，我们可以自己编写脚本完成对 vip 的管理，比如监控 mysql，如果 mysql 异常，我们停止 keepalived 就行，这样 vip 就会自动漂移
-rwxr-xr-x. 1 root root 9186 1 月 8 2012 master_ip_online_change // 在线切换时 vip 的管理，不是必须，同样可以可以自行编写简单的 shell 完成
-rwxr-xr-x. 1 root root 11867 1 月 8 2012 power_manager // 故障发生后关闭主机的脚本，不是必须
-rwxr-xr-x. 1 root root 1360 1 月 8 2012 send_report // 因故障切换后发送报警的脚本，不是必须，可自行编写简单的 shell 完成

192.168.2.131 [root scripts]$ cp * /usr/local/bin/

3. 配置 SSH 登录无密码验证（使用 key 登录，工作中常用，最好不要禁掉密码登录，如果禁了，可能会有问题）

在 server02 192.168.2.131 操作（Monitor）：

192.168.2.131 [root ~]$ ssh-keygen -t rsa
192.168.2.131 [root ~]$ ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.2.128
192.168.2.131 [root ~]$ ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.2.129
192.168.2.131 [root ~]$ ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.2.130 在 server02 192.168.2.128 操作（Master）：

192.168.2.128 [root ~]$ ssh-keygen -t rsa
192.168.2.128 [root ~]$ ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.2.129
192.168.2.128 [root ~]$ ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.2.130 在 server03 192.168.2.129 操作(slave)：

192.168.2.129 [root ~]$ ssh-keygen -t rsa
192.168.2.129 [root ~]$ ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.2.128
192.168.2.129 [root ~]$ ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.2.130 在 server04 192.168.2.130 操作(slave)：

192.168.2.130 [root ~]$ ssh-keygen -t rsa
192.168.2.130 [root ~]$ ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.2.128
192.168.2.130 [root ~]$ ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.2.129

4. 搭建主从复制环境

注意：binlog-do-db 和 replicate-ignore-db 设置必须相同。MHA 在启动时候会检测过滤规则，如果过滤规则不同，MHA 不启动监控和故障转移。

（1）在 Master 192.168.2.128(server02)上备份一份完整的数据：

192.168.2.128 [root ~]$ mysqldump -uroot -p123456 –master-data=2 –single-transaction -R –triggers -A > all.sql 其中 –master-data= 2 代表备份时刻记录 master 的 Binlog 位置和 Position，–single-transaction 意思是获取一致性快照，- R 意思是备份存储过程和函数，–triggres 的意思是备份触发器，- A 代表备份所有的库。更多信息请自行 mysqldump –help 查看。

（2）在 Master 192.168.2.128(server02)上创建复制用户:

mysql> grant replication slave on *.* to ‘repl’@’192.168.2.%’ identified by ‘123456’;
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)（3）查看主库备份时的 binlog 名称和位置，MASTER_LOG_FILE 和 MASTER_LOG_POS：

192.168.2.128 [root ~]$ head -n 30 all.sql | grep ‘CHANGE MASTER TO’
— CHANGE MASTER TO MASTER_LOG_FILE=’mysql-bin.000004′, MASTER_LOG_POS=245;（4）把备份复制到 192.168.2.129 和 192.168.2.130

192.168.2.128 [root ~]$ scp all.sql 192.168.2.129:/root/
all.sql 100% 500KB 500.5KB/s 00:00
192.168.2.128 [root ~]$ scp all.sql 192.168.2.130:/root/
all.sql（5）分别在两台服务器上导入备份，执行复制相关命令

在 slave 主机 server03 192.168.2.129 上操作：

192.168.2.129 [root ~]$ mysql -uroot -p123456 < ./all.sql
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)

mysql> CHANGE MASTER TO MASTER_HOST=’192.168.2.128′,MASTER_USER=’repl’, MASTER_PASSWORD=’123456′,MASTER_LOG_FILE=’mysql-bin.000004′,MASTER_LOG_POS=245;
Query OK, 0 rows affected (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.2.128
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 472
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 480
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

在 slave master04 192.168.2.130 上操作，导入备份，执行同步操作，如下：

192.168.2.130 [root ~]$ mysql -uroot -p123456 < ./all.sql
mysql> slave stop;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CHANGE MASTER TO MASTER_HOST=’192.168.2.128′,MASTER_USER=’repl’, MASTER_PASSWORD=’123456′,MASTER_LOG_FILE=’mysql-bin.000004′,MASTER_LOG_POS=245;
Query OK, 0 rows affected (0.04 sec)

mysql> slave start;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.2.128
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 472
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 480
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
（6）两台 slave 服务器设置 read_only（从库对外提供读服务，之所以没有写进配置文件，是因为随时 slave 会提升为 master）

192.168.2.129 [root ~]$ mysql -uroot -p123456 -e “set global read_only=1”
192.168.2.130 [root ~]$ mysql -uroot -p123456 -e “set global read_only=1″（7）创建监控用户（在 master 上执行，也就是 server02 192.168.2.128）：

mysql> grant all privileges on *.* to ‘root’@’192.168.2.%’ identified by ‘123456’;
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql>
到这里整个集群环境已经搭建完毕，剩下的就是配置 MHA 软件了。

5. 配置 MHA

（1）创建 MHA 的工作目录，并且创建相关配置文件（在软件包解压后的目录里面有样例配置文件）。

192.168.2.131 [root ~]$ mkdir -p /etc/masterha
192.168.2.131 [root ~]$ cp mha4mysql-manager-0.56/samples/conf/app1.cnf /etc/masterha/ 修改 app1.cnf 配置文件，修改后的文件内容如下（注意，配置文件中的注释需要去掉，我这里是为了解释清楚）：

[root@192.168.2.131 ~]# cat /etc/masterha/app1.cnf
[server default]
manager_workdir=/var/log/masterha/app1.log // 设置 manager 的工作目录
manager_log=/var/log/masterha/app1/manager.log // 设置 manager 的日志
master_binlog_dir=/data/mysql // 设置 master 保存 binlog 的位置，以便 MHA 可以找到 master 的日志，我这里的也就是 mysql 的数据目录
master_ip_failover_script= /usr/local/bin/master_ip_failover // 设置自动 failover 时候的切换脚本
master_ip_online_change_script= /usr/local/bin/master_ip_online_change // 设置手动切换时候的切换脚本
password=123456 // 设置 mysql 中 root 用户的密码，这个密码是前文中创建监控用户的那个密码
user=root 设置监控用户 root
ping_interval=1 // 设置监控主库，发送 ping 包的时间间隔，默认是 3 秒，尝试三次没有回应的时候自动进行 railover
remote_workdir=/tmp // 设置远端 mysql 在发生切换时 binlog 的保存位置
repl_password=123456 // 设置复制用户的密码
repl_user=repl // 设置复制环境中的复制用户名
report_script=/usr/local/bin/send_report // 设置发生切换后发送的报警的脚本
secondary_check_script= /usr/local/bin/masterha_secondary_check -s server03 -s server02 –user=root –master_host=server02 –master_ip=192.168.2.128 –master_port=3306 // 一旦 MHA 到 server02 的监控之间出现问题，MHA Manager 将会尝试从 server03 登录到 server02
shutdown_script=”” // 设置故障发生后关闭故障主机脚本（该脚本的主要作用是关闭主机放在发生脑裂, 这里没有使用）
ssh_user=root // 设置 ssh 的登录用户名

[server1]
hostname=192.168.2.128
port=3306

[server2]
hostname=192.168.2.129
port=3306
candidate_master=1
// 设置为候选 master，如果设置该参数以后，发生主从切换以后将会将此从库提升为主库，即使这个主库不是集群中事件最新的 slave
check_repl_delay=0 // 默认情况下如果一个 slave 落后 master 100M 的 relay logs 的话，MHA 将不会选择该 slave 作为一个新的 master，因为对于这个 slave 的恢复需要花费很长时间，通过设置 check_repl_delay=0,MHA 触发切换在选择一个新的 master 的时候将会忽略复制延时，这个参数对于设置了 candidate_master= 1 的主机非常有用，因为这个候选主在切换的过程中一定是新的 master

[server3]
hostname=192.168.2.130
port=3306
(2)设置 relay log 的清除方式（在每个 slave 节点上）：

在 slave master03 192.168.2.129 操作：

192.168.2.129 [root ~]$ mysql -uroot -p123456 -e “set global relay_log_purge=0″ 在 slave master04 192.168.2.130 操作：

192.168.2.130 [root ~]$ mysql -uroot -p123456 -e “set global relay_log_purge=0″ 注意：

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

设置定期清理 relay 脚本（两台 slave 服务器）：

在 slave master03 192.168.2.129 操作：

192.168.2.129 [root ~]$ cat purge_relay_log.sh
#!/bin/bash
user=root
passwd=123456
port=3306
log_dir=’/data/masterha/log’
work_dir=’/data’
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

192.168.2.129 [root ~]$ crontab -l
0 4 * * * /bin/bash /root/purge_relay_log.sh
在 slave master03 192.168.2.130 操作跟上面是一样的，这里不演示了。

参数说明：

–user mysql // 用户名
–password mysql // 密码
–port // 端口号
–workdir // 指定创建 relay log 的硬链接的位置，默认是 /var/tmp，由于系统不同分区创建硬链接文件会失败，故需要执行硬链接具体位置，成功执行脚本后，硬链接的中继日志文件被删除
–disable_relay_log_purge // 默认情况下，如果 relay_log_purge=1，脚本会什么都不清理，自动退出，通过设定这个参数，当 relay_log_purge= 1 的情况下会将 relay_log_purge 设置为 0。清理 relay log 之后，最后将参数设置为 OFF。
purge_relay_logs 脚本删除中继日志不会阻塞 SQL 线程。下面我们手动执行看看什么情况:

192.168.2.129 [root ~]$ purge_relay_logs –user=root –password=123456 –port=3306 -disable_relay_log_purge –workdir=/data/
2015-01-18 12:30:51: purge_relay_logs script started.
Found relay_log.info: /data/mysql/relay-log.info
Removing hard linked relay log files localhost-relay-bin* under /data/.. done.
Current relay log file: /data/mysql/localhost-relay-bin.000002
Archiving unused relay log files (up to /data/mysql/localhost-relay-bin.000001) …
Creating hard link for /data/mysql/localhost-relay-bin.000001 under /data//localhost-relay-bin.000001 .. ok.
Creating hard links for unused relay log files completed.
Executing SET GLOBAL relay_log_purge=1; FLUSH LOGS; sleeping a few seconds so that SQL thread can delete older relay log files (if it keeps up); SET GLOBAL relay_log_purge=0; .. ok.
Removing hard linked relay log files localhost-relay-bin* under /data/.. done.
2015-01-18 12:30:54: All relay log purging operations succeeded.

6. 检查 SSH 配置(server01 192.168.2.131 Monitor 监控节点上操作)，如下：

192.168.2.131 [root ~]$ masterha_check_ssh –conf=/etc/masterha/app1.cnf
Sun Jan 18 12:31:48 2015 – [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sun Jan 18 12:31:48 2015 – [info] Reading application default configurations from /etc/masterha/app1.cnf..
Sun Jan 18 12:31:48 2015 – [info] Reading server configurations from /etc/masterha/app1.cnf..
Sun Jan 18 12:31:48 2015 – [info] Starting SSH connection tests..
Sun Jan 18 12:31:49 2015 – [debug]
Sun Jan 18 12:31:48 2015 – [debug] Connecting via SSH from root@192.168.2.128(192.168.2.128:22) to root@192.168.2.129(192.168.2.129:22)..
Sun Jan 18 12:31:49 2015 – [debug] ok.
Sun Jan 18 12:31:49 2015 – [debug] Connecting via SSH from root@192.168.2.128(192.168.2.128:22) to root@192.168.2.130(192.168.2.130:22)..
Sun Jan 18 12:31:49 2015 – [debug] ok.
Sun Jan 18 12:31:50 2015 – [debug]
Sun Jan 18 12:31:49 2015 – [debug] Connecting via SSH from root@192.168.2.129(192.168.2.129:22) to root@192.168.2.128(192.168.2.128:22)..
Sun Jan 18 12:31:49 2015 – [debug] ok.
Sun Jan 18 12:31:49 2015 – [debug] Connecting via SSH from root@192.168.2.129(192.168.2.129:22) to root@192.168.2.130(192.168.2.130:22)..
Sun Jan 18 12:31:50 2015 – [debug] ok.
Sun Jan 18 12:31:50 2015 – [debug]
Sun Jan 18 12:31:49 2015 – [debug] Connecting via SSH from root@192.168.2.130(192.168.2.130:22) to root@192.168.2.128(192.168.2.128:22)..
Sun Jan 18 12:31:50 2015 – [debug] ok.
Sun Jan 18 12:31:50 2015 – [debug] Connecting via SSH from root@192.168.2.130(192.168.2.130:22) to root@192.168.2.129(192.168.2.129:22)..
Sun Jan 18 12:31:50 2015 – [debug] ok.
Sun Jan 18 12:31:50 2015 – [info] All SSH connection tests passed successfully.
可以看见各个节点 ssh 验证都是 ok 的。

7. 检查整个复制环境状况(server01 192.168.2.131 Monitor 监控节点上操作)，如下：

192.168.2.131 [root ~]$ masterha_check_repl –conf=/etc/masterha/app1.cnf
Sun Jan 18 13:08:11 2015 – [info] Executing command: save_binary_logs –command=test –start_pos=4 –binlog_dir=/data/mysql –output_file=/tmp/save_binary_logs_test –manager_version=0.56 –start_file=mysql-bin.000004
Sun Jan 18 13:08:11 2015 – [info] Connecting to root@192.168.2.128(192.168.2.128)..
Creating /tmp if not exists.. ok.
Checking output directory is accessible or not..
ok.
Binlog found at /data/mysql, up to mysql-bin.000004
Sun Jan 18 13:08:11 2015 – [info] Master setting check done.
Sun Jan 18 13:08:11 2015 – [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..
Sun Jan 18 13:08:11 2015 – [info] Executing command : apply_diff_relay_logs –command=test –slave_user=root –slave_host=192.168.2.129 –slave_ip=192.168.2.129 –slave_port=3306 –workdir=/tmp –target_version=5.5.60-log –manager_version=0.56 –relay_log_info=/data/mysql/relay-log.info –relay_dir=/data/mysql/ –slave_pass=xxx
Sun Jan 18 13:08:11 2015 – [info] Connecting to root@192.168.2.129(192.168.2.129:22)..
Can’t exec “mysqlbinlog”: 没有那个文件或目录 at /usr/local/share/perl5/MHA/BinlogManager.pm line 99.
mysqlbinlog version not found!
at /usr/local/bin/apply_diff_relay_logs line 463
Sun Jan 18 13:08:12 2015 – [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln193] Slaves settings check failed!
Sun Jan 18 13:08:12 2015 – [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln372] Slave configuration failed.
Sun Jan 18 13:08:12 2015 – [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln383] Error happend on checking configurations. at /usr/local/bin/masterha_check_repl line 48
Sun Jan 18 13:08:12 2015 – [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln478] Error happened on monitoring servers.
Sun Jan 18 13:08:12 2015 – [info] Got exit code 1 (Not master dead).

MySQL Replication Health is NOT OK!
192.168.2.131 [root ~]$
如果发现如下错误：

Can’t exec “mysqlbinlog”: No such file or directory at /usr/local/share/perl5/MHA/BinlogManager.pm line 99.
mysqlbinlog version not found!Testing mysql connection and privileges..sh: mysql: command not found
mysql command failed with rc 127:0! 可以通过以下方法解决（在所有节点上执行）：

192.168.2.128 [root ~]$ ln -s /usr/local/mysql/bin/mysqlbinlog /usr/local/bin/mysqlbinlog
192.168.2.128 [root ~]$ ln -s /usr/local/mysql/bin/mysql /usr/local/bin/mysql

192.168.2.129 [root ~]$ ln -s /usr/local/mysql/bin/mysqlbinlog /usr/local/bin/mysqlbinlog
192.168.2.129 [root ~]$ ln -s /usr/local/mysql/bin/mysql /usr/local/bin/mysql

192.168.2.130 [root ~]$ ln -s /usr/local/mysql/bin/mysqlbinlog /usr/local/bin/mysqlbinlog
192.168.2.130 [root ~]$ ln -s /usr/local/mysql/bin/mysql /usr/local/bin/mysql
再进行检查

192.168.2.131 [root ~]$ masterha_check_repl –conf=/etc/masterha/app1.cnf
Sun Jan 18 13:19:41 2015 – [info] Checking replication health on 192.168.2.129..
Sun Jan 18 13:19:41 2015 – [info] ok.
Sun Jan 18 13:19:41 2015 – [info] Checking replication health on 192.168.2.130..
Sun Jan 18 13:19:41 2015 – [info] ok.
Sun Jan 18 13:19:41 2015 – [info] Checking master_ip_failover_script status:
Sun Jan 18 13:19:41 2015 – [info] /usr/local/bin/master_ip_failover –command=status –ssh_user=root –orig_master_host=192.168.2.128 –orig_master_ip=192.168.2.128 –orig_master_port=3306
Bareword “FIXME_xxx” not allowed while “strict subs” in use at /usr/local/bin/master_ip_failover line 88.
Execution of /usr/local/bin/master_ip_failover aborted due to compilation errors.
Sun Jan 18 13:19:41 2015 – [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln214] Failed to get master_ip_failover_script status with return code 255:0.
Sun Jan 18 13:19:41 2015 – [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln383] Error happend on checking configurations. at /usr/local/bin/masterha_check_repl line 48
Sun Jan 18 13:19:41 2015 – [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln478] Error happened on monitoring servers.
Sun Jan 18 13:19:41 2015 – [info] Got exit code 1 (Not master dead).

MySQL Replication Health is NOT OK!
192.168.2.131 [root ~]$
还是报错，纠结 N 久，才发现原因是：原来 Failover 两种方式：一种是虚拟 IP 地址，一种是全局配置文件。MHA 并没有限定使用哪一种方式，而是让用户自己选择，虚拟 IP 地址的方式会牵扯到其它的软件, 比如 keepalive 软件，而且还要修改脚本 master_ip_failover。

所以先暂时注释 master_ip_failover_script= /usr/local/bin/master_ip_failover 这个选项。后面引入 keepalived 后和修改该脚本以后再开启该选项

192.168.2.131 [root ~]$ grep master_ip_failover /etc/masterha/app1.cnf
#master_ip_failover_script= /usr/local/bin/master_ip_failover 再次进行状态查看：

192.168.2.131 [root ~]$ masterha_check_repl –conf=/etc/masterha/app1.cnf
Sun Jan 18 13:23:57 2015 – [info] Slaves settings check done.
Sun Jan 18 13:23:57 2015 – [info]
192.168.2.128 (current master)
+–192.168.2.129
+–192.168.2.130

Sun Jan 18 13:23:57 2015 – [info] Checking replication health on 192.168.2.129..
Sun Jan 18 13:23:57 2015 – [info] ok.
Sun Jan 18 13:23:57 2015 – [info] Checking replication health on 192.168.2.130..
Sun Jan 18 13:23:57 2015 – [info] ok.
Sun Jan 18 13:23:57 2015 – [warning] master_ip_failover_script is not defined.
Sun Jan 18 13:23:57 2015 – [warning] shutdown_script is not defined.
Sun Jan 18 13:23:57 2015 – [info] Got exit code 0 (Not master dead).

MySQL Replication Health is OK.
192.168.2.131 [root ~]$
已经没有明显报错，只有两个警告而已，复制也显示正常了, 哈哈，没报错了，先乐一会 ^0^

8. 检查 MHA Manager 的状态
通过 master_check_status 脚本查看 Manager 的状态：

192.168.2.131 [root ~]$ masterha_check_status –conf=/etc/masterha/app1.cnf
app1 is stopped(2:NOT_RUNNING). 注意：如果正常，会显示 ”PING_OK”，否则会显示 ”NOT_RUNNING”，这代表 MHA 监控没有开启。

9. 开启 MHA Manager 监控 (server01 192.168.2.131 操作) 如下：

192.168.2.131 [root ~]$ mkdir -p /var/log/masterha/app1/
192.168.2.131 [root ~]$ nohup masterha_manager –conf=/etc/masterha/app1.cnf –remove_dead_master_conf –ignore_last_failover < /dev/null > /var/log/masterha/app1/manager.log 2>&1 &
[1] 13014
192.168.2.131 [root ~]$ 启动参数说明：

–remove_dead_master_conf // 该参数代表当发生主从切换后，老的主库的 ip 将会从配置文件中移除。

–manger_log // 日志存放位置

–ignore_last_failover // 在缺省情况下，如果 MHA 检测到连续发生宕机，且两次宕机间隔不足 8 小时的话，则不会进行 Failover，之所以这样限制是为了避免 ping-pong 效应。该参数代表忽略上次 MHA 触发切换产生的文件，默认情况下，MHA 发生切换后会在日志目录，也就是上面我设置的 /data 产生 app1.failover.complete 文件，下次再次切换的时候如果发现该目录下存在该文件将不允许触发切换，除非在第一次切换后收到删除该文件，为了方便，这里设置为 –ignore_last_failover。
查看 MHA Manager 监控是否正常：

192.168.2.131 [root ~]$ masterha_check_status –conf=/etc/masterha/app1.cnf
app1 (pid:13014) is running(0:PING_OK), master:192.168.2.128 可以看见已经在监控了，而且 master 的主机为 192.168.2.128

10. 查看启动日志 (server01 192.168.2.131 操作) 如下：

192.168.2.131 [root ~]$ tail -n20 /var/log/masterha/app1/manager.log
Sun Jan 18 13:27:22 2015 – [info] Connecting to root@192.168.2.130(192.168.2.130:22)..
Checking slave recovery environment settings..
Opening /data/mysql/relay-log.info … ok.
Relay log found at /data/mysql, up to localhost-relay-bin.000002
Temporary relay log file is /data/mysql/localhost-relay-bin.000002
Testing mysql connection and privileges.. done.
Testing mysqlbinlog output.. done.
Cleaning up test file(s).. done.
Sun Jan 18 13:27:22 2015 – [info] Slaves settings check done.
Sun Jan 18 13:27:22 2015 – [info]
192.168.2.128 (current master)
+–192.168.2.129
+–192.168.2.130

Sun Jan 18 13:27:22 2015 – [warning] master_ip_failover_script is not defined.
Sun Jan 18 13:27:22 2015 – [warning] shutdown_script is not defined.
Sun Jan 18 13:27:22 2015 – [info] Set master ping interval 1 seconds.
Sun Jan 18 13:27:22 2015 – [info] Set secondary check script: /usr/local/bin/masterha_secondary_check -s server03 -s server02 –user=root –master_host=server02 –master_ip=192.168.2.128 –master_port=3306
Sun Jan 18 13:27:22 2015 – [info] Starting ping health check on 192.168.2.128(192.168.2.128:3306)..
Sun Jan 18 13:27:22 2015 – [info] Ping(SELECT) succeeded, waiting until MySQL doesn’t respond..
192.168.2.131 [root ~]$
其中 ”Ping(SELECT) succeeded, waiting until MySQL doesn’t respond..” 说明整个系统已经开始监控了。

11. 关闭 MHA Manage 监控 (server01 192.168.2.131 操作) 如下：

关闭很简单，使用 masterha_stop 命令完成。（只是演示关闭，在测试中，必须是开启的状态，如果关了，在测试的时候务必记得开启）

192.168.2.131 [root ~]$ masterha_stop –conf=/etc/masterha/app1.cnf
Stopped app1 successfully.
[1]+ Exit 1 nohup masterha_manager –conf=/etc/masterha/app1.cnf –remove_dead_master_conf –ignore_last_failover < /dev/null > /var/log/masterha/app1/manager.log 2>&1
192.168.2.131 [root ~]$

12. 配置 VIP
vip 配置可以采用两种方式，一种通过 keepalived 的方式管理虚拟 ip 的浮动；另外一种通过脚本方式启动虚拟 ip 的方式（即不需要 keepalived 或者 heartbeat 类似的软件）。
下面先介绍通过安装 keepalived 来管理虚拟 IP 的浮动：

（1）下载软件进行并进行安装（两台 master，准确的说一台是 master，另外一台是备选 master，在没有切换以前是 slave）server02 192.168.2.128 操作：

192.168.2.128 [root ~]$ wget http://www.keepalived.org/software/keepalived-1.2.12.tar.gz
192.168.2.128 [root ~]$ tar xf keepalived-1.2.12.tar.gz
192.168.2.128 [root ~]$ cd keepalived-1.2.12
192.168.2.128 [root keepalived-1.2.12]$ ./configure –prefix=/usr/local/keepalived
192.168.2.128 [root keepalived-1.2.12]$ make && make install
192.168.2.128 [root keepalived-1.2.12]$ cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/init.d/
192.168.2.128 [root keepalived-1.2.12]$ cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
192.168.2.128 [root keepalived-1.2.12]$ mkdir /etc/keepalived
192.168.2.128 [root keepalived-1.2.12]$ cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/
192.168.2.128 [root keepalived-1.2.12]$ cp /usr/local/keepalived/sbin/keepalived /usr/sbin/
server03 192.168.2.129 也要执行上面的操作，安装是一样的，配置文件不一样，这里不演示，自已安装哈

（2）配置 keepalived 的配置文件，在 master 上配置（server02 192.168.2.128）操作如下:

192.168.2.128 [root keepalived-1.2.12]$ cat /etc/keepalived/keepalived.conf
! Configuration File for keepalived

global_defs {
notification_email {
saltstack@163.com
}
notification_email_from dba@dbserver.com
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id MySQL-HA
}

vrrp_instance VI_1 {
state BACKUP
interface eth0
virtual_router_id 51
priority 150
advert_int 1
nopreempt

authentication {
auth_type PASS
auth_pass 1111
}

virtual_ipaddress {
192.168.2.88
}
}
192.168.2.128 [root keepalived-1.2.12]$
其中 router_id MySQL HA 表示设定 keepalived 组的名称，将 192.168.2.88 这个虚拟 ip 绑定到该主机的 eth0 网卡上，并且设置了状态为 backup 模式，将 keepalived 的模式设置为非抢占模式（nopreempt），priority 150 表示设置的优先级为 150。下面的配置略有不同，但是都是一个意思。（还有一个细节要注意的，要看清楚自己的网卡是 eth0 做模拟 VIP, 还是 eth1）

在候选 master 上配置（server03 192.168.2.129）操作如下：

192.168.2.129 [root keepalived-1.2.12]$ cat /etc/keepalived/keepalived.conf
! Configuration File for keepalived

global_defs {
notification_email {
saltstack@163.com
}
notification_email_from dba@dbserver.com
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id MySQL-HA
}

vrrp_instance VI_1 {
state BACKUP
interface eth0
virtual_router_id 51
priority 120
advert_int 1
nopreempt

authentication {
auth_type PASS
auth_pass 1111
}

virtual_ipaddress {
192.168.2.88
}
}
192.168.2.129 [root keepalived-1.2.12]$
（3）启动 keepalived 服务，在 master 上启动并查看日志（server02 192.168.2.128）操作如下:

192.168.2.128 [root keepalived-1.2.12]$ /etc/init.d/keepalived start
正在启动 keepalived：[确定]
192.168.2.128 [root keepalived-1.2.12]$ tail -f /var/log/messages
Jan 18 13:47:20 localhost Keepalived_healthcheckers[4638]: Registering Kernel netlink reflector
Jan 18 13:47:20 localhost Keepalived_healthcheckers[4638]: Registering Kernel netlink command channel
Jan 18 13:47:20 localhost Keepalived_healthcheckers[4638]: Opening file ‘/etc/keepalived/keepalived.conf’.
Jan 18 13:47:20 localhost Keepalived_healthcheckers[4638]: Configuration is using : 7105 Bytes
Jan 18 13:47:20 localhost Keepalived_healthcheckers[4638]: Using LinkWatch kernel netlink reflector…
Jan 18 13:47:23 localhost Keepalived_vrrp[4639]: VRRP_Instance(VI_1) Transition to MASTER STATE
Jan 18 13:47:24 localhost Keepalived_vrrp[4639]: VRRP_Instance(VI_1) Entering MASTER STATE
Jan 18 13:47:24 localhost Keepalived_vrrp[4639]: VRRP_Instance(VI_1) setting protocol VIPs.
Jan 18 13:47:24 localhost Keepalived_vrrp[4639]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth0 for 192.168.2.88
Jan 18 13:47:24 localhost Keepalived_healthcheckers[4638]: Netlink reflector reports IP 192.168.2.88 added
Jan 18 13:47:29 localhost Keepalived_vrrp[4639]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth0 for 192.168.2.88
启动候选 master 的 keepalived（server03 192.168.2.129）操作如下：

192.168.2.129 [root keepalived-1.2.12]$ /etc/init.d/keepalived start
正在启动 keepalived：[确定]
192.168.2.129 [root keepalived-1.2.12]$
192.168.2.129 [root keepalived-1.2.12]$ tail -f /var/log/messages
Jan 18 13:52:31 localhost Keepalived_vrrp[4990]: Registering gratuitous ARP shared channel
Jan 18 13:52:31 localhost Keepalived_healthcheckers[4989]: Registering Kernel netlink command channel
Jan 18 13:52:31 localhost Keepalived_vrrp[4990]: Opening file ‘/etc/keepalived/keepalived.conf’.
Jan 18 13:52:31 localhost Keepalived_healthcheckers[4989]: Opening file ‘/etc/keepalived/keepalived.conf’.
Jan 18 13:52:31 localhost Keepalived_healthcheckers[4989]: Configuration is using : 7105 Bytes
Jan 18 13:52:31 localhost Keepalived_healthcheckers[4989]: Using LinkWatch kernel netlink reflector…
Jan 18 13:52:31 localhost Keepalived_vrrp[4990]: Configuration is using : 62850 Bytes
Jan 18 13:52:31 localhost Keepalived_vrrp[4990]: Using LinkWatch kernel netlink reflector…
Jan 18 13:52:31 localhost Keepalived_vrrp[4990]: VRRP_Instance(VI_1) Entering BACKUP STATE
Jan 18 13:52:31 localhost Keepalived_vrrp[4990]: VRRP sockpool: [ifindex(2), proto(112), unicast(0), fd(10,11)]
Jan 18 13:52:34 localhost Keepalived_vrrp[4990]: VRRP_Instance(VI_1) Transition to MASTER STATE
Jan 18 13:52:35 localhost Keepalived_vrrp[4990]: VRRP_Instance(VI_1) Entering MASTER STATE
Jan 18 13:52:35 localhost Keepalived_vrrp[4990]: VRRP_Instance(VI_1) setting protocol VIPs.
Jan 18 13:52:35 localhost Keepalived_vrrp[4990]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth0 for 192.168.2.88
Jan 18 13:52:35 localhost Keepalived_healthcheckers[4989]: Netlink reflector reports IP 192.168.2.88 added
Jan 18 13:52:40 localhost Keepalived_vrrp[4990]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth0 for 192.168.2.88
（4）查看绑定情况

192.168.2.128 [root keepalived-1.2.12]$ ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueue state UNKNOWN
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 00:0c:29:86:dc:2a brd ff:ff:ff:ff:ff:ff
inet 192.168.2.128/24 brd 192.168.2.255 scope global eth0
inet 192.168.2.88/32 scope global eth0
inet6 fe80::20c:29ff:fe86:dc2a/64 scope link
valid_lft forever preferred_lft forever
192.168.2.128 [root keepalived-1.2.12]$
发现已经将虚拟 IP 192.168.2.88 绑定了 master02 192.168.2.128 的网卡 eth0 上了

从上面的信息可以看到 keepalived 已经配置成功。

注意：
上面两台服务器的 keepalived 都设置为了 BACKUP 模式，在 keepalived 中 2 种模式，分别是 master->backup 模式和 backup->backup 模式。这两种模式有很大区别。在 master->backup 模式下，一旦主库宕机，虚拟 ip 会自动漂移到从库，当主库修复后，keepalived 启动后，还会把虚拟 ip 抢占过来，即使设置了非抢占模式（nopreempt）抢占 ip 的动作也会发生。在 backup->backup 模式下，当主库宕机后虚拟 ip 会自动漂移到从库上，当原主库恢复和 keepalived 服务启动后，并不会抢占新主的虚拟 ip，即使是优先级高于从库的优先级别，也不会发生抢占。为了减少 ip 漂移次数，通常是把修复好的主库当做新的备库。

（5）MHA 引入 keepalived（MySQL 服务进程挂掉时通过 MHA 停止 keepalived）:

要想把 keepalived 服务引入 MHA，我们只需要修改切换是触发的脚本文件 master_ip_failover 即可，在该脚本中添加在 master 发生宕机时对 keepalived 的处理。

1、编辑脚本 /usr/local/bin/master_ip_failover，修改后如下（server01 192.168.2.131）操作：

192.168.2.131 [root ~]$ cat /usr/local/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.2.88’;
my $ssh_start_vip = “/etc/init.d/keepalived start”;
my $ssh_stop_vip = “/etc/init.d/keepalived stop”;

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”) {

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”) {

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”;
exit 0;
}
else {
&usage();
exit 1;
}
}
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”;
}
192.168.2.131 [root ~]$
把 #master_ip_failover_script= /usr/local/bin/master_ip_failover 打开

192.168.2.131 [root ~]$ grep ‘master_ip_failover_script’ /etc/masterha/app1.cnf
master_ip_failover_script= /usr/local/bin/master_ip_failover 执行检测：

192.168.2.131 [root ~]$ masterha_check_repl –conf=/etc/masterha/app1.cnf
Sun Jan 18 14:00:43 2015 – [info] Slaves settings check done.
Sun Jan 18 14:00:43 2015 – [info]
192.168.2.128 (current master)
+–192.168.2.129
+–192.168.2.130

Sun Jan 18 14:00:43 2015 – [info] Checking replication health on 192.168.2.129..
Sun Jan 18 14:00:43 2015 – [info] ok.
Sun Jan 18 14:00:43 2015 – [info] Checking replication health on 192.168.2.130..
Sun Jan 18 14:00:43 2015 – [info] ok.
Sun Jan 18 14:00:43 2015 – [info] Checking master_ip_failover_script status:
Sun Jan 18 14:00:43 2015 – [info] /usr/local/bin/master_ip_failover –command=status –ssh_user=root –orig_master_host=192.168.2.128 –orig_master_ip=192.168.2.128 –orig_master_port=3306
Unmatched right curly bracket at /usr/local/bin/master_ip_failover line 76, at end of line
syntax error at /usr/local/bin/master_ip_failover line 76, near “}”
Execution of /usr/local/bin/master_ip_failover aborted due to compilation errors.
Sun Jan 18 14:00:43 2015 – [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln214] Failed to get master_ip_failover_script status with return code 255:0.
Sun Jan 18 14:00:43 2015 – [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln383] Error happend on checking configurations. at /usr/local/bin/masterha_check_repl line 48
Sun Jan 18 14:00:43 2015 – [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln478] Error happened on monitoring servers.
Sun Jan 18 14:00:43 2015 – [info] Got exit code 1 (Not master dead).

MySQL Replication Health is NOT OK!
报以上的错，折腾了 N 多人，因为好多人不懂 perl，看到模板就复制别人的代码，就是在复制的进去的时候，弄乱了，又手动调一下，导致各种各样的问题，我上面就是不小心导致的报错，手动修改了（cp 的时候有一行多了一个 #号），报错的大部份原因是 master_ip_failover 脚本导致的，而不要过多花时间纠结自己是否安装时安装少了东西，怀疑自己搭建的环境问题

再次执行检查：

192.168.2.131 [root ~]$ masterha_check_repl –conf=/etc/masterha/app1.cnf
Sun Jan 18 14:02:21 2015 – [info] Slaves settings check done.
Sun Jan 18 14:02:21 2015 – [info]
192.168.2.128 (current master)
+–192.168.2.129
+–192.168.2.130

Sun Jan 18 14:02:21 2015 – [info] Checking replication health on 192.168.2.129..
Sun Jan 18 14:02:21 2015 – [info] ok.
Sun Jan 18 14:02:21 2015 – [info] Checking replication health on 192.168.2.130..
Sun Jan 18 14:02:21 2015 – [info] ok.
Sun Jan 18 14:02:21 2015 – [info] Checking master_ip_failover_script status:
Sun Jan 18 14:02:21 2015 – [info] /usr/local/bin/master_ip_failover –command=status –ssh_user=root –orig_master_host=192.168.2.128 –orig_master_ip=192.168.2.128 –orig_master_port=3306

IN SCRIPT TEST====/etc/init.d/keepalived stop==/etc/init.d/keepalived start===

Checking the Status of the script.. OK
Sun Jan 18 14:02:21 2015 – [info] OK.
Sun Jan 18 14:02:21 2015 – [warning] shutdown_script is not defined.
Sun Jan 18 14:02:21 2015 – [info] Got exit code 0 (Not master dead).

MySQL Replication Health is OK.
192.168.2.131 [root ~]$
可以看见已经没有报错了，再乐一会吧，哈哈……

/usr/local/bin/master_ip_failover 添加或者修改的内容意思是当主库数据库发生故障时，会触发 MHA 切换，MHA Manager 会停掉主库上的 keepalived 服务，触发虚拟 ip 漂移到备选从库，从而完成切换。当然可以在 keepalived 里面引入脚本，这个脚本监控 mysql 是否正常运行，如果不正常，则调用该脚本杀掉 keepalived 进程。

2、以下进行模拟主 Master（192.168.2.128）down 了：

192.168.2.128 [root keepalived-1.2.12]$ /etc/init.d/mysqld stop
Shutting down MySQL… SUCCESS!
192.168.2.128 [root keepalived-1.2.12]$

在管理节点 (server01 192.168.2.131) 查看日志：（报错）

192.168.2.131 [root ~]$ tail -f /var/log/masterha/app1/manager.log
192.168.2.128 (current master)
+–192.168.2.129
+–192.168.2.130

Sun Jan 18 13:32:37 2015 – [warning] master_ip_failover_script is not defined.
Sun Jan 18 13:32:37 2015 – [warning] shutdown_script is not defined.
Sun Jan 18 13:32:37 2015 – [info] Set master ping interval 1 seconds.
Sun Jan 18 13:32:37 2015 – [info] Set secondary check script: /usr/local/bin/masterha_secondary_check -s server03 -s server02 –user=root –master_host=server02 –master_ip=192.168.2.128 –master_port=3306
Sun Jan 18 13:32:37 2015 – [info] Starting ping health check on 192.168.2.128(192.168.2.128:3306)..
Sun Jan 18 13:32:37 2015 – [info] Ping(SELECT) succeeded, waiting until MySQL doesn’t respond..
Sun Jan 18 14:32:03 2015 – [warning] Got error on MySQL select ping: 2006 (MySQL server has gone away)
Sun Jan 18 14:32:03 2015 – [info] Executing seconary network check script: /usr/local/bin/masterha_secondary_check -s server03 -s server02 –user=root –master_host=server02 –master_ip=192.168.2.128 –master_port=3306 –user=root –master_host=192.168.2.128 –master_ip=192.168.2.128 –master_port=3306
Sun Jan 18 14:32:03 2015 – [info] Executing SSH check script: save_binary_logs –command=test –start_pos=4 –binlog_dir=/data/mysql –output_file=/tmp/save_binary_logs_test –manager_version=0.56 –binlog_prefix=mysql-bin
Sun Jan 18 14:32:03 2015 – [info] HealthCheck: SSH to 192.168.2.128 is reachable.
Sun Jan 18 14:32:04 2015 – [warning] Got error on MySQL connect: 2013 (Lost connection to MySQL server at ‘reading initial communication packet’, system error: 111)
Sun Jan 18 14:32:04 2015 – [warning] Connection failed 1 time(s)..
Sun Jan 18 14:32:05 2015 – [warning] Got error on MySQL connect: 2013 (Lost connection to MySQL server at ‘reading initial communication packet’, system error: 111)
Sun Jan 18 14:32:05 2015 – [warning] Connection failed 2 time(s)..
Sun Jan 18 14:32:06 2015 – [warning] Got error on MySQL connect: 2013 (Lost connection to MySQL server at ‘reading initial communication packet’, system error: 111)
Sun Jan 18 14:32:06 2015 – [warning] Connection failed 3 time(s)..
ssh: Could not resolve hostname server03: Name or service not known
Monitoring server server03 is NOT reachable!
在管理节服务器 192.168.2.131 上添加 hosts:

192.168.2.131 [root ~]$ cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.2.128 server01
192.168.2.129 server02
192.168.2.130 server03 再查看日志（点下面加号可以查看日志）：

192.168.2.131 [root ~]$ tail -f /var/log/masterha/app1/manager.log

IN SCRIPT TEST====/etc/init.d/keepalived stop==/etc/init.d/keepalived start===

Checking the Status of the script.. OK
Sun Jan 18 17:11:19 2015 – [info] OK.
Sun Jan 18 17:11:19 2015 – [warning] shutdown_script is not defined.
Sun Jan 18 17:11:19 2015 – [info] Set master ping interval 1 seconds.
Sun Jan 18 17:11:19 2015 – [info] Set secondary check script: /usr/local/bin/masterha_secondary_check -s server03 -s server02 –user=root –master_host=server02 –master_ip=192.168.2.128 –master_port=3306
Sun Jan 18 17:11:19 2015 – [info] Starting ping health check on 192.168.2.128(192.168.2.128:3306)..
Sun Jan 18 17:11:19 2015 – [info] Ping(SELECT) succeeded, waiting until MySQL doesn’t respond..
Sun Jan 18 17:11:48 2015 – [warning] Got error on MySQL select ping: 2006 (MySQL server has gone away)
Sun Jan 18 17:11:48 2015 – [info] Executing SSH check script: save_binary_logs –command=test –start_pos=4 –binlog_dir=/data/mysql –output_file=/tmp/save_binary_logs_test –manager_version=0.53 –binlog_prefix=mysql-bin
Sun Jan 18 17:11:48 2015 – [info] Executing seconary network check script: /usr/local/bin/masterha_secondary_check -s server03 -s server02 –user=root –master_host=server02 –master_ip=192.168.2.128 –master_port=3306 –user=root –master_host=192.168.2.128 –master_ip=192.168.2.128 –master_port=3306
Sun Jan 18 17:11:48 2015 – [info] HealthCheck: SSH to 192.168.2.128 is reachable.
Monitoring server server03 is reachable, Master is not reachable from server03. OK.
Monitoring server server02 is reachable, Master is not reachable from server02. OK.
Sun Jan 18 17:11:48 2015 – [info] Master is not reachable from all other monitoring servers. Failover should start.
Sun Jan 18 17:11:49 2015 – [warning] Got error on MySQL connect: 2013 (Lost connection to MySQL server at ‘reading initial communication packet’, system error: 111)
Sun Jan 18 17:11:49 2015 – [warning] Connection failed 1 time(s)..
Sun Jan 18 17:11:50 2015 – [warning] Got error on MySQL connect: 2013 (Lost connection to MySQL server at ‘reading initial communication packet’, system error: 111)
Sun Jan 18 17:11:50 2015 – [warning] Connection failed 2 time(s)..
Sun Jan 18 17:11:51 2015 – [warning] Got error on MySQL connect: 2013 (Lost connection to MySQL server at ‘reading initial communication packet’, system error: 111)
Sun Jan 18 17:11:51 2015 – [warning] Connection failed 3 time(s)..
Sun Jan 18 17:11:51 2015 – [warning] Master is not reachable from health checker!
Sun Jan 18 17:11:51 2015 – [warning] Master 192.168.2.128(192.168.2.128:3306) is not reachable!
Sun Jan 18 17:11:51 2015 – [warning] SSH is reachable.
Sun Jan 18 17:11:51 2015 – [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..
Sun Jan 18 17:11:51 2015 – [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sun Jan 18 17:11:51 2015 – [info] Reading application default configurations from /etc/masterha/app1.cnf..
Sun Jan 18 17:11:51 2015 – [info] Reading server configurations from /etc/masterha/app1.cnf..
Sun Jan 18 17:11:51 2015 – [info] Dead Servers:
Sun Jan 18 17:11:51 2015 – [info] 192.168.2.128(192.168.2.128:3306)
Sun Jan 18 17:11:51 2015 – [info] Alive Servers:
Sun Jan 18 17:11:51 2015 – [info] 192.168.2.129(192.168.2.129:3306)
Sun Jan 18 17:11:51 2015 – [info] 192.168.2.130(192.168.2.130:3306)
Sun Jan 18 17:11:51 2015 – [info] Alive Slaves:
Sun Jan 18 17:11:51 2015 – [info] 192.168.2.129(192.168.2.129:3306) Version=5.5.30-log (oldest major version between slaves) log-bin:enabled
Sun Jan 18 17:11:51 2015 – [info] Replicating from 192.168.2.128(192.168.2.128:3306)
Sun Jan 18 17:11:51 2015 – [info] Primary candidate for the new Master (candidate_master is set)
Sun Jan 18 17:11:51 2015 – [info] 192.168.2.130(192.168.2.130:3306) Version=5.5.25-log (oldest major version between slaves) log-bin:enabled
Sun Jan 18 17:11:51 2015 – [info] Replicating from 192.168.2.128(192.168.2.128:3306)
Sun Jan 18 17:11:51 2015 – [info] Checking slave configurations..
Sun Jan 18 17:11:51 2015 – [info] read_only=1 is not set on slave 192.168.2.129(192.168.2.129:3306).
Sun Jan 18 17:11:51 2015 – [warning] relay_log_purge=0 is not set on slave 192.168.2.129(192.168.2.129:3306).
Sun Jan 18 17:11:51 2015 – [warning] relay_log_purge=0 is not set on slave 192.168.2.130(192.168.2.130:3306).
Sun Jan 18 17:11:51 2015 – [info] Checking replication filtering settings..
Sun Jan 18 17:11:51 2015 – [info] Replication filtering check ok.
Sun Jan 18 17:11:51 2015 – [info] Master is down!
Sun Jan 18 17:11:51 2015 – [info] Terminating monitoring script.
Sun Jan 18 17:11:51 2015 – [info] Got exit code 20 (Master dead).
Sun Jan 18 17:11:51 2015 – [info] MHA::MasterFailover version 0.53.
Sun Jan 18 17:11:51 2015 – [info] Starting master failover.
Sun Jan 18 17:11:51 2015 – [info]
Sun Jan 18 17:11:51 2015 – [info] * Phase 1: Configuration Check Phase..
Sun Jan 18 17:11:51 2015 – [info]
Sun Jan 18 17:11:51 2015 – [info] Dead Servers:
Sun Jan 18 17:11:51 2015 – [info] 192.168.2.128(192.168.2.128:3306)
Sun Jan 18 17:11:51 2015 – [info] Checking master reachability via mysql(double check)..
Sun Jan 18 17:11:51 2015 – [info] ok.
Sun Jan 18 17:11:51 2015 – [info] Alive Servers:
Sun Jan 18 17:11:51 2015 – [info] 192.168.2.129(192.168.2.129:3306)
Sun Jan 18 17:11:51 2015 – [info] 192.168.2.130(192.168.2.130:3306)
Sun Jan 18 17:11:51 2015 – [info] Alive Slaves:
Sun Jan 18 17:11:51 2015 – [info] 192.168.2.129(192.168.2.129:3306) Version=5.5.30-log (oldest major version between slaves) log-bin:enabled
Sun Jan 18 17:11:51 2015 – [info] Replicating from 192.168.2.128(192.168.2.128:3306)
Sun Jan 18 17:11:51 2015 – [info] Primary candidate for the new Master (candidate_master is set)
Sun Jan 18 17:11:51 2015 – [info] 192.168.2.130(192.168.2.130:3306) Version=5.5.25-log (oldest major version between slaves) log-bin:enabled
Sun Jan 18 17:11:51 2015 – [info] Replicating from 192.168.2.128(192.168.2.128:3306)
Sun Jan 18 17:11:51 2015 – [info] ** Phase 1: Configuration Check Phase completed.
Sun Jan 18 17:11:51 2015 – [info]
Sun Jan 18 17:11:51 2015 – [info] * Phase 2: Dead Master Shutdown Phase..
Sun Jan 18 17:11:51 2015 – [info]
Sun Jan 18 17:11:51 2015 – [info] Forcing shutdown so that applications never connect to the current master..
Sun Jan 18 17:11:51 2015 – [info] Executing master IP deactivatation script:
Sun Jan 18 17:11:51 2015 – [info] /usr/local/bin/master_ip_failover –orig_master_host=192.168.2.128 –orig_master_ip=192.168.2.128 –orig_master_port=3306 –command=stopssh –ssh_user=root

IN SCRIPT TEST====/etc/init.d/keepalived stop==/etc/init.d/keepalived start===

Disabling the VIP on old master: 192.168.2.128
Sun Jan 18 17:11:51 2015 – [info] done.
Sun Jan 18 17:11:51 2015 – [warning] shutdown_script is not set. Skipping explicit shutting down of the dead master.
Sun Jan 18 17:11:51 2015 – [info] * Phase 2: Dead Master Shutdown Phase completed.
Sun Jan 18 17:11:51 2015 – [info]
Sun Jan 18 17:11:51 2015 – [info] * Phase 3: Master Recovery Phase..
Sun Jan 18 17:11:51 2015 – [info]
Sun Jan 18 17:11:51 2015 – [info] * Phase 3.1: Getting Latest Slaves Phase..
Sun Jan 18 17:11:51 2015 – [info]
Sun Jan 18 17:11:51 2015 – [info] The latest binary log file/position on all slaves is mysql-bin.000014:107
Sun Jan 18 17:11:51 2015 – [info] Latest slaves (Slaves that received relay log files to the latest):
Sun Jan 18 17:11:51 2015 – [info] 192.168.2.129(192.168.2.129:3306) Version=5.5.30-log (oldest major version between slaves) log-bin:enabled
Sun Jan 18 17:11:51 2015 – [info] Replicating from 192.168.2.128(192.168.2.128:3306)
Sun Jan 18 17:11:51 2015 – [info] Primary candidate for the new Master (candidate_master is set)
Sun Jan 18 17:11:51 2015 – [info] 192.168.2.130(192.168.2.130:3306) Version=5.5.25-log (oldest major version between slaves) log-bin:enabled
Sun Jan 18 17:11:51 2015 – [info] Replicating from 192.168.2.128(192.168.2.128:3306)
Sun Jan 18 17:11:51 2015 – [info] The oldest binary log file/position on all slaves is mysql-bin.000014:107
Sun Jan 18 17:11:51 2015 – [info] Oldest slaves:
Sun Jan 18 17:11:51 2015 – [info] 192.168.2.129(192.168.2.129:3306) Version=5.5.30-log (oldest major version between slaves) log-bin:enabled
Sun Jan 18 17:11:51 2015 – [info] Replicating from 192.168.2.128(192.168.2.128:3306)
Sun Jan 18 17:11:51 2015 – [info] Primary candidate for the new Master (candidate_master is set)
Sun Jan 18 17:11:51 2015 – [info] 192.168.2.130(192.168.2.130:3306) Version=5.5.25-log (oldest major version between slaves) log-bin:enabled
Sun Jan 18 17:11:51 2015 – [info] Replicating from 192.168.2.128(192.168.2.128:3306)
Sun Jan 18 17:11:51 2015 – [info]
Sun Jan 18 17:11:51 2015 – [info] * Phase 3.2: Saving Dead Master’s Binlog Phase..
Sun Jan 18 17:11:51 2015 – [info]
Sun Jan 18 17:11:51 2015 – [info] Fetching dead master’s binary logs..
Sun Jan 18 17:11:51 2015 – [info] Executing command on the dead master 192.168.2.128(192.168.2.128:3306): save_binary_logs –command=save –start_file=mysql-bin.000014 –start_pos=107 –binlog_dir=/data/mysql –output_file=/tmp/saved_master_binlog_from_192.168.2.128_3306_20150118171151.binlog –handle_raw_binlog=1 –disable_log_bin=0 –manager_version=0.53
Creating /tmp if not exists.. ok.
Concat binary/relay logs from mysql-bin.000014 pos 107 to mysql-bin.000014 EOF into /tmp/saved_master_binlog_from_192.168.2.128_3306_20150118171151.binlog ..
Dumping binlog format description event, from position 0 to 107.. ok.
Dumping effective binlog data from /data/mysql/mysql-bin.000014 position 107 to tail(126).. ok.
Concat succeeded.
Sun Jan 18 17:11:52 2015 – [info] scp from root@192.168.2.128:/tmp/saved_master_binlog_from_192.168.2.128_3306_20150118171151.binlog to local:/var/log/masterha/app1.log/saved_master_binlog_from_192.168.2.128_3306_20150118171151.binlog succeeded.
Sun Jan 18 17:11:52 2015 – [info] HealthCheck: SSH to 192.168.2.129 is reachable.
Sun Jan 18 17:11:52 2015 – [info] HealthCheck: SSH to 192.168.2.130 is reachable.
Sun Jan 18 17:11:53 2015 – [info]
Sun Jan 18 17:11:53 2015 – [info] * Phase 3.3: Determining New Master Phase..
Sun Jan 18 17:11:53 2015 – [info]
Sun Jan 18 17:11:53 2015 – [info] Finding the latest slave that has all relay logs for recovering other slaves..
Sun Jan 18 17:11:53 2015 – [info] All slaves received relay logs to the same position. No need to resync each other.
Sun Jan 18 17:11:53 2015 – [info] Searching new master from slaves..
Sun Jan 18 17:11:53 2015 – [info] Candidate masters from the configuration file:
Sun Jan 18 17:11:53 2015 – [info] 192.168.2.129(192.168.2.129:3306) Version=5.5.30-log (oldest major version between slaves) log-bin:enabled
Sun Jan 18 17:11:53 2015 – [info] Replicating from 192.168.2.128(192.168.2.128:3306)
Sun Jan 18 17:11:53 2015 – [info] Primary candidate for the new Master (candidate_master is set)
Sun Jan 18 17:11:53 2015 – [info] Non-candidate masters:
Sun Jan 18 17:11:53 2015 – [info] Searching from candidate_master slaves which have received the latest relay log events..
Sun Jan 18 17:11:53 2015 – [info] New master is 192.168.2.129(192.168.2.129:3306)
Sun Jan 18 17:11:53 2015 – [info] Starting master failover..
Sun Jan 18 17:11:53 2015 – [info]
From:
192.168.2.128 (current master)
+–192.168.2.129
+–192.168.2.130

To:
192.168.2.129 (new master)
+–192.168.2.130
Sun Jan 18 17:11:53 2015 – [info]
Sun Jan 18 17:11:53 2015 – [info] * Phase 3.3: New Master Diff Log Generation Phase..
Sun Jan 18 17:11:53 2015 – [info]
Sun Jan 18 17:11:53 2015 – [info] This server has all relay logs. No need to generate diff files from the latest slave.
Sun Jan 18 17:11:53 2015 – [info] Sending binlog..
Sun Jan 18 17:11:53 2015 – [info] scp from local:/var/log/masterha/app1.log/saved_master_binlog_from_192.168.2.128_3306_20150118171151.binlog to root@192.168.2.129:/tmp/saved_master_binlog_from_192.168.2.128_3306_20150118171151.binlog succeeded.
Sun Jan 18 17:11:53 2015 – [info]
Sun Jan 18 17:11:53 2015 – [info] * Phase 3.4: Master Log Apply Phase..
Sun Jan 18 17:11:53 2015 – [info]
Sun Jan 18 17:11:53 2015 – [info] *NOTICE: If any error happens from this phase, manual recovery is needed.
Sun Jan 18 17:11:53 2015 – [info] Starting recovery on 192.168.2.129(192.168.2.129:3306)..
Sun Jan 18 17:11:53 2015 – [info] Generating diffs succeeded.
Sun Jan 18 17:11:53 2015 – [info] Waiting until all relay logs are applied.
Sun Jan 18 17:11:53 2015 – [info] done.
Sun Jan 18 17:11:53 2015 – [info] Getting slave status..
Sun Jan 18 17:11:53 2015 – [info] This slave(192.168.2.129)’s Exec_Master_Log_Pos equals to Read_Master_Log_Pos(mysql-bin.000014:107). No need to recover from Exec_Master_Log_Pos.
Sun Jan 18 17:11:53 2015 – [info] Connecting to the target slave host 192.168.2.129, running recover script..
Sun Jan 18 17:11:53 2015 – [info] Executing command: apply_diff_relay_logs –command=apply –slave_user=root –slave_host=192.168.2.129 –slave_ip=192.168.2.129 –slave_port=3306 –apply_files=/tmp/saved_master_binlog_from_192.168.2.128_3306_20150118171151.binlog –workdir=/tmp –target_version=5.5.30-log –timestamp=20150118171151 –handle_raw_binlog=1 –disable_log_bin=0 –manager_version=0.53 –slave_pass=xxx
Sun Jan 18 17:11:53 2015 – [info]
Applying differential binary/relay log files /tmp/saved_master_binlog_from_192.168.2.128_3306_20150118171151.binlog on 192.168.2.129:3306. This may take long time…
Applying log files succeeded.
Sun Jan 18 17:11:53 2015 – [info] All relay logs were successfully applied.
Sun Jan 18 17:11:53 2015 – [info] Getting new master’s binlog name and position..
Sun Jan 18 17:11:53 2015 – [info] mysql-bin.000005:61791
Sun Jan 18 17:11:53 2015 – [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST=’192.168.2.129′, MASTER_PORT=3306, MASTER_LOG_FILE=’mysql-bin.000005′, MASTER_LOG_POS=61791, MASTER_USER=’repl’, MASTER_PASSWORD=’xxx’;
Sun Jan 18 17:11:53 2015 – [info] Executing master IP activate script:
Sun Jan 18 17:11:53 2015 – [info] /usr/local/bin/master_ip_failover –command=start –ssh_user=root –orig_master_host=192.168.2.128 –orig_master_ip=192.168.2.128 –orig_master_port=3306 –new_master_host=192.168.2.129 –new_master_ip=192.168.2.129 –new_master_port=3306

IN SCRIPT TEST====/etc/init.d/keepalived stop==/etc/init.d/keepalived start===

Enabling the VIP – 192.168.2.88 on the new master – 192.168.2.129
Sun Jan 18 17:11:53 2015 – [info] OK.
Sun Jan 18 17:11:53 2015 – [info] ** Finished master recovery successfully.
Sun Jan 18 17:11:53 2015 – [info] * Phase 3: Master Recovery Phase completed.
Sun Jan 18 17:11:53 2015 – [info]
Sun Jan 18 17:11:53 2015 – [info] * Phase 4: Slaves Recovery Phase..
Sun Jan 18 17:11:53 2015 – [info]
Sun Jan 18 17:11:53 2015 – [info] * Phase 4.1: Starting Parallel Slave Diff Log Generation Phase..
Sun Jan 18 17:11:53 2015 – [info]
Sun Jan 18 17:11:53 2015 – [info] — Slave diff file generation on host 192.168.2.130(192.168.2.130:3306) started, pid: 19762. Check tmp log /var/log/masterha/app1.log/192.168.2.130_3306_20150118171151.log if it takes time..
Sun Jan 18 17:11:53 2015 – [info]
Sun Jan 18 17:11:53 2015 – [info] Log messages from 192.168.2.130 …
Sun Jan 18 17:11:53 2015 – [info]
Sun Jan 18 17:11:53 2015 – [info] This server has all relay logs. No need to generate diff files from the latest slave.
Sun Jan 18 17:11:53 2015 – [info] End of log messages from 192.168.2.130.
Sun Jan 18 17:11:53 2015 – [info] — 192.168.2.130(192.168.2.130:3306) has the latest relay log events.
Sun Jan 18 17:11:53 2015 – [info] Generating relay diff files from the latest slave succeeded.
Sun Jan 18 17:11:53 2015 – [info]
Sun Jan 18 17:11:53 2015 – [info] * Phase 4.2: Starting Parallel Slave Log Apply Phase..
Sun Jan 18 17:11:53 2015 – [info]
Sun Jan 18 17:11:53 2015 – [info] — Slave recovery on host 192.168.2.130(192.168.2.130:3306) started, pid: 19764. Check tmp log /var/log/masterha/app1.log/192.168.2.130_3306_20150118171151.log if it takes time..
Sun Jan 18 17:11:55 2015 – [info]
Sun Jan 18 17:11:55 2015 – [info] Log messages from 192.168.2.130 …
Sun Jan 18 17:11:55 2015 – [info]
Sun Jan 18 17:11:53 2015 – [info] Sending binlog..
Sun Jan 18 17:11:54 2015 – [info] scp from local:/var/log/masterha/app1.log/saved_master_binlog_from_192.168.2.128_3306_20150118171151.binlog to root@192.168.2.130:/tmp/saved_master_binlog_from_192.168.2.128_3306_20150118171151.binlog succeeded.
Sun Jan 18 17:11:54 2015 – [info] Starting recovery on 192.168.2.130(192.168.2.130:3306)..
Sun Jan 18 17:11:54 2015 – [info] Generating diffs succeeded.
Sun Jan 18 17:11:54 2015 – [info] Waiting until all relay logs are applied.
Sun Jan 18 17:11:54 2015 – [info] done.
Sun Jan 18 17:11:54 2015 – [info] Getting slave status..
Sun Jan 18 17:11:54 2015 – [info] This slave(192.168.2.130)’s Exec_Master_Log_Pos equals to Read_Master_Log_Pos(mysql-bin.000014:107). No need to recover from Exec_Master_Log_Pos.
Sun Jan 18 17:11:54 2015 – [info] Connecting to the target slave host 192.168.2.130, running recover script..
Sun Jan 18 17:11:54 2015 – [info] Executing command: apply_diff_relay_logs –command=apply –slave_user=root –slave_host=192.168.2.130 –slave_ip=192.168.2.130 –slave_port=3306 –apply_files=/tmp/saved_master_binlog_from_192.168.2.128_3306_20150118171151.binlog –workdir=/tmp –target_version=5.5.25-log –timestamp=20150118171151 –handle_raw_binlog=1 –disable_log_bin=0 –manager_version=0.53 –slave_pass=xxx
Sun Jan 18 17:11:54 2015 – [info]
Applying differential binary/relay log files /tmp/saved_master_binlog_from_192.168.2.128_3306_20150118171151.binlog on 192.168.2.130:3306. This may take long time…
Applying log files succeeded.
Sun Jan 18 17:11:54 2015 – [info] All relay logs were successfully applied.
Sun Jan 18 17:11:54 2015 – [info] Resetting slave 192.168.2.130(192.168.2.130:3306) and starting replication from the new master 192.168.2.129(192.168.2.129:3306)..
Sun Jan 18 17:11:55 2015 – [info] Executed CHANGE MASTER.
Sun Jan 18 17:11:55 2015 – [info] Slave started.
Sun Jan 18 17:11:55 2015 – [info] End of log messages from 192.168.2.130.
Sun Jan 18 17:11:55 2015 – [info] — Slave recovery on host 192.168.2.130(192.168.2.130:3306) succeeded.
Sun Jan 18 17:11:55 2015 – [info] All new slave servers recovered successfully.
Sun Jan 18 17:11:55 2015 – [info]
Sun Jan 18 17:11:55 2015 – [info] * Phase 5: New master cleanup phease..
Sun Jan 18 17:11:55 2015 – [info]
Sun Jan 18 17:11:55 2015 – [info] Resetting slave info on the new master..
Sun Jan 18 17:11:55 2015 – [info] 192.168.2.129: Resetting slave info succeeded.
Sun Jan 18 17:11:55 2015 – [info] Master failover to 192.168.2.129(192.168.2.129:3306) completed successfully.
Sun Jan 18 17:11:55 2015 – [info] Deleted server1 entry from /etc/masterha/app1.cnf .
Sun Jan 18 17:11:55 2015 – [info]

—– Failover Report —–

app1: MySQL Master failover 192.168.2.128 to 192.168.2.129 succeeded

Master 192.168.2.128 is down!

Check MHA Manager logs at localhost.localdomain:/var/log/masterha/app1/manager.log for details.

Started automated(non-interactive) failover.
Invalidated master IP address on 192.168.2.128.
The latest slave 192.168.2.129(192.168.2.129:3306) has all relay logs for recovery.
Selected 192.168.2.129 as a new master.
192.168.2.129: OK: Applying all logs succeeded.
192.168.2.129: OK: Activated master IP address.
192.168.2.130: This host has the latest relay log events.
Generating relay diff files from the latest slave succeeded.
192.168.2.130: OK: Applying all logs succeeded. Slave started, replicating from 192.168.2.129.
192.168.2.129: Resetting slave info succeeded.
Master failover to 192.168.2.129(192.168.2.129:3306) completed successfully.
Sun Jan 18 17:11:55 2015 – [info] Sending mail..
Unknown option: conf
View Code
3、在之前的 Master（192.168.2.128）上查看一下 vip：

192.168.2.128 [root keepalived-1.2.12]$ ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueue state UNKNOWN
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 00:0c:29:86:dc:2a brd ff:ff:ff:ff:ff:ff
inet 192.168.2.128/24 brd 192.168.2.255 scope global eth0
inet6 fe80::20c:29ff:fe86:dc2a/64 scope link
valid_lft forever preferred_lft forever
192.168.2.128 [root keepalived-1.2.12]$
可以看到 vip 已经不在 down 的机器上了

去候选的 master（server03 192.168.2.129）也就是现在的新 master 查看是否有 vip 漂过：

192.168.2.129 [root keepalived-1.2.12]$ ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueue state UNKNOWN
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 00:0c:29:66:95:64 brd ff:ff:ff:ff:ff:ff
inet 192.168.2.129/24 brd 192.168.2.255 scope global eth0
inet 192.168.2.88/32 scope global eth0
inet6 fe80::20c:29ff:fe66:9564/64 scope link
valid_lft forever preferred_lft forever
192.168.2.129 [root keepalived-1.2.12]$
哈哈，看到 vip 已经成功漂移过来了。

从 tail -f /var/log/masterha/app1/manager.log 的信息可以发现最后有这样的字眼：

The latest slave 192.168.2.129(192.168.2.129:3306) has all relay logs for recovery.
Selected 192.168.2.129 as a new master.
192.168.2.129: OK: Applying all logs succeeded.
192.168.2.129: OK: Activated master IP address.
192.168.2.130: This host has the latest relay log events.
Generating relay diff files from the latest slave succeeded.
192.168.2.130: OK: Applying all logs succeeded. Slave started, replicating from 192.168.2.129.
192.168.2.129: Resetting slave info succeeded.
Master failover to 192.168.2.129(192.168.2.129:3306) completed successfully.
Sun Jan 18 17:11:55 2015 – [info] Sending mail..
Unknown option: conf
看到上面的 Sending mail 了吧，哈哈，已经正常发邮件了，看图：

MySQL 高可用之 MHA 的搭建

发邮件的设置要在监控节点 192.168.2.131 上操作：

192.168.2.131 [root bin]$ cat /etc/masterha/app1.cnf |grep “report_script”
report_script=/usr/local/bin/send_reportsend_report 这个脚本在安装好软件后就会有，但我前面说了，这些脚本有很多地方不够完善，包括 send_report 的发邮件脚本，下面说明发设置，并把代码 share 出来：

MySQL 高可用之 MHA 的搭建

脚本代码：（该脚本是 37wan DBA- 邓亚运分享，博客地址在博文后面贴出）

#!/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=’xxxxxxx@163.com’;
my $mail_user=’xxxxxxx@163.com’;
my $mail_pass=’Password’;
my $mail_to=[‘949538827@qq.com’,’15521xxxx@139.com’];
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;View Code

更多详情见请继续阅读下一页的精彩内容：http://www.linuxidc.com/Linux/2016-05/130923p2.htm

4、在管理节点查看一下配置文件 /etc/masterha/app1.cnf 可以发现 [server1] 的内容已经被自动去掉了（server01 192.168.2.131）：

192.168.2.131 [root ~]$ cat /etc/masterha/app1.cnf
[server default]
manager_log=/var/log/masterha/app1/manager.log
manager_workdir=/var/log/masterha/app1.log
master_binlog_dir=/data/MySQL
master_ip_failover_script=/usr/local/bin/master_ip_failover
master_ip_online_change_script=/usr/local/bin/master_ip_online_change
password=123456
ping_interval=1
remote_workdir=/tmp
repl_password=123456
repl_user=repl
report_script=/usr/local/bin/send_report
secondary_check_script=/usr/local/bin/masterha_secondary_check -s server03 -s server02 –user=root –master_host=server02 –master_ip=192.168.2.128 –master_port=3306
shutdown_script=””
ssh_user=root
user=root

[server2]
candidate_master=1
check_repl_delay=0
hostname=192.168.2.129
port=3306

[server3]
hostname=192.168.2.130
port=3306

（2）通过脚本的方式管理 VIP。这里是修改 /usr/local/bin/master_ip_failover，也可以使用其他的语言完成，比如 php 语言。使用 php 脚本编写的 failover 这里就不介绍了。修改完成后内容如下，而且如果使用脚本管理 vip 的话，需要手动在 master 服务器上绑定一个 vip

192.168.2.128 [root ~]$ /sbin/ifconfig eth0:1 192.168.2.88/24
192.168.2.128 [root ~]$ ifconfig
eth0 Link encap:Ethernet HWaddr 00:0C:29:86:DC:2A
inet addr:192.168.2.128 Bcast:192.168.2.255 Mask:255.255.255.0
inet6 addr: fe80::20c:29ff:fe86:dc2a/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:41643 errors:0 dropped:0 overruns:0 frame:0
TX packets:24696 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:31624443 (30.1 MiB) TX bytes:3388815 (3.2 MiB)

eth0:1 Link encap:Ethernet HWaddr 00:0C:29:86:DC:2A
inet addr:192.168.2.88 Bcast:192.168.2.255 Mask:255.255.255.0
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
在管理节点（server01 192.168.2.131）修改下 /usr/local/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.2.88’;
my $key = ‘1’;
my $ssh_start_vip = “/sbin/ifconfig eth1:$key $vip”;
my $ssh_stop_vip = “/sbin/ifconfig eth1:$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”) {

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”) {

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”;
exit 0;
}
else {
&usage();
exit 1;
}
}
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”;
}
（1）在 slave 库（192.168.2.129）上停掉 slave IO 线程，模拟主从延时

mysql> stop slave io_thread;
Query OK, 0 rows affected (0.03 sec)（2）在 master 库（192.168.2.128）安装 sysbench，进行 sysbench 数据生成，在 sbtest 库下生成 sbtest 表，共 10W 记录

192.168.2.128 [root ~]$ yum install sysbench -y
192.168.2.128 [root ~]$ mysql -uroot -p123456 -e “create database sbtest;”
192.168.2.128 [root ~]$ sysbench –test=oltp –oltp-table-size=100000 –oltp-read-only=off –init-rng=on –num-threads=1 –max-requests=0 –oltp-dist-type=uniform –max-time=1800 –mysql-user=root –mysql-socket=/tmp/mysql.sock –mysql-password=123456 –db-driver=mysql –mysql-table-engine=innodb –oltp-test-mode=complex prepare 另外一台 slave 我们没有停止 io 线程，所以还在继续接收日志。

（3）在 slave 库（192.168.2.129）开启 slave IO 线程：

mysql> start slave io_thread;
Query OK, 0 rows affected (0.00 sec)

（4）停掉 master 库（192.168.2.128）操作如下：

192.168.2.128 [root ~]$ /etc/init.d/mysqld stop
Shutting down MySQL…. SUCCESS!

（5）在管理节点查看日志：

192.168.2.131 [root ~]$ tail -f /var/log/masterha/app1/manager.log

IN SCRIPT TEST====/etc/init.d/keepalived stop==/etc/init.d/keepalived start===

Checking the Status of the script.. OK
Sun Jan 18 17:48:32 2015 – [info] OK.
Sun Jan 18 17:48:32 2015 – [warning] shutdown_script is not defined.
Sun Jan 18 17:48:32 2015 – [info] Set master ping interval 1 seconds.
Sun Jan 18 17:48:32 2015 – [info] Set secondary check script: /usr/local/bin/masterha_secondary_check -s server03 -s server02 –user=root –master_host=server02 –master_ip=192.168.2.128 –master_port=3306
Sun Jan 18 17:48:32 2015 – [info] Starting ping health check on 192.168.2.128(192.168.2.128:3306)..
Sun Jan 18 17:48:32 2015 – [info] Ping(SELECT) succeeded, waiting until MySQL doesn’t respond..
Sun Jan 18 17:52:38 2015 – [warning] Got error on MySQL select ping: 2006 (MySQL server has gone away)
Sun Jan 18 17:52:38 2015 – [info] Executing SSH check script: save_binary_logs –command=test –start_pos=4 –binlog_dir=/data/mysql –output_file=/tmp/save_binary_logs_test –manager_version=0.53 –binlog_prefix=mysql-bin
Sun Jan 18 17:52:38 2015 – [info] Executing seconary network check script: /usr/local/bin/masterha_secondary_check -s server03 -s server02 –user=root –master_host=server02 –master_ip=192.168.2.128 –master_port=3306 –user=root –master_host=192.168.2.128 –master_ip=192.168.2.128 –master_port=3306
Sun Jan 18 17:52:38 2015 – [info] HealthCheck: SSH to 192.168.2.128 is reachable.
Monitoring server server03 is reachable, Master is not reachable from server03. OK.
Monitoring server server02 is reachable, Master is not reachable from server02. OK.
Sun Jan 18 17:52:38 2015 – [info] Master is not reachable from all other monitoring servers. Failover should start.
Sun Jan 18 17:52:39 2015 – [warning] Got error on MySQL connect: 2013 (Lost connection to MySQL server at ‘reading initial communication packet’, system error: 111)
Sun Jan 18 17:52:39 2015 – [warning] Connection failed 1 time(s)..
Sun Jan 18 17:52:40 2015 – [warning] Got error on MySQL connect: 2013 (Lost connection to MySQL server at ‘reading initial communication packet’, system error: 111)
Sun Jan 18 17:52:40 2015 – [warning] Connection failed 2 time(s)..
Sun Jan 18 17:52:41 2015 – [warning] Got error on MySQL connect: 2013 (Lost connection to MySQL server at ‘reading initial communication packet’, system error: 111)
Sun Jan 18 17:52:41 2015 – [warning] Connection failed 3 time(s)..
Sun Jan 18 17:52:41 2015 – [warning] Master is not reachable from health checker!
Sun Jan 18 17:52:41 2015 – [warning] Master 192.168.2.128(192.168.2.128:3306) is not reachable!
Sun Jan 18 17:52:41 2015 – [warning] SSH is reachable.
Sun Jan 18 17:52:41 2015 – [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..
Sun Jan 18 17:52:41 2015 – [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sun Jan 18 17:52:41 2015 – [info] Reading application default configurations from /etc/masterha/app1.cnf..
Sun Jan 18 17:52:41 2015 – [info] Reading server configurations from /etc/masterha/app1.cnf..
Sun Jan 18 17:52:41 2015 – [info] Dead Servers:
Sun Jan 18 17:52:41 2015 – [info] 192.168.2.128(192.168.2.128:3306)
Sun Jan 18 17:52:41 2015 – [info] Alive Servers:
Sun Jan 18 17:52:41 2015 – [info] 192.168.2.129(192.168.2.129:3306)
Sun Jan 18 17:52:41 2015 – [info] 192.168.2.130(192.168.2.130:3306)
Sun Jan 18 17:52:41 2015 – [info] Alive Slaves:
Sun Jan 18 17:52:41 2015 – [info] 192.168.2.129(192.168.2.129:3306) Version=5.5.30-log (oldest major version between slaves) log-bin:enabled
Sun Jan 18 17:52:41 2015 – [info] Replicating from 192.168.2.128(192.168.2.128:3306)
Sun Jan 18 17:52:41 2015 – [info] Primary candidate for the new Master (candidate_master is set)
Sun Jan 18 17:52:41 2015 – [info] 192.168.2.130(192.168.2.130:3306) Version=5.5.25-log (oldest major version between slaves) log-bin:enabled
Sun Jan 18 17:52:41 2015 – [info] Replicating from 192.168.2.128(192.168.2.128:3306)
Sun Jan 18 17:52:41 2015 – [info] Checking slave configurations..
Sun Jan 18 17:52:41 2015 – [info] read_only=1 is not set on slave 192.168.2.129(192.168.2.129:3306).
Sun Jan 18 17:52:41 2015 – [warning] relay_log_purge=0 is not set on slave 192.168.2.129(192.168.2.129:3306).
Sun Jan 18 17:52:41 2015 – [warning] relay_log_purge=0 is not set on slave 192.168.2.130(192.168.2.130:3306).
Sun Jan 18 17:52:41 2015 – [info] Checking replication filtering settings..
Sun Jan 18 17:52:41 2015 – [info] Replication filtering check ok.
Sun Jan 18 17:52:41 2015 – [info] Master is down!
Sun Jan 18 17:52:41 2015 – [info] Terminating monitoring script.
Sun Jan 18 17:52:41 2015 – [info] Got exit code 20 (Master dead).
Sun Jan 18 17:52:41 2015 – [info] MHA::MasterFailover version 0.53.
Sun Jan 18 17:52:41 2015 – [info] Starting master failover.
Sun Jan 18 17:52:41 2015 – [info]
Sun Jan 18 17:52:41 2015 – [info] * Phase 1: Configuration Check Phase..
Sun Jan 18 17:52:41 2015 – [info]
Sun Jan 18 17:52:41 2015 – [info] Dead Servers:
Sun Jan 18 17:52:41 2015 – [info] 192.168.2.128(192.168.2.128:3306)
Sun Jan 18 17:52:41 2015 – [info] Checking master reachability via mysql(double check)..
Sun Jan 18 17:52:41 2015 – [info] ok.
Sun Jan 18 17:52:41 2015 – [info] Alive Servers:
Sun Jan 18 17:52:41 2015 – [info] 192.168.2.129(192.168.2.129:3306)
Sun Jan 18 17:52:41 2015 – [info] 192.168.2.130(192.168.2.130:3306)
Sun Jan 18 17:52:41 2015 – [info] Alive Slaves:
Sun Jan 18 17:52:41 2015 – [info] 192.168.2.129(192.168.2.129:3306) Version=5.5.30-log (oldest major version between slaves) log-bin:enabled
Sun Jan 18 17:52:41 2015 – [info] Replicating from 192.168.2.128(192.168.2.128:3306)
Sun Jan 18 17:52:41 2015 – [info] Primary candidate for the new Master (candidate_master is set)
Sun Jan 18 17:52:41 2015 – [info] 192.168.2.130(192.168.2.130:3306) Version=5.5.25-log (oldest major version between slaves) log-bin:enabled
Sun Jan 18 17:52:41 2015 – [info] Replicating from 192.168.2.128(192.168.2.128:3306)
Sun Jan 18 17:52:41 2015 – [info] ** Phase 1: Configuration Check Phase completed.
Sun Jan 18 17:52:41 2015 – [info]
Sun Jan 18 17:52:41 2015 – [info] * Phase 2: Dead Master Shutdown Phase..
Sun Jan 18 17:52:41 2015 – [info]
Sun Jan 18 17:52:41 2015 – [info] Forcing shutdown so that applications never connect to the current master..
Sun Jan 18 17:52:41 2015 – [info] Executing master IP deactivatation script:
Sun Jan 18 17:52:41 2015 – [info] /usr/local/bin/master_ip_failover –orig_master_host=192.168.2.128 –orig_master_ip=192.168.2.128 –orig_master_port=3306 –command=stopssh –ssh_user=root

IN SCRIPT TEST====/sbin/ifconfig eth0:1 down==/sbin/ifconfig eth0:1 192.168.2.88/24===

Disabling the VIP on old master: 192.168.2.128
Sun Jan 18 17:52:41 2015 – [info] done.
Sun Jan 18 17:52:41 2015 – [warning] shutdown_script is not set. Skipping explicit shutting down of the dead master.
Sun Jan 18 17:52:41 2015 – [info] * Phase 2: Dead Master Shutdown Phase completed.
Sun Jan 18 17:52:41 2015 – [info]
Sun Jan 18 17:52:41 2015 – [info] * Phase 3: Master Recovery Phase..
Sun Jan 18 17:52:41 2015 – [info]
Sun Jan 18 17:52:41 2015 – [info] * Phase 3.1: Getting Latest Slaves Phase..
Sun Jan 18 17:52:41 2015 – [info]
Sun Jan 18 17:52:41 2015 – [info] The latest binary log file/position on all slaves is mysql-bin.000015:107
Sun Jan 18 17:52:41 2015 – [info] Latest slaves (Slaves that received relay log files to the latest):
Sun Jan 18 17:52:41 2015 – [info] 192.168.2.129(192.168.2.129:3306) Version=5.5.30-log (oldest major version between slaves) log-bin:enabled
Sun Jan 18 17:52:41 2015 – [info] Replicating from 192.168.2.128(192.168.2.128:3306)
Sun Jan 18 17:52:41 2015 – [info] Primary candidate for the new Master (candidate_master is set)
Sun Jan 18 17:52:41 2015 – [info] 192.168.2.130(192.168.2.130:3306) Version=5.5.25-log (oldest major version between slaves) log-bin:enabled
Sun Jan 18 17:52:41 2015 – [info] Replicating from 192.168.2.128(192.168.2.128:3306)
Sun Jan 18 17:52:41 2015 – [info] The oldest binary log file/position on all slaves is mysql-bin.000015:107
Sun Jan 18 17:52:41 2015 – [info] Oldest slaves:
Sun Jan 18 17:52:41 2015 – [info] 192.168.2.129(192.168.2.129:3306) Version=5.5.30-log (oldest major version between slaves) log-bin:enabled
Sun Jan 18 17:52:41 2015 – [info] Replicating from 192.168.2.128(192.168.2.128:3306)
Sun Jan 18 17:52:41 2015 – [info] Primary candidate for the new Master (candidate_master is set)
Sun Jan 18 17:52:41 2015 – [info] 192.168.2.130(192.168.2.130:3306) Version=5.5.25-log (oldest major version between slaves) log-bin:enabled
Sun Jan 18 17:52:41 2015 – [info] Replicating from 192.168.2.128(192.168.2.128:3306)
Sun Jan 18 17:52:41 2015 – [info]
Sun Jan 18 17:52:41 2015 – [info] * Phase 3.2: Saving Dead Master’s Binlog Phase..
Sun Jan 18 17:52:41 2015 – [info]
Sun Jan 18 17:52:41 2015 – [info] Fetching dead master’s binary logs..
Sun Jan 18 17:52:41 2015 – [info] Executing command on the dead master 192.168.2.128(192.168.2.128:3306): save_binary_logs –command=save –start_file=mysql-bin.000015 –start_pos=107 –binlog_dir=/data/mysql –output_file=/tmp/saved_master_binlog_from_192.168.2.128_3306_20150118175241.binlog –handle_raw_binlog=1 –disable_log_bin=0 –manager_version=0.53
Creating /tmp if not exists.. ok.
Concat binary/relay logs from mysql-bin.000015 pos 107 to mysql-bin.000015 EOF into /tmp/saved_master_binlog_from_192.168.2.128_3306_20150118175241.binlog ..
Dumping binlog format description event, from position 0 to 107.. ok.
Dumping effective binlog data from /data/mysql/mysql-bin.000015 position 107 to tail(126).. ok.
Concat succeeded.
Sun Jan 18 17:52:42 2015 – [info] scp from root@192.168.2.128:/tmp/saved_master_binlog_from_192.168.2.128_3306_20150118175241.binlog to local:/var/log/masterha/app1.log/saved_master_binlog_from_192.168.2.128_3306_20150118175241.binlog succeeded.
Sun Jan 18 17:52:42 2015 – [info] HealthCheck: SSH to 192.168.2.129 is reachable.
Sun Jan 18 17:52:43 2015 – [info] HealthCheck: SSH to 192.168.2.130 is reachable.
Sun Jan 18 17:52:43 2015 – [info]
Sun Jan 18 17:52:43 2015 – [info] * Phase 3.3: Determining New Master Phase..
Sun Jan 18 17:52:43 2015 – [info]
Sun Jan 18 17:52:43 2015 – [info] Finding the latest slave that has all relay logs for recovering other slaves..
Sun Jan 18 17:52:43 2015 – [info] All slaves received relay logs to the same position. No need to resync each other.
Sun Jan 18 17:52:43 2015 – [info] Searching new master from slaves..
Sun Jan 18 17:52:43 2015 – [info] Candidate masters from the configuration file:
Sun Jan 18 17:52:43 2015 – [info] 192.168.2.129(192.168.2.129:3306) Version=5.5.30-log (oldest major version between slaves) log-bin:enabled
Sun Jan 18 17:52:43 2015 – [info] Replicating from 192.168.2.128(192.168.2.128:3306)
Sun Jan 18 17:52:43 2015 – [info] Primary candidate for the new Master (candidate_master is set)
Sun Jan 18 17:52:43 2015 – [info] Non-candidate masters:
Sun Jan 18 17:52:43 2015 – [info] Searching from candidate_master slaves which have received the latest relay log events..
Sun Jan 18 17:52:43 2015 – [info] New master is 192.168.2.129(192.168.2.129:3306)
Sun Jan 18 17:52:43 2015 – [info] Starting master failover..
Sun Jan 18 17:52:43 2015 – [info]
From:
192.168.2.128 (current master)
+–192.168.2.129
+–192.168.2.130

To:
192.168.2.129 (new master)
+–192.168.2.130
Sun Jan 18 17:52:43 2015 – [info]
Sun Jan 18 17:52:43 2015 – [info] * Phase 3.3: New Master Diff Log Generation Phase..
Sun Jan 18 17:52:43 2015 – [info]
Sun Jan 18 17:52:43 2015 – [info] This server has all relay logs. No need to generate diff files from the latest slave.
Sun Jan 18 17:52:43 2015 – [info] Sending binlog..
Sun Jan 18 17:52:43 2015 – [info] scp from local:/var/log/masterha/app1.log/saved_master_binlog_from_192.168.2.128_3306_20150118175241.binlog to root@192.168.2.129:/tmp/saved_master_binlog_from_192.168.2.128_3306_20150118175241.binlog succeeded.
Sun Jan 18 17:52:43 2015 – [info]
Sun Jan 18 17:52:43 2015 – [info] * Phase 3.4: Master Log Apply Phase..
Sun Jan 18 17:52:43 2015 – [info]
Sun Jan 18 17:52:43 2015 – [info] *NOTICE: If any error happens from this phase, manual recovery is needed.
Sun Jan 18 17:52:43 2015 – [info] Starting recovery on 192.168.2.129(192.168.2.129:3306)..
Sun Jan 18 17:52:43 2015 – [info] Generating diffs succeeded.
Sun Jan 18 17:52:43 2015 – [info] Waiting until all relay logs are applied.
Sun Jan 18 17:52:43 2015 – [info] done.
Sun Jan 18 17:52:43 2015 – [info] Getting slave status..
Sun Jan 18 17:52:43 2015 – [info] This slave(192.168.2.129)’s Exec_Master_Log_Pos equals to Read_Master_Log_Pos(mysql-bin.000015:107). No need to recover from Exec_Master_Log_Pos.
Sun Jan 18 17:52:43 2015 – [info] Connecting to the target slave host 192.168.2.129, running recover script..
Sun Jan 18 17:52:43 2015 – [info] Executing command: apply_diff_relay_logs –command=apply –slave_user=root –slave_host=192.168.2.129 –slave_ip=192.168.2.129 –slave_port=3306 –apply_files=/tmp/saved_master_binlog_from_192.168.2.128_3306_20150118175241.binlog –workdir=/tmp –target_version=5.5.30-log –timestamp=20150118175241 –handle_raw_binlog=1 –disable_log_bin=0 –manager_version=0.53 –slave_pass=xxx
Sun Jan 18 17:52:43 2015 – [info]
Applying differential binary/relay log files /tmp/saved_master_binlog_from_192.168.2.128_3306_20150118175241.binlog on 192.168.2.129:3306. This may take long time…
Applying log files succeeded.
Sun Jan 18 17:52:43 2015 – [info] All relay logs were successfully applied.
Sun Jan 18 17:52:43 2015 – [info] Getting new master’s binlog name and position..
Sun Jan 18 17:52:43 2015 – [info] mysql-bin.000005:61791
Sun Jan 18 17:52:43 2015 – [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST=’192.168.2.129′, MASTER_PORT=3306, MASTER_LOG_FILE=’mysql-bin.000005′, MASTER_LOG_POS=61791, MASTER_USER=’repl’, MASTER_PASSWORD=’xxx’;
Sun Jan 18 17:52:43 2015 – [info] Executing master IP activate script:
Sun Jan 18 17:52:43 2015 – [info] /usr/local/bin/master_ip_failover –command=start –ssh_user=root –orig_master_host=192.168.2.128 –orig_master_ip=192.168.2.128 –orig_master_port=3306 –new_master_host=192.168.2.129 –new_master_ip=192.168.2.129 –new_master_port=3306

IN SCRIPT TEST====/sbin/ifconfig eth0:1 down==/sbin/ifconfig eth0:1 192.168.2.88/24===

Enabling the VIP – 192.168.2.88/24 on the new master – 192.168.2.129
Sun Jan 18 17:52:44 2015 – [info] OK.
Sun Jan 18 17:52:44 2015 – [info] ** Finished master recovery successfully.
Sun Jan 18 17:52:44 2015 – [info] * Phase 3: Master Recovery Phase completed.
Sun Jan 18 17:52:44 2015 – [info]
Sun Jan 18 17:52:44 2015 – [info] * Phase 4: Slaves Recovery Phase..
Sun Jan 18 17:52:44 2015 – [info]
Sun Jan 18 17:52:44 2015 – [info] * Phase 4.1: Starting Parallel Slave Diff Log Generation Phase..
Sun Jan 18 17:52:44 2015 – [info]
Sun Jan 18 17:52:44 2015 – [info] — Slave diff file generation on host 192.168.2.130(192.168.2.130:3306) started, pid: 20195. Check tmp log /var/log/masterha/app1.log/192.168.2.130_3306_20150118175241.log if it takes time..
Sun Jan 18 17:52:44 2015 – [info]
Sun Jan 18 17:52:44 2015 – [info] Log messages from 192.168.2.130 …
Sun Jan 18 17:52:44 2015 – [info]
Sun Jan 18 17:52:44 2015 – [info] This server has all relay logs. No need to generate diff files from the latest slave.
Sun Jan 18 17:52:44 2015 – [info] End of log messages from 192.168.2.130.
Sun Jan 18 17:52:44 2015 – [info] — 192.168.2.130(192.168.2.130:3306) has the latest relay log events.
Sun Jan 18 17:52:44 2015 – [info] Generating relay diff files from the latest slave succeeded.
Sun Jan 18 17:52:44 2015 – [info]
Sun Jan 18 17:52:44 2015 – [info] * Phase 4.2: Starting Parallel Slave Log Apply Phase..
Sun Jan 18 17:52:44 2015 – [info]
Sun Jan 18 17:52:44 2015 – [info] — Slave recovery on host 192.168.2.130(192.168.2.130:3306) started, pid: 20197. Check tmp log /var/log/masterha/app1.log/192.168.2.130_3306_20150118175241.log if it takes time..
Sun Jan 18 17:52:45 2015 – [info]
Sun Jan 18 17:52:45 2015 – [info] Log messages from 192.168.2.130 …
Sun Jan 18 17:52:45 2015 – [info]
Sun Jan 18 17:52:44 2015 – [info] Sending binlog..
Sun Jan 18 17:52:44 2015 – [info] scp from local:/var/log/masterha/app1.log/saved_master_binlog_from_192.168.2.128_3306_20150118175241.binlog to root@192.168.2.130:/tmp/saved_master_binlog_from_192.168.2.128_3306_20150118175241.binlog succeeded.
Sun Jan 18 17:52:44 2015 – [info] Starting recovery on 192.168.2.130(192.168.2.130:3306)..
Sun Jan 18 17:52:44 2015 – [info] Generating diffs succeeded.
Sun Jan 18 17:52:44 2015 – [info] Waiting until all relay logs are applied.
Sun Jan 18 17:52:44 2015 – [info] done.
Sun Jan 18 17:52:44 2015 – [info] Getting slave status..
Sun Jan 18 17:52:44 2015 – [info] This slave(192.168.2.130)’s Exec_Master_Log_Pos equals to Read_Master_Log_Pos(mysql-bin.000015:107). No need to recover from Exec_Master_Log_Pos.
Sun Jan 18 17:52:44 2015 – [info] Connecting to the target slave host 192.168.2.130, running recover script..
Sun Jan 18 17:52:44 2015 – [info] Executing command: apply_diff_relay_logs –command=apply –slave_user=root –slave_host=192.168.2.130 –slave_ip=192.168.2.130 –slave_port=3306 –apply_files=/tmp/saved_master_binlog_from_192.168.2.128_3306_20150118175241.binlog –workdir=/tmp –target_version=5.5.25-log –timestamp=20150118175241 –handle_raw_binlog=1 –disable_log_bin=0 –manager_version=0.53 –slave_pass=xxx
Sun Jan 18 17:52:45 2015 – [info]
Applying differential binary/relay log files /tmp/saved_master_binlog_from_192.168.2.128_3306_20150118175241.binlog on 192.168.2.130:3306. This may take long time…
Applying log files succeeded.
Sun Jan 18 17:52:45 2015 – [info] All relay logs were successfully applied.
Sun Jan 18 17:52:45 2015 – [info] Resetting slave 192.168.2.130(192.168.2.130:3306) and starting replication from the new master 192.168.2.129(192.168.2.129:3306)..
Sun Jan 18 17:52:45 2015 – [info] Executed CHANGE MASTER.
Sun Jan 18 17:52:45 2015 – [info] Slave started.
Sun Jan 18 17:52:45 2015 – [info] End of log messages from 192.168.2.130.
Sun Jan 18 17:52:45 2015 – [info] — Slave recovery on host 192.168.2.130(192.168.2.130:3306) succeeded.
Sun Jan 18 17:52:45 2015 – [info] All new slave servers recovered successfully.
Sun Jan 18 17:52:45 2015 – [info]
Sun Jan 18 17:52:45 2015 – [info] * Phase 5: New master cleanup phease..
Sun Jan 18 17:52:45 2015 – [info]
Sun Jan 18 17:52:45 2015 – [info] Resetting slave info on the new master..
Sun Jan 18 17:52:45 2015 – [info] 192.168.2.129: Resetting slave info succeeded.
Sun Jan 18 17:52:45 2015 – [info] Master failover to 192.168.2.129(192.168.2.129:3306) completed successfully.
Sun Jan 18 17:52:45 2015 – [info] Deleted server1 entry from /etc/masterha/app1.cnf .
Sun Jan 18 17:52:45 2015 – [info]

—– Failover Report —–

app1: MySQL Master failover 192.168.2.128 to 192.168.2.129 succeeded

Master 192.168.2.128 is down!

Check MHA Manager logs at localhost.localdomain:/var/log/masterha/app1/manager.log for details.

Started automated(non-interactive) failover.
Invalidated master IP address on 192.168.2.128.
The latest slave 192.168.2.129(192.168.2.129:3306) has all relay logs for recovery.
Selected 192.168.2.129 as a new master.
192.168.2.129: OK: Applying all logs succeeded.
192.168.2.129: OK: Activated master IP address.
192.168.2.130: This host has the latest relay log events.
Generating relay diff files from the latest slave succeeded.
192.168.2.130: OK: Applying all logs succeeded. Slave started, replicating from 192.168.2.129.
192.168.2.129: Resetting slave info succeeded.
Master failover to 192.168.2.129(192.168.2.129:3306) completed successfully.
Sun Jan 18 17:52:45 2015 – [info] Sending mail..
Unknown option: confView Code
（6）在新的 Master192.168.2.129 上查看数据有没有同步过来，因为在还没创建 tbtest 库的时候，就停了 slave sql 线程:

mysql> use sbtest
Database changed
mysql> select count(*) from sbtest;
+———-+
| count(*) |
+———-+
| 100000 |
+———-+
1 row in set (0.03 sec)

mysql>
可以看到落后的数据也同步过来了

（7）查看来 vip 的漂移情况：

192.168.2.129 [root keepalived-1.2.12]$ ifconfig
eth0 Link encap:Ethernet HWaddr 00:0C:29:66:95:64
inet addr:192.168.2.129 Bcast:192.168.2.255 Mask:255.255.255.0
inet6 addr: fe80::20c:29ff:fe66:9564/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:48779 errors:0 dropped:0 overruns:0 frame:0
TX packets:31696 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:46635239 (44.4 MiB) TX bytes:3067487 (2.9 MiB)

eth0:1 Link encap:Ethernet HWaddr 00:0C:29:66:95:64
inet addr:192.168.2.88 Bcast:192.168.2.255 Mask:255.255.255.0
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
虚拟 IP 已经成功漂移到候选的 master 192.168.2.129 上了

在做上面通过使用脚本管理 vip 的实验时，发现很奇怪的事情，就是我查看切换成功后，我去查看再在那台是 master 库时：

192.168.2.131 [root bin]$ masterha_check_status –conf=/etc/masterha/app1.cnf
app1 is stopped(2:NOT_RUNNING).
192.168.2.131 [root bin]$ 发现 MHA Manager 挂了，这下就呆了，该不会那里配置错了吧，到时真的没想明白后来看了同学的博客得知，官网上对这种情况有解释：

意思是安装一个进程工具，通过该工具结合脚本来管理进程。可以参考官方资料：https://code.google.com/p/mysql-master-ha/wiki/Runnning_Background

为了不让大家不乱，我再次把实验环境贴出来：

角色 ip 地址主机名 server_id 类型
Monitor host 192.168.2.131 server01 – 监控复制组
Master 192.168.2.128 server02 1 写入
Candicate master 192.168.2.129 server03 2 读
Slave 192.168.2.130 server04 3 读

二. 手动 Failover（MHA Manager 必须没有运行）

当主服务器故障时，人工手动调用 MHA 来进行故障切换操作，具体命令如下：

先停 MHA Manager:

192.168.2.131 [root ~]$ masterha_stop –conf=/etc/masterha/app1.cnf
Stopped app1 successfully.
[1]+ Exit 1 nohup masterha_manager –conf=/etc/masterha/app1.cnf –remove_dead_master_conf –ignore_last_failover < /dev/null > /var/log/masterha/app1/manager.log 2>&1 (wd: /usr/local/bin)
(wd now: ~)
192.168.2.131 [root ~]$ 在 Manager 主机上操作如下：

192.168.2.131 [root bin]$ masterha_master_switch –master_state=dead –conf=/etc/masterha/app1.cnf –dead_master_host=192.168.2.128 –dead_master_port=3306 –new_master_host=192.168.2.129 –new_master_port=3306 –ignore_last_failover
–dead_master_ip=<dead_master_ip> is not set. Using 192.168.2.128.
Mon Jan 19 00:42:18 2015 – [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Mon Jan 19 00:42:18 2015 – [info] Reading application default configurations from /etc/masterha/app1.cnf..
Mon Jan 19 00:42:18 2015 – [info] Reading server configurations from /etc/masterha/app1.cnf..
Mon Jan 19 00:42:18 2015 – [info] MHA::MasterFailover version 0.56.
Mon Jan 19 00:42:18 2015 – [info] Starting master failover.
Mon Jan 19 00:42:18 2015 – [info]
Mon Jan 19 00:42:18 2015 – [info] * Phase 1: Configuration Check Phase..
Mon Jan 19 00:42:18 2015 – [info]
Mon Jan 19 00:42:19 2015 – [info] Dead Servers:
Mon Jan 19 00:42:19 2015 – [error][/usr/local/share/perl5/MHA/MasterFailover.pm, ln181] None of server is dead. Stop failover.
Mon Jan 19 00:42:19 2015 – [error][/usr/local/share/perl5/MHA/ManagerUtil.pm, ln178] Got ERROR: at /usr/local/bin/masterha_master_switch line 53
看到报错了，报错的原因：MHA manager 检测到没有 dead 的 server，将报错，并结束 failover，也就说，我们要手动关了主库，才能正常切换：

192.168.2.128 [root ~]$ /etc/init.d/mysqld stop
Shutting down MySQL… SUCCESS! 再执行手动 failover 命令：

192.168.2.131 [root bin]$ masterha_master_switch –master_state=dead –conf=/etc/masterha/app1.cnf –dead_master_host=192.168.2.128 –dead_master_port=3306 –new_master_host=192.168.2.129 –new_master_port=3306 –ignore_last_failover

–dead_master_ip=<dead_master_ip> is not set. Using 192.168.2.128.
Sun Jan 18 19:49:20 2015 – [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sun Jan 18 19:49:20 2015 – [info] Reading application default configurations from /etc/masterha/app1.cnf..
Sun Jan 18 19:49:20 2015 – [info] Reading server configurations from /etc/masterha/app1.cnf..
Sun Jan 18 19:49:20 2015 – [info] MHA::MasterFailover version 0.53.
Sun Jan 18 19:49:20 2015 – [info] Starting master failover.
Sun Jan 18 19:49:20 2015 – [info]
Sun Jan 18 19:49:20 2015 – [info] * Phase 1: Configuration Check Phase..
Sun Jan 18 19:49:20 2015 – [info]
Sun Jan 18 19:49:20 2015 – [info] Dead Servers:
Sun Jan 18 19:49:20 2015 – [info] 192.168.2.128(192.168.2.128:3306)
Sun Jan 18 19:49:20 2015 – [info] Checking master reachability via mysql(double check)..
Sun Jan 18 19:49:20 2015 – [info] ok.
Sun Jan 18 19:49:20 2015 – [info] Alive Servers:
Sun Jan 18 19:49:20 2015 – [info] 192.168.2.129(192.168.2.129:3306)
Sun Jan 18 19:49:20 2015 – [info] 192.168.2.130(192.168.2.130:3306)
Sun Jan 18 19:49:20 2015 – [info] Alive Slaves:
Sun Jan 18 19:49:20 2015 – [info] 192.168.2.129(192.168.2.129:3306) Version=5.5.30-log (oldest major version between slaves) log-bin:enabled
Sun Jan 18 19:49:20 2015 – [info] Replicating from 192.168.2.128(192.168.2.128:3306)
Sun Jan 18 19:49:20 2015 – [info] Primary candidate for the new Master (candidate_master is set)
Sun Jan 18 19:49:20 2015 – [info] 192.168.2.130(192.168.2.130:3306) Version=5.5.25-log (oldest major version between slaves) log-bin:enabled
Sun Jan 18 19:49:20 2015 – [info] Replicating from 192.168.2.128(192.168.2.128:3306)
Master 192.168.2.128 is dead. Proceed? (yes/NO): yes
Sun Jan 18 19:49:24 2015 – [info] ** Phase 1: Configuration Check Phase completed.
Sun Jan 18 19:49:24 2015 – [info]
Sun Jan 18 19:49:24 2015 – [info] * Phase 2: Dead Master Shutdown Phase..
Sun Jan 18 19:49:24 2015 – [info]
Sun Jan 18 19:49:24 2015 – [info] HealthCheck: SSH to 192.168.2.128 is reachable.
Sun Jan 18 19:49:24 2015 – [info] Forcing shutdown so that applications never connect to the current master..
Sun Jan 18 19:49:24 2015 – [info] Executing master IP deactivatation script:
Sun Jan 18 19:49:24 2015 – [info] /usr/local/bin/master_ip_failover –orig_master_host=192.168.2.128 –orig_master_ip=192.168.2.128 –orig_master_port=3306 –command=stopssh –ssh_user=root

IN SCRIPT TEST====/sbin/ifconfig eth0:1 down==/sbin/ifconfig eth0:1 192.168.2.88/24===

Disabling the VIP on old master: 192.168.2.128
Sun Jan 18 19:49:24 2015 – [info] done.
Sun Jan 18 19:49:24 2015 – [warning] shutdown_script is not set. Skipping explicit shutting down of the dead master.
Sun Jan 18 19:49:24 2015 – [info] * Phase 2: Dead Master Shutdown Phase completed.
Sun Jan 18 19:49:24 2015 – [info]
Sun Jan 18 19:49:24 2015 – [info] * Phase 3: Master Recovery Phase..
Sun Jan 18 19:49:24 2015 – [info]
Sun Jan 18 19:49:24 2015 – [info] * Phase 3.1: Getting Latest Slaves Phase..
Sun Jan 18 19:49:24 2015 – [info]
Sun Jan 18 19:49:24 2015 – [info] The latest binary log file/position on all slaves is mysql-bin.000016:107
Sun Jan 18 19:49:24 2015 – [info] Latest slaves (Slaves that received relay log files to the latest):
Sun Jan 18 19:49:24 2015 – [info] 192.168.2.129(192.168.2.129:3306) Version=5.5.30-log (oldest major version between slaves) log-bin:enabled
Sun Jan 18 19:49:24 2015 – [info] Replicating from 192.168.2.128(192.168.2.128:3306)
Sun Jan 18 19:49:24 2015 – [info] Primary candidate for the new Master (candidate_master is set)
Sun Jan 18 19:49:24 2015 – [info] 192.168.2.130(192.168.2.130:3306) Version=5.5.25-log (oldest major version between slaves) log-bin:enabled
Sun Jan 18 19:49:24 2015 – [info] Replicating from 192.168.2.128(192.168.2.128:3306)
Sun Jan 18 19:49:24 2015 – [info] The oldest binary log file/position on all slaves is mysql-bin.000016:107
Sun Jan 18 19:49:24 2015 – [info] Oldest slaves:
Sun Jan 18 19:49:24 2015 – [info] 192.168.2.129(192.168.2.129:3306) Version=5.5.30-log (oldest major version between slaves) log-bin:enabled
Sun Jan 18 19:49:24 2015 – [info] Replicating from 192.168.2.128(192.168.2.128:3306)
Sun Jan 18 19:49:24 2015 – [info] Primary candidate for the new Master (candidate_master is set)
Sun Jan 18 19:49:24 2015 – [info] 192.168.2.130(192.168.2.130:3306) Version=5.5.25-log (oldest major version between slaves) log-bin:enabled
Sun Jan 18 19:49:24 2015 – [info] Replicating from 192.168.2.128(192.168.2.128:3306)
Sun Jan 18 19:49:24 2015 – [info]
Sun Jan 18 19:49:24 2015 – [info] * Phase 3.2: Saving Dead Master’s Binlog Phase..
Sun Jan 18 19:49:24 2015 – [info]
Sun Jan 18 19:49:25 2015 – [info] Fetching dead master’s binary logs..
Sun Jan 18 19:49:25 2015 – [info] Executing command on the dead master 192.168.2.128(192.168.2.128:3306): save_binary_logs –command=save –start_file=mysql-bin.000016 –start_pos=107 –binlog_dir=/data/mysql –output_file=/tmp/saved_master_binlog_from_192.168.2.128_3306_20150118194920.binlog –handle_raw_binlog=1 –disable_log_bin=0 –manager_version=0.53
Creating /tmp if not exists.. ok.
Concat binary/relay logs from mysql-bin.000016 pos 107 to mysql-bin.000016 EOF into /tmp/saved_master_binlog_from_192.168.2.128_3306_20150118194920.binlog ..
Dumping binlog format description event, from position 0 to 107.. ok.
Dumping effective binlog data from /data/mysql/mysql-bin.000016 position 107 to tail(126).. ok.
Concat succeeded.
saved_master_binlog_from_192.168.2.128_3306_20150118194920.binlog 100% 126 0.1KB/s 00:00
Sun Jan 18 19:49:25 2015 – [info] scp from root@192.168.2.128:/tmp/saved_master_binlog_from_192.168.2.128_3306_20150118194920.binlog to local:/var/log/masterha/app1.log/saved_master_binlog_from_192.168.2.128_3306_20150118194920.binlog succeeded.
Sun Jan 18 19:49:25 2015 – [info] HealthCheck: SSH to 192.168.2.129 is reachable.
Sun Jan 18 19:49:26 2015 – [info] HealthCheck: SSH to 192.168.2.130 is reachable.
Sun Jan 18 19:49:26 2015 – [info]
Sun Jan 18 19:49:26 2015 – [info] * Phase 3.3: Determining New Master Phase..
Sun Jan 18 19:49:26 2015 – [info]
Sun Jan 18 19:49:26 2015 – [info] Finding the latest slave that has all relay logs for recovering other slaves..
Sun Jan 18 19:49:26 2015 – [info] All slaves received relay logs to the same position. No need to resync each other.
Sun Jan 18 19:49:26 2015 – [info] 192.168.2.129 can be new master.
Sun Jan 18 19:49:26 2015 – [info] New master is 192.168.2.129(192.168.2.129:3306)
Sun Jan 18 19:49:26 2015 – [info] Starting master failover..
Sun Jan 18 19:49:26 2015 – [info]
From:
192.168.2.128 (current master)
+–192.168.2.129
+–192.168.2.130

To:
192.168.2.129 (new master)
+–192.168.2.130

Starting master switch from 192.168.2.128(192.168.2.128:3306) to 192.168.2.129(192.168.2.129:3306)? (yes/NO): yes
Sun Jan 18 19:49:31 2015 – [info] New master decided manually is 192.168.2.129(192.168.2.129:3306)
Sun Jan 18 19:49:31 2015 – [info]
Sun Jan 18 19:49:31 2015 – [info] * Phase 3.3: New Master Diff Log Generation Phase..
Sun Jan 18 19:49:31 2015 – [info]
Sun Jan 18 19:49:31 2015 – [info] This server has all relay logs. No need to generate diff files from the latest slave.
Sun Jan 18 19:49:31 2015 – [info] Sending binlog..
saved_master_binlog_from_192.168.2.128_3306_20150118194920.binlog 100% 126 0.1KB/s 00:00
Sun Jan 18 19:49:31 2015 – [info] scp from local:/var/log/masterha/app1.log/saved_master_binlog_from_192.168.2.128_3306_20150118194920.binlog to root@192.168.2.129:/tmp/saved_master_binlog_from_192.168.2.128_3306_20150118194920.binlog succeeded.
Sun Jan 18 19:49:31 2015 – [info]
Sun Jan 18 19:49:31 2015 – [info] * Phase 3.4: Master Log Apply Phase..
Sun Jan 18 19:49:31 2015 – [info]
Sun Jan 18 19:49:31 2015 – [info] *NOTICE: If any error happens from this phase, manual recovery is needed.
Sun Jan 18 19:49:31 2015 – [info] Starting recovery on 192.168.2.129(192.168.2.129:3306)..
Sun Jan 18 19:49:31 2015 – [info] Generating diffs succeeded.
Sun Jan 18 19:49:31 2015 – [info] Waiting until all relay logs are applied.
Sun Jan 18 19:49:31 2015 – [info] done.
Sun Jan 18 19:49:31 2015 – [info] Getting slave status..
Sun Jan 18 19:49:31 2015 – [info] This slave(192.168.2.129)’s Exec_Master_Log_Pos equals to Read_Master_Log_Pos(mysql-bin.000016:107). No need to recover from Exec_Master_Log_Pos.
Sun Jan 18 19:49:31 2015 – [info] Connecting to the target slave host 192.168.2.129, running recover script..
Sun Jan 18 19:49:31 2015 – [info] Executing command: apply_diff_relay_logs –command=apply –slave_user=root –slave_host=192.168.2.129 –slave_ip=192.168.2.129 –slave_port=3306 –apply_files=/tmp/saved_master_binlog_from_192.168.2.128_3306_20150118194920.binlog –workdir=/tmp –target_version=5.5.30-log –timestamp=20150118194920 –handle_raw_binlog=1 –disable_log_bin=0 –manager_version=0.53 –slave_pass=xxx
Sun Jan 18 19:49:32 2015 – [info]
Applying differential binary/relay log files /tmp/saved_master_binlog_from_192.168.2.128_3306_20150118194920.binlog on 192.168.2.129:3306. This may take long time…
Applying log files succeeded.
Sun Jan 18 19:49:32 2015 – [info] All relay logs were successfully applied.
Sun Jan 18 19:49:32 2015 – [info] Getting new master’s binlog name and position..
Sun Jan 18 19:49:32 2015 – [info] mysql-bin.000005:61791
Sun Jan 18 19:49:32 2015 – [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST=’192.168.2.129′, MASTER_PORT=3306, MASTER_LOG_FILE=’mysql-bin.000005′, MASTER_LOG_POS=61791, MASTER_USER=’repl’, MASTER_PASSWORD=’xxx’;
Sun Jan 18 19:49:32 2015 – [info] Executing master IP activate script:
Sun Jan 18 19:49:32 2015 – [info] /usr/local/bin/master_ip_failover –command=start –ssh_user=root –orig_master_host=192.168.2.128 –orig_master_ip=192.168.2.128 –orig_master_port=3306 –new_master_host=192.168.2.129 –new_master_ip=192.168.2.129 –new_master_port=3306

IN SCRIPT TEST====/sbin/ifconfig eth0:1 down==/sbin/ifconfig eth0:1 192.168.2.88/24===

Enabling the VIP – 192.168.2.88/24 on the new master – 192.168.2.129
Sun Jan 18 19:49:32 2015 – [info] OK.
Sun Jan 18 19:49:32 2015 – [info] ** Finished master recovery successfully.
Sun Jan 18 19:49:32 2015 – [info] * Phase 3: Master Recovery Phase completed.
Sun Jan 18 19:49:32 2015 – [info]
Sun Jan 18 19:49:32 2015 – [info] * Phase 4: Slaves Recovery Phase..
Sun Jan 18 19:49:32 2015 – [info]
Sun Jan 18 19:49:32 2015 – [info] * Phase 4.1: Starting Parallel Slave Diff Log Generation Phase..
Sun Jan 18 19:49:32 2015 – [info]
Sun Jan 18 19:49:32 2015 – [info] — Slave diff file generation on host 192.168.2.130(192.168.2.130:3306) started, pid: 20692. Check tmp log /var/log/masterha/app1.log/192.168.2.130_3306_20150118194920.log if it takes time..
Sun Jan 18 19:49:32 2015 – [info]
Sun Jan 18 19:49:32 2015 – [info] Log messages from 192.168.2.130 …
Sun Jan 18 19:49:32 2015 – [info]
Sun Jan 18 19:49:32 2015 – [info] This server has all relay logs. No need to generate diff files from the latest slave.
Sun Jan 18 19:49:32 2015 – [info] End of log messages from 192.168.2.130.
Sun Jan 18 19:49:32 2015 – [info] — 192.168.2.130(192.168.2.130:3306) has the latest relay log events.
Sun Jan 18 19:49:32 2015 – [info] Generating relay diff files from the latest slave succeeded.
Sun Jan 18 19:49:32 2015 – [info]
Sun Jan 18 19:49:32 2015 – [info] * Phase 4.2: Starting Parallel Slave Log Apply Phase..
Sun Jan 18 19:49:32 2015 – [info]
Sun Jan 18 19:49:32 2015 – [info] — Slave recovery on host 192.168.2.130(192.168.2.130:3306) started, pid: 20694. Check tmp log /var/log/masterha/app1.log/192.168.2.130_3306_20150118194920.log if it takes time..
saved_master_binlog_from_192.168.2.128_3306_20150118194920.binlog 100% 126 0.1KB/s 00:00
Sun Jan 18 19:49:33 2015 – [info]
Sun Jan 18 19:49:33 2015 – [info] Log messages from 192.168.2.130 …
Sun Jan 18 19:49:33 2015 – [info]
Sun Jan 18 19:49:32 2015 – [info] Sending binlog..
Sun Jan 18 19:49:32 2015 – [info] scp from local:/var/log/masterha/app1.log/saved_master_binlog_from_192.168.2.128_3306_20150118194920.binlog to root@192.168.2.130:/tmp/saved_master_binlog_from_192.168.2.128_3306_20150118194920.binlog succeeded.
Sun Jan 18 19:49:33 2015 – [info] Starting recovery on 192.168.2.130(192.168.2.130:3306)..
Sun Jan 18 19:49:33 2015 – [info] Generating diffs succeeded.
Sun Jan 18 19:49:33 2015 – [info] Waiting until all relay logs are applied.
Sun Jan 18 19:49:33 2015 – [info] done.
Sun Jan 18 19:49:33 2015 – [info] Getting slave status..
Sun Jan 18 19:49:33 2015 – [info] This slave(192.168.2.130)’s Exec_Master_Log_Pos equals to Read_Master_Log_Pos(mysql-bin.000016:107). No need to recover from Exec_Master_Log_Pos.
Sun Jan 18 19:49:33 2015 – [info] Connecting to the target slave host 192.168.2.130, running recover script..
Sun Jan 18 19:49:33 2015 – [info] Executing command: apply_diff_relay_logs –command=apply –slave_user=root –slave_host=192.168.2.130 –slave_ip=192.168.2.130 –slave_port=3306 –apply_files=/tmp/saved_master_binlog_from_192.168.2.128_3306_20150118194920.binlog –workdir=/tmp –target_version=5.5.25-log –timestamp=20150118194920 –handle_raw_binlog=1 –disable_log_bin=0 –manager_version=0.53 –slave_pass=xxx
Sun Jan 18 19:49:33 2015 – [info]
Applying differential binary/relay log files /tmp/saved_master_binlog_from_192.168.2.128_3306_20150118194920.binlog on 192.168.2.130:3306. This may take long time…
Applying log files succeeded.
Sun Jan 18 19:49:33 2015 – [info] All relay logs were successfully applied.
Sun Jan 18 19:49:33 2015 – [info] Resetting slave 192.168.2.130(192.168.2.130:3306) and starting replication from the new master 192.168.2.129(192.168.2.129:3306)..
Sun Jan 18 19:49:33 2015 – [info] Executed CHANGE MASTER.
Sun Jan 18 19:49:33 2015 – [info] Slave started.
Sun Jan 18 19:49:33 2015 – [info] End of log messages from 192.168.2.130.
Sun Jan 18 19:49:33 2015 – [info] — Slave recovery on host 192.168.2.130(192.168.2.130:3306) succeeded.
Sun Jan 18 19:49:33 2015 – [info] All new slave servers recovered successfully.
Sun Jan 18 19:49:33 2015 – [info]
Sun Jan 18 19:49:33 2015 – [info] * Phase 5: New master cleanup phease..
Sun Jan 18 19:49:33 2015 – [info]
Sun Jan 18 19:49:33 2015 – [info] Resetting slave info on the new master..
Sun Jan 18 19:49:33 2015 – [info] 192.168.2.129: Resetting slave info succeeded.
Sun Jan 18 19:49:33 2015 – [info] Master failover to 192.168.2.129(192.168.2.129:3306) completed successfully.
Sun Jan 18 19:49:33 2015 – [info]

—– Failover Report —–

app1: MySQL Master failover 192.168.2.128 to 192.168.2.129 succeeded

Master 192.168.2.128 is down!

Check MHA Manager logs at localhost.localdomain for details.

Started manual(interactive) failover.
Invalidated master IP address on 192.168.2.128.
The latest slave 192.168.2.129(192.168.2.129:3306) has all relay logs for recovery.
Selected 192.168.2.129 as a new master.
192.168.2.129: OK: Applying all logs succeeded.
192.168.2.129: OK: Activated master IP address.
192.168.2.130: This host has the latest relay log events.
Generating relay diff files from the latest slave succeeded.
192.168.2.130: OK: Applying all logs succeeded. Slave started, replicating from 192.168.2.129.
192.168.2.129: Resetting slave info succeeded.
Master failover to 192.168.2.129(192.168.2.129:3306) completed successfully.
Sun Jan 18 19:49:33 2015 – [info] Sending mail..
View Code

三、MHA 的在线切换

192.168.2.131 [root bin]$ masterha_master_switch –conf=/etc/masterha/app1.cnf –master_state=alive –new_master_host=192.168.2.129 –new_master_port=3306 –orig_master_is_new_slave –running_updates_limit=10000
Mon Jan 19 01:51:39 2015 – [info] MHA::MasterRotate version 0.56.
Mon Jan 19 01:51:39 2015 – [info] Starting online master switch..
Mon Jan 19 01:51:39 2015 – [info]
Mon Jan 19 01:51:39 2015 – [info] * Phase 1: Configuration Check Phase..
Mon Jan 19 01:51:39 2015 – [info]
Mon Jan 19 01:51:39 2015 – [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Mon Jan 19 01:51:39 2015 – [info] Reading application default configuration from /etc/masterha/app1.cnf..
Mon Jan 19 01:51:39 2015 – [info] Reading server configuration from /etc/masterha/app1.cnf..
Mon Jan 19 01:51:39 2015 – [info] GTID failover mode = 0
Mon Jan 19 01:51:39 2015 – [info] Current Alive Master: 192.168.2.128(192.168.2.128:3306)
Mon Jan 19 01:51:39 2015 – [info] Alive Slaves:
Mon Jan 19 01:51:39 2015 – [info] 192.168.2.129(192.168.2.129:3306) Version=5.5.30-log (oldest major version between slaves) log-bin:enabled
Mon Jan 19 01:51:39 2015 – [info] Replicating from 192.168.2.128(192.168.2.128:3306)
Mon Jan 19 01:51:39 2015 – [info] Primary candidate for the new Master (candidate_master is set)
Mon Jan 19 01:51:39 2015 – [info] 192.168.2.130(192.168.2.130:3306) Version=5.5.25-log (oldest major version between slaves) log-bin:enabled
Mon Jan 19 01:51:39 2015 – [info] Replicating from 192.168.2.128(192.168.2.128:3306)

It is better to execute FLUSH NO_WRITE_TO_BINLOG TABLES on the master before switching. Is it ok to execute on 192.168.2.128(192.168.2.128:3306)? (YES/no): yes
Mon Jan 19 01:51:46 2015 – [info] Executing FLUSH NO_WRITE_TO_BINLOG TABLES. This may take long time..
Mon Jan 19 01:51:46 2015 – [info] ok.
Mon Jan 19 01:51:46 2015 – [info] Checking MHA is not monitoring or doing failover..
Mon Jan 19 01:51:46 2015 – [info] Checking replication health on 192.168.2.129..
Mon Jan 19 01:51:46 2015 – [info] ok.
Mon Jan 19 01:51:46 2015 – [info] Checking replication health on 192.168.2.130..
Mon Jan 19 01:51:46 2015 – [info] ok.
Mon Jan 19 01:51:46 2015 – [info] 192.168.2.129 can be new master.
Mon Jan 19 01:51:46 2015 – [info]
From:
192.168.2.128(192.168.2.128:3306) (current master)
+–192.168.2.129(192.168.2.129:3306)
+–192.168.2.130(192.168.2.130:3306)

To:
192.168.2.129(192.168.2.129:3306) (new master)
+–192.168.2.130(192.168.2.130:3306)
+–192.168.2.128(192.168.2.128:3306)

Starting master switch from 192.168.2.128(192.168.2.128:3306) to 192.168.2.129(192.168.2.129:3306)? (yes/NO): yes
Mon Jan 19 01:51:50 2015 – [info] Checking whether 192.168.2.129(192.168.2.129:3306) is ok for the new master..
Mon Jan 19 01:51:50 2015 – [info] ok.
Mon Jan 19 01:51:50 2015 – [info] 192.168.2.128(192.168.2.128:3306): SHOW SLAVE STATUS returned empty result. To check replication filtering rules, temporarily executing CHANGE MASTER to a dummy host.
Mon Jan 19 01:51:50 2015 – [info] 192.168.2.128(192.168.2.128:3306): Resetting slave pointing to the dummy host.
Mon Jan 19 01:51:50 2015 – [info] ** Phase 1: Configuration Check Phase completed.
Mon Jan 19 01:51:50 2015 – [info]
Mon Jan 19 01:51:50 2015 – [info] * Phase 2: Rejecting updates Phase..
Mon Jan 19 01:51:50 2015 – [info]
Mon Jan 19 01:51:50 2015 – [info] Executing master ip online change script to disable write on the current master:
Mon Jan 19 01:51:50 2015 – [info] /usr/local/bin/master_ip_online_change –command=stop –orig_master_host=192.168.2.128 –orig_master_ip=192.168.2.128 –orig_master_port=3306 –orig_master_user=’root’ –orig_master_password=’123456′ –new_master_host=192.168.2.129 –new_master_ip=192.168.2.129 –new_master_port=3306 –new_master_user=’root’ –new_master_password=’123456′ –orig_master_ssh_user=root –new_master_ssh_user=root –orig_master_is_new_slave
Mon Jan 19 01:51:50 2015 173112 Set read_only on the new master.. ok.
Mon Jan 19 01:51:50 2015 178943 Drpping app user on the orig master..
Mon Jan 19 01:51:50 2015 180438 Set read_only=1 on the orig master.. ok.
Mon Jan 19 01:51:50 2015 183258 Killing all application threads..
Mon Jan 19 01:51:50 2015 183387 done.
Mon Jan 19 01:51:50 2015 – [info] ok.
Mon Jan 19 01:51:50 2015 – [info] Locking all tables on the orig master to reject updates from everybody (including root):
Mon Jan 19 01:51:50 2015 – [info] Executing FLUSH TABLES WITH READ LOCK..
Mon Jan 19 01:51:50 2015 – [info] ok.
Mon Jan 19 01:51:50 2015 – [info] Orig master binlog:pos is mysql-bin.000017:107.
Mon Jan 19 01:51:50 2015 – [info] Waiting to execute all relay logs on 192.168.2.129(192.168.2.129:3306)..
Mon Jan 19 01:51:50 2015 – [info] master_pos_wait(mysql-bin.000017:107) completed on 192.168.2.129(192.168.2.129:3306). Executed 0 events.
Mon Jan 19 01:51:50 2015 – [info] done.
Mon Jan 19 01:51:50 2015 – [info] Getting new master’s binlog name and position..
Mon Jan 19 01:51:50 2015 – [info] mysql-bin.000005:61791
Mon Jan 19 01:51:50 2015 – [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST=’192.168.2.129′, MASTER_PORT=3306, MASTER_LOG_FILE=’mysql-bin.000005′, MASTER_LOG_POS=61791, MASTER_USER=’repl’, MASTER_PASSWORD=’xxx’;
Mon Jan 19 01:51:50 2015 – [info] Executing master ip online change script to allow write on the new master:
Mon Jan 19 01:51:50 2015 – [info] /usr/local/bin/master_ip_online_change –command=start –orig_master_host=192.168.2.128 –orig_master_ip=192.168.2.128 –orig_master_port=3306 –orig_master_user=’root’ –orig_master_password=’123456′ –new_master_host=192.168.2.129 –new_master_ip=192.168.2.129 –new_master_port=3306 –new_master_user=’root’ –new_master_password=’123456′ –orig_master_ssh_user=root –new_master_ssh_user=root –orig_master_is_new_slave
Mon Jan 19 01:51:50 2015 443208 Set read_only=0 on the new master.
Mon Jan 19 01:51:50 2015 444741 Creating app user on the new master..
Mon Jan 19 01:51:50 2015 – [info] ok.
Mon Jan 19 01:51:50 2015 – [info]
Mon Jan 19 01:51:50 2015 – [info] * Switching slaves in parallel..
Mon Jan 19 01:51:50 2015 – [info]
Mon Jan 19 01:51:50 2015 – [info] — Slave switch on host 192.168.2.130(192.168.2.130:3306) started, pid: 23040
Mon Jan 19 01:51:50 2015 – [info]
Mon Jan 19 01:51:50 2015 – [info] Log messages from 192.168.2.130 …
Mon Jan 19 01:51:50 2015 – [info]
Mon Jan 19 01:51:50 2015 – [info] Waiting to execute all relay logs on 192.168.2.130(192.168.2.130:3306)..
Mon Jan 19 01:51:50 2015 – [info] master_pos_wait(mysql-bin.000017:107) completed on 192.168.2.130(192.168.2.130:3306). Executed 0 events.
Mon Jan 19 01:51:50 2015 – [info] done.
Mon Jan 19 01:51:50 2015 – [info] Resetting slave 192.168.2.130(192.168.2.130:3306) and starting replication from the new master 192.168.2.129(192.168.2.129:3306)..
Mon Jan 19 01:51:50 2015 – [info] Executed CHANGE MASTER.
Mon Jan 19 01:51:50 2015 – [info] Slave started.
Mon Jan 19 01:51:50 2015 – [info] End of log messages from 192.168.2.130 …
Mon Jan 19 01:51:50 2015 – [info]
Mon Jan 19 01:51:50 2015 – [info] — Slave switch on host 192.168.2.130(192.168.2.130:3306) succeeded.
Mon Jan 19 01:51:50 2015 – [info] Unlocking all tables on the orig master:
Mon Jan 19 01:51:50 2015 – [info] Executing UNLOCK TABLES..
Mon Jan 19 01:51:50 2015 – [info] ok.
Mon Jan 19 01:51:50 2015 – [info] Starting orig master as a new slave..
Mon Jan 19 01:51:50 2015 – [info] Resetting slave 192.168.2.128(192.168.2.128:3306) and starting replication from the new master 192.168.2.129(192.168.2.129:3306)..
Mon Jan 19 01:51:50 2015 – [info] Executed CHANGE MASTER.
Mon Jan 19 01:51:50 2015 – [info] Slave started.
Mon Jan 19 01:51:50 2015 – [info] All new slave servers switched successfully.
Mon Jan 19 01:51:50 2015 – [info]
Mon Jan 19 01:51:50 2015 – [info] * Phase 5: New master cleanup phase..
Mon Jan 19 01:51:50 2015 – [info]
Mon Jan 19 01:51:50 2015 – [info] 192.168.2.129: Resetting slave info succeeded.
Mon Jan 19 01:51:50 2015 – [info] Switching master to 192.168.2.129(192.168.2.129:3306) completed successfully.
192.168.2.131 [root bin]$
View Code
在许多情况下，需要将现有的主服务器迁移到另外一台服务器上。比如主服务器硬件故障，RAID 控制卡需要重建，将主服务器移到性能更好的服务器上等等。维护主服务器引起性能下降，导致停机时间至少无法写入数据。另外，阻塞或杀掉当前运行的会话会导致主主之间数据不一致的问题发生。MHA 提供快速切换和优雅的阻塞写入，这个切换过程只需要 0.5-2s 的时间，这段时间内数据是无法写入的。在很多情况下，0.5-2s 的阻塞写入是可以接受的。因此切换主服务器不需要计划分配维护时间窗口。

MHA 在线切换的大概过程：
（1）检测复制设置和确定当前主服务器
（2）确定新的主服务器
（3）阻塞写入到当前主服务器
（4）等待所有从服务器赶上复制
（5）授予写入到新的主服务器
（6）重新设置从服务器

注意，在线切换的时候应用架构需要考虑以下两个问题：

1. 自动识别 master 和 slave 的问题（master 的机器可能会切换），如果采用了 vip 的方式，基本可以解决这个问题。

2. 负载均衡的问题（可以定义大概的读写比例，每台机器可承担的负载比例，当有机器离开集群时，需要考虑这个问题）

为了保证数据完全一致性，在最快的时间内完成切换，MHA 的在线切换必须满足以下条件才会切换成功，否则会切换失败。

(1)所有 slave 的 IO 线程都在运行

(2)所有 slave 的 SQL 线程都在运行

(3)所有的 show slave status 的输出中 Seconds_Behind_Master 参数小于或者等于 running_updates_limit 秒，如果在切换过程中不指定 running_updates_limit, 那么默认情况下 running_updates_limit 为 1 秒。

(4)在 master 端，通过 show processlist 输出，没有一个更新花费的时间大于 running_updates_limit 秒。

在线切换步骤如下：

在 MHA Manager 服务器 192.168.2.131 上操作，首先，停掉 MHA 监控：

192.168.2.131 [root ~]$ masterha_stop –conf=/etc/masterha/app1.cnf
Stopped app1 successfully.
[1]+ Exit 1 nohup masterha_manager –conf=/etc/masterha/app1.cnf –remove_dead_master_conf –ignore_last_failover < /dev/null > /var/log/masterha/app1/manager.log 2>&1 (wd: /usr/local/bin)
(wd now: ~)
192.168.2.131 [root ~]$ 执行在线切换命令：（以下是 0.53 版本的 manager 和 node 包报的错）

Starting master switch from 192.168.2.128(192.168.2.128:3306) to 192.168.2.129(192.168.2.129:3306)? (yes/NO): yes
Sun Jan 18 20:06:17 2015 – [info] Checking whether 192.168.2.129(192.168.2.129:3306) is ok for the new master..
Sun Jan 18 20:06:17 2015 – [info] ok.
Sun Jan 18 20:06:17 2015 – [info] 192.168.2.128(192.168.2.128:3306): SHOW SLAVE STATUS returned empty result. To check replication filtering rules, temporarily executing CHANGE MASTER to a dummy host.
Sun Jan 18 20:06:17 2015 – [info] 192.168.2.128(192.168.2.128:3306): Resetting slave pointing to the dummy host.
Sun Jan 18 20:06:17 2015 – [info] ** Phase 1: Configuration Check Phase completed.
Sun Jan 18 20:06:17 2015 – [info]
Sun Jan 18 20:06:17 2015 – [info] * Phase 2: Rejecting updates Phase..
Sun Jan 18 20:06:17 2015 – [info]
Sun Jan 18 20:06:17 2015 – [info] Executing master ip online change script to disable write on the current master:
Sun Jan 18 20:06:17 2015 – [info] /usr/local/bin/master_ip_online_change –command=stop –orig_master_host=192.168.2.128 –orig_master_ip=192.168.2.128 –orig_master_port=3306 –new_master_host=192.168.2.129 –new_master_ip=192.168.2.129 –new_master_port=3306
Got Error: DBI connect(‘;host=192.168.2.129;port=3306;mysql_connect_timeout=4’,”,…) failed: Access denied for user ‘root’@’192.168.2.131’ (using password: NO) at /usr/local/share/perl5/MHA/DBHelper.pm line 181
at /usr/local/bin/master_ip_online_change line 138

Sun Jan 18 20:06:17 2015 – [error][/usr/local/share/perl5/MHA/ManagerUtil.pm, ln178] Got ERROR: at /usr/local/bin/masterha_master_switch line 53
原因是脚本 master_ip_online_change 不完整，需要自己进行相应的修改，脚本中 new_master_password 这个变量获取不到，导致在线切换失败，所以进行了相关的硬编码，直接把 mysql 的 root 用户密码赋值给变量 new_master_password，但 mha4mysql-manager-0.56 和 mha4mysql-node-0.56 版本已经不需要自己把密码直接赋值了，它自己能读出来，之前版本貌似在读 new_master_password 变量时，总获取不到值（perl 脚本我也不太懂，需要大家一起来改善，哈哈）

下面来看来 0.56 版本的执行情况：

192.168.2.131 [root bin]$ masterha_master_switch –conf=/etc/masterha/app1.cnf –master_state=alive –new_master_host=192.168.2.129 –new_master_port=3306 –orig_master_is_new_slave –running_updates_limit=10000

192.168.2.131 [root bin]$ masterha_master_switch –conf=/etc/masterha/app1.cnf –master_state=alive –new_master_host=192.168.2.129 –new_master_port=3306 –orig_master_is_new_slave –running_updates_limit=10000
Mon Jan 19 01:51:39 2015 – [info] MHA::MasterRotate version 0.56.
Mon Jan 19 01:51:39 2015 – [info] Starting online master switch..
Mon Jan 19 01:51:39 2015 – [info]
Mon Jan 19 01:51:39 2015 – [info] * Phase 1: Configuration Check Phase..
Mon Jan 19 01:51:39 2015 – [info]
Mon Jan 19 01:51:39 2015 – [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Mon Jan 19 01:51:39 2015 – [info] Reading application default configuration from /etc/masterha/app1.cnf..
Mon Jan 19 01:51:39 2015 – [info] Reading server configuration from /etc/masterha/app1.cnf..
Mon Jan 19 01:51:39 2015 – [info] GTID failover mode = 0
Mon Jan 19 01:51:39 2015 – [info] Current Alive Master: 192.168.2.128(192.168.2.128:3306)
Mon Jan 19 01:51:39 2015 – [info] Alive Slaves:
Mon Jan 19 01:51:39 2015 – [info] 192.168.2.129(192.168.2.129:3306) Version=5.5.30-log (oldest major version between slaves) log-bin:enabled
Mon Jan 19 01:51:39 2015 – [info] Replicating from 192.168.2.128(192.168.2.128:3306)
Mon Jan 19 01:51:39 2015 – [info] Primary candidate for the new Master (candidate_master is set)
Mon Jan 19 01:51:39 2015 – [info] 192.168.2.130(192.168.2.130:3306) Version=5.5.25-log (oldest major version between slaves) log-bin:enabled
Mon Jan 19 01:51:39 2015 – [info] Replicating from 192.168.2.128(192.168.2.128:3306)

It is better to execute FLUSH NO_WRITE_TO_BINLOG TABLES on the master before switching. Is it ok to execute on 192.168.2.128(192.168.2.128:3306)? (YES/no): yes
Mon Jan 19 01:51:46 2015 – [info] Executing FLUSH NO_WRITE_TO_BINLOG TABLES. This may take long time..
Mon Jan 19 01:51:46 2015 – [info] ok.
Mon Jan 19 01:51:46 2015 – [info] Checking MHA is not monitoring or doing failover..
Mon Jan 19 01:51:46 2015 – [info] Checking replication health on 192.168.2.129..
Mon Jan 19 01:51:46 2015 – [info] ok.
Mon Jan 19 01:51:46 2015 – [info] Checking replication health on 192.168.2.130..
Mon Jan 19 01:51:46 2015 – [info] ok.
Mon Jan 19 01:51:46 2015 – [info] 192.168.2.129 can be new master.
Mon Jan 19 01:51:46 2015 – [info]
From:
192.168.2.128(192.168.2.128:3306) (current master)
+–192.168.2.129(192.168.2.129:3306)
+–192.168.2.130(192.168.2.130:3306)

To:
192.168.2.129(192.168.2.129:3306) (new master)
+–192.168.2.130(192.168.2.130:3306)
+–192.168.2.128(192.168.2.128:3306)

Starting master switch from 192.168.2.128(192.168.2.128:3306) to 192.168.2.129(192.168.2.129:3306)? (yes/NO): yes
Mon Jan 19 01:51:50 2015 – [info] Checking whether 192.168.2.129(192.168.2.129:3306) is ok for the new master..
Mon Jan 19 01:51:50 2015 – [info] ok.
Mon Jan 19 01:51:50 2015 – [info] 192.168.2.128(192.168.2.128:3306): SHOW SLAVE STATUS returned empty result. To check replication filtering rules, temporarily executing CHANGE MASTER to a dummy host.
Mon Jan 19 01:51:50 2015 – [info] 192.168.2.128(192.168.2.128:3306): Resetting slave pointing to the dummy host.
Mon Jan 19 01:51:50 2015 – [info] ** Phase 1: Configuration Check Phase completed.
Mon Jan 19 01:51:50 2015 – [info]
Mon Jan 19 01:51:50 2015 – [info] * Phase 2: Rejecting updates Phase..
Mon Jan 19 01:51:50 2015 – [info]
Mon Jan 19 01:51:50 2015 – [info] Executing master ip online change script to disable write on the current master:
Mon Jan 19 01:51:50 2015 – [info] /usr/local/bin/master_ip_online_change –command=stop –orig_master_host=192.168.2.128 –orig_master_ip=192.168.2.128 –orig_master_port=3306 –orig_master_user=’root’ –orig_master_password=’123456′ –new_master_host=192.168.2.129 –new_master_ip=192.168.2.129 –new_master_port=3306 –new_master_user=’root’ –new_master_password=’123456′ –orig_master_ssh_user=root –new_master_ssh_user=root –orig_master_is_new_slave
Mon Jan 19 01:51:50 2015 173112 Set read_only on the new master.. ok.
Mon Jan 19 01:51:50 2015 178943 Drpping app user on the orig master..
Mon Jan 19 01:51:50 2015 180438 Set read_only=1 on the orig master.. ok.
Mon Jan 19 01:51:50 2015 183258 Killing all application threads..
Mon Jan 19 01:51:50 2015 183387 done.
Mon Jan 19 01:51:50 2015 – [info] ok.
Mon Jan 19 01:51:50 2015 – [info] Locking all tables on the orig master to reject updates from everybody (including root):
Mon Jan 19 01:51:50 2015 – [info] Executing FLUSH TABLES WITH READ LOCK..
Mon Jan 19 01:51:50 2015 – [info] ok.
Mon Jan 19 01:51:50 2015 – [info] Orig master binlog:pos is mysql-bin.000017:107.
Mon Jan 19 01:51:50 2015 – [info] Waiting to execute all relay logs on 192.168.2.129(192.168.2.129:3306)..
Mon Jan 19 01:51:50 2015 – [info] master_pos_wait(mysql-bin.000017:107) completed on 192.168.2.129(192.168.2.129:3306). Executed 0 events.
Mon Jan 19 01:51:50 2015 – [info] done.
Mon Jan 19 01:51:50 2015 – [info] Getting new master’s binlog name and position..
Mon Jan 19 01:51:50 2015 – [info] mysql-bin.000005:61791
Mon Jan 19 01:51:50 2015 – [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST=’192.168.2.129′, MASTER_PORT=3306, MASTER_LOG_FILE=’mysql-bin.000005′, MASTER_LOG_POS=61791, MASTER_USER=’repl’, MASTER_PASSWORD=’xxx’;
Mon Jan 19 01:51:50 2015 – [info] Executing master ip online change script to allow write on the new master:
Mon Jan 19 01:51:50 2015 – [info] /usr/local/bin/master_ip_online_change –command=start –orig_master_host=192.168.2.128 –orig_master_ip=192.168.2.128 –orig_master_port=3306 –orig_master_user=’root’ –orig_master_password=’123456′ –new_master_host=192.168.2.129 –new_master_ip=192.168.2.129 –new_master_port=3306 –new_master_user=’root’ –new_master_password=’123456′ –orig_master_ssh_user=root –new_master_ssh_user=root –orig_master_is_new_slave
Mon Jan 19 01:51:50 2015 443208 Set read_only=0 on the new master.
Mon Jan 19 01:51:50 2015 444741 Creating app user on the new master..
Mon Jan 19 01:51:50 2015 – [info] ok.
Mon Jan 19 01:51:50 2015 – [info]
Mon Jan 19 01:51:50 2015 – [info] * Switching slaves in parallel..
Mon Jan 19 01:51:50 2015 – [info]
Mon Jan 19 01:51:50 2015 – [info] — Slave switch on host 192.168.2.130(192.168.2.130:3306) started, pid: 23040
Mon Jan 19 01:51:50 2015 – [info]
Mon Jan 19 01:51:50 2015 – [info] Log messages from 192.168.2.130 …
Mon Jan 19 01:51:50 2015 – [info]
Mon Jan 19 01:51:50 2015 – [info] Waiting to execute all relay logs on 192.168.2.130(192.168.2.130:3306)..
Mon Jan 19 01:51:50 2015 – [info] master_pos_wait(mysql-bin.000017:107) completed on 192.168.2.130(192.168.2.130:3306). Executed 0 events.
Mon Jan 19 01:51:50 2015 – [info] done.
Mon Jan 19 01:51:50 2015 – [info] Resetting slave 192.168.2.130(192.168.2.130:3306) and starting replication from the new master 192.168.2.129(192.168.2.129:3306)..
Mon Jan 19 01:51:50 2015 – [info] Executed CHANGE MASTER.
Mon Jan 19 01:51:50 2015 – [info] Slave started.
Mon Jan 19 01:51:50 2015 – [info] End of log messages from 192.168.2.130 …
Mon Jan 19 01:51:50 2015 – [info]
Mon Jan 19 01:51:50 2015 – [info] — Slave switch on host 192.168.2.130(192.168.2.130:3306) succeeded.
Mon Jan 19 01:51:50 2015 – [info] Unlocking all tables on the orig master:
Mon Jan 19 01:51:50 2015 – [info] Executing UNLOCK TABLES..
Mon Jan 19 01:51:50 2015 – [info] ok.
Mon Jan 19 01:51:50 2015 – [info] Starting orig master as a new slave..
Mon Jan 19 01:51:50 2015 – [info] Resetting slave 192.168.2.128(192.168.2.128:3306) and starting replication from the new master 192.168.2.129(192.168.2.129:3306)..
Mon Jan 19 01:51:50 2015 – [info] Executed CHANGE MASTER.
Mon Jan 19 01:51:50 2015 – [info] Slave started.
Mon Jan 19 01:51:50 2015 – [info] All new slave servers switched successfully.
Mon Jan 19 01:51:50 2015 – [info]
Mon Jan 19 01:51:50 2015 – [info] * Phase 5: New master cleanup phase..
Mon Jan 19 01:51:50 2015 – [info]
Mon Jan 19 01:51:50 2015 – [info] 192.168.2.129: Resetting slave info succeeded.
Mon Jan 19 01:51:50 2015 – [info] Switching master to 192.168.2.129(192.168.2.129:3306) completed successfully.
192.168.2.131 [root bin]$
View Code
参数说明：

–orig_master_is_new_slave 切换时加上此参数是将原 master 变为 slave 节点，如果不加此参数，原来的 master 将不启动

–running_updates_limit=10000, 故障切换时, 候选 master 如果有延迟的话，mha 切换不能成功，加上此参数表示延迟在此时间范围内都可切换（单位为 s），但是切换的时间长短是由 recover 时 relay 日志的大小决定 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_is_new_slave, $orig_master_host,
$orig_master_ip, $orig_master_port, $orig_master_user,
$orig_master_password, $orig_master_ssh_user, $new_master_host,
$new_master_ip, $new_master_port, $new_master_user,
$new_master_password, $new_master_ssh_user
);
my $vip = ‘192.168.2.88/24’;
my $key = ‘1’;
my $ssh_start_vip = “/sbin/ifconfig eth0:$key $vip”;
my $ssh_stop_vip = “/sbin/ifconfig eth0:$key down”;
my $orig_master_ssh_port = 22;
my $new_master_ssh_port = 22;
GetOptions(
‘command=s’ => \$command,
‘orig_master_is_new_slave’ => \$orig_master_is_new_slave,
‘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,
‘orig_master_ssh_user=s’ => \$orig_master_ssh_user,
‘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,
‘new_master_ssh_user=s’ => \$new_master_ssh_user,
‘orig_master_ssh_port=i’ => \$orig_master_ssh_port,
‘new_master_ssh_port=i’ => \$new_master_ssh_port,
);

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} );
}

## 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
eval {
`ssh -p$orig_master_ssh_port $orig_master_ssh_user\@$orig_master_host \” $ssh_stop_vip \”`;
};
if ($@) {
warn $@;
}
$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
`ssh -p$new_master_ssh_port $new_master_ssh_user\@$new_master_host \” $ssh_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;
}
}

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=port\n”;
die;
}
View Code
说明可以参考官网：https://code.google.com/p/mysql-master-ha/wiki/Parameters#master_ip_online_change_script（自备梯子）

2、修复宕机的 Master

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

从上面信息可以看到：

All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST=’192.168.2.129′, MASTER_PORT=3306, MASTER_LOG_FILE=’mysql-bin.000005′, MASTER_LOG_POS=61791, MASTER_USER=’repl’, MASTER_PASSWORD=’xxx’; 意思是说，如果 Master 主机修复好了，可以在修复好后的 Master 执行 CHANGE MASTER 操作，作为新的 slave 库。

目前高可用方案可以一定程度上实现数据库的高可用，比如前面文章介绍的 MMM，heartbeat+drbd，Cluster 等。还有 percona 的 Galera Cluster 等。这些高可用软件各有优劣。在进行高可用方案选择时，主要是看业务还有对数据一致性方面的要求。最后出于对数据库的高可用和数据一致性的要求，推荐使用 MHA 架构。

总结：

一、尽信书, 不如不信，有时按着书本上做某个实验，书上能做出来，但不代表我们按着步骤走也能成功

二、要学会从官网找资料，这样让你懂得更多，因为有时谷歌、百度都找不出相关的资料

三、MHA 的实验要多测试，要理解它的切换过程，有很多人搭建完，测试到能转换就完事了，这是远远不够的。

四、MHA 环境搭建好后，很多 perl 脚本是不够完善的，需要自己去修改，很多问题都是 perl 脚本引起的

五、每个人的搭建环境不一样，遇到的问题可能也不一样，这很正常，只要肯下功夫，终能解决的

参考资料：

https://code.google.com/p/mysql-master-ha/wiki/TableOfContents?tm=6

大部份 MHA 理论知识参考很赞的书籍《深入浅出 MySQL》（第 2 版）http://www.linuxidc.com/Linux/2016-05/130922.htm

本文永久更新链接地址：http://www.linuxidc.com/Linux/2016-05/130923.htm