MySQL MHA 部署全攻略：从零搭建高可用数据库架构

1.MHA介绍

MySQL的MHA（Master High Availability）是一种用于MySQL高可用性的解决方案，旨在提高MySQL数据库集群的可用性和故障恢复能力。MHA主要通过自动故障切换来确保数据库的高可用性，它能够在主数据库出现故障时，自动将从数据库提升为主数据库，以避免长时间的服务中断。

2.MHA组件介绍

1. MHA Manager（MHA 管理器）
   功能：MHA Manager 是 MHA 的控制中心，负责监控 MySQL 主从数据库的健康状态，并在主服务器故障时触发自动故障切换。
   角色：
   监控数据库主从节点的状态。
   在主服务器发生故障时，选择健康的从服务器并提升为新的主服务器。
   管理数据库节点的配置（如：主库和从库的切换）。
   在主库恢复后，自动将恢复的主库重新配置为从库。
   部署：MHA Manager 通常运行在一个独立的服务器上，不与数据库实例共享。

2. MHA Node（MHA 节点）
   功能：MHA Node 是 MHA 管理器和 MySQL 数据库之间的中介，负责与 MHA Manager 进行通信，并执行主从切换操作。
   角色：
   向 MHA Manager 上报数据库节点（主库和从库）的健康状态。
   参与故障切换流程，包括主库的提升和从库的降级。
   部署：每个 MySQL 实例（主服务器和从服务器）都需要安装和配置 MHA Node，以便与 MHA Manager 通信。

3.集群规划

主机名	IP地址	组件(角色)	版本
test-server-01	192.168.40.180	MHA Manager	0.58
test-server-02	192.168.40.181	MySQL（Master）、MHA Node	5.7.20、0.58
test-server-03	192.168.40.182	MySQL（Master）、MHA Node	5.7.20、0.58
test-server-04	192.168.40.183	MySQL（Master）、MHA Node	5.7.20、0.58

4.服务器初始化

1. 服务器免密

MHA Manager使用SSH登录到主从数据库节点，执行故障检测、切换主库等操作，包括在故障切换过程中通过SSH访问数据库服务器执行切换操作（例如停止服务、修改配置文件等）。

#MHA Manager免密
[root@test-server-01 ~]# ssh-keygen -t rsa
[root@test-server-01 ~]# ssh-copy-id 192.168.40.180
[root@test-server-01 ~]# ssh-copy-id 192.168.40.181
[root@test-server-01 ~]# ssh-copy-id 192.168.40.182
[root@test-server-01 ~]# ssh-copy-id 192.168.40.183#MySQL Master
[root@test-server-02 ~]# ssh-keygen -t rsa
[root@test-server-02 ~]# ssh-copy-id 192.168.40.180
[root@test-server-02 ~]# ssh-copy-id 192.168.40.181
[root@test-server-02 ~]# ssh-copy-id 192.168.40.182
[root@test-server-02 ~]# ssh-copy-id 192.168.40.183#MySQL Slave 1
[root@test-server-03 ~]# ssh-keygen -t rsa
[root@test-server-03 ~]# ssh-copy-id 192.168.40.180
[root@test-server-03 ~]# ssh-copy-id 192.168.40.181
[root@test-server-03 ~]# ssh-copy-id 192.168.40.182
[root@test-server-03 ~]# ssh-copy-id 192.168.40.183#MySQL Slave 2
[root@test-server-04 ~]# ssh-keygen -t rsa
[root@test-server-04 ~]# ssh-copy-id 192.168.40.180
[root@test-server-04 ~]# ssh-copy-id 192.168.40.181
[root@test-server-04 ~]# ssh-copy-id 192.168.40.182
[root@test-server-04 ~]# ssh-copy-id 192.168.40.183

2. 关闭防火墙

#所有机器 
[root@test-server-01 ~]# systemctl stop firewalld ; systemctl disable firewalld

3. 配置时间同步

#所有机器
#安装ntpdate命令
[root@test-server-01 ~]# yum install ntpdate -y#跟网络时间做同步
[root@test-server-01 ~]# ntpdate cn.pool.ntp.org#把时间同步做成计划任务
[root@test-server-01 ~]# crontab -e
* *  * * * /usr/sbin/ntpdate   cn.pool.ntp.org

5.MySQL集群部署

MySQL组件下载地址

选择对应版本下载

5.1 安装MySQL集群

三台MySQL节点操作

1. 上传安装包到/usr/local/src目录

[root@test-server02 src]# ll mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz
-rw-r--r-- 1 root root 641127384 Feb 19 13:39 mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz

2. 解压安装包

[root@test-server02 src]# tar -xvf mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz

3. 移动安装包，重新命名

[root@test-server02 src]# mv mysql-5.7.20-linux-glibc2.12-x86_64 /usr/local/mysql5.7

4. 设置路径环境变量

[root@test-server02 src]# echo 'export  PATH=/usr/local/mysql5.7/bin:$PATH' >> /etc/profile
[root@test-server02 src]# source /etc/profile

5. 创建MySQL用户

[root@test-server02 src]# useradd -M -s /sbin/nologin  mysql

6. 创建MySQL数据目录

[root@test-server02 src]# mkdir -pv  /opt/mysqldb/{temp,log,data}

7. 赋予MySQL用户权限

[root@test-server02 src]# chown -R mysql:mysql /opt/mysqldb

8. 初始化MySQL数据目录

[root@test-server02 src]# mysqld --initialize  --datadir=/opt/mysqldb/data --user=mysql2025-02-19T06:40:36.493733Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2025-02-19T06:40:36.494435Z 0 [ERROR] Can't find error-message file '/usr/local/mysql/share/errmsg.sys'. Check error-message file location and 'lc-messages-dir' configuration directive.
2025-02-19T06:40:37.775262Z 0 [Warning] InnoDB: New log files created, LSN=45790
2025-02-19T06:40:37.983917Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2025-02-19T06:40:38.309684Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 6e2db754-ee8c-11ef-b1a1-000c292b8fdc.
2025-02-19T06:40:38.375782Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2025-02-19T06:40:38.378205Z 1 [Note] A temporary password is generated for root@localhost: n-HaNrreh2_t#ERROR不需要管，出现同上日志代表初始化成功。
n-HaNrreh2_t：MySQL初始化之后的密码，需要保存下来等会使用

9. 配置MySQL

#MySQL Master配置
[root@test-server02 src]# cat /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql5.7
user=mysql
port=3306
datadir=/opt/mysqldb/data
log-error=/opt/mysqldb/log/err.log
pid-file=/opt/mysqldb/temp/mysqld.pid
socket=/opt/mysqldb/temp/mysqld.sock
symbolic-links=0
server_id=1
gtid-mode=on
enforce-gtid-consistency=true
relay_log_purge=0
log_slave_updates=ON
log_bin=/opt/mysqldb/log/binlog
binlog_format=ROW[client]
socket=/opt/mysqldb/temp/mysqld.sock
default-character-set=utf8#MySQL Slave1配置
[root@test-server03 src]# cat /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql5.7
user=mysql
port=3306
datadir=/opt/mysqldb/data
log-error=/opt/mysqldb/log/err.log
pid-file=/opt/mysqldb/temp/mysqld.pid
socket=/opt/mysqldb/temp/mysqld.sock
symbolic-links=0
server_id=2
gtid-mode=on
enforce-gtid-consistency=true
relay_log_purge=0
log_slave_updates=ON
log_bin=/opt/mysqldb/log/binlog
binlog_format=ROW[client]
socket=/opt/mysqldb/temp/mysqld.sock
default-character-set=utf8#MySQL Slave2配置
[root@test-server04 src]# cat /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql5.7
user=mysql
port=3306
datadir=/opt/mysqldb/data
log-error=/opt/mysqldb/log/err.log
pid-file=/opt/mysqldb/temp/mysqld.pid
socket=/opt/mysqldb/temp/mysqld.sock
symbolic-links=0
server_id=3
gtid-mode=on
enforce-gtid-consistency=true
relay_log_purge=0
log_slave_updates=ON
log_bin=/opt/mysqldb/log/binlog
binlog_format=ROW[client]
socket=/opt/mysqldb/temp/mysqld.sock
default-character-set=utf8#server_id三台不能相同，数据目录改成自己的

10. 创建MySQL启动文件

#三台MySQL节点操作
[root@test-server02 src]# cat /usr/lib/systemd/system/mysqld.service
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target[Install]
WantedBy=multi-user.target[Service]
User=mysql
Group=mysql
Type=forking
PIDFile=/opt/mysqldb/temp/mysqld.pid
# Disable service start and stop timeout logic of systemd for mysqld service.
TimeoutSec=0
# Execute pre and post scripts as root
PermissionsStartOnly=true
# Needed to create system tables
# ExecStartPre=/usr/bin/mysqld_pre_systemd
# Start main service
ExecStart=/usr/local/mysql5.7/bin/mysqld --daemonize --pid-file=/opt/mysqldb/temp/mysqld.pid $MYSQLD_OPTS
# Use this to switch malloc implementation
# EnvironmentFile=-/etc/sysconfig/mysql
# Sets open_files_limit
LimitNOFILE = 5000
Restart=on-failure
RestartPreventExitStatus=1
PrivateTmp=false#目录根据自己情况修改
#赋予执行权限
[root@test-server02 src]# chmod 644 /usr/lib/systemd/system/mysqld.service    

11. 启动MySQL

#三台MySQL节点操作
#systemd重新加载配置
[root@test-server02 src]# systemctl daemon-reload#设置开机自启
[root@test-server02 src]# systemctl enable mysqld
Created symlink from /etc/systemd/system/multi-user.target.wants/mysqld.service to /usr/lib/systemd/system/mysqld.service.#重启MySQL
[root@test-server02 src]# systemctl restart mysqld#查看MySQL状态
[root@test-server02 src]# systemctl status mysqld

12. 修改MySQL密码

#登录MySQL
[root@test-server02 src]# mysql -uroot -pn-HaNrreh2_t  #每台MySQL的密码不同#三台MySQL节点操作
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';

5.2 配置一主两从

1. 授予权限

三台MySQL节点操作

#授予该用户进行主从复制所需的权限
mysql> grant replication slave on *.* to 'repl_user'@'192.168.40.%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)#刷新
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

2. 查看二进制文件名和位置信息

master节点操作

mysql> show master status;
+---------------+----------+--------------+------------------+------------------------------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+---------------+----------+--------------+------------------+------------------------------------------+
| binlog.000001 |     1467 |              |                  | 6e2db754-ee8c-11ef-b1a1-000c292b8fdc:1-6 |
+---------------+----------+--------------+------------------+------------------------------------------+

3. 配置从节点

Slave 1和Slave 2执行

#根据自己的信息进行修改
mysql> change master to master_host='192.168.40.181',master_user='repl_user',master_password='123456',master_log_file='binlog.000001',master_log_pos=1467;
Query OK, 0 rows affected, 2 warnings (0.03 sec)mysql> start slave;
Query OK, 0 rows affected (0.01 sec)mysql> show slave status \G;
*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.40.181Master_User: repl_userMaster_Port: 3306Connect_Retry: 60Master_Log_File: binlog.000001Read_Master_Log_Pos: 1467Relay_Log_File: test-server03-relay-bin.000002Relay_Log_Pos: 317Relay_Master_Log_File: binlog.000001Slave_IO_Running: YesSlave_SQL_Running: Yes#两个YES代表MySQL已经成功设置成了主从

4. 读写分离

Slave 1和Slave 2执行

#两个从库必须设置为只读模式：
mysql> set global read_only=1;
Query OK, 0 rows affected (0.00 sec)

5.3 测试MySQL主从

MySQL Master操作

CREATE DATABASE test_db;USE test_db;CREATE TABLE test_table (id INT PRIMARY KEY,name VARCHAR(50)
);INSERT INTO test_table (id, name) VALUES (1, 'John');
INSERT INTO test_table (id, name) VALUES (2, 'Alice');

MySQL Slave 1和Slave 2操作

mysql> select * from test_db.test_table;
+----+-------+
| id | name  |
+----+-------+
|  1 | John  |
|  2 | Alice |
+----+-------+
2 rows in set (0.00 sec)
#出来数据代表主从成功

5.4 赋予MHA用户连接权限

Master操作即可

mysql> grant all privileges on *.* to 'mha_user'@'192.168.40.%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)#主从复制已完成，所以从不用单独操作

6.安装MHA环境

• 安装MHA依赖环境

#四台机器全部操作
[root@test-server-01 ~]# yum install epel-release --nogpgcheck -y[root@test-server-01 ~]# yum install -y perl-DBD-MySQL \
perl-Config-Tiny \
perl-Log-Dispatch \
perl-Parallel-ForkManager \
perl-ExtUtils-CBuilder \
perl-ExtUtils-MakeMaker \
perl-CPAN

6.1 安装MHA Node

MHA Node下载地址

四台机器全部安装MHA Node

1. 上传部署包到/opt目录

[root@test-server-01 opt]# ll mha4mysql-node-0.58.tar.gz
-rw-r--r-- 1 root root 56220 Feb 20 09:09 mha4mysql-node-0.58.tar.gz

2. 生成Makefile

[root@test-server-01 opt]# tar -xf mha4mysql-node-0.58.tar.gz
[root@test-server-01 opt]# cd mha4mysql-node-0.58
[root@test-server-01 mha4mysql-node-0.58]# perl Makefile.PL
*** Module::AutoInstall version 1.06
*** Checking for Perl dependencies...
[Core Features]
- DBI        ...loaded. (1.627)
- DBD::mysql ...loaded. (4.023)
*** Module::AutoInstall configuration finished.
Checking if your kit is complete...
Looks good
Writing Makefile for mha4mysql::node

3. 编译安装

[root@test-server-01 mha4mysql-node-0.58]# make && make install#安装完成之后 /usr/local/bin会生成四个文件
apply_diff_relay_logs
filter_mysqlbinlog
purge_relay_logs
save_binary_logs1.apply_diff_relay_logs #识别差异的中继日志事件并将其差异的事件应用于其他的 slave
2.filter_mysqlbinlog    #去除不必要的 ROLLBACK 事件（MHA 已不再使用这个工具）
3.purge_relay_logs      #清除中继日志（不会阻塞 SQL 线程）
4.save_binary_logs      #保存和复制 master 的二进制日志

6.2 安装MHA Manager

MHA Manager下载地址

MHA Manager节点操作

1. 上传部署包到opt目录

[root@test-server-01 opt]# ll mha4mysql-manager-0.58.tar.gz
-rw-r--r-- 1 root root 119801 Feb 20 09:37 mha4mysql-manager-0.58.tar.gz

2. 生成Makefile

[root@test-server-01 opt]# tar -xf mha4mysql-manager-0.58.tar.gz
[root@test-server-01 opt]# cd mha4mysql-manager-0.58
[root@test-server-01 mha4mysql-manager-0.58]# perl Makefile.PL
*** Module::AutoInstall version 1.06
*** Checking for Perl dependencies...
[Core Features]
- DBI                   ...loaded. (1.627)
- DBD::mysql            ...loaded. (4.023)
- Time::HiRes           ...loaded. (1.9725)
- Config::Tiny          ...loaded. (2.14)
- Log::Dispatch         ...loaded. (2.41)
- Parallel::ForkManager ...loaded. (1.18)
- MHA::NodeConst        ...loaded. (0.58)
*** Module::AutoInstall configuration finished.
Checking if your kit is complete...
Looks good
Writing Makefile for mha4mysql::manager

3. 编译安装

[root@test-server-01 mha4mysql-manager-0.58]# make && make install#安装完成之后 /usr/local/bin会生成几个文件
masterha_check_repl
masterha_check_ssh
masterha_check_status
masterha_conf_host
masterha_manager
masterha_master_monitor
masterha_master_switch
masterha_secondary_check
masterha_stop1. masterha_check_repl：用于检查 MySQL 主从复制的状态，确保主从复制正常运行并进行必要的修复操作。
2. masterha_check_ssh：检查 MySQL 主从节点之间的 SSH 连接状态，确保 SSH 连接正常，这对 MHA 工具的正常操作至关重要。
3. masterha_check_status：用于检查 MySQL 主从复制环境的状态，包括检查主从复制延迟等信息。
4. masterha_conf_host：用于配置 MHA 工具中的主机信息，包括主从节点的连接信息等。
5. masterha_manager：主要的 MHA 工具，用于监控和管理 MySQL 主从复制环境的自动故障转移和故障恢复。
6. masterha_master_monitor：用于监控 MySQL 主服务器的状态，以及检测主服务器是否发生故障。
7. masterha_master_switch：用于手动切换 MySQL 主从复制环境中的主服务器，执行主服务器的切换操作。
8. masterha_secondary_check：用于检查 MySQL 主从复制中的辅助节点（Secondary）状态，确保辅助节点正常。
9. masterha_stop：用于停止 MHA 工具或相关服务的操作。

7.配置MHA环境

1. 复制相关脚本到/usr/local/bin目录

[root@test-server-01 mha4mysql-manager-0.58]# cp -r samples/scripts /usr/local/bin/
[root@test-server-01 mha4mysql-manager-0.58]# ll /usr/local/bin/scripts/
total 32
-rwxr-xr-x 1 root root  3648 Feb 20 09:52 master_ip_failover
-rwxr-xr-x 1 root root  9870 Feb 20 09:52 master_ip_online_change
-rwxr-xr-x 1 root root 11867 Feb 20 09:52 power_manager
-rwxr-xr-x 1 root root  1360 Feb 20 09:52 send_report1.master_ip_failover #自动切换时 VIP 管理的脚本
2.master_ip_online_change #在线切换时 vip 的管理
3.power_manager #故障发生后关闭主机的脚本
4.send_report #因故障切换后发送报警的脚本

2. 复制自动切换VIP管理脚本到/usr/local/bin目录。使用该脚本管理VIP

[root@test-server-01 mha4mysql-manager-0.58]# cp -r /usr/local/bin/scripts/master_ip_failover /usr/local/bin/
[root@test-server-01 mha4mysql-manager-0.58]# cp -r /usr/local/bin/scripts/master_ip_online_change  /usr/local/bin/
#master_ip_online_change脚本没有修改不知道会不会用到，但是下面配置中指定了该路径

3. 修改脚本内容

注：只需要修改$vip $brdc $ifdev即可

[root@test-server-01 mha4mysql-manager-0.58]# cd /usr/local/bin/
#删除原有的 复制下面的直接粘贴即可。VIP根据自己的网段修改
#粘贴的时候记得 vim 编辑文件之后：Esc :set paste之后再进行粘贴
[root@test-server-01 bin]# vim master_ip_failover
#!/usr/bin/env perl
use strict;
use warnings FATAL => 'all';
use Getopt::Long;
my ($command, $ssh_user, $orig_master_host, $orig_master_ip, $orig_master_port, $new_master_host, $new_master_ip, $new_master_port);
my $vip = '192.168.40.200';
my $brdc = '192.168.40.255';
my $ifdev = 'ens33';
my $key = '1';
my $ssh_start_vip = "/sbin/ifconfig ens33:$key $vip";
my $ssh_stop_vip = "/sbin/ifconfig ens33:$key down";
my $exit_code = 0;
GetOptions('command=s' => \$command,'ssh_user=s' => \$ssh_user,'orig_master_host=s' => \$orig_master_host,'orig_master_ip=s' => \$orig_master_ip,'orig_master_port=i' => \$orig_master_port,'new_master_host=s' => \$new_master_host,'new_master_ip=s' => \$new_master_ip,'new_master_port=i' => \$new_master_port,
);
exit &main();
sub main {print "\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n";if ($command eq "stop" || $command eq "stopssh") {my $exit_code = 1;eval {print "Disabling the VIP on old master: $orig_master_host\n";&stop_vip();$exit_code = 0;};if ($@) {warn "Got Error: $@\n";exit $exit_code;}exit $exit_code;}elsif ($command eq "start") {my $exit_code = 10;eval {print "Enabling the VIP - $vip on the new master - $new_master_host\n";&start_vip();$exit_code = 0;};if ($@) {warn $@;exit $exit_code;}exit $exit_code;}elsif ($command eq "status") {print "Checking the Status of the script.. OK\n";exit 0;}else {&usage();exit 1;}
}sub start_vip() {`ssh $ssh_user\@$new_master_host "$ssh_start_vip"`;
}
sub stop_vip() {`ssh $ssh_user\@$orig_master_host "$ssh_stop_vip"`;
}
sub usage {print "Usage: master_ip_failover --command=start|stop|stopssh|status --orig_master_host=host --orig_master_ip=ip --orig_master_port=port --new_master_host=host --new_master_ip=ip --new_master_port=port\n";
}

4. 创建MHA目录并拷贝文件

master_binlog_dir=/opt/mysqldb/data 是MySQL二进制日志文件目录，该目录写错会导致MySQL MHA集群VIP飘逸失败

[root@test-server-01 bin]# mkdir /etc/masterha
[root@test-server-01 bin]# cp -r /opt/mha4mysql-manager-0.58/samples/conf/app1.cnf /etc/masterha/
[root@test-server-01 bin]# cat /etc/masterha/app1.cnf
[server default]
manager_log=/etc/masterha/manager.log
manager_workdir=/etc/masterha/mha
master_binlog_dir=/opt/mysqldb/data #指定 MySQL 主服务器的二进制日志文件目录。
master_ip_failover_script=/usr/local/bin/master_ip_failover
master_ip_online_change_script=/usr/local/bin/master_ip_online_change
user=mha_user
password=123456
port=3306
ping_interval=1
remote_workdir=/etc/masterha/mha-node
repl_user=repl_user
repl_password=123456
secondary_check_script=/usr/local/bin/masterha_secondary_check -s 192.168.40.182 -s 192.168.40.183
shutdown_script=""
ssh_user=root[server1]
hostname=192.168.40.181
port=3306[server2]
candidate_master=1
check_repl_delay=0
hostname=192.168.40.182
port=3306[server3]
hostname=192.168.40.183
port=3306#配置解释
1.[server default]：这是默认服务器配置部分，包含了 MHA 管理器的相关配置。
manager_log：指定 MHA 管理器的日志文件路径。
manager_workdir：指定 MHA 管理器的工作目录。
master_binlog_dir：指定 MySQL 主服务器的二进制日志文件目录。
master_ip_failover_script：指定 IP 切换脚本，用于在主服务器发生故障时执行 IP 切换。
master_ip_online_change_script：指定在线 IP 变更脚本。
user：MHA 用户名。
password：MHA 用户密码。
port：MySQL 端口号。
ping_interval：指定 MHA 管理器检测 MySQL 高可用的间隔时间。
remote_workdir：指定节点服务器的工作目录。
repl_user：MySQL 复制用户。
repl_password：MySQL 复制用户密码。
secondary_check_script：指定用于检查备用服务器的脚本。
shutdown_script：指定关闭脚本。
ssh_user：指定用于 SSH 连接的用户名。2.[server1]：定义了一个 MySQL 服务器节点，其中包含：
hostname：主 MySQL 服务器的 IP 地址。
port：MySQL 端口号。3.[server2]：定义了另一个 MySQL 服务器节点，标记为候选主服务器，包含：
candidate_master：标记此服务器为候选主服务器。
check_repl_delay：检查复制延迟设置为 0。
hostname：第二个 MySQL 服务器的 IP 地址。
port：MySQL 端口号。4.[server3]：定义了另一个 MySQL 服务器节点，其中包含：
hostname：第三个 MySQL 服务器的 IP 地址。
port：MySQL 端口号。

5. MySQL Master机器手动开启VIP

#没有ifconfig提前安装,全部机器安装
[root@test-server02 ~]# yum install net-tools -y
#Master执行
[root@test-server02 ~]# /sbin/ifconfig ens33:1 192.168.40.200/24
You have new mail in /var/spool/mail/root
[root@test-server02 ~]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00inet 127.0.0.1/8 scope host lovalid_lft forever preferred_lft foreverinet6 ::1/128 scope hostvalid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000link/ether 00:0c:29:2b:8f:dc brd ff:ff:ff:ff:ff:ffinet 192.168.40.181/24 brd 192.168.40.255 scope global noprefixroute ens33valid_lft forever preferred_lft foreverinet 192.168.40.200/24 brd 192.168.40.255 scope global secondary ens33:1valid_lft forever preferred_lft foreverinet6 fe80::eb22:4e3:6568:88de/64 scope link noprefixroutevalid_lft forever preferred_lft foreverinet6 fe80::38d1:dfef:9158:a02b/64 scope link tentative noprefixroute dadfailedvalid_lft forever preferred_lft forever
3: docker0: <NO-CARRIER,BROADCAST,MULTICAST,UP> mtu 1500 qdisc noqueue state DOWN group defaultlink/ether 02:42:d0:ec:ae:7f brd ff:ff:ff:ff:ff:ffinet 172.17.0.1/16 brd 172.17.255.255 scope global docker0valid_lft forever preferred_lft forever#192.168.40.200VIP 已经出现

6. MHA Manager测试

1.Manager节点测试ssh免密
[root@test-server-01 ~]# masterha_check_ssh -conf=/etc/masterha/app1.cnfThu Feb 20 10:53:10 2025 - [info] All SSH connection tests passed successfully.
#最后以上输出以上内容代表正常2.Manager节点测试MySQL主从状态
[root@test-server-01 ~]# masterha_check_repl -conf=/etc/masterha/app1.cnfMySQL Replication Health is OK.
#最后以上输出以上内容代表正常

7. 启动MHA Manager

#启动MHA
[root@test-server-01 ~]# nohup masterha_manager --conf=/etc/masterha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /etc/masterha/manager.log 2>&1 &
[1] 10828#查看MHA状态
[root@test-server-01 ~]# masterha_check_status --conf=/etc/masterha/app1.cnf
app1 (pid:10828) is running(0:PING_OK), master:192.168.40.181
#启动成功

8.MySQL MHA高可用集群测试

8.1 通过VIP连接MySQL

#通过VIP连接数据库
[root@test-server-01 ~]# mysql -h192.168.40.200 -umha_user -p123456
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 5.7.20-log MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.#Master创建数据库
MySQL [(none)]> create database test;
Query OK, 1 row affected (0.01 sec)#Slaver 1和Slaver 2查看
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
| test_db            |
+--------------------+
6 rows in set (0.01 sec)#成功

8.2模拟故障测试

1. #查看manager日志，观察manager状态

[root@test-server-01 ~]# tailf -n100 /etc/masterha/manager.log

2. 停止Master MySQL

[root@test-server02 ~]# systemctl stop mysqld

3. Slave 1节点查看vip是否飘逸

[root@test-server-03 ~]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00inet 127.0.0.1/8 scope host lovalid_lft forever preferred_lft foreverinet6 ::1/128 scope hostvalid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000link/ether 00:0c:29:b9:50:43 brd ff:ff:ff:ff:ff:ffinet 192.168.40.182/24 brd 192.168.40.255 scope global noprefixroute ens33valid_lft forever preferred_lft foreverinet 192.168.40.200/24 brd 192.168.40.255 scope global secondary ens33:1valid_lft forever preferred_lft foreverinet6 fe80::eb22:4e3:6568:88de/64 scope link tentative noprefixroute dadfailedvalid_lft forever preferred_lft foreverinet6 fe80::38d1:dfef:9158:a02b/64 scope link tentative noprefixroute dadfailedvalid_lft forever preferred_lft foreverinet6 fe80::1c4c:acc5:6453:90f3/64 scope link tentative noprefixroute dadfailedvalid_lft forever preferred_lft forever
3: docker0: <NO-CARRIER,BROADCAST,MULTICAST,UP> mtu 1500 qdisc noqueue state DOWN group defaultlink/ether 02:42:56:61:72:fd brd ff:ff:ff:ff:ff:ffinet 172.17.0.1/16 brd 172.17.255.255 scope global docker0valid_lft forever preferred_lft forever#此时发现vip 200已经飘逸到了182节点 这个是slave 1的节点

4. manager日志

#manager日志中最下面出现下方日志就证明已经切换成功了
----- Failover Report -----
app1: MySQL Master failover 192.168.40.181(192.168.40.181:3306) to 192.168.40.182(192.168.40.182:3306) succeeded
Master 192.168.40.181(192.168.40.181:3306) is down!
Check MHA Manager logs at test-server-01:/etc/masterha/manager.log for details.
Started automated(non-interactive) failover.
Invalidated master IP address on 192.168.40.181(192.168.40.181:3306)
Selected 192.168.40.182(192.168.40.182:3306) as a new master.
192.168.40.182(192.168.40.182:3306): OK: Applying all logs succeeded.
192.168.40.182(192.168.40.182:3306): OK: Activated master IP address.
192.168.40.183(192.168.40.183:3306): OK: Slave started, replicating from 192.168.40.182(192.168.40.182:3306)
192.168.40.182(192.168.40.182:3306): Resetting slave info succeeded.
Master failover to 192.168.40.182(192.168.40.182:3306) completed successfully.

5. 查看MySQL状态

登录MySQL Slave2查看从状态

[root@test-server-04 ~]# mysql -uroot -p123456
mysql> show slave status \G;
*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.40.182Master_User: repl_userMaster_Port: 3306Connect_Retry: 60Master_Log_File: binlog.000002Read_Master_Log_Pos: 1700Relay_Log_File: test-server-04-relay-bin.000003Relay_Log_Pos: 744Relay_Master_Log_File: binlog.000002Slave_IO_Running: YesSlave_SQL_Running: Yes#Master_Host已经成功由192.168.40.181切换成了192.168.40.181。到此MHA集群已经成功搭建了

6. 故障修复

恢复MySQL

[root@test-server-02 ~]# systemctl start mysqld

主数据库查看master状态

mysql> show master status;
+---------------+----------+--------------+------------------+------------------------------------------------------------------------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                                                                  |
+---------------+----------+--------------+------------------+------------------------------------------------------------------------------------+
| binlog.000002 |     1700 |              |                  | 7a69b6e0-eff7-11ef-b307-000c292b8fdc:3-7,
7a7fcdf5-eff7-11ef-ab74-000c29b95043:1-2 |
+---------------+----------+--------------+------------------+------------------------------------------------------------------------------------+
1 row in set (0.00 sec)#正常写入写入数据的情况下Position会一直变，可以和研发沟通空闲时间进行锁库进行配置
1. 锁库命令：FLUSH TABLE WITH READ LOCK;
2. 解锁命令： unlock tables;
#配置完解开即可#原master做成Slave数据库
mysql> change master to master_host='192.168.40.182',master_user='repl_user',master_password='123456',master_log_file='binlog.000002',master_log_pos=1700;
Query OK, 0 rows affected, 2 warnings (0.03 sec)mysql> start slave;
Query OK, 0 rows affected (0.01 sec)mysql> show slave status \G;
*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.40.182Master_User: repl_userMaster_Port: 3306Connect_Retry: 60Master_Log_File: binlog.000002Read_Master_Log_Pos: 1700Relay_Log_File: test-server-02-relay-bin.000002Relay_Log_Pos: 317Relay_Master_Log_File: binlog.000002Slave_IO_Running: YesSlave_SQL_Running: Yes#已经成功变成了Slave,并且成功连接到了新的Master.

7. 修改MHA配置

由于停掉了MySQL， /etc/masterha/app1.cnf配置里面默认删除一开始定义的[server1]，现在新增一下

[root@test-server-01 masterha]# cat /etc/masterha/app1.cnf
[server default]
manager_log=/etc/masterha/manager.log
manager_workdir=/etc/masterha/mha
master_binlog_dir=/opt/mysqldb/data
master_ip_failover_script=/usr/local/bin/master_ip_failover
master_ip_online_change_script=/usr/local/bin/master_ip_online_change
user=mha_user
password=123456
port=3306
ping_interval=1
remote_workdir=/etc/masterha/mha-node
repl_user=repl_user
repl_password=123456
secondary_check_script=/usr/local/bin/masterha_secondary_check -s 192.168.40.182 -s 192.168.40.183
shutdown_script=""
ssh_user=root[server1]
hostname=192.168.40.181
port=3306[server2]
candidate_master=1
check_repl_delay=0
hostname=192.168.40.182
port=3306[server3]
hostname=192.168.40.183
port=3306

8. MHA测试

1.Manager节点测试ssh免密
[root@test-server-01 ~]# masterha_check_ssh -conf=/etc/masterha/app1.cnfThu Feb 20 10:53:10 2025 - [info] All SSH connection tests passed successfully.
#最后以上输出以上内容代表正常2.Manager节点测试MySQL主从状态
[root@test-server-01 ~]# masterha_check_repl -conf=/etc/masterha/app1.cnfMySQL Replication Health is OK.
#最后以上输出以上内容代表正常

9. 启动manager

#启动MHA
[root@test-server-01 ~]# nohup masterha_manager --conf=/etc/masterha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /etc/masterha/manager.log 2>&1 &

到这部署就成功了。部署两边就全都懂了

9.参考文档

1.MySQL集群高可用架构之MHA

2.Mysql数据库——MHA高可用配置及故障切换

3.MySQL高可用MHA