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"，共同学习，共同成长！！！