[TOC]

简介

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工作原理总结为如下

从宕机崩溃的master保存二进制日志事件（binlog events）
识别含有最新更新的slave
应用差异的中继日志（relay log）到其他的slave；
应用从master保存的二进制日志事件（binlog events）；
提升一个slave为新的master；
使其他的slave连接新的master进行复制；

Manager工具包

组件名称

组件说明

masterha_check_ssh

检查MHA的SSH配置状况

masterha_check_repl

检查MySQL复制状况

masterha_manger

启动MHA

masterha_check_status

检测当前MHA运行状态

masterha_master_monitor

检测master是否宕机

masterha_master_switch

控制故障转移（自动或者手动）

masterha_conf_host

添加或删除配置的server信息

Node工具包

这些工具通常由MHA Manager的脚本触发，无需人为操作

组件名称

组件说明

save_binary_logs

保存和复制master的二进制日志

apply_diff_relay_logs

识别差异的中继日志事件并将其差异的事件应用于其他的slave

filter_mysqlbinlog

去除不必要的ROLLBACK事件（MHA已不再使用这个工具）

purge_relay_logs

清除中继日志（不会阻塞SQL线程）

注意：

为了尽可能的减少主库硬件损坏宕机造成的数据丢失，因此在配置MHA的同时建议配置成MySQL 5.5的半同步复制。关于半同步复制原理各位自己进行查阅。（不是必须）

环境准备

操作系统

内核版本

主机名

MySQL 版本

ip地址

角色

centos 7.5

5.1.3-1.el7

manager.mha

MySQL 5.7.18

10.0.20.200

Manager

centos 7.5

5.1.3-1.el7

node01.mha

MySQL 5.7.18

10.0.20.201

node01 mysql-master

centos 7.5

5.1.3-1.el7

node02.mha

MySQL 5.7.18

10.0.20.202

node02 mysql-slave

centos 7.5

5.1.3-1.el7

node03.mha

MySQL 5.7.18

10.0.20.203

node03 mysql-slave

centos 7.5

5.1.3-1.el7

node04.mha

MySQL 5.7.18

10.0.20.204

node04 mysql-slave

MHA Manager 版本

GitHub下载地址

百度网盘下载地址

v0.58

GitHub下载地址

百度网盘地址提取码：lzb0

MHA Node 版本

GitHub下载地址

百度网盘下载地址

v0.58

GitHub下载地址

百度网盘地址提取码：4e6h

秘钥互信

配置所有机器相互之间root用户秘钥互信

在所有机器上执行:

生成密钥对

ssh-keygen -t dsa -f ~/.ssh/id_rsa -P ""

推送公钥

ssh-copy-id -i /root/.ssh/id_rsa.pub root@10.0.20.200 ssh-copy-id -i /root/.ssh/id_rsa.pub root@10.0.20.201 ssh-copy-id -i /root/.ssh/id_rsa.pub root@10.0.20.202 ssh-copy-id -i /root/.ssh/id_rsa.pub root@10.0.20.203 ssh-copy-id -i /root/.ssh/id_rsa.pub root@10.0.20.204

此时所有的机器之间以完成互信，无需密码等即可ssh登陆

安装基础依赖包

在所有机器上执行:

yum install -y perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker perl-CPAN perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes

安装MHA组件

安装 MHA Node组件

在所有节点上执行

1[root@node01 ~]# cd /opt/soft
2[root@node01 soft]# ll
3total 639152
4-rw-r--r-- 1 root root     56220 Jun 12 17:59 mha4mysql-node-0.58.tar.gz
5-rw-r--r-- 1 root root 654430368 Jun 11 11:21 mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz

解压安装

具体命令执行输出就不复制出来了

1[root@node01 soft]# tar xf mha4mysql-node-0.58.tar.gz
2[root@node01 soft]# cd mha4mysql-node-0.58
3[root@node01 mha4mysql-node-0.58]# perl Makefile.PL
4[root@node01 mha4mysql-node-0.58]# make && make install

Node安装完成后会得到四个工具

1[root@node01 mha4mysql-node-0.58]# ll /usr/local/bin/
2total 48
3-r-xr-xr-x 1 root root 17639 Jun 13 15:00 apply_diff_relay_logs
4-r-xr-xr-x 1 root root  4807 Jun 13 15:00 filter_mysqlbinlog
5-r-xr-xr-x 1 root root  8337 Jun 13 15:00 purge_relay_logs
6-r-xr-xr-x 1 root root  7525 Jun 13 15:00 save_binary_logs

安装 MHA Manager 组件

在 Manager 节点执行安装

不用在Node节点上安装

1[root@manager soft]# tar xf mha4mysql-manager-0.58.tar.gz 
2[root@manager soft]# cd mha4mysql-manager-0.58
3[root@manager mha4mysql-manager-0.58]# ls
4AUTHORS  bin  COPYING  debian  inc  lib  Makefile.PL  MANIFEST  META.yml  README  rpm  samples  t  tests
5[root@manager mha4mysql-manager-0.58]# perl Makefile.PL
6[root@manager mha4mysql-manager-0.58]# make && make install

查看 Manager 工具

1[root@manager mha4mysql-manager-0.58]# ll /usr/local/bin/
2total 88
3-r-xr-xr-x 1 root root 17639 Jun 13 15:10 apply_diff_relay_logs
4-r-xr-xr-x 1 root root  4807 Jun 13 15:10 filter_mysqlbinlog
5-r-xr-xr-x 1 root root  1995 Jun 13 15:13 masterha_check_repl
6-r-xr-xr-x 1 root root  1779 Jun 13 15:13 masterha_check_ssh
7-r-xr-xr-x 1 root root  1865 Jun 13 15:13 masterha_check_status
8-r-xr-xr-x 1 root root  3201 Jun 13 15:13 masterha_conf_host
9-r-xr-xr-x 1 root root  2517 Jun 13 15:13 masterha_manager
10-r-xr-xr-x 1 root root  2165 Jun 13 15:13 masterha_master_monitor
11-r-xr-xr-x 1 root root  2373 Jun 13 15:13 masterha_master_switch
12-r-xr-xr-x 1 root root  5172 Jun 13 15:13 masterha_secondary_check
13-r-xr-xr-x 1 root root  1739 Jun 13 15:13 masterha_stop
14-r-xr-xr-x 1 root root  8337 Jun 13 15:10 purge_relay_logs
15-r-xr-xr-x 1 root root  7525 Jun 13 15:10 save_binary_logs

建立 MySQL 一主三从

本文章主要实现是MHA集群，MySQL集群直接贴命令和my.cnf配置

在四台 Node 节点上，实现，node01 为 master，剩下三个 node 为 slave 。

1[root@node01 mysql-5.7]# rpm -qa |grep mariadb | xargs rpm -e --nodeps
2[root@node01 soft]# useradd -s /sbin/nologin -M mysql
3[root@node01 soft]# tar xf mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz
4[root@node01 soft]# mv mysql-5.7.18-linux-glibc2.5-x86_64 mysql-5.7
5[root@node01 soft]# mv mysql-5.7 /usr/local/
6[root@node01 soft]# ln -s /usr/local/mysql-5.7 /usr/local/mysql
7[root@node01 soft]# cd /usr/local/mysql-5.7
8[root@node01 mysql-5.7]# echo 'export PATH=$PATH:/usr/local/mysql-5.7/bin' >> /etc/profile
9[root@node01 mysql-5.7]# source /etc/profile
10[root@node01 mysql-5.7]# mysql -V
11mysql  Ver 14.14 Distrib 5.7.18, for linux-glibc2.5 (x86_64) using  EditLine wrapper
12[root@node01 mysql-5.7]# cp support-files/mysql.server /etc/init.d/mysqld
13[root@node01 mysql-5.7]# sed -i 's@/etc/my.cnf@/usr/local/mysql-5.7/my.cnf@g' /etc/init.d/mysqld
14[root@node01 mysql-5.7]# sed -i 's@/usr/local/mysql/data@/opt/mysql_data@g' /etc/init.d/mysqld
15[root@node01 mysql-5.7]# chkconfig mysqld on
16[root@node01 mysql-5.7]# mkdir /opt/mysql_data
17[root@node01 mysql-5.7]# chown -R mysql.mysql /usr/local/mysql-5.7
18[root@node01 mysql-5.7]# chown -R mysql.mysql /opt/mysql_data
19[root@node01 mysql-5.7]#ln -s /usr/local/mysql/bin/mysqlbinlog /usr/local/bin/mysqlbinlog
20[root@node01 mysql-5.7]#ln -s /usr/local/mysql/bin/mysql /usr/local/bin/mysql

my.cnf 配置文件

注意需要把my.cnf 中的server-id的的值四台node不能重复，否则主从会建立失败。

1[root@node04 mysql-5.7]# cat my.cnf 
2[client]
3socket = /tmp/mysql.sock
4port=3306
5
6[mysql]
7default-character-set=utf8
8socket = /tmp/mysql.sock
9
10[mysqld]
11socket = /tmp/mysql.sock
12character-set-server=utf8
13basedir=/usr/local/mysql-5.7
14datadir=/opt/mysql_data
15port=3306
16pid-file=/opt/mysql_data/mysqld.pid
17
18# 四台node不可重复
19server-id=204
20
21skip-name-resolve
22
23default-storage-engine=INNODB
24explicit_defaults_for_timestamp = true
25
26gtid_mode = on	
27enforce_gtid_consistency = 1
28log_slave_updates = 1
29
30plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
31loose_rpl_semi_sync_master_enabled = 1
32loose_rpl_semi_sync_slave_enabled = 1
33loose_rpl_semi_sync_master_timeout = 5000
34
35
36relay-log = mysql-relay-bin
37replicate-wild-ignore-table=mysql.%
38replicate-wild-ignore-table=test.%
39replicate-wild-ignore-table=information_schema.%
40
41max_connections=2000
42query_cache_size=0
43table_open_cache=2000
44tmp_table_size=246M
45thread_cache_size=300
46thread_stack = 192k
47key_buffer_size=512M
48read_buffer_size=4M
49read_rnd_buffer_size=32M
50
51
52innodb_data_home_dir = /opt/mysql_data
53innodb_flush_log_at_trx_commit=0
54innodb_log_buffer_size=16M
55
56# 此选项修改为实际运行mysql机器内存的%60 - %80
57innodb_buffer_pool_size=13G
58
59innodb_log_file_size=128M
60innodb_thread_concurrency=128
61innodb_autoextend_increment=1000
62innodb_buffer_pool_instances=8
63innodb_concurrency_tickets=5000
64innodb_old_blocks_time=1000
65innodb_open_files=300
66innodb_stats_on_metadata=0
67innodb_file_per_table=1
68innodb_checksum_algorithm=0
69
70back_log = 80
71flush_time = 0
72join_buffer_size = 128M
73max_allowed_packet = 1024M
74max_connect_errors = 2000
75open_files_limit = 4161
76query_cache_type = 0
77sort_buffer_size = 32M
78table_definition_cache = 1400
79binlog_row_event_max_size = 8K
80sync_master_info = 10000
81sync_relay_log = 10000
82sync_relay_log_info = 10000
83bulk_insert_buffer_size = 64M
84interactive_timeout = 120
85wait_timeout = 120
86log-bin-trust-function-creators=1
87sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 
88
89[mysqld_safe]
90log-error = /opt/mysql_data/error.log
91pid-file = /opt/mysql_data/mysqld.pid

初始化 MySQL

node01

1[root@node01 mysql-5.7]# mysqld --initialize  --user=mysql --basedir=/usr/local/mysql-5.7 --datadir=/opt/mysql_data
22019-06-13T07:59:00.947482Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
32019-06-13T07:59:01.056859Z 0 [Warning] InnoDB: New log files created, LSN=45790
42019-06-13T07:59:01.076218Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
52019-06-13T07:59:01.129463Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 1ae29152-8db1-11e9-9d54-005056990727.
62019-06-13T07:59:01.129873Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
72019-06-13T07:59:01.130247Z 1 [Note] A temporary password is generated for root@localhost: 1qGoEiI7ga#U

node02

1[root@node02 mysql-5.7]# mysqld --initialize  --user=mysql --basedir=/usr/local/mysql-5.7 --datadir=/opt/mysql_data
22019-06-13T07:59:00.952176Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
32019-06-13T07:59:01.092736Z 0 [Warning] InnoDB: New log files created, LSN=45790
42019-06-13T07:59:01.116696Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
52019-06-13T07:59:01.171324Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 1ae8f47b-8db1-11e9-b8bb-0050569972c0.
62019-06-13T07:59:01.171711Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
72019-06-13T07:59:01.172126Z 1 [Note] A temporary password is generated for root@localhost: qTwtKAOue7:o

node03

1[root@node03 mysql-5.7]# mysqld --initialize  --user=mysql --basedir=/usr/local/mysql-5.7 --datadir=/opt/mysql_data
22019-06-13T07:59:00.949924Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
32019-06-13T07:59:01.090890Z 0 [Warning] InnoDB: New log files created, LSN=45790
42019-06-13T07:59:01.116166Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
52019-06-13T07:59:01.171335Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 1ae8f4ef-8db1-11e9-b6ae-0050569975f7.
62019-06-13T07:59:01.171753Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
72019-06-13T07:59:01.172159Z 1 [Note] A temporary password is generated for root@localhost: XIu,h#*HQ5&M

node04

12019-06-13T07:59:00.955598Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
22019-06-13T07:59:01.090420Z 0 [Warning] InnoDB: New log files created, LSN=45790
32019-06-13T07:59:01.113972Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
42019-06-13T07:59:01.166754Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 1ae84210-8db1-11e9-b6fe-005056992c6b.
52019-06-13T07:59:01.167145Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
62019-06-13T07:59:01.167537Z 1 [Note] A temporary password is generated for root@localhost: 26jvaV)XAy>G

执行完初始化操作后，最后会给予root的默认密码，使用此密码登陆后，要第一时间修改root密码，否则不允许操作数据库；

alter user 'root'@'localhost' identified by '123456';

启动MySQL 并简单配置

1# /etc/init.d/mysqld start
2Starting MySQL.Logging to '/opt/mysql_data/error.log'.
3.. SUCCESS!

登陆MySQL 并修改密码

1[root@node01 mysql-5.7]# mysql -uroot -p
2Enter password: 
3Welcome to the MySQL monitor.  Commands end with ; or \g.
4Your MySQL connection id is 3
5Server version: 5.7.18
6
7Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
8
9Oracle is a registered trademark of Oracle Corporation and/or its
10affiliates. Other names may be trademarks of their respective
11owners.
12
13Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
14
15mysql> alter user user() identified by "123456";
16Query OK, 0 rows affected (0.00 sec)

所有mysql增加主从用户

1mysql> grant replication slave on *.* to 'repl'@'10.0.20.%' identified by '123456';
2Query OK, 0 rows affected, 1 warning (0.00 sec)
3
4mysql> grant all on *.* to 'root'@'%' identified by '123456';
5Query OK, 0 rows affected, 1 warning (0.00 sec)
6
7mysql> flush privileges;
8Query OK, 0 rows affected (0.00 sec)

建立一主三从

node01 的MySQL执行

1mysql> show master status;
2+------------------+----------+--------------+------------------+-------------------+
3| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
4+------------------+----------+--------------+------------------+-------------------+
5| mysql-bin.000002 |      154 |              |                  |                   |
6+------------------+----------+--------------+------------------+-------------------+
71 row in set (0.00 sec)

node02、node03、node04 都执行下列语句

change master to master_host='10.0.20.201',master_user='repl',master_password='123456',master_log_file='mysql-bin.000002',master_log_pos=463;

show slave status\G; #查看slave IO和slave sql是否都正常

特别说明

下面开始配置Manager机器，本人的所有机器，均做了bond网卡绑定，所有机器的网卡名都为bond0，大家根据自己的网卡名称自行修改，还有发送邮件的邮箱以及微信公众号的相关配置，均需要修改为自己的。

本次是用vip 是: 10.0.20.199

大家根据自己的情况，做出对应的修改。

MHA Manager 配置

下面配置，均在manager机器上操作。

1# 创建MHA配置文件目录
2mkdir /etc/mha
3# 创建MHA脚本目录
4mkdir /etc/mha/scripts
5# 创建MHA日志目录
6mkdir /var/log/mha/
7# 创建日志目录
8mkdir /var/log/mha/app1 -p
9# 创建日志文件
10touch /var/log/mha/app1/manager.log

MHA 配置文件

1[root@manager mha]# cat /etc/masterha_default.cnf 
2
3[server default]
4user=root
5password=SIjiayong.123
6
7repl_user=repl
8repl_password=SIjiayong.123
9
10ssh_user=root
11
12ping_interval=1
13master_binlog_dir=/opt/mysql_data
14
15manager_workdir=/var/log/mha/app1.log
16manager_log=/var/log/mha/manager.log
17master_ip_failover_script="/etc/mha/scripts/master_ip_failover"
18master_ip_online_change_script="/etc/mha/scripts/master_ip_online_change"
19report_script="/etc/mha/scripts/send_report"
20remote_workdir=/tmp
21secondary_check_script= /usr/local/bin/masterha_secondary_check -s 10.0.20.201 -s 10.0.20.202 -s 10.0.20.203 -s 10.0.20.204
22shutdown_script=""
23
24
25[root@manager ~]# cat /etc/mha/app1.cnf 
26[server1]
27hostname=10.0.20.201
28port=3306
29
30[server2]
31hostname=10.0.20.202
32port=3306
33candidate_master=1
34check_repl_delay=0
35
36[server3]
37hostname=10.0.20.203
38port=3306
39
40[server4]
41hostname=10.0.20.204
42port=3306
43

配置文件说明

MHA主要配置文件说明

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的日志
master_ip_failover_script= /usr/local/bin/master_ip_failover：设置自动failover时候的切换脚本
master_ip_online_change_script= /usr/local/bin/master_ip_online_change：设置手动切换时候的切换脚本
user=root：设置监控mysql的用户
password=dayi123：设置监控mysql的用户，需要授权能够在manager节点远程登录
ping_interval=1：设置监控主库，发送ping包的时间间隔，默认是3秒，尝试三次没有回应的时候自动进行railover
remote_workdir=/tmp：设置远端mysql在发生切换时binlog的保存位置
repl_user=repl ：设置mysql中用于复制的用户密码
repl_password=replication：设置mysql中用于复制的用户
report_script=/usr/local/send_report：设置发生切换后发送的报警的脚本
shutdown_script=""：设置故障发生后关闭故障主机脚本（该脚本的主要作用是关闭主机放在发生脑裂,这里没有使用）
ssh_user=root //设置ssh的登录用户名
candidate_master=1：在节点下设置，设置当前节点为候选的master
slave check_repl_delay=0 :在节点配置下设置，默认情况下如果一个slave落后master 100M的relay logs的话，MHA将不会选择该slave作为一个新的master；这个选项对于对于设置了candidate_master=1的主机非常有用

脚本配置

自动 VIP 管理配置

#为了防止脑裂发生,推荐生产环境采用脚本的方式来管理虚拟 ip,而不是使用 keepalived来完成

vim /etc/mha/scripts/master_ip_failover

1#!/usr/bin/env perl
2
3use strict;
4use warnings FATAL => 'all';
5
6use Getopt::Long;
7
8my (
9    $command,          $ssh_user,        $orig_master_host, $orig_master_ip,
10    $orig_master_port, $new_master_host, $new_master_ip,    $new_master_port
11);
12
13my $vip = '10.0.20.199/24';
14my $key = '1';
15my $ssh_start_vip = "/sbin/ifconfig bond0:$key $vip";
16my $ssh_stop_vip = "/sbin/ifconfig bond0:$key down";
17
18GetOptions(
19    'command=s'          => \$command,
20    'ssh_user=s'         => \$ssh_user,
21    'orig_master_host=s' => \$orig_master_host,
22    'orig_master_ip=s'   => \$orig_master_ip,
23    'orig_master_port=i' => \$orig_master_port,
24    'new_master_host=s'  => \$new_master_host,
25    'new_master_ip=s'    => \$new_master_ip,
26    'new_master_port=i'  => \$new_master_port,
27);
28
29exit &main();
30
31sub main {
32
33    print "\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n";
34
35    if ( $command eq "stop" || $command eq "stopssh" ) {
36
37        my $exit_code = 1;
38        eval {
39            print "Disabling the VIP on old master: $orig_master_host \n";
40            &stop_vip();
41            $exit_code = 0;
42        };
43        if ($@) {
44            warn "Got Error: $@\n";
45            exit $exit_code;
46        }
47        exit $exit_code;
48    }
49    elsif ( $command eq "start" ) {
50
51        my $exit_code = 10;
52        eval {
53            print "Enabling the VIP - $vip on the new master - $new_master_host \n";
54            &start_vip();
55            $exit_code = 0;
56        };
57        if ($@) {
58            warn $@;
59            exit $exit_code;
60        }
61        exit $exit_code;
62    }
63    elsif ( $command eq "status" ) {
64        print "Checking the Status of the script.. OK \n";
65        exit 0;
66    }
67    else {
68        &usage();
69        exit 1;
70    }
71}
72
73sub start_vip() {
74    `ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`;
75}
76sub stop_vip() {
77     return 0  unless  ($ssh_user);
78    `ssh $ssh_user\@$orig_master_host \" $ssh_stop_vip \"`;
79}
80
81sub usage {
82    print
83    "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";
84}

配置邮件和微信报警脚本

1# 安装发送邮件的工具
2yum install mailx -y

mail邮件发送程序，需要先配置好发送这信息

vim /etc/mail.rc

1set from=*****@163.com
2set smtp=smtp.163.com
3set smtp-auth-user=*****
4#拿163邮箱来说这个不是密码，而是授权码
5set smtp-auth-password=*****
6set smtp-auth=login

这是具体的邮件和微信发送脚本

vim /etc/mha/scripts/send_report

1#!/bin/bash
2source /root/.bash_profile
3# 解析变量
4orig_master_host=`echo "$1" | awk -F = '{print $2}'`
5new_master_host=`echo "$2" | awk -F = '{print $2}'`
6new_slave_hosts=`echo "$3" | awk -F = '{print $2}'`
7subject=`echo "$4" | awk -F = '{print $2}'`
8body=`echo "$5" | awk -F = '{print $2}'`
9#定义收件人地址
10email="***@***.com"
11
12# 下面这俩个需要微信公众号中自行获取
13CropID='******************'
14Secret='***************************************'
15
16GURL="https://qyapi.weixin.qq.com/cgi-bin/gettoken?corpid=$CropID&corpsecret=$Secret"
17Gtoken=$(/usr/bin/curl -s -G $GURL | awk -F\" '{print $10}')
18
19PURL="https://qyapi.weixin.qq.com/cgi-bin/message/send?access_token=$Gtoken"
20
21function body() {
22        #企业号中的应用id
23        local int AppID=1000002
24        #部门成员id，
25        local UserID=$1
26        #部门id，定义了范围，组内成员都可接收到消息
27        local PartyID='2|3'
28        #过滤出zabbix传递的第三个参数
29        local Msg=$(echo "$@" | cut -d" " -f3-)
30        printf '{\n'
31        printf '\t"touser": "'"$UserID"\"",\n"
32        printf '\t"toparty": "'"$PartyID"\"",\n"
33        printf '\t"msgtype": "text",\n'
34        printf '\t"agentid": "'" $AppID "\"",\n"
35        printf '\t"text": {\n'
36        printf '\t\t"content": "'"$Msg"\""\n"
37        printf '\t},\n'
38        printf '\t"safe":"0"\n'
39        printf '}\n'
40}
41
42
43
44
45
46
47tac /var/log/mha/app1/manager.log | sed -n 2p | grep 'successfully' > /dev/null
48if [ $? -eq 0 ]
49	then
50	messages=`echo -e "MHA $subject 主从切换成功\n master:$orig_master_host --> $new_master_host \n $body \n 当前从库:$new_slave_hosts"` 
51	echo "$messages" | mail -s "Mysql 实例宕掉，MHA $subject 切换成功" $email >>/tmp/mailx.log 2>&1 
52        /usr/bin/curl --data-ascii "$(body 1 1 ${messages})" ${PURL}
53	else
54	messages=`echo -e "MHA $subject 主从切换失败\n master:$orig_master_host --> $new_master_host \n $body" `
55	echo "$messages" | mail -s ""Mysql 实例宕掉，MHA $subject 切换失败"" $email >>/tmp/mailx.log 2>&1  
56        /usr/bin/curl --data-ascii "$(body 1 1 ${messages})" ${PURL}
57fi
58

手动 VIP 管理配置脚本

vim /etc/mha/scripts/master_ip_online_change

1#!/bin/bash
2source /root/.bash_profile
3
4vip=`echo '10.0.20.199/24'`  #设置VIP
5key=`echo '1'`
6
7command=`echo "$1" | awk -F = '{print $2}'`
8orig_master_host=`echo "$2" | awk -F = '{print $2}'`
9new_master_host=`echo "$7" | awk -F = '{print $2}'`
10orig_master_ssh_user=`echo "${12}" | awk -F = '{print $2}'`
11new_master_ssh_user=`echo "${13}" | awk -F = '{print $2}'`
12
13#要求服务的网卡识别名一样
14stop_vip=`echo "ssh root@$orig_master_host /usr/sbin/ifconfig bond0:$key down"`
15start_vip=`echo "ssh root@$new_master_host /usr/sbin/ifconfig bond0:$key $vip"`
16
17if [ $command = 'stop' ]
18  then
19    echo -e "\n\n\n****************************\n"
20    echo -e "Disabled thi VIP - $vip on old master: $orig_master_host \n"
21    $stop_vip
22    if [ $? -eq 0 ]
23      then
24	echo "Disabled the VIP successfully"
25      else
26	echo "Disabled the VIP failed"
27    fi
28    echo -e "***************************\n\n\n"
29  fi
30
31if [ $command = 'start' -o $command = 'status' ]
32  then
33    echo -e "\n\n\n*************************\n"
34    echo -e "Enabling the VIP - $vip on new master: $new_master_host \n"
35    $start_vip
36    if [ $? -eq 0 ]
37      then
38	echo "Enabled the VIP successfully"
39      else
40	echo "Enabled the VIP failed"
41    fi
42    echo -e "***************************\n\n\n"
43fi

赋权

最后给刚刚配置的三个脚本增加执行权限

1chmod +x /etc/mha/scripts/master_ip_failover 
2chmod +x /etc/mha/scripts/master_ip_online_change 
3chmod +x /etc/mha/scripts/send_report

验证 MHA 相关操作

验证 ssh 信任登录是否成功

通过 masterha_check_ssh 命令验证

1[root@manager scripts]# masterha_check_ssh --conf=/etc/mha/app1.cnf
2# 最后出现以下提示，则表示通过
3Thu Jun 13 17:19:34 2019 - [info] All SSH connection tests passed successfully.

验证 mysql 主从复制是否成功

通过 masterha_check_repl 命令验证

1[root@manager mha]# masterha_check_repl --conf=/etc/masterha_default.cnf
2# 最后出现以下提示，则表示通过
3MySQL Replication Health is OK.

启动 MHA

手动第一次添加vip

本次在node01 上操作

先在node01 的 MySQL master上绑定vip，只需要在master绑定这一次，以后会自动切换

1[root@node01 mysql-5.7]# /usr/sbin/ifconfig bond0:1 10.0.20.199/24
2[root@node01 mysql-5.7]# ip a | grep 20
3    inet 10.0.20.201/24 brd 10.0.20.255 scope global bond0
4    inet 10.0.20.199/24 brd 10.0.20.255 scope global secondary bond0:1

启动

这一步在manager上操作

nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 &

检查 MHA 状态

1[root@manager mha]# masterha_check_status --conf=/etc/mha/app1.cnf
2app1 (pid:4745) is running(0:PING_OK), master:10.0.20.201

MHA 的日志保存在/var/log/masterha/app1/manager.log 下

1[root@manager mha]# tailf /var/log/mha/manager.log
2#如果最后一行是如下，表明启动成功
3Thu Jun 13 17:31:41 2019 - [info] Starting ping health check on 10.0.20.201(10.0.20.201:3306)..
4Thu Jun 13 17:31:41 2019 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond..

关闭

若已处于监控状态，需要停掉它

masterha_stop --conf=/etc/mha/app1.cnf

模拟宕机测试

手动停止node01 的 MySQL master，然后查看其它节点情况。

1[root@node01 ~]# /etc/init.d/mysqld  stop
2Shutting down MySQL............ SUCCESS!
3[root@node01 ~]# ip a | grep 20
4    inet 10.0.20.201/24 brd 10.0.20.255 scope global bond0

在node02 上查看VIP

1[root@node02 ~]# ip a | grep 20
2    inet 10.0.20.202/24 brd 10.0.20.255 scope global bond0
3    inet 10.0.20.199/24 brd 10.0.20.255 scope global secondary bond0:1

在node03 上查看主从同步状态和地址

1[root@node03 ~]# mysql -uroot -p123456 -e "show slave status\G" | egrep 'Master_Host|Slave_IO_Running|Slave_SQL_Running'
2mysql: [Warning] Using a password on the command line interface can be insecure.
3                  Master_Host: 10.0.20.202
4             Slave_IO_Running: Yes
5            Slave_SQL_Running: Yes
6      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

在node04 上查看主从同步状态和地址

1[root@node04 ~]# mysql -uroot -p123456 -e "show slave status\G" | egrep 'Master_Host|Slave_IO_Running|Slave_SQL_Running'
2mysql: [Warning] Using a password on the command line interface can be insecure.
3                  Master_Host: 10.0.20.202
4             Slave_IO_Running: Yes
5            Slave_SQL_Running: Yes
6      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

查看Manager日志

1[root@manager mha]# tailf manager.log
2Fri Jun 14 10:01:03 2019 - [warning] Got error on MySQL select ping: 2006 (MySQL server has gone away)
3Fri Jun 14 10:01:03 2019 - [info] Executing SSH check script: exit 0
4Fri Jun 14 10:01:03 2019 - [info] Executing secondary network check script: /usr/local/bin/masterha_secondary_check -s 10.0.20.201 -s 10.0.20.202 -s 10.0.20.203 -s 10.0.20.204  --user=root  --master_host=10.0.20.201  --master_ip=10.0.20.201  --master_port=3306 --master_user=root --master_password=123456 --ping_type=SELECT
5Fri Jun 14 10:01:03 2019 - [info] HealthCheck: SSH to 10.0.20.201 is reachable.
6Monitoring server 10.0.20.201 is reachable, Master is not reachable from 10.0.20.201. OK.
7Monitoring server 10.0.20.202 is reachable, Master is not reachable from 10.0.20.202. OK.
8Monitoring server 10.0.20.203 is reachable, Master is not reachable from 10.0.20.203. OK.
9Fri Jun 14 10:01:04 2019 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '10.0.20.201' (111))
10Fri Jun 14 10:01:04 2019 - [warning] Connection failed 2 time(s)..
11Monitoring server 10.0.20.204 is reachable, Master is not reachable from 10.0.20.204. OK.
12Fri Jun 14 10:01:04 2019 - [info] Master is not reachable from all other monitoring servers. Failover should start.
13Fri Jun 14 10:01:05 2019 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '10.0.20.201' (111))
14Fri Jun 14 10:01:05 2019 - [warning] Connection failed 3 time(s)..
15Fri Jun 14 10:01:06 2019 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '10.0.20.201' (111))
16Fri Jun 14 10:01:06 2019 - [warning] Connection failed 4 time(s)..
17Fri Jun 14 10:01:06 2019 - [warning] Master is not reachable from health checker!
18Fri Jun 14 10:01:06 2019 - [warning] Master 10.0.20.201(10.0.20.201:3306) is not reachable!
19Fri Jun 14 10:01:06 2019 - [warning] SSH is reachable.
20Fri Jun 14 10:01:06 2019 - [info] Connecting to a master server failed. Reading configuration file /etc/masterha_default.cnf and /etc/mha/app1.cnf again, and trying to connect to all servers to check server status..
21Fri Jun 14 10:01:06 2019 - [info] Reading default configuration from /etc/masterha_default.cnf..
22Fri Jun 14 10:01:06 2019 - [info] Reading application default configuration from /etc/mha/app1.cnf..
23Fri Jun 14 10:01:06 2019 - [info] Reading server configuration from /etc/mha/app1.cnf..
24Fri Jun 14 10:01:07 2019 - [info] GTID failover mode = 1
25Fri Jun 14 10:01:07 2019 - [info] Dead Servers:
26Fri Jun 14 10:01:07 2019 - [info]   10.0.20.201(10.0.20.201:3306)
27Fri Jun 14 10:01:07 2019 - [info] Alive Servers:
28Fri Jun 14 10:01:07 2019 - [info]   10.0.20.202(10.0.20.202:3306)
29Fri Jun 14 10:01:07 2019 - [info]   10.0.20.203(10.0.20.203:3306)
30Fri Jun 14 10:01:07 2019 - [info]   10.0.20.204(10.0.20.204:3306)
31Fri Jun 14 10:01:07 2019 - [info] Alive Slaves:
32Fri Jun 14 10:01:07 2019 - [info]   10.0.20.202(10.0.20.202:3306)  Version=5.7.18-log (oldest major version between slaves) log-bin:enabled
33Fri Jun 14 10:01:07 2019 - [info]     GTID ON
34Fri Jun 14 10:01:07 2019 - [info]     Replicating from 10.0.20.201(10.0.20.201:3306)
35Fri Jun 14 10:01:07 2019 - [info]     Primary candidate for the new Master (candidate_master is set)
36Fri Jun 14 10:01:07 2019 - [info]   10.0.20.203(10.0.20.203:3306)  Version=5.7.18-log (oldest major version between slaves) log-bin:enabled
37Fri Jun 14 10:01:07 2019 - [info]     GTID ON
38Fri Jun 14 10:01:07 2019 - [info]     Replicating from 10.0.20.201(10.0.20.201:3306)
39Fri Jun 14 10:01:07 2019 - [info]   10.0.20.204(10.0.20.204:3306)  Version=5.7.18-log (oldest major version between slaves) log-bin:enabled
40Fri Jun 14 10:01:07 2019 - [info]     GTID ON
41Fri Jun 14 10:01:07 2019 - [info]     Replicating from 10.0.20.201(10.0.20.201:3306)
42Fri Jun 14 10:01:07 2019 - [info] Checking slave configurations..
43Fri Jun 14 10:01:07 2019 - [info]  read_only=1 is not set on slave 10.0.20.202(10.0.20.202:3306).
44Fri Jun 14 10:01:07 2019 - [info]  read_only=1 is not set on slave 10.0.20.203(10.0.20.203:3306).
45Fri Jun 14 10:01:07 2019 - [info]  read_only=1 is not set on slave 10.0.20.204(10.0.20.204:3306).
46Fri Jun 14 10:01:07 2019 - [info] Checking replication filtering settings..
47Fri Jun 14 10:01:07 2019 - [info]  Replication filtering check ok.
48Fri Jun 14 10:01:07 2019 - [info] Master is down!
49Fri Jun 14 10:01:07 2019 - [info] Terminating monitoring script.
50Fri Jun 14 10:01:07 2019 - [info] Got exit code 20 (Master dead).
51Fri Jun 14 10:01:07 2019 - [info] MHA::MasterFailover version 0.58.
52Fri Jun 14 10:01:07 2019 - [info] Starting master failover.
53Fri Jun 14 10:01:07 2019 - [info] 
54Fri Jun 14 10:01:07 2019 - [info] * Phase 1: Configuration Check Phase..
55Fri Jun 14 10:01:07 2019 - [info] 
56Fri Jun 14 10:01:08 2019 - [info] GTID failover mode = 1
57Fri Jun 14 10:01:08 2019 - [info] Dead Servers:
58Fri Jun 14 10:01:08 2019 - [info]   10.0.20.201(10.0.20.201:3306)
59Fri Jun 14 10:01:08 2019 - [info] Checking master reachability via MySQL(double check)...
60Fri Jun 14 10:01:08 2019 - [info]  ok.
61Fri Jun 14 10:01:08 2019 - [info] Alive Servers:
62Fri Jun 14 10:01:08 2019 - [info]   10.0.20.202(10.0.20.202:3306)
63Fri Jun 14 10:01:08 2019 - [info]   10.0.20.203(10.0.20.203:3306)
64Fri Jun 14 10:01:08 2019 - [info]   10.0.20.204(10.0.20.204:3306)
65Fri Jun 14 10:01:08 2019 - [info] Alive Slaves:
66Fri Jun 14 10:01:08 2019 - [info]   10.0.20.202(10.0.20.202:3306)  Version=5.7.18-log (oldest major version between slaves) log-bin:enabled
67Fri Jun 14 10:01:08 2019 - [info]     GTID ON
68Fri Jun 14 10:01:08 2019 - [info]     Replicating from 10.0.20.201(10.0.20.201:3306)
69Fri Jun 14 10:01:08 2019 - [info]     Primary candidate for the new Master (candidate_master is set)
70Fri Jun 14 10:01:08 2019 - [info]   10.0.20.203(10.0.20.203:3306)  Version=5.7.18-log (oldest major version between slaves) log-bin:enabled
71Fri Jun 14 10:01:08 2019 - [info]     GTID ON
72Fri Jun 14 10:01:08 2019 - [info]     Replicating from 10.0.20.201(10.0.20.201:3306)
73Fri Jun 14 10:01:08 2019 - [info]   10.0.20.204(10.0.20.204:3306)  Version=5.7.18-log (oldest major version between slaves) log-bin:enabled
74Fri Jun 14 10:01:08 2019 - [info]     GTID ON
75Fri Jun 14 10:01:08 2019 - [info]     Replicating from 10.0.20.201(10.0.20.201:3306)
76Fri Jun 14 10:01:08 2019 - [info] Starting GTID based failover.
77Fri Jun 14 10:01:08 2019 - [info] 
78Fri Jun 14 10:01:08 2019 - [info] ** Phase 1: Configuration Check Phase completed.
79Fri Jun 14 10:01:08 2019 - [info] 
80Fri Jun 14 10:01:08 2019 - [info] * Phase 2: Dead Master Shutdown Phase..
81Fri Jun 14 10:01:08 2019 - [info] 
82Fri Jun 14 10:01:08 2019 - [info] Forcing shutdown so that applications never connect to the current master..
83Fri Jun 14 10:01:08 2019 - [info] Executing master IP deactivation script:
84Fri Jun 14 10:01:08 2019 - [info]   /etc/mha/scripts/master_ip_failover --orig_master_host=10.0.20.201 --orig_master_ip=10.0.20.201 --orig_master_port=3306 --command=stopssh --ssh_user=root  
85
86
87IN SCRIPT TEST====/sbin/ifconfig bond0:1 down==/sbin/ifconfig bond0:1 10.0.20.199/24===
88
89Disabling the VIP on old master: 10.0.20.201 
90Fri Jun 14 10:01:08 2019 - [info]  done.
91Fri Jun 14 10:01:08 2019 - [warning] shutdown_script is not set. Skipping explicit shutting down of the dead master.
92Fri Jun 14 10:01:08 2019 - [info] * Phase 2: Dead Master Shutdown Phase completed.
93Fri Jun 14 10:01:08 2019 - [info] 
94Fri Jun 14 10:01:08 2019 - [info] * Phase 3: Master Recovery Phase..
95Fri Jun 14 10:01:08 2019 - [info] 
96Fri Jun 14 10:01:08 2019 - [info] * Phase 3.1: Getting Latest Slaves Phase..
97Fri Jun 14 10:01:08 2019 - [info] 
98Fri Jun 14 10:01:08 2019 - [info] The latest binary log file/position on all slaves is mysql-bin.000004:194
99Fri Jun 14 10:01:08 2019 - [info] Retrieved Gtid Set: 6211616e-8db3-11e9-be15-005056990727:3-5
100Fri Jun 14 10:01:08 2019 - [info] Latest slaves (Slaves that received relay log files to the latest):
101Fri Jun 14 10:01:08 2019 - [info]   10.0.20.202(10.0.20.202:3306)  Version=5.7.18-log (oldest major version between slaves) log-bin:enabled
102Fri Jun 14 10:01:08 2019 - [info]     GTID ON
103Fri Jun 14 10:01:08 2019 - [info]     Replicating from 10.0.20.201(10.0.20.201:3306)
104Fri Jun 14 10:01:08 2019 - [info]     Primary candidate for the new Master (candidate_master is set)
105Fri Jun 14 10:01:08 2019 - [info]   10.0.20.203(10.0.20.203:3306)  Version=5.7.18-log (oldest major version between slaves) log-bin:enabled
106Fri Jun 14 10:01:08 2019 - [info]     GTID ON
107Fri Jun 14 10:01:08 2019 - [info]     Replicating from 10.0.20.201(10.0.20.201:3306)
108Fri Jun 14 10:01:08 2019 - [info]   10.0.20.204(10.0.20.204:3306)  Version=5.7.18-log (oldest major version between slaves) log-bin:enabled
109Fri Jun 14 10:01:08 2019 - [info]     GTID ON
110Fri Jun 14 10:01:08 2019 - [info]     Replicating from 10.0.20.201(10.0.20.201:3306)
111Fri Jun 14 10:01:08 2019 - [info] The oldest binary log file/position on all slaves is mysql-bin.000004:194
112Fri Jun 14 10:01:08 2019 - [info] Retrieved Gtid Set: 6211616e-8db3-11e9-be15-005056990727:3-5
113Fri Jun 14 10:01:08 2019 - [info] Oldest slaves:
114Fri Jun 14 10:01:08 2019 - [info]   10.0.20.202(10.0.20.202:3306)  Version=5.7.18-log (oldest major version between slaves) log-bin:enabled
115Fri Jun 14 10:01:08 2019 - [info]     GTID ON
116Fri Jun 14 10:01:08 2019 - [info]     Replicating from 10.0.20.201(10.0.20.201:3306)
117Fri Jun 14 10:01:08 2019 - [info]     Primary candidate for the new Master (candidate_master is set)
118Fri Jun 14 10:01:08 2019 - [info]   10.0.20.203(10.0.20.203:3306)  Version=5.7.18-log (oldest major version between slaves) log-bin:enabled
119Fri Jun 14 10:01:08 2019 - [info]     GTID ON
120Fri Jun 14 10:01:08 2019 - [info]     Replicating from 10.0.20.201(10.0.20.201:3306)
121Fri Jun 14 10:01:08 2019 - [info]   10.0.20.204(10.0.20.204:3306)  Version=5.7.18-log (oldest major version between slaves) log-bin:enabled
122Fri Jun 14 10:01:08 2019 - [info]     GTID ON
123Fri Jun 14 10:01:08 2019 - [info]     Replicating from 10.0.20.201(10.0.20.201:3306)
124Fri Jun 14 10:01:08 2019 - [info] 
125Fri Jun 14 10:01:08 2019 - [info] * Phase 3.3: Determining New Master Phase..
126Fri Jun 14 10:01:08 2019 - [info] 
127Fri Jun 14 10:01:08 2019 - [info] Searching new master from slaves..
128Fri Jun 14 10:01:08 2019 - [info]  Candidate masters from the configuration file:
129Fri Jun 14 10:01:08 2019 - [info]   10.0.20.202(10.0.20.202:3306)  Version=5.7.18-log (oldest major version between slaves) log-bin:enabled
130Fri Jun 14 10:01:08 2019 - [info]     GTID ON
131Fri Jun 14 10:01:08 2019 - [info]     Replicating from 10.0.20.201(10.0.20.201:3306)
132Fri Jun 14 10:01:08 2019 - [info]     Primary candidate for the new Master (candidate_master is set)
133Fri Jun 14 10:01:08 2019 - [info]  Non-candidate masters:
134Fri Jun 14 10:01:08 2019 - [info]  Searching from candidate_master slaves which have received the latest relay log events..
135Fri Jun 14 10:01:08 2019 - [info] New master is 10.0.20.202(10.0.20.202:3306)
136Fri Jun 14 10:01:08 2019 - [info] Starting master failover..
137Fri Jun 14 10:01:08 2019 - [info] 
138From:
13910.0.20.201(10.0.20.201:3306) (current master)
140 +--10.0.20.202(10.0.20.202:3306)
141 +--10.0.20.203(10.0.20.203:3306)
142 +--10.0.20.204(10.0.20.204:3306)
143
144To:
14510.0.20.202(10.0.20.202:3306) (new master)
146 +--10.0.20.203(10.0.20.203:3306)
147 +--10.0.20.204(10.0.20.204:3306)
148Fri Jun 14 10:01:08 2019 - [info] 
149Fri Jun 14 10:01:08 2019 - [info] * Phase 3.3: New Master Recovery Phase..
150Fri Jun 14 10:01:08 2019 - [info] 
151Fri Jun 14 10:01:08 2019 - [info]  Waiting all logs to be applied.. 
152Fri Jun 14 10:01:08 2019 - [info]   done.
153Fri Jun 14 10:01:08 2019 - [info] Getting new master's binlog name and position..
154Fri Jun 14 10:01:08 2019 - [info]  mysql-bin.000002:194
155Fri Jun 14 10:01:08 2019 - [info]  All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='10.0.20.202', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='xxx';
156Fri Jun 14 10:01:08 2019 - [info] Master Recovery succeeded. File:Pos:Exec_Gtid_Set: mysql-bin.000002, 194, 6211616e-8db3-11e9-be15-005056990727:4-5
157Fri Jun 14 10:01:08 2019 - [info] Executing master IP activate script:
158Fri Jun 14 10:01:08 2019 - [info]   /etc/mha/scripts/master_ip_failover --command=start --ssh_user=root --orig_master_host=10.0.20.201 --orig_master_ip=10.0.20.201 --orig_master_port=3306 --new_master_host=10.0.20.202 --new_master_ip=10.0.20.202 --new_master_port=3306 --new_master_user='root'   --new_master_password=xxx
159Unknown option: new_master_user
160Unknown option: new_master_password
161
162
163IN SCRIPT TEST====/sbin/ifconfig bond0:1 down==/sbin/ifconfig bond0:1 10.0.20.199/24===
164
165Enabling the VIP - 10.0.20.199/24 on the new master - 10.0.20.202 
166Fri Jun 14 10:01:08 2019 - [info]  OK.
167Fri Jun 14 10:01:08 2019 - [info] ** Finished master recovery successfully.
168Fri Jun 14 10:01:08 2019 - [info] * Phase 3: Master Recovery Phase completed.
169Fri Jun 14 10:01:08 2019 - [info] 
170Fri Jun 14 10:01:08 2019 - [info] * Phase 4: Slaves Recovery Phase..
171Fri Jun 14 10:01:08 2019 - [info] 
172Fri Jun 14 10:01:08 2019 - [info] 
173Fri Jun 14 10:01:08 2019 - [info] * Phase 4.1: Starting Slaves in parallel..
174Fri Jun 14 10:01:08 2019 - [info] 
175Fri Jun 14 10:01:08 2019 - [info] -- Slave recovery on host 10.0.20.203(10.0.20.203:3306) started, pid: 2838. Check tmp log /var/log/mha/10.0.20.203_3306_20190614100107.log if it takes time..
176Fri Jun 14 10:01:08 2019 - [info] -- Slave recovery on host 10.0.20.204(10.0.20.204:3306) started, pid: 2839. Check tmp log /var/log/mha/10.0.20.204_3306_20190614100107.log if it takes time..
177Fri Jun 14 10:01:09 2019 - [info] 
178Fri Jun 14 10:01:09 2019 - [info] Log messages from 10.0.20.204 ...
179Fri Jun 14 10:01:09 2019 - [info] 
180Fri Jun 14 10:01:08 2019 - [info]  Resetting slave 10.0.20.204(10.0.20.204:3306) and starting replication from the new master 10.0.20.202(10.0.20.202:3306)..
181Fri Jun 14 10:01:08 2019 - [info]  Executed CHANGE MASTER.
182Fri Jun 14 10:01:08 2019 - [info]  Slave started.
183Fri Jun 14 10:01:08 2019 - [info]  gtid_wait(6211616e-8db3-11e9-be15-005056990727:4-5) completed on 10.0.20.204(10.0.20.204:3306). Executed 0 events.
184Fri Jun 14 10:01:09 2019 - [info] End of log messages from 10.0.20.204.
185Fri Jun 14 10:01:09 2019 - [info] -- Slave on host 10.0.20.204(10.0.20.204:3306) started.
186Fri Jun 14 10:01:10 2019 - [info] 
187Fri Jun 14 10:01:10 2019 - [info] Log messages from 10.0.20.203 ...
188Fri Jun 14 10:01:10 2019 - [info] 
189Fri Jun 14 10:01:08 2019 - [info]  Resetting slave 10.0.20.203(10.0.20.203:3306) and starting replication from the new master 10.0.20.202(10.0.20.202:3306)..
190Fri Jun 14 10:01:08 2019 - [info]  Executed CHANGE MASTER.
191Fri Jun 14 10:01:09 2019 - [info]  Slave started.
192Fri Jun 14 10:01:09 2019 - [info]  gtid_wait(6211616e-8db3-11e9-be15-005056990727:4-5) completed on 10.0.20.203(10.0.20.203:3306). Executed 0 events.
193Fri Jun 14 10:01:10 2019 - [info] End of log messages from 10.0.20.203.
194Fri Jun 14 10:01:10 2019 - [info] -- Slave on host 10.0.20.203(10.0.20.203:3306) started.
195Fri Jun 14 10:01:10 2019 - [info] All new slave servers recovered successfully.
196Fri Jun 14 10:01:10 2019 - [info] 
197Fri Jun 14 10:01:10 2019 - [info] * Phase 5: New master cleanup phase..
198Fri Jun 14 10:01:10 2019 - [info] 
199Fri Jun 14 10:01:10 2019 - [info] Resetting slave info on the new master..
200Fri Jun 14 10:01:10 2019 - [info]  10.0.20.202: Resetting slave info succeeded.
201Fri Jun 14 10:01:10 2019 - [info] Master failover to 10.0.20.202(10.0.20.202:3306) completed successfully.
202Fri Jun 14 10:01:10 2019 - [info] Deleted server1 entry from /etc/mha/app1.cnf .
203Fri Jun 14 10:01:10 2019 - [info] 
204
205----- Failover Report -----
206
207app1: MySQL Master failover 10.0.20.201(10.0.20.201:3306) to 10.0.20.202(10.0.20.202:3306) succeeded
208
209Master 10.0.20.201(10.0.20.201:3306) is down!
210
211Check MHA Manager logs at manager.mha:/var/log/mha/manager.log for details.
212
213Started automated(non-interactive) failover.
214Invalidated master IP address on 10.0.20.201(10.0.20.201:3306)
215Selected 10.0.20.202(10.0.20.202:3306) as a new master.
21610.0.20.202(10.0.20.202:3306): OK: Applying all logs succeeded.
21710.0.20.202(10.0.20.202:3306): OK: Activated master IP address.
21810.0.20.204(10.0.20.204:3306): OK: Slave started, replicating from 10.0.20.202(10.0.20.202:3306)
21910.0.20.203(10.0.20.203:3306): OK: Slave started, replicating from 10.0.20.202(10.0.20.202:3306)
22010.0.20.202(10.0.20.202:3306): Resetting slave info succeeded.
221Master failover to 10.0.20.202(10.0.20.202:3306) completed successfully.
222Fri Jun 14 10:01:10 2019 - [info] Sending mail..
223  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
224                                 Dload  Upload   Total   Spent    Left  Speed
225100   347  100    45  100   302    133    897 --:--:-- --:--:-- --:--:--   898

由上面的日志以及各节点状态看出，vip已经自动漂移到node02的服务器上，并且node02自动提升为主库，node03 和 node04 自动同步node02的库。

同时也收到了微信和邮件告警。

自动切换步骤

从上面的输出可以看出整个 MHA 的切换过程,共包括以下的步骤：

配置文件检查阶段,这个阶段会检查整个集群配置文件配置
宕机的 master 处理,这个阶段包括虚拟 ip 摘除操作,主机关机操作（由于没有定义power_manager脚本，不会关机）
复制 dead maste 和最新 slave 相差的 relay log,并保存到 MHA Manger 具体的目录下
识别含有最新更新的 slave
应用从 master 保存的二进制日志事件(binlog events)（这点信息对于将故障master修复后加入集群很重要）
提升一个 slave 为新的 master 进行复制
使其他的 slave 连接新的 master 进行复制

修复后重新加入集群

切换完成后,关注如下变化:

vip 自动从原来的 master 切换到新的 master,同时,manager 节点的监控进程自动退出。
在日志目录(/var/log/mha/app1)产生一个 app1.failover.complete 文件
/etc/mha/app1.cnf 配置文件中原来老的 master 配置被删除。

模拟宕机的时候，停止了MySQL进程，现在重新启动MySQL，并加入到Node02 的从库中

node02 操作

1[root@node02 ~]# mysql -uroot -p123456 -e 'show master status\G'
2mysql: [Warning] Using a password on the command line interface can be insecure.
3*************************** 1. row ***************************
4             File: mysql-bin.000002
5         Position: 194
6     Binlog_Do_DB: 
7 Binlog_Ignore_DB: 
8Executed_Gtid_Set: 6211616e-8db3-11e9-be15-005056990727:4-5

node01 操作

1mysql> change master to master_host='10.0.20.202',master_user='repl',master_password='123456',master_log_file='mysql-bin.000002',master_log_pos=194;
2Query OK, 0 rows affected, 2 warnings (0.00 sec)
3
4mysql> start slave;
5Query OK, 0 rows affected (0.00 sec)
6mysql> exit
7Bye
8[root@node01 ~]# mysql -uroot -p123456 -e "show slave status\G" | egrep 'Master_Host|Slave_IO_Running|Slave_SQL_Running'
9mysql: [Warning] Using a password on the command line interface can be insecure.
10                  Master_Host: 10.0.20.202
11             Slave_IO_Running: Yes
12            Slave_SQL_Running: Yes
13      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

manager 操作

需要注意的是，当发生宕机切换后，manager中的MHA进程会自动停止，在修复后，需要手动再次启动

当发生宕机切换，MHA会自动把宕机的信息从app1.cnf配置文件中删除，修复后机器，要把信息重新写入到app1.cnf中。

修改前

1[root@manager mha]# pwd
2/etc/mha
3[root@manager mha]# cat app1.cnf 
4[server2]
5candidate_master=1
6check_repl_delay=0
7hostname=10.0.20.202
8port=3306
9
10[server3]
11hostname=10.0.20.203
12port=3306
13
14[server4]
15hostname=10.0.20.204
16port=3306

修改后

1[root@manager mha]# cat app1.cnf 
2[server1]
3candidate_master=1
4check_repl_delay=0
5hostname=10.0.20.201
6
7[server2]
8hostname=10.0.20.202
9port=3306
10
11[server3]
12hostname=10.0.20.203
13port=3306
14
15[server4]
16hostname=10.0.20.204
17port=3306

重新启动MHA

修改好配置文件后，再次启动MHA即可

nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 &

此时修复完成。

在线进行切换

在许多情况下，需要将现有的主服务器迁移到另外一台服务器上。比如主服务器硬件故障，RAID 控制卡需要重建，将主服务器移到性能更好的服务器上等等。维护主服务器引起性能下降，导致停机时间至少无法写入数据。另外，阻塞或杀掉当前运行的会话会导致主主之间数据不一致的问题发生。 MHA 提供快速切换和优雅的阻塞写入，这个切换过程只需要 0.5-2s 的时间，这段时间内数据是无法写入的。在很多情况下，0.5-2s 的阻塞写入是可以接受的。因此切换主服务器不需要计划分配维护时间窗口。

MHA在线切换的大概过程：

检测复制设置和确定当前主服务器
确定新的主服务器
阻塞写入到当前主服务器
等待所有从服务器赶上复制
授予写入到新的主服务器
重新设置从服务器

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

自动识别master和slave的问题（master的机器可能会切换），如果采用了vip的方式，基本可以解决这个问题。
负载均衡的问题（可以定义大概的读写比例，每台机器可承担的负载比例，当有机器离开集群时，需要考虑这个问题）

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

所有slave的IO线程都在运行
所有slave的SQL线程都在运行
所有的show slave status的输出中Seconds_Behind_Master参数小于或者等于running_updates_limit秒，如果在切换过程中不指定running_updates_limit,那么默认情况下running_updates_limit为1秒。
在master端，通过show processlist输出，没有一个更新花费的时间大于running_updates_limit秒。

停止MHA 的manager 监控

1[root@manager mha]# masterha_stop --conf=/etc/mha/app1.cnf
2Stopped app1 successfully.
3[1]+  Exit 1                  nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1

执行切换命令

进行在线切换操作

模拟在线切换主库操作，原主库10.0.20.202变为slave，10.0.20.201提升为新的主库

上一次进行了模拟宕机测试，最开始的主库是201，切换到了202位主库了

[root@manager mha]# masterha_master_switch --conf=/etc/mha/app1.cnf --master_state=alive --new_master_host=10.0.20.201 --orig_master_is_new_slave --running_updates_limit=10000 --interactive=0

执行后输出的日志如下：

1Fri Jun 14 11:30:26 2019 - [info] MHA::MasterRotate version 0.58.
2Fri Jun 14 11:30:26 2019 - [info] Starting online master switch..
3Fri Jun 14 11:30:26 2019 - [info] 
4Fri Jun 14 11:30:26 2019 - [info] * Phase 1: Configuration Check Phase..
5Fri Jun 14 11:30:26 2019 - [info] 
6Fri Jun 14 11:30:26 2019 - [info] Reading default configuration from /etc/masterha_default.cnf..
7Fri Jun 14 11:30:26 2019 - [info] Reading application default configuration from /etc/mha/app1.cnf..
8Fri Jun 14 11:30:26 2019 - [info] Reading server configuration from /etc/mha/app1.cnf..
9Fri Jun 14 11:30:27 2019 - [info] GTID failover mode = 1
10Fri Jun 14 11:30:27 2019 - [info] Current Alive Master: 10.0.20.202(10.0.20.202:3306)
11Fri Jun 14 11:30:27 2019 - [info] Alive Slaves:
12Fri Jun 14 11:30:27 2019 - [info]   10.0.20.201(10.0.20.201:3306)  Version=5.7.18-log (oldest major version between slaves) log-bin:enabled
13Fri Jun 14 11:30:27 2019 - [info]     GTID ON
14Fri Jun 14 11:30:27 2019 - [info]     Replicating from 10.0.20.202(10.0.20.202:3306)
15Fri Jun 14 11:30:27 2019 - [info]     Primary candidate for the new Master (candidate_master is set)
16Fri Jun 14 11:30:27 2019 - [info]   10.0.20.203(10.0.20.203:3306)  Version=5.7.18-log (oldest major version between slaves) log-bin:enabled
17Fri Jun 14 11:30:27 2019 - [info]     GTID ON
18Fri Jun 14 11:30:27 2019 - [info]     Replicating from 10.0.20.202(10.0.20.202:3306)
19Fri Jun 14 11:30:27 2019 - [info]   10.0.20.204(10.0.20.204:3306)  Version=5.7.18-log (oldest major version between slaves) log-bin:enabled
20Fri Jun 14 11:30:27 2019 - [info]     GTID ON
21Fri Jun 14 11:30:27 2019 - [info]     Replicating from 10.0.20.202(10.0.20.202:3306)
22Fri Jun 14 11:30:27 2019 - [info] Executing FLUSH NO_WRITE_TO_BINLOG TABLES. This may take long time..
23Fri Jun 14 11:30:27 2019 - [info]  ok.
24Fri Jun 14 11:30:27 2019 - [info] Checking MHA is not monitoring or doing failover..
25Fri Jun 14 11:30:27 2019 - [info] Checking replication health on 10.0.20.201..
26Fri Jun 14 11:30:27 2019 - [info]  ok.
27Fri Jun 14 11:30:27 2019 - [info] Checking replication health on 10.0.20.203..
28Fri Jun 14 11:30:27 2019 - [info]  ok.
29Fri Jun 14 11:30:27 2019 - [info] Checking replication health on 10.0.20.204..
30Fri Jun 14 11:30:27 2019 - [info]  ok.
31Fri Jun 14 11:30:27 2019 - [info] 10.0.20.201 can be new master.
32Fri Jun 14 11:30:27 2019 - [info] 
33From:
3410.0.20.202(10.0.20.202:3306) (current master)
35 +--10.0.20.201(10.0.20.201:3306)
36 +--10.0.20.203(10.0.20.203:3306)
37 +--10.0.20.204(10.0.20.204:3306)
38
39To:
4010.0.20.201(10.0.20.201:3306) (new master)
41 +--10.0.20.203(10.0.20.203:3306)
42 +--10.0.20.204(10.0.20.204:3306)
43 +--10.0.20.202(10.0.20.202:3306)
44Fri Jun 14 11:30:27 2019 - [info] Checking whether 10.0.20.201(10.0.20.201:3306) is ok for the new master..
45Fri Jun 14 11:30:27 2019 - [info]  ok.
46Fri Jun 14 11:30:27 2019 - [info] 10.0.20.202(10.0.20.202:3306): SHOW SLAVE STATUS returned empty result. To check replication filtering rules, temporarily executing CHANGE MASTER to a dummy host.
47Fri Jun 14 11:30:27 2019 - [info] 10.0.20.202(10.0.20.202:3306): Resetting slave pointing to the dummy host.
48Fri Jun 14 11:30:27 2019 - [info] ** Phase 1: Configuration Check Phase completed.
49Fri Jun 14 11:30:27 2019 - [info] 
50Fri Jun 14 11:30:27 2019 - [info] * Phase 2: Rejecting updates Phase..
51Fri Jun 14 11:30:27 2019 - [info] 
52Fri Jun 14 11:30:27 2019 - [info] Executing master ip online change script to disable write on the current master:
53Fri Jun 14 11:30:27 2019 - [info]   /etc/mha/scripts/master_ip_online_change --command=stop --orig_master_host=10.0.20.202 --orig_master_ip=10.0.20.202 --orig_master_port=3306 --orig_master_user='root' --new_master_host=10.0.20.201 --new_master_ip=10.0.20.201 --new_master_port=3306 --new_master_user='root' --orig_master_ssh_user=root --new_master_ssh_user=root   --orig_master_is_new_slave --orig_master_password=xxx --new_master_password=xxx
54
55
56
57****************************
58
59Disabled thi VIP - 10.0.20.199/24 on old master: 10.0.20.202 
60
61Disabled the VIP successfully
62***************************
63
64
65
66Fri Jun 14 11:30:27 2019 - [info]  ok.
67Fri Jun 14 11:30:27 2019 - [info] Locking all tables on the orig master to reject updates from everybody (including root):
68Fri Jun 14 11:30:27 2019 - [info] Executing FLUSH TABLES WITH READ LOCK..
69Fri Jun 14 11:30:27 2019 - [info]  ok.
70Fri Jun 14 11:30:27 2019 - [info] Orig master binlog:pos is mysql-bin.000002:194.
71Fri Jun 14 11:30:27 2019 - [info]  Waiting to execute all relay logs on 10.0.20.201(10.0.20.201:3306)..
72Fri Jun 14 11:30:27 2019 - [info]  master_pos_wait(mysql-bin.000002:194) completed on 10.0.20.201(10.0.20.201:3306). Executed 0 events.
73Fri Jun 14 11:30:27 2019 - [info]   done.
74Fri Jun 14 11:30:27 2019 - [info] Getting new master's binlog name and position..
75Fri Jun 14 11:30:27 2019 - [info]  mysql-bin.000005:194
76Fri Jun 14 11:30:27 2019 - [info]  All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='10.0.20.201', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='xxx';
77Fri Jun 14 11:30:27 2019 - [info] Executing master ip online change script to allow write on the new master:
78Fri Jun 14 11:30:27 2019 - [info]   /etc/mha/scripts/master_ip_online_change --command=start --orig_master_host=10.0.20.202 --orig_master_ip=10.0.20.202 --orig_master_port=3306 --orig_master_user='root' --new_master_host=10.0.20.201 --new_master_ip=10.0.20.201 --new_master_port=3306 --new_master_user='root' --orig_master_ssh_user=root --new_master_ssh_user=root   --orig_master_is_new_slave --orig_master_password=xxx --new_master_password=xxx
79
80
81
82*************************
83
84Enabling the VIP - 10.0.20.199/24 on new master: 10.0.20.201 
85
86Enabled the VIP successfully
87***************************
88
89
90
91Fri Jun 14 11:30:27 2019 - [info]  ok.
92Fri Jun 14 11:30:27 2019 - [info] 
93Fri Jun 14 11:30:27 2019 - [info] * Switching slaves in parallel..
94Fri Jun 14 11:30:27 2019 - [info] 
95Fri Jun 14 11:30:27 2019 - [info] -- Slave switch on host 10.0.20.203(10.0.20.203:3306) started, pid: 7081
96Fri Jun 14 11:30:27 2019 - [info] 
97Fri Jun 14 11:30:27 2019 - [info] -- Slave switch on host 10.0.20.204(10.0.20.204:3306) started, pid: 7082
98Fri Jun 14 11:30:27 2019 - [info] 
99Fri Jun 14 11:30:29 2019 - [info] Log messages from 10.0.20.203 ...
100Fri Jun 14 11:30:29 2019 - [info] 
101Fri Jun 14 11:30:27 2019 - [info]  Waiting to execute all relay logs on 10.0.20.203(10.0.20.203:3306)..
102Fri Jun 14 11:30:27 2019 - [info]  master_pos_wait(mysql-bin.000002:194) completed on 10.0.20.203(10.0.20.203:3306). Executed 0 events.
103Fri Jun 14 11:30:27 2019 - [info]   done.
104Fri Jun 14 11:30:27 2019 - [info]  Resetting slave 10.0.20.203(10.0.20.203:3306) and starting replication from the new master 10.0.20.201(10.0.20.201:3306)..
105Fri Jun 14 11:30:27 2019 - [info]  Executed CHANGE MASTER.
106Fri Jun 14 11:30:28 2019 - [info]  Slave started.
107Fri Jun 14 11:30:29 2019 - [info] End of log messages from 10.0.20.203 ...
108Fri Jun 14 11:30:29 2019 - [info] 
109Fri Jun 14 11:30:29 2019 - [info] -- Slave switch on host 10.0.20.203(10.0.20.203:3306) succeeded.
110Fri Jun 14 11:30:29 2019 - [info] Log messages from 10.0.20.204 ...
111Fri Jun 14 11:30:29 2019 - [info] 
112Fri Jun 14 11:30:27 2019 - [info]  Waiting to execute all relay logs on 10.0.20.204(10.0.20.204:3306)..
113Fri Jun 14 11:30:27 2019 - [info]  master_pos_wait(mysql-bin.000002:194) completed on 10.0.20.204(10.0.20.204:3306). Executed 0 events.
114Fri Jun 14 11:30:27 2019 - [info]   done.
115Fri Jun 14 11:30:27 2019 - [info]  Resetting slave 10.0.20.204(10.0.20.204:3306) and starting replication from the new master 10.0.20.201(10.0.20.201:3306)..
116Fri Jun 14 11:30:27 2019 - [info]  Executed CHANGE MASTER.
117Fri Jun 14 11:30:28 2019 - [info]  Slave started.
118Fri Jun 14 11:30:29 2019 - [info] End of log messages from 10.0.20.204 ...
119Fri Jun 14 11:30:29 2019 - [info] 
120Fri Jun 14 11:30:29 2019 - [info] -- Slave switch on host 10.0.20.204(10.0.20.204:3306) succeeded.
121Fri Jun 14 11:30:29 2019 - [info] Unlocking all tables on the orig master:
122Fri Jun 14 11:30:29 2019 - [info] Executing UNLOCK TABLES..
123Fri Jun 14 11:30:29 2019 - [info]  ok.
124Fri Jun 14 11:30:29 2019 - [info] Starting orig master as a new slave..
125Fri Jun 14 11:30:29 2019 - [info]  Resetting slave 10.0.20.202(10.0.20.202:3306) and starting replication from the new master 10.0.20.201(10.0.20.201:3306)..
126Fri Jun 14 11:30:29 2019 - [info]  Executed CHANGE MASTER.
127Fri Jun 14 11:30:30 2019 - [info]  Slave started.
128Fri Jun 14 11:30:30 2019 - [info] All new slave servers switched successfully.
129Fri Jun 14 11:30:30 2019 - [info] 
130Fri Jun 14 11:30:30 2019 - [info] * Phase 5: New master cleanup phase..
131Fri Jun 14 11:30:30 2019 - [info] 
132Fri Jun 14 11:30:30 2019 - [info]  10.0.20.201: Resetting slave info succeeded.
133Fri Jun 14 11:30:30 2019 - [info] Switching master to 10.0.20.201(10.0.20.201:3306) completed successfully.

查看状态

node01

1[root@node01 ~]# mysql -uroot -p123456 -e 'show slave status\G'
2mysql: [Warning] Using a password on the command line interface can be insecure.
3[root@node01 ~]# ip a | grep 20
4    inet 10.0.20.201/24 brd 10.0.20.255 scope global bond0
5    inet 10.0.20.199/24 brd 10.0.20.255 scope global secondary bond0:1

node02

1[root@node02 ~]# mysql -uroot -p123456 -e "show slave status\G" | egrep 'Master_Host|Slave_IO_Running|Slave_SQL_Running'
2mysql: [Warning] Using a password on the command line interface can be insecure.
3                  Master_Host: 10.0.20.201
4             Slave_IO_Running: Yes
5            Slave_SQL_Running: Yes
6      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
7[root@node02 ~]# ip a | grep 20
8    inet 10.0.20.202/24 brd 10.0.20.255 scope global bond0

node03

1[root@node03 ~]# mysql -uroot -p123456 -e "show slave status\G" | egrep 'Master_Host|Slave_IO_Running|Slave_SQL_Running'
2mysql: [Warning] Using a password on the command line interface can be insecure.
3                  Master_Host: 10.0.20.201
4             Slave_IO_Running: Yes
5            Slave_SQL_Running: Yes
6      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

node04

1[root@node04 ~]# mysql -uroot -p123456 -e "show slave status\G" | egrep 'Master_Host|Slave_IO_Running|Slave_SQL_Running'
2mysql: [Warning] Using a password on the command line interface can be insecure.
3                  Master_Host: 10.0.20.201
4             Slave_IO_Running: Yes
5            Slave_SQL_Running: Yes
6      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

从上面各个数据库的状态可以看出来，主库已经变成了node01了，并且vip也漂移到node01的机器上了。

报错解决

报错一、

报错信息：

1Can't locate Parallel/ForkManager.pm in @INC (@INC contains: /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 /usr/local/share/perl5/MHA/SSHCheck.pm line 31.
2BEGIN failed--compilation aborted at /usr/local/share/perl5/MHA/SSHCheck.pm line 31.
3Compilation failed in require at /usr/local/bin/masterha_check_ssh line 25.
4BEGIN failed--compilation aborted at /usr/local/bin/masterha_check_ssh line 25.

解决：

1yum install cpan
2
3cpan Module::Build
4
5# 上面的命令执行完成后，需要重新解压 mha-manager 的包
6再次执行 
7
8perl Makefile.PL
9
10make
11# 这一步中 会提示 输入，都输入yes 回车
12
13make install

MySQL 高可用架构 之 MHA (Centos 7.5 MySQL 5.7.18 MHA 0.58)

简介

环境准备

秘钥互信

安装基础依赖包

安装MHA组件

安装 MHA Node组件

安装 MHA Manager 组件

建立 MySQL 一主三从

初始化 MySQL

启动MySQL 并简单配置

建立 一主三从

特别说明

MHA Manager 配置

MHA 配置文件

配置文件说明

脚本配置

自动 VIP 管理配置

配置邮件和微信报警脚本

手动 VIP 管理配置脚本

赋权

验证 MHA 相关操作

验证 ssh 信任登录是否成功

验证 mysql 主从复制是否成功

启动 MHA

手动第一次添加vip

启动

关闭

模拟宕机测试

自动切换步骤

修复后重新加入集群

node02 操作

node01 操作

manager 操作

修改前

修改后

重新启动MHA

在线进行切换

停止MHA 的manager 监控

执行切换命令

查看状态

报错解决

报错一、

评论区

相关推荐

MySQL 高可用架构之 MHA (Centos 7.5 MySQL 5.7.18 MHA 0.58)

建立一主三从