MySQL --MHA 高可用架构构建

数据库

服务器 ip 信息

server 1 172.19.120.141  master
server 2 172.19.120.142  slave
server 3 172.19.120.143  backup
互信端口 默认22
绑定方法：/sbin/ifconfig 网卡名称:网卡序号 ip/mask
vip 172.19.120.239/22
e.g. /sbin/ifconfig eth0:1 172.19.120.239/22
系统版本
CentOS 7
#自动切换时vip管理的脚本，如果使用keepalived的，可以编写脚本完成对vip的管理
master_ip_failover 
#在线切换时vip的管理
master_ip_online_change 
#故障发生后关闭主机的脚本
power_manager
#因故障切换后发送报警的脚本
send_report

refer

https://www.cnblogs.com/John-2011/p/7808554.html
https://devopstack.cn/%E6%95%B0%E6%8D%AE%E5%BA%93/363.html#421_node
https://www.cnblogs.com/shengdimaya/p/6949882.html

mha集群中所有机器相互配置互信 （root)

• 生成ssh-key

ssh-keygen -t rsa

• 复制 id_rsa.pub 到 每台机器上的authorized_keys中 或者使用 ssh-copy-id -i /root/.ssh/id_rsa.pub 下发密钥（需要知道ssh用户的登陆密码）

## 有密码
ssh-copy-id -i /root/.ssh/id_rsa.pub -p 22 -o StrictHostKeyChecking=no root@server1/2/3
## 无密码
touch authorized_keys
chmod 600 /root/.ssh/authorized_keys

• 在每台机器的 hosts.allow 添加mha组中所有server的白名单 /etc/hosts.allow 例如

#mha
sshd:172.19.120.141/255.255.255.255 172.19.120.142/255.255.255.255 172.19.120.143/255.255.255.255

• 测试互信(root）

[root@server2 .ssh]# ssh -p22 172.19.120.141
Last failed login: Sat Nov  7 13:23:40 CST 2020 from 172.19.120.143 on ssh:notty
There were 4 failed login attempts since the last successful login.
Last login: Fri Nov  6 18:35:02 2020
[root@server1 ~]# exit

安装MHA

• 部署node节点

yum install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes -y
wget https://github.com/yoshinorim/mha4mysql-node/releases/download/v0.58/mha4mysql-node-0.58-0.el7.centos.noarch.rpm
rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm

• 创建mha用户和 禁止删除relaylog

## mysql中创建用户
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, FILE, SUPER, LOCK TABLES, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'mhamonitor'@server1/2/2 identified by 'xxxx';
mysql> select user.host from mysql.user where  user='mhamonitor';
+----------------+
| host           |
+----------------+
| 172.19.120.141 |
| 172.19.120.142 |
| 172.19.120.143 |
+----------------+
## 禁止删除中继日志,详细脚本内容见文档末尾
mkdir /var/job
touch purge_relay_logs.sh
-----
## 将禁止删除的任务加入定时任务中
echo "0 4 * * * /bin/bash /var/job/purge_relay_log.sh" >> /var/spool/cron/root

部署管理监控mha节点 以 143 server3 作为管理节点

• 安装管理端软件

# yum install perl-Parallel-ForkManager perl-Log-Dispatch  perl-Config-Tiny perl-DBD-MySQL -y
Package perl-Parallel-ForkManager-1.18-2.el7.noarch already installed and latest version
Package perl-Log-Dispatch-2.41-1.el7.1.noarch already installed and latest version
Package perl-Config-Tiny-2.14-7.el7.noarch already installed and latest version
Package perl-DBD-MySQL-4.023-6.el7.x86_64 already installed and latest version
# 安装管理节点rpm包
wget https://github.com/yoshinorim/mha4mysql-manager/releases/download/v0.58/mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
rpm -ivh mha4mysql-manager-0.58-0.el7.centos.noarch.rpm

• 确认vip已经绑定 host组vip（ip,port)

• 创建配置文件

mkdir -p /etc/masterha/
touch /etc/masterha/app_33066.cnf
-----------------------------------------
[server default]
manager_workdir=/var/log/masterha/app_33066
manager_log=/var/log/masterha/app_33066/manager.log
master_binlog_dir=/var/mysql33066/log
master_ip_failover_script=/usr/local/bin/master_ip_failover_33066
master_ip_online_change_script=/usr/local/bin/master_ip_online_change_33066
user=mhamonitor
password=xxxxxx
ping_interval=1
remote_workdir=/tmp
repl_password=xxxxx
repl_user=mhamonitor
#secondary_check_script=/usr/local/bin/masterha_secondary_check_33066
#init_conf_load_script=/usr/local/bin/init_conf_load_script_33066
#report_script=/usr/local/bin/send_report_33066
ssh_user=root
ssh_port=22
[server1]
hostname=172.19.120.141
port=33066
master_binlog_dir=/var/mysql33066/log
candidate_master=1
[server2]
hostname=172.19.120.142
port=33066
master_binlog_dir=/var/mysql33066/log
candidate_master=1
[server3]
hostname=172.19.120.143
port=33066
master_binlog_dir=/var/mysql33066/log
no_master=1

• mha需要用到的脚本文件

master_ip_failover
master_ip_online_change
purge_relay_log
monitor_manager_33066.sh
/var/log/masterha/app_33066.cnf

定时任务

*/1 * * * * /bin/bash /var/job/monitor_manager_33066

部署完后查看日志 成功

Checking the Status of the script.. OK
Sat Nov  7 16:24:03 2020 - [info]  OK.
Sat Nov  7 16:24:03 2020 - [warning] shutdown_script is not defined.
Sat Nov  7 16:24:03 2020 - [info] Set master ping interval 1 seconds.
Sat Nov  7 16:24:03 2020 - [info] Set secondary check script: /usr/local/bin/masterha_secondary_check_33066
Sat Nov  7 16:24:03 2020 - [info] Starting ping health check on 172.19.120.141(172.19.120.141:33066)..
Sat Nov  7 16:24:03 2020 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond..

monitor_manager

#! /bin/bash
if [ `ps -ef | grep "masterha_manager -conf=/etc/masterha/app_33066.cnf" | grep -v grep |wc -l` -eq 0 ]
then
    nohup /usr/bin/masterha_manager -conf=/etc/masterha/app_33066.cnf --ignore_fail_on_start 2>&1 /var/log/masterha/app1/manager.log &
fi

purge_relay_log

WORKDIR=$(cd $(dirname $0);pwd)
host=172.19.120.141
user='mhamonitor'
passwd='xxxx'
port=33066
log_dir="/var/log/purge_relay_logs_${port}"
work_dir='/var/job'
purge='/usr/bin/purge_relay_logs'
if [ ! -d $work_dir ]
then
   mkdir $work_dir -p
fi
if [ ! -d $log_dir ]
then
   mkdir $log_dir -p
fi
$purge --host=$host --user=$user --password=$passwd --disable_relay_log_purge --port=$port --workdir=$work_dir >> $log_dir/purge_relay_logs.log 2>&1

master_ip_online_change

略[ github]

master_ip_failover

略[github]