MySQL高可用配置与MHA集群

• • 一、MHA概述
  • • 1、什么是MHA
    • 2、MHA 的组成
    • 3、MHA 的特点
  • 二、MHA搭建准备
  • • 1、**MHA架构：**
    • 2、**故障模拟：**
  • 三、实战MySQL高可用
  • • 1、关闭所有机子的防火墙 核心防护 安装数据库，准备主从复制
    • 2、修改 Master、Slave1、Slave2 节点的 Mysql主配置文件/etc/my.cnf
    • 3、验证主从同步
    • 4、安装MHA
    • 5、在所有服务器上配置无密码认证
    • 6、建立mha 文件夹和配置文
    • 7、在Master上手动添加vip
    • 8、在 manager 节点上测试 ssh 无密码认证
    • 9、在 manager 节点上测试 mysql 主从连接情况
    • 10、在 manager 节点上启动 MHA
  • 四、故障模拟
  • • 1、在 manager 节点上监控观察日志记录
    • 2、模拟master节点故障关闭服务
    • 3、查看manager节点动态日志
  • 总结

一、MHA概述

1、什么是MHA

MHA（MasterHigh Availability）是一套优秀的MySQL高可用环境下故障切换和主从复制的软件。

MHA 的出现就是解决MySQL 单点故障的问题。

MySQL故障切换过程中，MHA能做到0-30秒内自动完成故障切换操作。

MHA能在故障切换的过程中最大程度上保证数据的一致性，以达到真正意义上的高可用。

2、MHA 的组成

（1）MHA Node（数据节点）

MHA Node 运行在每台 MySQL 服务器上。

（2）MHA Manager（管理节点）

• MHA Manager 可以单独部署在一台独立的机器上，管理多个 master-slave 集群；也可以部署在一台 slave 节点上。

• MHA Manager 会定时探测集群中的 master 节点。当 master 出现故障时，它可以自动将最新数据的 slave 提升为新的 master， 然后将所有其他的 slave 重新指向新的 master。整个故障转移过程对应用程序完全透明。

3、MHA 的特点

自动故障切换过程中，MHA试图从宕机的主服务器上保存二进制日志，最大程度的保证数据不丢失。

使用半同步复制，可以大大降低数据丢失的风险，如果只有一个slave已经收到了最新的二进制日志，MHA可以将

最新的二进制日志应用于其他所有的slave服务器上，因此可以保证所有节点的数据一致性。

目前MHA支持一主多从架构，最少三台服务器，即一主两从。

二、MHA搭建准备

1、MHA架构：

• 数据库安装
• 一主两从
• MHA搭建

2、故障模拟：

• 主库失效
• 备选主库成为主库
• 原故障主库恢复重新加入到MHA成为从库

实验环境：

Master：192.168.10.130

Slave1：192.168.10.132

Slave2：192.168.10.133

MHA manager：192.168.10.134

实验图示：

三、实战MySQL高可用

1、关闭所有机子的防火墙 核心防护 安装数据库，准备主从复制

[root@Master ~]#systemctl stop firewalld
[root@Master ~]#setenforce 0[root@Slave1 ~]#systemctl stop firewalld
[root@Slave1 ~]#setenforce 0[root@Slave2 ~]#systemctl stop firewalld
[root@Slave2 ~]#setenforce 0[root@MHA ~]#systemctl stop firewalld
[root@MHA ~]#setenforce 0

2、修改 Master、Slave1、Slave2 节点的 Mysql主配置文件/etc/my.cnf

###########Master节点###########
vim /etc/my.cnf
[mysqld]
server_id=130
log-bin=/data/mysql/mysql-bin
skip_name_resolve=1
general_log
[root@Master ~]#mkdir /data/mysql/ -p        #建立文件夹
[root@Master ~]#chown mysql.mysql /data/ -R  #修改权限
[root@Master ~]#systemctl restart mysqld     #重启服务[root@Master ~]#mysql -uroot -pAdmin@123     #进入数据库grant replication slave on *.* to test@'192.168.10.%' identified by 'Admin@123';    #建立复制用户grant all on *.* to mhauser@'192.168.10.%' identified by 'Admin@123';      #建立mha管理账户
mysql> show master status;   #节点查看二进制文件和同步点
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      742 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)mysql> select user,host from mysql.user;

############Slave1节点##########
vim /etc/my.cnf
server_id=132
log-bin=/data/mysql/mysql-bin
relay-log=relay-log-bin
relay-log-index=slave-relay-bin.index
skip_name_resolve=1
general_log [root@Slave1 ~]#mkdir /data/mysql -p    #建立文件夹
[root@Slave1 ~]#chown mysql.mysql /data/ -R   #修改权限
[root@Slave1 ~]#systemctl restart mysqld   #重启
[root@Slave1 ~]#mysql -uroot -pAdmin@123  #进入数据库
mysql> CHANGE MASTER TOMASTER_HOST='192.168.10.130',MASTER_USER='test',MASTER_PASSWORD='Admin@123',MASTER_PORT=3306,MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=742;
Query OK, 0 rows affected, 2 warnings (0.02 sec)grant replication slave on *.* to test@'192.168.10.%' identified by 'Admin@123';  #建立复制用户
grant all on *.* to mhauser@'192.168.10.%' identified by 'Admin@123';     #建立mha管理账户mysql> start slave;      #开启线程，开启主从复制mysql> show slave status\G  #查看设置的状态

#########Slave2节点###########
vim /etc/my.cnf
[mysqld]
server_id=133
log-bin=/data/mysql/mysql-bin
relay-log=relay-log-bin
relay-log-index=slave-relay-bin.index
skip_name_resolve=1
general_log
[root@Slave2 ~]#mkdir /data/mysql -p
[root@Slave2 ~]#chown mysql.mysql /data/ -R
[root@Slave2 ~]#systemctl restart mysqld
mysql> CHANGE MASTER TOMASTER_HOST='192.168.10.130',MASTER_USER='test',MASTER_PASSWORD='Admin@123',MASTER_PORT=3306,MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=742;
Query OK, 0 rows affected, 2 warnings (0.02 sec)grant replication slave on *.* to test@'192.168.10.%' identified by 'Admin@123';  #建立复制用户
grant all on *.* to mhauser@'192.168.10.%' identified by 'Admin@123';      #建立mha管理账户mysql> start slave;      #开启线程，开启主从复制mysql> show slave status\G  #查看设置的状态

3、验证主从同步

4、安装MHA

上传软件包mha，一主两从都需要上传这个包 mha4mysql-node-0.58-0.el7.centos.noarch.rpm，所需的包可去官网下载
[root@MHA opt]#ls
mha4mysql-manager-0.58-0.el7.centos.noarch.rpm  rh
mha4mysql-node-0.58-0.el7.centos.noarch.rpm
[root@MHA opt]#yum install epel-release.noarch  -y
[root@MHA opt]#yum install  -y mha4mysql-*[root@Slave2 opt]#yum install mha4mysql-node-0.58-0.el7.centos.noarch.rpm -y[root@Slave1 opt]#yum install  mha4mysql-node-0.58-0.el7.centos.noarch.rpm -y[root@Master opt]#yum install mha4mysql-node-0.58-0.el7.centos.noarch.rpm -y

5、在所有服务器上配置无密码认证

[root@MHA ~]#ssh-keygen
[root@MHA ~]#ssh-copy-id 127.0.0.1
#自己和自己实现免密钥登录[root@MHA ~]#rsync -a .ssh 192.168.10.130:/root/
[root@MHA ~]#rsync -a .ssh 192.168.10.132:/root/
[root@MHA ~]#rsync -a .ssh 192.168.10.133:/root/

6、建立mha 文件夹和配置文

[root@MHA ~]#mkdir /etc/mastermha
[root@MHA ~]#vim /etc/mastermha/app1.cnf[server default]
user=mhauser
password=Admin@123                            #设置mysql中root用户的密码
manager_workdir=/data/mastermha/app1/         #manager工作目录
manager_log=/data/mastermha/app1/manager.log  #manager日志
remote_workdir=/data/mastermha/app1/   #设置远端mysql在发生切换时binlog的保存位置
ssh_user=root             #设置ssh的登录用户名
repl_user=test            #设置复制用户的用户
repl_password=Admin@123   #设置复制用户的密码
ping_interval=1           #设置监控主库，发送ping包的时间间隔1秒，默认是3秒，尝试三次没有回应的时候自动进行failover
master_ip_failover_script=/usr/local/bin/master_ip_failover   #设置自动failover时候的切换脚本，也就是上面的那个脚本
check_repl_delay=0
master_binlog_dir=/var/lib/mysql    #master保存binlog的位置，这里的路径要与master里配置的binlog的路径一致，以便MHA能找到[server1]
hostname=192.168.10.130
candidate_master=1[server2]
hostname=192.168.10.132
candidate_master=1[server3]
hostname=192.168.10.133#保存退出
[root@MHA ~]#vim master_ip_failover
[root@MHA ~]#chmod +x master_ip_failover
[root@MHA ~]# mv master_ip_failover  /usr/local/bin/    #准备脚本文件   注意注释

7、在Master上手动添加vip

[root@Master opt]#ifconfig ens33:1 192.168.10.188/24

8、在 manager 节点上测试 ssh 无密码认证

[root@MHA ~]#masterha_check_ssh --conf=/etc/mastermha/app1.cnfTue Mar 21 19:09:08 2023 - [warning] Global configuration file /etc/masterha_default.cnf not found                                                                 . Skipping.
Tue Mar 21 19:09:08 2023 - [info] Reading application default configuration from /etc/mastermha/ap                                                                 p1.cnf..
Tue Mar 21 19:09:08 2023 - [info] Reading server configuration from /etc/mastermha/app1.cnf..
Tue Mar 21 19:09:08 2023 - [info] Starting SSH connection tests..
Tue Mar 21 19:09:09 2023 - [debug]
Tue Mar 21 19:09:08 2023 - [debug]  Connecting via SSH from root@192.168.10.130(192.168.10.130:22)                                                                  to root@192.168.10.132(192.168.10.132:22)..
Tue Mar 21 19:09:08 2023 - [debug]   ok.
Tue Mar 21 19:09:08 2023 - [debug]  Connecting via SSH from root@192.168.10.130(192.168.10.130:22)                                                                  to root@192.168.10.133(192.168.10.133:22)..
Tue Mar 21 19:09:09 2023 - [debug]   ok.
Tue Mar 21 19:09:09 2023 - [debug]
Tue Mar 21 19:09:08 2023 - [debug]  Connecting via SSH from root@192.168.10.132(192.168.10.132:22)                                                                  to root@192.168.10.130(192.168.10.130:22)..
Tue Mar 21 19:09:09 2023 - [debug]   ok.
Tue Mar 21 19:09:09 2023 - [debug]  Connecting via SSH from root@192.168.10.132(192.168.10.132:22)                                                                  to root@192.168.10.133(192.168.10.133:22)..
Tue Mar 21 19:09:09 2023 - [debug]   ok.
Tue Mar 21 19:09:10 2023 - [debug]
Tue Mar 21 19:09:09 2023 - [debug]  Connecting via SSH from root@192.168.10.133(192.168.10.133:22)                                                                  to root@192.168.10.130(192.168.10.130:22)..
Tue Mar 21 19:09:09 2023 - [debug]   ok.
Tue Mar 21 19:09:09 2023 - [debug]  Connecting via SSH from root@192.168.10.133(192.168.10.133:22)                                                                  to root@192.168.10.132(192.168.10.132:22)..
Tue Mar 21 19:09:10 2023 - [debug]   ok.
Tue Mar 21 19:09:10 2023 - [info] All SSH connection tests passed successfully.

9、在 manager 节点上测试 mysql 主从连接情况

[root@MHA ~]#masterha_check_repl --conf=/etc/mastermha/app1.cnfTue Mar 21 21:40:46 2023 - [warning] Global configuration file /etc/masterha_default.cnf not found                                                                 . Skipping.
Tue Mar 21 21:40:46 2023 - [info] Reading application default configuration from /etc/mastermha/ap                                                                 p1.cnf..
Tue Mar 21 21:40:46 2023 - [info] Reading server configuration from /etc/mastermha/app1.cnf..
Tue Mar 21 21:40:46 2023 - [info] MHA::MasterMonitor version 0.58.
Tue Mar 21 21:40:47 2023 - [info] GTID failover mode = 0
Tue Mar 21 21:40:47 2023 - [info] Dead Servers:
Tue Mar 21 21:40:47 2023 - [info] Alive Servers:
Tue Mar 21 21:40:47 2023 - [info]   192.168.10.130(192.168.10.130:3306)
Tue Mar 21 21:40:47 2023 - [info]   192.168.10.132(192.168.10.132:3306)
Tue Mar 21 21:40:47 2023 - [info]   192.168.10.133(192.168.10.133:3306)
Tue Mar 21 21:40:47 2023 - [info] Alive Slaves:
Tue Mar 21 21:40:47 2023 - [info]   192.168.10.132(192.168.10.132:3306)  Version=5.7.41-log (oldes                                                                 t major version between slaves) log-bin:enabled
Tue Mar 21 21:40:47 2023 - [info]     Replicating from 192.168.10.130(192.168.10.130:3306)
Tue Mar 21 21:40:47 2023 - [info]     Primary candidate for the new Master (candidate_master is se                                                                 t)
Tue Mar 21 21:40:47 2023 - [info]   192.168.10.133(192.168.10.133:3306)  Version=5.7.41-log (oldes                                                                 t major version between slaves) log-bin:enabled
Tue Mar 21 21:40:47 2023 - [info]     Replicating from 192.168.10.130(192.168.10.130:3306)
Tue Mar 21 21:40:47 2023 - [info] Current Alive Master: 192.168.10.130(192.168.10.130:3306)
Tue Mar 21 21:40:47 2023 - [info] Checking slave configurations..
Tue Mar 21 21:40:47 2023 - [info]  read_only=1 is not set on slave 192.168.10.132(192.168.10.132:3                                                                 306).
Tue Mar 21 21:40:47 2023 - [warning]  relay_log_purge=0 is not set on slave 192.168.10.132(192.168                                                                 .10.132:3306).
Tue Mar 21 21:40:47 2023 - [info]  read_only=1 is not set on slave 192.168.10.133(192.168.10.133:3                                                                 306).
Tue Mar 21 21:40:47 2023 - [warning]  relay_log_purge=0 is not set on slave 192.168.10.133(192.168                                                                 .10.133:3306).
Tue Mar 21 21:40:47 2023 - [info] Checking replication filtering settings..
Tue Mar 21 21:40:47 2023 - [info]  binlog_do_db= , binlog_ignore_db=
Tue Mar 21 21:40:47 2023 - [info]  Replication filtering check ok.
Tue Mar 21 21:40:47 2023 - [info] GTID (with auto-pos) is not supported
Tue Mar 21 21:40:47 2023 - [info] Starting SSH connection tests..
Tue Mar 21 21:40:50 2023 - [info] All SSH connection tests passed successfully.
Tue Mar 21 21:40:50 2023 - [info] Checking MHA Node version..
Tue Mar 21 21:40:50 2023 - [info]  Version check ok.
Tue Mar 21 21:40:50 2023 - [info] Checking SSH publickey authentication settings on the current ma                                                                 ster..
Tue Mar 21 21:40:50 2023 - [info] HealthCheck: SSH to 192.168.10.130 is reachable.
Tue Mar 21 21:40:51 2023 - [info] Master MHA Node version is 0.58.
Tue Mar 21 21:40:51 2023 - [info] Checking recovery script configurations on 192.168.10.130(192.16                                                                 8.10.130:3306)..
Tue Mar 21 21:40:51 2023 - [info]   Executing command: save_binary_logs --command=test --start_pos                                                                 =4 --binlog_dir=/var/lib/mysql --output_file=/data/mastermha/app1//save_binary_logs_test --manager                                                                 _version=0.58 --start_file=mysql-bin.000006
Tue Mar 21 21:40:51 2023 - [info]   Connecting to root@192.168.10.130(192.168.10.130:22)..Creating /data/mastermha/app1 if not exists.. Creating directory /data/mastermha/app1.. done.ok.Checking output directory is accessible or not..ok.Binlog found at /var/lib/mysql, up to mysql-bin.000006
Tue Mar 21 21:40:51 2023 - [info] Binlog setting check done.
Tue Mar 21 21:40:51 2023 - [info] Checking SSH publickey authentication and checking recovery scri                                                                 pt configurations on all alive slave servers..
Tue Mar 21 21:40:51 2023 - [info]   Executing command : apply_diff_relay_logs --command=test --sla                                                                 ve_user='mhauser' --slave_host=192.168.10.132 --slave_ip=192.168.10.132 --slave_port=3306 --workdi                                                                 r=/data/mastermha/app1/ --target_version=5.7.41-log --manager_version=0.58 --relay_log_info=/var/l                                                                 ib/mysql/relay-log.info  --relay_dir=/var/lib/mysql/  --slave_pass=xxx
Tue Mar 21 21:40:51 2023 - [info]   Connecting to root@192.168.10.132(192.168.10.132:22)..
Creating directory /data/mastermha/app1/.. done.Checking slave recovery environment settings..Opening /var/lib/mysql/relay-log.info ... ok.Relay log found at /var/lib/mysql, up to relay-log-bin.000014Temporary relay log file is /var/lib/mysql/relay-log-bin.000014Checking if super_read_only is defined and turned on.. not present or turned off, ignoring.Testing mysql connection and privileges..
mysql: [Warning] Using a password on the command line interface can be insecure.done.Testing mysqlbinlog output.. done.Cleaning up test file(s).. done.
Tue Mar 21 21:40:51 2023 - [info]   Executing command : apply_diff_relay_logs --command=test --sla                                                                 ve_user='mhauser' --slave_host=192.168.10.133 --slave_ip=192.168.10.133 --slave_port=3306 --workdi                                                                 r=/data/mastermha/app1/ --target_version=5.7.41-log --manager_version=0.58 --relay_log_info=/var/l                                                                 ib/mysql/relay-log.info  --relay_dir=/var/lib/mysql/  --slave_pass=xxx
Tue Mar 21 21:40:51 2023 - [info]   Connecting to root@192.168.10.133(192.168.10.133:22)..
Creating directory /data/mastermha/app1/.. done.Checking slave recovery environment settings..Opening /var/lib/mysql/relay-log.info ... ok.Relay log found at /var/lib/mysql, up to relay-log-bin.000012Temporary relay log file is /var/lib/mysql/relay-log-bin.000012Checking if super_read_only is defined and turned on.. not present or turned off, ignoring.Testing mysql connection and privileges..
mysql: [Warning] Using a password on the command line interface can be insecure.done.Testing mysqlbinlog output.. done.Cleaning up test file(s).. done.
Tue Mar 21 21:40:51 2023 - [info] Slaves settings check done.
Tue Mar 21 21:40:51 2023 - [info]
192.168.10.130(192.168.10.130:3306) (current master)+--192.168.10.132(192.168.10.132:3306)+--192.168.10.133(192.168.10.133:3306)Tue Mar 21 21:40:51 2023 - [info] Checking replication health on 192.168.10.132..
Tue Mar 21 21:40:51 2023 - [info]  ok.
Tue Mar 21 21:40:51 2023 - [info] Checking replication health on 192.168.10.133..
Tue Mar 21 21:40:51 2023 - [info]  ok.
Tue Mar 21 21:40:51 2023 - [info] Checking master_ip_failover_script status:
Tue Mar 21 21:40:51 2023 - [info]   /usr/local/bin/master_ip_failover --command=status --ssh_user=                                                                 root --orig_master_host=192.168.10.130 --orig_master_ip=192.168.10.130 --orig_master_port=3306IN SCRIPT TEST====/sbin/ifconfig ens33:1 down==/sbin/ifconfig ens33:1 192.168.10.188/24;/sbin/arpi                                                                 ng -I ens33 -c 3 -s 192.168.10.188/24 192.168.10.2 >/dev/null 2>&1===Checking the Status of the script.. OK
Tue Mar 21 21:40:52 2023 - [info]  OK.
Tue Mar 21 21:40:52 2023 - [warning] shutdown_script is not defined.
Tue Mar 21 21:40:52 2023 - [info] Got exit code 0 (Not master dead).MySQL Replication Health is OK.   #出现该字样说明主从连接正常

10、在 manager 节点上启动 MHA

[root@MHA ~]#masterha_manager --conf=/etc/mastermha/app1.cnf  #非后台启动
在另开一台查看一下是否启动
[root@MHA ~]#masterha_check_status --conf=/etc/mastermha/app1.cnf
app1 (pid:65363) is running(0:PING_OK), master:192.168.10.130

四、故障模拟

1、在 manager 节点上监控观察日志记录

[root@MHA ~]#tail -f /data/mastermha/app1/manager.log

2、模拟master节点故障关闭服务

正常自动切换一次后，MHA 进程会退出。HMA 会自动修改 app1.cnf 文件内容，将宕机的 master节点删除。

slave1 会自动接管 VIP，成为新的master。

3、查看manager节点动态日志

总结

MHA作用：mysql的高可用+故障切换

核心部分：MHA组件

• manager:主要功能：做MHA的启动、关闭管理和检测mysql各种健康状态
• node:在发生故障时，尽可能地保存二进制日志，并且实现故障切换（VIP地址漂移）