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