MySQL的MHA
MHA的介绍
原理：当Master出现故障时，它可以自动将最新数据的Slave提升为新的Master,然后将所有其他的Slave重新指向新的Master
MHA能够在较短的时间内实现自动故障检测和故障转移，通常在10-30秒以内;在复制框架中，MHA能够很好地解决复制过程中的数据一致性问题，由于不需要在现有的replication中添加额外的服务器，仅需要一个manager节点，而一个Manager能管理多套复制，所以能大大地节约服务器的数量;另外，安装简单，无性能损耗，以及不需要修改现有的复制部署也是它的优势之处。
MHA的工作原理
MHA的架构
MHA的工具
node 工具
[root@db01 bin]# ll
-rwxrwxr-x 1 root root 17639 Mar 23 2018 apply_diff_relay_logs # 对比relay log找到新主库
-rwxrwxr-x 1 root root 4807 Mar 23 2018 filter_mysqlbinlog # 截取binlog
-rwxrwxr-x 1 root root 8337 Mar 23 2018 purge_relay_logs # 删除relay log工具
-rwxrwxr-x 1 root root 7525 Mar 23 2018 save_binary_logs # 保存binlog
master 工具
[root@db01 bin]# ll
masterha_check_repl # 检测主从复制
masterha_check_ssh # 检测ssh免密
masterha_check_status # 检测MHA的运行状态 systemctl status mha
masterha_conf_host # MHA虚拟主机配置（MHA做完切换后，会将宕机主库从配置文件中摘除）
masterha_manager # MHA的启动命令 systemctl start mha
masterha_master_monitor # 检测主库心跳
masterha_master_switch # 切换工具
masterha_secondary_check # 检测TCP/IP连接
masterha_stop # 停止MHA
systemctl stop mha
MHA的优点总结
1）Masterfailover and slave promotion can be done very quickly
自动故障转移快
2）Mastercrash does not result in data inconsistency
主库崩溃不存在数据一致性问题
3）Noneed to modify current MySQL settings (MHA works with regular MySQL)
不需要对当前mysql环境做重大修改
4）Noneed to increase lots of servers
不需要添加额外的服务器(仅一台manager就可管理上百个replication)
5）Noperformance penalty
性能优秀，可工作在半同步复制和异步复制，当监控mysql状态时，仅需要每隔N秒向master发送ping包(默认3秒)，所以对性能无影响。你可以理解为MHA的性能和简单的主从复制框架性能一样。
6）Works with any storage engine
只要replication支持的存储引擎，MHA都支持，不会局限于innodb
部署MHA
环境
前期环境准备
# 传统主从复制
1.主库开启binlog，从库不开
2.主库和从库server_id不同，从库之间可以相同
3.主库要创建主从复制用户，从库可以不创建
# 基于MHA的主从复制
1.主库开启binlog，从库也要开启binlog
2.主库和从库server_id不同，从库之间也不能相同
3.主库要创建主从复制用户，从库必须创建主从复制用户
#### 配置文件
## 主库配置文件
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
basedir=/app/mysql
datadir=/app/mysql/data
server_id=1
log-bin=mysql-bin
skip_name_resolve
## 从库配置文件
[root@db02 ~]# vim /etc/my.cnf
[mysqld]
basedir=/app/mysql
datadir=/app/mysql/data
server_id=2
log-bin=mysql-bin
skip_name_resolve
[root@db03 ~]# vim /etc/my.cnf
[mysqld]
basedir=/app/mysql
datadir=/app/mysql/data
server_id=3
log-bin=mysql-bin
skip_name_resolve
[root@db04 ~]# vim /etc/my.cnf
[mysqld]
basedir=/app/mysql
datadir=/app/mysql/data
server_id=4
log-bin=mysql-bin
skip_name_resolve
## 重启数据库
[root@db01 ~]# systemctl restart mysqld
[root@db02 ~]# systemctl restart mysqld
[root@db03 ~]# systemctl restart mysqld
[root@db04 ~]# systemctl restart mysqld
## 三台从库设置只读（临时生效）
mysql> set global read_only=1;
## 四台数据库关闭relaylog自动删除功能（永久生效）
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
relay_log_purge=0
[root@db02 ~]# vim /etc/my.cnf
[mysqld]
relay_log_purge=0
[root@db03 ~]# vim /etc/my.cnf
[mysqld]
relay_log_purge=0
[root@db04 ~]# vim /etc/my.cnf
[mysqld]
relay_log_purge=0
node的安装
[root@db01 ~]# yum localinstall -y mha4mysql-node-0.58-0.el7.centos.noarch.rpm
[root@db02 ~]# yum localinstall -y mha4mysql-node-0.58-0.el7.centos.noarch.rpm
[root@db03 ~]# yum localinstall -y mha4mysql-node-0.58-0.el7.centos.noarch.rpm
[root@db04 ~]# yum localinstall -y mha4mysql-node-0.58-0.el7.centos.noarch.rpm
[root@mha-manager ~]# yum localinstall -y mha4mysql-node-0.58-0.el7.centos.noarch.rpm
master的安装
[root@mha-manager ~]# yum localinstall -y mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
命令软连接
[root@db01 ~]# ln -s /app/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog
[root@db01 ~]# ln -s /app/mysql/bin/mysql /usr/bin/mysql
[root@db02 ~]# ln -s /app/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog
[root@db02 ~]# ln -s /app/mysql/bin/mysql /usr/bin/mysql
[root@db03 ~]# ln -s /app/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog
[root@db03 ~]# ln -s /app/mysql/bin/mysql /usr/bin/mysql
[root@db04 ~]# ln -s /app/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog
[root@db04 ~]# ln -s /app/mysql/bin/mysql /usr/bin/mysql
ssh免密
## 生成密钥对
[root@db01 ~]# ssh-keygen -t dsa -P '' -f ~/.ssh/id_dsa >/dev/null 2>&1
[root@db02 ~]# ssh-keygen -t dsa -P '' -f ~/.ssh/id_dsa >/dev/null 2>&1
[root@db03 ~]# ssh-keygen -t dsa -P '' -f ~/.ssh/id_dsa >/dev/null 2>&1
[root@db04 ~]# ssh-keygen -t dsa -P '' -f ~/.ssh/id_dsa >/dev/null 2>&1
[root@mha-manager ~]# ssh-keygen -t dsa -P '' -f ~/.ssh/id_dsa >/dev/null 2>&1
## 推送公钥
[root@db01 ~]# sshpass -p 1 ssh-copy-id -i ~/.ssh/id_dsa.pub [email protected]
[root@db01 ~]# sshpass -p 1 ssh-copy-id -i ~/.ssh/id_dsa.pub [email protected]
[root@db01 ~]# sshpass -p 1 ssh-copy-id -i ~/.ssh/id_dsa.pub [email protected]
[root@db01 ~]# sshpass -p 1 ssh-copy-id -i ~/.ssh/id_dsa.pub [email protected]
[root@db01 ~]# sshpass -p 1 ssh-copy-id -i ~/.ssh/id_dsa.pub [email protected]
[root@db02 ~]# sshpass -p 1 ssh-copy-id -i ~/.ssh/id_dsa.pub [email protected]
[root@db02 ~]# sshpass -p 1 ssh-copy-id -i ~/.ssh/id_dsa.pub [email protected]
[root@db02 ~]# sshpass -p 1 ssh-copy-id -i ~/.ssh/id_dsa.pub [email protected]
[root@db02 ~]# sshpass -p 1 ssh-copy-id -i ~/.ssh/id_dsa.pub [email protected]
[root@db02 ~]# sshpass -p 1 ssh-copy-id -i ~/.ssh/id_dsa.pub [email protected]
[root@db03 ~]# sshpass -p 1 ssh-copy-id -i ~/.ssh/id_dsa.pub [email protected]
[root@db03 ~]# sshpass -p 1 ssh-copy-id -i ~/.ssh/id_dsa.pub [email protected]
[root@db03 ~]# sshpass -p 1 ssh-copy-id -i ~/.ssh/id_dsa.pub [email protected]
[root@db03 ~]# sshpass -p 1 ssh-copy-id -i ~/.ssh/id_dsa.pub [email protected]
[root@db03 ~]# sshpass -p 1 ssh-copy-id -i ~/.ssh/id_dsa.pub [email protected]
[root@db04 ~]# sshpass -p 1 ssh-copy-id -i ~/.ssh/id_dsa.pub [email protected]
[root@db04 ~]# sshpass -p 1 ssh-copy-id -i ~/.ssh/id_dsa.pub [email protected]
[root@db04 ~]# sshpass -p 1 ssh-copy-id -i ~/.ssh/id_dsa.pub [email protected]
[root@db04 ~]# sshpass -p 1 ssh-copy-id -i ~/.ssh/id_dsa.pub [email protected]
[root@db04 ~]# sshpass -p 1 ssh-copy-id -i ~/.ssh/id_dsa.pub [email protected]
[root@mha-manager ~]# sshpass -p 1 ssh-copy-id -i ~/.ssh/id_dsa.pub [email protected]
[root@mha-manager ~]# sshpass -p 1 ssh-copy-id -i ~/.ssh/id_dsa.pub [email protected]
[root@mha-manager ~]# sshpass -p 1 ssh-copy-id -i ~/.ssh/id_dsa.pub [email protected]
[root@mha-manager ~]# sshpass -p 1 ssh-copy-id -i ~/.ssh/id_dsa.pub [email protected]
[root@mha-manager ~]# sshpass -p 1 ssh-copy-id -i ~/.ssh/id_dsa.pub [email protected]
## 检测每台机器的免密
for i in 50 51 52 53 54;do ssh [email protected].$i "ifconfig|awk 'NR==2{print $2}'";done
配置MHA
# 1.创建配置文件存放目录
[root@mha-manager ~]# mkdir /etc/mha
# 2.编写配置文件
[root@mha-manager ~]# vim /etc/mha/app1.cnf
[server default]
manager_log=/etc/mha/logs/manager.log
manager_workdir=/etc/mha/app1
master_binlog_dir=/app/mysql/data
user=mha
password=mha
ping_interval=2
repl_user=rep
repl_password=123
ssh_user=root
ssh_port=22
[server1]
#candidate_master=1
#check_repl_delay=0
hostname=172.16.1.51
port=3306
[server2]
#candidate_master=1
#check_repl_delay=0
hostname=172.16.1.52
port=3306
[server3]
hostname=172.16.1.53
port=3306
[server4]
hostname=172.16.1.54
port=3306
# 3.创建MHA管理用户
mysql> grant all on *.* to mha@'172.16.1.5%' identified by 'mha';
# 4.日志和工作目录目录创建
[root@mha-manager ~]# mkdir -p /etc/mha/{logs,app1}
[root@mha-manager ~]# ll /etc/mha/
drwxr-xr-x 2 root root 6 Aug 27 12:20 app1
-rw-r--r-- 1 root root 4 Aug 27 12:16 app1.cnf
drwxr-xr-x 2 root root 6 Aug 27 12:19 logs
# 5.检测MHA ssh免密
[root@mha-manager ~]# masterha_check_ssh --conf=/etc/mha/app1.cnf
All SSH connection tests passed successfully.
# 6.检测MHA 主从复制
[root@mha-manager ~]# masterha_check_repl --conf=/etc/mha/app1.cnf
MySQL Replication Health is OK.
# 7.启动MHA
nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /etc/mha/logs/manager.log 2>&1 &
masterha_manager：启动命令
--conf=/etc/mha/app1.cnf：指定配置文件
--remove_dead_master_conf：做完切换后，从配置文件中摘除宕机的主库
--ignore_last_failover：忽略上一次切换
# 8.启动后检测
[root@mha-manager ~]# masterha_check_status --conf=/etc/mha/app1.cnf
app1 (pid:19524) is running(0:PING_OK), master:172.16.1.51
MHA切换机制：
1.MHA在做一次切换后，生成一个锁文件（app1.failover.complete），在工作目录下，8个小时之内，无法做第二次切换 2.MHA切换完成后，会自动结束MHA的进程 3.在所有从库数据相同时，MHA会选择配置文件中标签id最小的切换
使用systemd 管理 MHA
[root@mha-manager ~]# vim /usr/lib/systemd/system/mha.service
[Unit]
Description=MHA
After=network.target sshd-keygen.service
Wants=sshd-keygen.service
[Service]
Type=sample
ExecStart=/usr/bin/masterha_manager --conf=/etc/mha/app1.cnf --
remove_dead_master_conf --ignore_last_failover > /etc/mha/logs/manager.log
ExecStop=/usr/bin/masterha_stop --conf=/etc/mha/app1.cnf
[Install]
WantedBy=multi-user.target
MHA的太子
candidate_master=1 // 设立太子，但是如果太子落后其他机器数据超过100M，就废储
check_repl_delay=0 // 关闭对太子落后的检测
MHA传统主从的bin_log保存路径
# 1.先将binlog保存在宕机主库的 /var/tmp/
[root@db02 ~]# ll
/var/tmp/saved_master_binlog_from_172.16.1.52_3306_20240827154042.binlog
-rw-r--r-- 1 root root 177 Aug 27 15:40
/var/tmp/saved_master_binlog_from_172.16.1.52_3306_20240827154042.binlog
# 2.将binlog从宕机主库保存到manager所在的机器
[root@mha-manager ~]# ll
/etc/mha/app1/saved_master_binlog_from_172.16.1.52_3306_20240827154042.binlog
-rw-r--r-- 1 root root 177 Aug 27 15:40
/etc/mha/app1/saved_master_binlog_from_172.16.1.52_3306_20240827154042.binlog
# 3.将binlog从manager所在的机器发送给新主库
[root@db04 ~]# ll
/var/tmp/saved_master_binlog_from_172.16.1.52_3306_20240827154042.binlog
-rw-r--r-- 1 root root 177 Aug 27 15:40
/var/tmp/saved_master_binlog_from_172.16.1.52_3306_20240827154042.binlog