Mysql MHA部署-04MHA配置
一 Mysql MHA部署-01介绍
二 Mysql MHA部署-02主从复制
三 Mysql MHA部署-03MHA软件安装
四 Mysql MHA部署-04MHA配置
五 Mysql MHA部署-05故障转移
六 Mysql MHA部署-06手动切换
七 Mysql MHA部署-07常见问题
架构说明：
参考：
http://www.zhaibibei.cn/mysql/mha/
一：MySQL MHA部署前准备
1.1 hosts文件
首先我们在四台服务器上添加其他服务器的hosts信息
[root@rac1 mha]# vim /etc/hosts
192.168.2.222 rac4
192.168.2.187 rac1
192.168.2.188 rac2
192.168.2.223 rac3
1.2 配置免密码登录
接下来我们需要配置各服务请求间的免密码登录
ssh-kengen 命令后请一路下一步
---192.168.2.187 rac1
ssh-keygen -t rsa
ssh-copy-id 192.168.2.188
ssh-copy-id 192.168.2.223
ssh-copy-id 192.168.2.222
ssh-copy-id rac2
ssh-copy-id rac3
ssh-copy-id rac4
---192.168.2.188 rac2
ssh-keygen -t rsa
ssh-copy-id 192.168.2.187
ssh-copy-id 192.168.2.223
ssh-copy-id 192.168.2.222
ssh-copy-id rac1
ssh-copy-id rac3
ssh-copy-id rac4
---192.168.2.223 rac3
ssh-keygen -t rsa
ssh-copy-id 192.168.2.187
ssh-copy-id 192.168.2.188
ssh-copy-id 192.168.2.222
ssh-copy-id rac1
ssh-copy-id rac2
ssh-copy-id rac4
---192.168.2.222 rac4
ssh-keygen -t rsa
ssh-copy-id 192.168.2.187
ssh-copy-id 192.168.2.188
ssh-copy-id 192.168.2.223
ssh-copy-id rac1
ssh-copy-id rac2
ssh-copy-id rac3
1.3 创建监控账户
接下来我们创建用于MHA监控的数据库账户
主库 187：
master数据库
mysql> grant all privileges on *.* to 'monitor'@'192.168.2.187' identified by '123456';
mysql> grant all privileges on *.* to 'monitor'@'192.168.2.188' identified by '123456';
mysql> grant all privileges on *.* to 'monitor'@'192.168.2.222' identified by '123456';
mysql> grant all privileges on *.* to 'monitor'@'192.168.2.223' identified by '123456';
mysql> grant all privileges on *.* to 'monitor'@'127.0.0.1' identified by '123456';
mysql> flush privileges;
mysql> select host,user from mysql.user;
+---------------+---------------+
| host | user |
+---------------+---------------+
| 127.0.0.1 | monitor |
| 192.168.2.187 | monitor |
| 192.168.2.187 | repl |
| 192.168.2.188 | monitor |
| 192.168.2.188 | repl |
| 192.168.2.222 | monitor |
| 192.168.2.223 | monitor |
| 192.168.2.223 | repl |
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | root |
+---------------+---------------+
11 rows in set (0.00 sec)
1.4 关闭防火墙
如果防火墙开始，需要开通服务器间3306端口的通信
1.5 关闭relay日志自动清理
由于默认情况下从库的relay logs会在SQL线程执行完毕后被自动删除，但是对于MHA场景下，对于某些滞后从库的恢复依赖于其他从库的relay log，因此采取禁用自动删除功能以及定期清理的办法
使用purge_relay_logs脚本这个后面说明
主从库3台
relay_log_purge=0
1.6 从库开启只读
从库需要设置只读模式
2台从库
read_only=1
1.7 master节点绑定VIP
刚开始需要手动将VIP绑定至master节点
[root@rac1 mysql]# ifconfig |grep 192
inet 192.168.2.187 netmask 255.255.255.0 broadcast 192.168.2.255
inet 192.168.122.1 netmask 255.255.255.0 broadcast 192.168.122.255
[root@rac1 mysql]# /sbin/ifconfig enp0s3:2 192.168.2.189 netmask 255.255.255.0 up
[root@rac1 mysql]# ifconfig |grep 192
inet 192.168.2.187 netmask 255.255.255.0 broadcast 192.168.2.255
inet 192.168.2.189 netmask 255.255.255.0 broadcast 192.168.2.255
inet 192.168.122.1 netmask 255.255.255.0 broadcast 192.168.122.255
1.8 准备自定义脚本
这里我们通过网盘下载，放到/etc/mha/script下面并赋予可执行权限
链接: https://pan.baidu.com/s/18H12bTIYHi2H0dXs2DI7NA 提取码: mwkq
管理节点：192.168.2.222 RAC4
[root@rac4 ~]# mkdir /etc/mha/script -p
[root@rac4 ~]# cp /package/mysql/MHA/script/* /etc/mha/script/
[root@rac4 ~]# ll -rth /etc/mha/script/
total 24K
-rw-r--r-- 1 root root 2.4K Mar 14 19:56 send_report
-rw-r--r-- 1 root root 11K Mar 14 19:56 master_ip_online_change
-rw-r--r-- 1 root root 4.3K Mar 14 19:56 master_ip_failover
接下来我们修改相关的文件
---master_ip_failover
该文件只需要修改
my $vip = '192.168.2.189';
my $key = "0";
my $ssh_start_vip = "/sbin/ifconfig enp0s3:$key $vip/24";
my $ssh_stop_vip = "/sbin/ifconfig enp0s3:$key down";
my $ssh_send_garp = "/sbin/arping -U $vip -I enp0s3 -c 1";
---master_ip_online_change
该文件也是需要修改
my $vip = '192.168.2.189';
my $key = "0";
my $ssh_start_vip = "/sbin/ifconfig enp0s3:$key $vip/24";
my $ssh_stop_vip = "/sbin/ifconfig enp0s3:$key down";
my $ssh_send_garp = "/sbin/arping -U $vip -I enp0s3 -c 1";
---send_report
该文件需要修改邮件配置，这个后面做介绍
二 MySQL MHA部署指南
2.1 管理节点配置
[root@rac4 script]# vim /etc/mha/mha.conf
[server default]
# mysql user and password
user=monitor
password=123456
ssh_user=root
# working directory on the manager
manager_workdir=/etc/mha/manager
manager_log=/etc/mha/manager/mha.log
ping_interval=1
# working directory on MySQL servers
remote_workdir=/etc/mha/node
# replication user
repl_user=repl
repl_password=rpl
master_binlog_dir=/datalog/mysql/binlog
## customized script
master_ip_failover_script=/etc/mha/script/master_ip_failover
master_ip_online_change_script=/etc/mha/script/master_ip_online_change
secondary_check_script=/usr/bin/masterha_secondary_check -s 192.168.2.188 -s 192.168.2.223 --user=root --master_host=192.168.2.187 --master_ip=192.168.2.187 --master_port=3306
[server1]
hostname=rac1
candidate_master=1
[server2]
hostname=rac2
candidate_master=1
[server3]
hostname=rac3
no_master=1
接下来我们来解释每个参数的意义：
manager_workdir 管理节点工作目录，mha运行时会有状态文件生成
masnager_log 管理节点生成的日志
ping_interval 管理节点检测主库状态的间隔，默认为3s
remote_workdir MHA node工作目录，不存在会自动建立，如果不允许创建，MHA Node自动异常退出
master_binlog_dir 指定主库的二进制日志位置，防止管理节点无法连接主库获取日志位置
master_ip_failover_script 发生切换时VIP漂移脚本
master_ip_online_change_script 用于手动切换时VIP的漂移
secondary_check_script 若管理节点无法连接主库，则调用该脚本从从库检测主库状态
candidate_master 指定如果主库发生故障，优先提升该节点为主库
no_master 指定该节点不会成为主库
2.2 测试连接
2.2.1 测试ssh是否设置正确
管理节点
[root@rac4 script]# masterha_check_ssh --conf=/etc/mha/mha.conf
Sat Mar 14 20:10:42 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sat Mar 14 20:10:42 2020 - [info] Reading application default configuration from /etc/mha/mha.conf..
Sat Mar 14 20:10:42 2020 - [info] Reading server configuration from /etc/mha/mha.conf..
Sat Mar 14 20:10:42 2020 - [info] Starting SSH connection tests..
Sat Mar 14 20:10:44 2020 - [debug]
Sat Mar 14 20:10:42 2020 - [debug] Connecting via SSH from root@rac1(192.168.2.187:22) to root@rac2(192.168.2.188:22)..
Sat Mar 14 20:10:43 2020 - [debug] ok.
Sat Mar 14 20:10:43 2020 - [debug] Connecting via SSH from root@rac1(192.168.2.187:22) to root@rac3(192.168.2.223:22)..
Sat Mar 14 20:10:44 2020 - [debug] ok.
Sat Mar 14 20:10:45 2020 - [debug]
Sat Mar 14 20:10:43 2020 - [debug] Connecting via SSH from root@rac3(192.168.2.223:22) to root@rac1(192.168.2.187:22)..
Sat Mar 14 20:10:44 2020 - [debug] ok.
Sat Mar 14 20:10:44 2020 - [debug] Connecting via SSH from root@rac3(192.168.2.223:22) to root@rac2(192.168.2.188:22)..
Sat Mar 14 20:10:45 2020 - [debug] ok.
Sat Mar 14 20:10:45 2020 - [debug]
Sat Mar 14 20:10:43 2020 - [debug] Connecting via SSH from root@rac2(192.168.2.188:22) to root@rac1(192.168.2.187:22)..
Sat Mar 14 20:10:44 2020 - [debug] ok.
Sat Mar 14 20:10:44 2020 - [debug] Connecting via SSH from root@rac2(192.168.2.188:22) to root@rac3(192.168.2.223:22)..
Sat Mar 14 20:10:44 2020 - [debug] ok.
Sat Mar 14 20:10:45 2020 - [info] All SSH connection tests passed successfully.
测试通过会有成功字样打印出来
2.2.2 测试复制是否正常
管理节点222(rac4)
[root@rac4 script]# masterha_check_repl --conf=/etc/mha/mha.conf
Sat Mar 14 20:14:42 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sat Mar 14 20:14:42 2020 - [info] Reading application default configuration from /etc/mha/mha.conf..
Sat Mar 14 20:14:42 2020 - [info] Reading server configuration from /etc/mha/mha.conf..
Sat Mar 14 20:14:42 2020 - [info] MHA::MasterMonitor version 0.56.
Sat Mar 14 20:14:43 2020 - [info] GTID failover mode = 1
Sat Mar 14 20:14:43 2020 - [info] Dead Servers:
Sat Mar 14 20:14:43 2020 - [info] Alive Servers:
Sat Mar 14 20:14:43 2020 - [info] rac1(192.168.2.187:3306)
Sat Mar 14 20:14:43 2020 - [info] rac2(192.168.2.188:3306)
Sat Mar 14 20:14:43 2020 - [info] rac3(192.168.2.223:3306)
Sat Mar 14 20:14:43 2020 - [info] Alive Slaves:
Sat Mar 14 20:14:43 2020 - [info] rac2(192.168.2.188:3306) Version=5.7.28-log (oldest major version between slaves) log-bin:enabled
Sat Mar 14 20:14:43 2020 - [info] GTID ON
Sat Mar 14 20:14:43 2020 - [info] Replicating from 192.168.2.187(192.168.2.187:3306)
Sat Mar 14 20:14:43 2020 - [info] Primary candidate for the new Master (candidate_master is set)
Sat Mar 14 20:14:43 2020 - [info] rac3(192.168.2.223:3306) Version=5.7.28-log (oldest major version between slaves) log-bin:enabled
Sat Mar 14 20:14:43 2020 - [info] GTID ON
Sat Mar 14 20:14:43 2020 - [info] Replicating from 192.168.2.187(192.168.2.187:3306)
Sat Mar 14 20:14:43 2020 - [info] Not candidate for the new Master (no_master is set)
Sat Mar 14 20:14:43 2020 - [info] Current Alive Master: rac1(192.168.2.187:3306)
Sat Mar 14 20:14:43 2020 - [info] Checking slave configurations..
Sat Mar 14 20:14:43 2020 - [info] Checking replication filtering settings..
Sat Mar 14 20:14:43 2020 - [info] binlog_do_db= , binlog_ignore_db=
Sat Mar 14 20:14:43 2020 - [info] Replication filtering check ok.
Sat Mar 14 20:14:43 2020 - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.
Sat Mar 14 20:14:43 2020 - [info] Checking SSH publickey authentication settings on the current master..
Sat Mar 14 20:14:43 2020 - [info] HealthCheck: SSH to rac1 is reachable.
Sat Mar 14 20:14:43 2020 - [info]
rac1(192.168.2.187:3306) (current master)
+--rac2(192.168.2.188:3306)
+--rac3(192.168.2.223:3306)
Sat Mar 14 20:14:43 2020 - [info] Checking replication health on rac2..
Sat Mar 14 20:14:43 2020 - [info] ok.
Sat Mar 14 20:14:43 2020 - [info] Checking replication health on rac3..
Sat Mar 14 20:14:43 2020 - [info] ok.
Sat Mar 14 20:14:43 2020 - [info] Checking master_ip_failover_script status:
Sat Mar 14 20:14:43 2020 - [info] /etc/mha/script/master_ip_failover --command=status --ssh_user=root --orig_master_host=rac1 --orig_master_ip=192.168.2.187 --orig_master_port=3306
Sat Mar 14 20:14:43 2020 - [info] OK.
Sat Mar 14 20:14:43 2020 - [warning] shutdown_script is not defined.
Sat Mar 14 20:14:43 2020 - [info] Got exit code 0 (Not master dead).
MySQL Replication Health is OK.
测试通过会有成功字样打印出来
通过输出我们可以看到相关架构信息
3 启动MHA
首先我们可以检测MHA是否已启动
管理节点
[root@rac4 script]# masterha_check_status --conf=/etc/mha/mha.conf
mha is stopped(2:NOT_RUNNING).
我们在manager上执行如下命令开启MHA
管理节点
[root@rac4 script]# nohup masterha_manager --conf=/etc/mha/mha.conf --remove_dead_master_conf &
--remove_dead_master_conf意思为当发生切换后，老的主库信息会从配置文件删除
[1] 4360
[root@rac4 script]# nohup: ignoring input and appending output to ‘nohup.out’
[root@rac4 script]# masterha_check_status --conf=/etc/mha/mha.conf
mha (pid:4360) is running(0:PING_OK), master:rac1
4停止MHA
[root@rac4 script]# masterha_stop -conf=/etc/mha/mha.conf
Stopped mha successfully.
[1]+ Exit 1 nohup masterha_manager --conf=/etc/mha/mha.conf --remove_dead_master_conf
[root@rac4 script]# masterha_check_status --conf=/etc/mha/mha.conf
mha is stopped(2:NOT_RUNNING).
5设置relay log清理计划
前面我们说到我们已经取消了relay log的自动清理，这里需要在主从库3台上设置定时任务手动清理
这里首先需要在root用户环境变量中增加mysql相关路径
主从库三台
export MYSQL_HOME=/usr/local/mysql
export PATH=$HOME/bin:$MYSQL_HOME/bin:$PATH
0 4 1 * * /usr/bin/purge_relay_logs --user=monitor --password=123456 -disable_relay_log_purge --workdir=/tmp/ >> /tmp/purge_relay_logs.log 2>&1
欢迎关注我的微信公众号"IT小Chen"，共同学习，共同成长！！！
