自動failover

MHA：

MHA 服務

項目：搭建Mysql主從復制、MHA高可用架構

實驗項目IP地址配置：

MHA下載地址

項目步驟：?

一、修改主機名

二、編寫一鍵安裝mha node腳本和一鍵安裝mha mangaer腳本，并執行安裝

三、搭建Mysql主從復制集群（注意所有的Mysql機器都需要打開二進制日志，可以實現自動故障切換）

四、將安裝包?mha4mysql-node和腳本一鍵安裝mha node腳本傳輸給Mysql主從復制集群，并運行腳本安裝（下載依賴的時候最好翻墻下載）

五、所有服務器互相建立免密通道

5.1、mha manager對所有mysql服務器建立免密通道

5.2、master對slave1、slave2建立免密通道

5.3、slave1對master、slave2建立免密通道

?5.4、slave2對master、slave1建立免密通道

六、在Mysql的主從復制服務器里，配置mha相關信息

6.1、所有mysql服務器（master、slave1、slave2）將mysql命令和mysqlbinlog二進制文件操作命令軟鏈接到/usr/sbin，方便manager管理節點，因為/usr/sbin/ 目錄下可以被直接調用。

6.2、所有mysql服務器新建允許manager訪問的授權用戶mha，密碼123456

七、在mha manager節點上配置好相關腳本、管理節點服務器

7.1、mha manager節點上復制相關腳本到/usr/local/bin下

7.2、復制自動切換時vip管理的腳本到/usr/local/bin下

7.3、修改master_ip_failover文件內容，配置vip（只配置vip(192.168.2.227)相關參數，其他默認不修改）

7.4、創建 MHA 軟件目錄并復制配置文件，使用app1.cnf配置文件來管理 mysql 節點服務器，配置文件一般放在/etc/目錄下

7.5、master服務器上手工開啟vip

7.6、測試：manager節點上測試ssh免密通道，如果正常最后會輸出successfully

7.7、manager節點后臺開啟MHA

八、故障轉移效果測試，模擬matser宕機，指定slave1成為新的master

8.1、模擬master宕機，停掉master

8.2、查看自動故障檢測的效果

8.3、查看/etc/masterha/app1.cnf文件是否發生改變

8.4、再來看看slave2的master_info信息（確定master服務轉移到了salve1上）

九、原master故障修復（原master轉為slave，指向slave1）

9.1、原master開啟mysqld

9.2、修復主從，原master修改master_info指向新的master（原slave1）

9.3、在 manager 節點上修改配置文件/etc/masterha/app1.cnf（再把這個記錄添加進去，因為master宕機后原來的server1會被自動刪除）

9.4、重啟mha manager，并檢查此時的master

自動failover

自動故障切換（Automatic Failover）是一種系統設計和配置策略，旨在在出現故障時自動將服務從一個失敗的節點轉移到另一個健康的節點，以保持系統的可用性。自動故障切換通常用于分布式系統、數據庫集群、高可用性架構等場景，以減少系統停機時間并確保業務連續性。

MHA：

MHA（Master HA）是一款開源的 MySQL 的高可用程序，它為 MySQL主從復制架構提供了 automating master failover （自動化主故障轉移）功能。MHA 在監控到 master 節點故障時，會提升其中擁有最新數據的 slave 節點成為新的master 節點，在此期間，MHA 會通過于其它從節點獲取額外信息來避免一致性方面的問題。MHA 還提供了 master 節點的在線切換功能，即按需切換 master/slave 節點。　

參考：MYSQL高可用架構之MHA實戰一數據庫主從配置（真實可用）_51CTO博客_mysql數據庫主從搭建

基于mycat2+mha+keepalived的半同步主從復制MySQL cluster_mb643815027e44d的技術博客_51CTO博客

MHA 服務

MHA 服務有兩種角色， MHA Manager(管理節點)和 MHA Node(數據節點)：

MHA Manager： 通常單獨部署在一臺獨立機器上管理多個 master/slave 集群(組)，每個 master/slave 集群稱作一個 application，用來管理統籌整個集群。

MHA node： 運行在每臺 MySQL 服務器上(master/slave/manager)，它通過監控具備解析和清理

logs 功能的腳本來加快故障轉移：?主要是接收管理節點所發出指令的代理，代理需要運行在每一個 mysql 節點上。簡單講 node 就是用來收集從節點服務器上所生成的 bin-log 。對比打算提升為新的主節點之上的從節點的是否擁有并完成操作，如果沒有發給新主節點在本地應用后提升為主節點。

在MHA自動故障切換過程中，MHA試圖從宕機的主服務器上保存二進制日志，最大程度的保證數據的不丟失，但這并不總是可行的。例如，如果主服務器硬件故障或無法通過ssh訪問，MHA沒法保存二進制日志，只進行故障轉移而丟失了最新的數據。使用MySQL 5.7的半同步復制，可以大大降低數據丟失的風險。MHA可以與半同步復制結合起來。如果只有一個slave已經收到了最新的二進制日志，MHA可以將最新的二進制日志應用于其他所有的 slave服務器上，因此可以保證所有節點的數據一致性。

由上圖我們可以看出，每個復制組內部和 Manager 之間都需要ssh實現無密碼互連，只有這樣，在 Master 出故障時， Manager 才能順利的連接進去，實現主從切換功能。?

項目：搭建Mysql主從復制、MHA高可用架構

實驗項目IP地址配置：

mha_manager:

manager：192.168.2.141? ? #用于監控管理

vip：192.168.2.227

mha_node:

master：192.168.2.150? ? #開啟 bin-log relay-log

slave-1：192.168.2.151? ?#開啟 bin-log relay-log

slave-2：192.168.2.152? ?#開啟 bin-log relay-log

MHA下載地址

mha4mysql-manager-0.58.tar.gz：

wget https://github.com/yoshinorim/mha4mysql-manager/releases/download/v0.58/mha4mysql-manager-0.58.tar.gz

mha4mysql-node-0.58.tar.gz：

wget https://github.com/yoshinorim/mha4mysql-node/releases/download/v0.58/mha4mysql-node-0.58.tar.gz

mha4mysql安裝包集合

鏈接：https://pan.baidu.com/s/1cyM1syv8NjwOW8ExR0E21Q?pwd=z52d?
提取碼：z52d

[root@mha_manager ~]#  ls
anaconda-ks.cfg  mha4mysql-manager-0.58.tar.gz  mha4mysql-node-0.58.tar.gz
[root@mha_manager ~]#

項目步驟：?

一、修改主機名

[root@web-3 ~]# hostnamectl set-hostname mha_manager
[root@web-3 ~]# su -
上一次登錄：五 8月 11 13:28:25 CST 2023從 192.168.2.7pts/0 上
[root@mha_manager ~]#

二、編寫一鍵安裝mha node腳本和一鍵安裝mha mangaer腳本，并執行安裝

一鍵安裝mha node腳本

[root@mha_manager ~]# cat onekey_install_mha_node.sh 
#查看可以安裝或者已安裝的rpm包，并且作緩存
yum list#下載epel源
yum install epel-release --nogpgcheck -y#下載依賴包
yum install -y perl-DBD-MySQL \
perl-Config-Tiny \
perl-Log-Dispatch \
perl-Parallel-ForkManager \
perl-ExtUtils-CBuilder \
perl-ExtUtils-MakeMaker \
perl-CPAN#軟件包mha4mysql-node-0.58.tar.gz放入/root目錄下
cd ~
tar zxvf mha4mysql-node-0.58.tar.gz
cd mha4mysql-node-0.58#編譯安裝
perl Makefile.PL
make && make install

一鍵安裝mha mangaer腳本

[root@mha_manager ~]# cat onekey_install_mha_manager.sh 
#查看可以安裝或者已安裝的rpm包，并且作緩存
yum list#下載epel源
yum install epel-release --nogpgcheck -y#下載依賴包
yum install -y perl-DBD-MySQL \
perl-Config-Tiny \
perl-Log-Dispatch \
perl-Parallel-ForkManager \
perl-ExtUtils-CBuilder \
perl-ExtUtils-MakeMaker \
perl-CPAN#軟件包mha4mysql-manager-0.58.tar.gz放入/root目錄下
cd ~
tar zxvf mha4mysql-manager-0.58.tar.gz
cd mha4mysql-manager-0.58#編譯安裝
perl Makefile.PL
make && make install

三、搭建Mysql主從復制集群（注意所有的Mysql機器都需要打開二進制日志，可以實現自動故障切換）

參考：Mysql - 配置Mysql主從復制-keepalived高可用-讀寫分離集群_Claylpf的博客-CSDN博客

四、將安裝包?mha4mysql-node和腳本一鍵安裝mha node腳本傳輸給Mysql主從復制集群，并運行腳本安裝（下載依賴的時候最好翻墻下載）

[root@mha_manager ~]# scp onekey_install_mha_node.sh root@192.168.2.150:~
The authenticity of host '192.168.2.150 (192.168.2.150)' can't be established.
ECDSA key fingerprint is SHA256:rUDllK9IdVfMva40nDGHGyHLkpuXrHJyRHRPuLbkkv8.
ECDSA key fingerprint is MD5:6d:46:aa:d1:48:87:92:8b:14:ca:d2:18:af:3b:89:51.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.2.150' (ECDSA) to the list of known hosts.
root@192.168.2.150's password: 
onekey_install_mha_node.sh                                                                                                                                 100%  481   745.0KB/s   00:00    
[root@mha_manager ~]# scp onekey_install_mha_node.sh root@192.168.2.151:~
The authenticity of host '192.168.2.151 (192.168.2.151)' can't be established.
ECDSA key fingerprint is SHA256:3SsW//YjcK0UTRAlQkOUcqMcFMaQEhZ1xRSUgHRs/JQ.
ECDSA key fingerprint is MD5:58:8e:3f:27:fb:f5:4e:83:56:70:e6:fd:f7:d0:9d:17.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.2.151' (ECDSA) to the list of known hosts.
root@192.168.2.151's password: 
onekey_install_mha_node.sh                                                                                                                                 100%  481   287.5KB/s   00:00    
[root@mha_manager ~]# scp onekey_install_mha_node.sh root@192.168.2.152:~
The authenticity of host '192.168.2.152 (192.168.2.152)' can't be established.
ECDSA key fingerprint is SHA256:t7FSFcUpEOJYIGkZo1HvvfqhsezGEz7WEScc4KTgQDU.
ECDSA key fingerprint is MD5:7c:68:1c:c3:aa:a5:34:b7:f7:4b:18:0b:93:fb:a6:76.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.2.152' (ECDSA) to the list of known hosts.
root@192.168.2.152's password: 
onekey_install_mha_node.sh                                                                                                                                 100%  481   397.8KB/s   00:00    
[root@mha_manager ~]#

[root@mysql-3 ~]# bash onekey_install_mha_node.sh

注：下載依賴包perl的時候如果無法成功下載，可以嘗試轉換源為：The Comprehensive Perl Archive Network - www.cpan.org?，并翻墻下載

五、所有服務器互相建立免密通道

參考：Linux - SSH服務 - SCP - 免密通道建立_linux ssh服務狀態_Claylpf的博客-CSDN博客

5.1、mha manager對所有mysql服務器建立免密通道

[root@mha_manager .ssh]# ssh-keygen
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa): 
/root/.ssh/id_rsa already exists.
Overwrite (y/n)? y
Enter passphrase (empty for no passphrase): 
Enter same passphrase again: 
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
SHA256:36631NGvhLwX3HXPFgkfo8t/C0g+k59hqkGi1cn0/cA root@mha_manager
The key's randomart image is:
+---[RSA 2048]----+
|                 |
|             . o |
|         .    + +|
|        + o o. =o|
|       oS= o.Eoo*|
|      o o.ooo==.*|
|     .   ..*=+++.|
|          .oBo=o.|
|         .o++=..o|
+----[SHA256]-----+
[root@mha_manager .ssh]# ssh-copy-id root@192.168.2.150
/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/root/.ssh/id_rsa.pub"
/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
root@192.168.2.150's password: Number of key(s) added: 1Now try logging into the machine, with:   "ssh 'root@192.168.2.150'"
and check to make sure that only the key(s) you wanted were added.[root@mha_manager .ssh]# [root@mha_manager .ssh]# ssh-copy-id root@192.168.2.151[root@mha_manager .ssh]# ssh-copy-id root@192.168.2.152

5.2、master對slave1、slave2建立免密通道

[root@mysql-1 ~]# ssh-keygen
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa): 
Enter passphrase (empty for no passphrase): 
Enter same passphrase again: 
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
SHA256:rB6Rg0nbJCHYxWxuBafl4HDB8+1RuuOpHC9/5LYRTAI root@mysql-1
The key's randomart image is:
+---[RSA 2048]----+
| oo=BoE          |
|. .=*B..  .      |
|   o=+o..o.      |
|   .oB.o++       |
|   .+ =.Soo      |
|       ++ ..     |
|      +. =.      |
|     o.+o +.     |
|      ++oo..     |
+----[SHA256]-----+
[root@mysql-1 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub 192.168.2.151
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/root/.ssh/id_rsa.pub"
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
root@192.168.2.151's password: Number of key(s) added: 1Now try logging into the machine, with:   "ssh '192.168.2.151'"
and check to make sure that only the key(s) you wanted were added.[root@mysql-1 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub 192.168.2.152
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/root/.ssh/id_rsa.pub"
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
root@192.168.2.152's password: Number of key(s) added: 1Now try logging into the machine, with:   "ssh '192.168.2.152'"
and check to make sure that only the key(s) you wanted were added.[root@mysql-1 ~]#

5.3、slave1對master、slave2建立免密通道

[root@mysql-2 ~]# ssh-keygen
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa): 
Enter passphrase (empty for no passphrase): 
Enter same passphrase again: 
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
SHA256:MMCE8STghhwmha65CVG/w3/9k8/T96sFfcr75CFMTGs root@mysql-2
The key's randomart image is:
+---[RSA 2048]----+
|o*+=+            |
|B ++..           |
|o= .. o      .   |
|o.  .  o    o o  |
|.o . .  S    E ..|
|+   +       +....|
|.o   o   .   +o+.|
|o     . . . o.+++|
|       .   ..+=+B|
+----[SHA256]-----+
[root@mysql-2 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub 192.168.2.150
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/root/.ssh/id_rsa.pub"
The authenticity of host '192.168.2.150 (192.168.2.150)' can't be established.
ECDSA key fingerprint is SHA256:rUDllK9IdVfMva40nDGHGyHLkpuXrHJyRHRPuLbkkv8.
ECDSA key fingerprint is MD5:6d:46:aa:d1:48:87:92:8b:14:ca:d2:18:af:3b:89:51.
Are you sure you want to continue connecting (yes/no)? yes
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
root@192.168.2.150's password: Number of key(s) added: 1Now try logging into the machine, with:   "ssh '192.168.2.150'"
and check to make sure that only the key(s) you wanted were added.[root@mysql-2 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub 192.168.2.152
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/root/.ssh/id_rsa.pub"
The authenticity of host '192.168.2.152 (192.168.2.152)' can't be established.
ECDSA key fingerprint is SHA256:t7FSFcUpEOJYIGkZo1HvvfqhsezGEz7WEScc4KTgQDU.
ECDSA key fingerprint is MD5:7c:68:1c:c3:aa:a5:34:b7:f7:4b:18:0b:93:fb:a6:76.
Are you sure you want to continue connecting (yes/no)? yes
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
root@192.168.2.152's password: 
Permission denied, please try again.
root@192.168.2.152's password: Number of key(s) added: 1Now try logging into the machine, with:   "ssh '192.168.2.152'"
and check to make sure that only the key(s) you wanted were added.[root@mysql-2 ~]#

?5.4、slave2對master、slave1建立免密通道

[root@mysql-3 ~]# ssh-keygen
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa): 
Enter passphrase (empty for no passphrase): 
Enter same passphrase again: 
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
SHA256:m6F9WyLFkNnweKy2ERj3LflPDHqU5ZUL+S8FpCbXhtw root@mysql-3
The key's randomart image is:
+---[RSA 2048]----+
|      . o    .+ o|
|       + X + @.o |
|      . * X @ E..|
|         * O + o.|
|        S + o o..|
|       + B . o. .|
|      . * o . .. |
|         o +     |
|          .      |
+----[SHA256]-----+
[root@mysql-3 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub 192.168.2.150
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/root/.ssh/id_rsa.pub"
The authenticity of host '192.168.2.150 (192.168.2.150)' can't be established.
ECDSA key fingerprint is SHA256:rUDllK9IdVfMva40nDGHGyHLkpuXrHJyRHRPuLbkkv8.
ECDSA key fingerprint is MD5:6d:46:aa:d1:48:87:92:8b:14:ca:d2:18:af:3b:89:51.
Are you sure you want to continue connecting (yes/no)? yes
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
root@192.168.2.150's password: Number of key(s) added: 1Now try logging into the machine, with:   "ssh '192.168.2.150'"
and check to make sure that only the key(s) you wanted were added.[root@mysql-3 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub 192.168.2.151
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/root/.ssh/id_rsa.pub"
The authenticity of host '192.168.2.151 (192.168.2.151)' can't be established.
ECDSA key fingerprint is SHA256:3SsW//YjcK0UTRAlQkOUcqMcFMaQEhZ1xRSUgHRs/JQ.
ECDSA key fingerprint is MD5:58:8e:3f:27:fb:f5:4e:83:56:70:e6:fd:f7:d0:9d:17.
Are you sure you want to continue connecting (yes/no)? yes
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
root@192.168.2.151's password: Number of key(s) added: 1Now try logging into the machine, with:   "ssh '192.168.2.151'"
and check to make sure that only the key(s) you wanted were added.[root@mysql-3 ~]#

六、在Mysql的主從復制服務器里，配置mha相關信息

6.1、所有mysql服務器（master、slave1、slave2）將mysql命令和mysqlbinlog二進制文件操作命令軟鏈接到/usr/sbin，方便manager管理節點，因為`/usr/sbin/` 目錄下可以被直接調用。

[root@mysql-1 ~]# ln -s /usr/local/mysql/bin/mysql /usr/sbin/
[root@mysql-1 ~]# ln -s /usr/local/mysql/bin/mysqlbinlog /usr/sbin/[root@mysql-2 ~]# ln -s /usr/local/mysql/bin/mysql /usr/sbin/
[root@mysql-2 ~]# ln -s /usr/local/mysql/bin/mysqlbinlog /usr/sbin/[root@mysql-3 ~]# ln -s /usr/local/mysql/bin/mysql /usr/sbin/
[root@mysql-3 ~]# ln -s /usr/local/mysql/bin/mysqlbinlog /usr/sbin/

6.2、所有mysql服務器新建允許manager訪問的授權用戶mha，密碼123456

root@(none) 17:21  mysql>grant all on *.* to 'mha'@'192.168.2.%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.01 sec)root@(none) 17:21  mysql>grant all on *.* to 'mha'@'192.168.2.150' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)root@(none) 17:22  mysql>grant all on *.* to 'mha'@'192.168.2.151' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)root@(none) 17:22  mysql>grant all on *.* to 'mha'@'192.168.2.152' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)root@(none) 17:22  mysql>select user,host from mysql.user;
+---------------+---------------+
| user          | host          |
+---------------+---------------+
| claylpf       | %             |
| sc_slave      | %             |
| mha           | 192.168.2.%   |
| mha           | 192.168.2.150 |
| mha           | 192.168.2.151 |
| mha           | 192.168.2.152 |
| mysql.session | localhost     |
| mysql.sys     | localhost     |
| root          | localhost     |
+---------------+---------------+
9 rows in set (0.00 sec)root@(none) 17:22  mysql>

七、在mha manager節點上配置好相關腳本、管理節點服務器

7.1、mha manager節點上復制相關腳本到/usr/local/bin下

[root@localhost ~]# cp -rp /root/mha4mysql-manager-0.58/samples/scripts/ /usr/local/bin/
[root@localhost ~]# cd /usr/local/bin/
[root@localhost bin]# ls
masterha_check_repl  masterha_check_status  masterha_manager         masterha_master_switch    masterha_stop
masterha_check_ssh   masterha_conf_host     masterha_master_monitor  masterha_secondary_check  scripts
[root@localhost bin]# 
[root@localhost bin]# cd scripts/
[root@localhost scripts]# ls
master_ip_failover  master_ip_online_change  power_manager  send_report
[root@localhost scripts]#

7.2、復制自動切換時vip管理的腳本到/usr/local/bin下

[root@localhost scripts]# cp /usr/local/bin/scripts/master_ip_failover /usr/local/bin/
[root@localhost scripts]# ls
master_ip_failover  master_ip_online_change  power_manager  send_report
[root@localhost scripts]#

7.3、修改master_ip_failover文件內容，配置vip（只配置vip(192.168.2.227)相關參數，其他默認不修改）

[root@localhost scripts]# > master_ip_failover  #清空文件內容，復制以下內容
[root@localhost scripts]# vim master_ip_failover 
[root@localhost scripts]# cat 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.2.227';								#指定vip的地址，自己指定
my $brdc = '192.168.2.255';								#指定vip的廣播地址
my $ifdev = 'ens33';										#指定vip綁定的網卡
my $key = '1';												#指定vip綁定的虛擬網卡序列號
my $ssh_start_vip = "/sbin/ifconfig ens33:$key $vip";		#代表此變量值為ifconfig ens33:1 192.168.2.227
my $ssh_stop_vip = "/sbin/ifconfig ens33:$key down";		#代表此變量值為ifconfig ens33:1 192.168.2.227 down
my $exit_code = 0;											#指定退出狀態碼為0
#my $ssh_start_vip = "/usr/sbin/ip addr add $vip/24 brd $brdc dev $ifdev label $ifdev:$key;/usr/sbin/arping -q -A -c 1 -I $ifdev $vip;iptables -F;";
#my $ssh_stop_vip = "/usr/sbin/ip addr del $vip/24 dev $ifdev label $ifdev:$key";
##################################################################################
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 \"`;
}
## A simple system call that disable the VIP on the old_master
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";
}[root@localhost scripts]#

7.4、創建 MHA 軟件目錄并復制配置文件，使用app1.cnf配置文件來管理 mysql 節點服務器，配置文件一般放在/etc/目錄下

注意：注釋只是提示用，編輯配置文件時最好不要加注釋，否則很可能會出錯

[root@mha_manager scripts]# mkdir /etc/masterha
[root@mha_manager scripts]# cp /root/mha4mysql-manager-0.58/samples/conf/app1.cnf /etc/masterha/
[root@mha_manager scripts]# cd /etc/masterha/
[root@mha_manager masterha]# ls
app1.cnf
[root@mha_manager masterha]# >app1.cnf #清空原有內容
[root@mha_manager masterha]# vim app1.cnf [server default]
manager_log=/var/log/masterha/app1/manager.log　　　　　  #manager日志
manager_workdir=/var/log/masterha/app1.log　　　　		#manager工作目錄
master_binlog_dir=/data/mysql/　　　　　　　　 　#master保存binlog的位置，這里的路徑要與master里配置的binlog的路徑一致，以便MHA能找到
master_ip_failover_script=/usr/local/bin/master_ip_failover　          　#設置自動failover時候的切換腳本，也就是上面的那個腳本
master_ip_online_change_script=/usr/local/bin/master_ip_online_change　　#設置手動切換時候的切換腳本
user=mha					#設置監控用戶mha
password=123456			#設置mysql中mha用戶的密碼，這個密碼是前文中創建監控用戶的那個密碼
ping_interval=1				#設置監控主庫，發送ping包的時間間隔1秒，默認是3秒，嘗試三次沒有回應的時候自動進行failover
remote_workdir=/tmp			#設置遠端mysql在發生切換時binlog的保存位置
repl_user=slave			#設置復制用戶的用戶slave
repl_password=123456		#設置復制用戶slave的密碼
report_script=/usr/local/send_report　　　　　#設置發生切換后發送的報警的腳本
secondary_check_script=/usr/local/bin/masterha_secondary_check -s 192.168.2.151 -s 192.168.2.152	#指定檢查的從服務器IP地址
shutdown_script=""			#設置故障發生后關閉故障主機腳本（該腳本的主要作用是關閉主機防止發生腦裂,這里沒有使用）
ssh_user=root				#設置ssh的登錄用戶名[server1]
#master
hostname=192.168.2.150 
port=3306[server2]
#slave1
hostname=192.168.2.151
port=3306
candidate_master=1
#設置為候選master，設置該參數以后，發生主從切換以后將會將此從庫提升為主庫，即使這個主庫不是集群中最新的slavecheck_repl_delay=0
#默認情況下如果一個slave落后master 超過100M的relay logs的話，MHA將不會選擇該slave作為一個新的master， 因為對于這個slave的恢復需要花費很長時間；通過設置check_repl_delay=0，MHA觸發切換在選擇一個新的master的時候將會忽略復制延時，這個參數對于設置了candidate_master=1的主機非常有用，因為這個候選主在切換的過程中一定是新的master[server3]
#slave2
hostname=192.168.2.152
port=3306

7.5、master服務器上手工開啟vip

[root@mysql-1 ~]# ifconfig ens33:1 192.168.2.227/24
[root@mysql-1 ~]# ip add
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 host valid_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:61:50:77 brd ff:ff:ff:ff:ff:ffinet 192.168.2.150/24 brd 192.168.2.255 scope global noprefixroute ens33valid_lft forever preferred_lft foreverinet 192.168.2.227/24 brd 192.168.2.255 scope global secondary ens33:1valid_lft forever preferred_lft foreverinet6 fe80::20c:29ff:fe61:5077/64 scope link valid_lft forever preferred_lft forever
[root@mysql-1 ~]#

7.6、測試：manager節點上測試ssh免密通道，如果正常最后會輸出successfully

[root@mha_manager masterha]# masterha_check_ssh -conf=/etc/masterha/app1.cnf
Mon May  8 11:50:00 2023 - [info] All SSH connection tests passed successfully.

基于mycat2+mha+keepalived的半同步主從復制MySQL cluster_MySQL_09

如果報錯，多看日志/var/log/masterha/app1/manager.log

注意是否每臺mysql間都建立了ssh免密通道，否則會報錯

如果報錯，思考是否軟鏈接建立好了？或者主從復制搭建正確了

在 manager 節點上測試 mysql 主從連接情況，最后出現 MySQL Replication Health is OK 字樣說明正常。

[root@mha_manager masterha]# masterha_check_repl -conf=/etc/masterha/app1.cnf
MySQL Replication Health is OK.

7.7、manager節點后臺開啟MHA

[root@mha_manager masterha]# nohup masterha_manager --conf=/etc/masterha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/masterha/app1/manager.log 2>&1 &
[1] 5085

查看 MHA 狀態，可以看到當前的 master 是 Mysql1 節點。

[root@mha_manager masterha]# masterha_check_status --conf=/etc/masterha/app1.cnf
app1 (pid:5085) is running(0:PING_OK), master:192.168.2.150

查看MHA日志，看到當前matser是192.168.2.150

[root@mha_manager masterha]# cat /var/log/masterha/app1/manager.log | grep "current master"
Mon May  14 11:57:07 2023 - [info] Checking SSH publickey authentication settings on the current master..
192.168.2.150(192.168.2.150:3306) (current master)

查看mha進程

[root@mha_manager bin]# ps aux|grep manager
root       5085  0.1  4.5 299656 21992 pts/0    S    11:57   0:12 perl /usr/local/bin/masterha_manager --conf=/etc/masterha/app1.cnf --remove_dead_master_conf --ignore_last_failover
root      14939  0.0  0.2 112824   984 pts/0    S+   14:39   0:00 grep --color=auto manager

八、故障轉移效果測試，模擬matser宕機，指定slave1成為新的master

manager節點監控日志記錄（實時監控）

[root@mha_manager bin]# tail -f /var/log/masterha/app1/manager.log

8.1、模擬master宕機，停掉master

[root@mysql-1 mysql]# service mysqld stop

8.2、查看自動故障檢測的效果

查看vip是否漂移到了slave1

基于mycat2+mha+keepalived的半同步主從復制MySQL cluster_MySQL_11

查看日志信息

基于mycat2+mha+keepalived的半同步主從復制MySQL cluster_MySQL_12

日志顯示，master已經切換到了192.168.2.151（slave1）

slave2也已經選擇slave1作為master

8.3、查看/etc/masterha/app1.cnf文件是否發生改變

基于mycat2+mha+keepalived的半同步主從復制MySQL cluster_mysql_13

發現原來的server1配置被刪除了

8.4、再來看看slave2的master_info信息（確定master服務轉移到了salve1上）

基于mycat2+mha+keepalived的半同步主從復制MySQL cluster_MySQL_14

九、原master故障修復（原master轉為slave，指向slave1）

9.1、原master開啟mysqld

[root@mysql-1 ~]# service mysqld start

9.2、修復主從，原master修改master_info指向新的master（原slave1）

在mysql-2（slave1）上進行操作

root@(none) 17:18  mysql>change master to master_host='192.168.2.151',master_user='slave',master_password='123456',master_port=3306,master_auto_position=1;
root@(none) 17:19  mysql>start slave;
root@(none) 17:19  mysql>show slave status\G;

9.3、在 manager 節點上修改配置文件/etc/masterha/app1.cnf（再把這個記錄添加進去，因為master宕機后原來的server1會被自動刪除）

[server default]
manager_log=/var/log/masterha/app1/manager.log
manager_workdir=/var/log/masterha/app1
master_binlog_dir=/data/mysql/
master_ip_failover_script=/usr/local/bin/master_ip_failover
master_ip_online_change_script=/usr/local/bin/master_ip_online_change
password=123456
ping_interval=1
remote_workdir=/tmp
repl_password=123456
repl_user=slave
secondary_check_script=/usr/local/bin/masterha_secondary_check -s 192.168.2.151 -s 192.168.2.152
shutdown_script=""
ssh_user=root
user=mha[server1]
hostname=192.168.2.151   #原slave1的IP地址
port=3306[server2]
candidate_master=1
check_repl_delay=0
hostname=192.168.2.150   #原master的IP地址
port=3306[server3]
hostname=192.168.2.152   #原slave2的IP地址
port=3306

基于mycat2+mha+keepalived的半同步主從復制MySQL cluster_mysql_16

9.4、重啟mha manager，并檢查此時的master

[root@mha_manager ~]# masterha_stop --conf=/etc/masterha/app1.cnf
Stopped app1 successfully.
[1]+  退出 1                nohup masterha_manager --conf=/etc/masterha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/masterha/app1/manager.log 2>&1
[root@mha_manager ~]# nohup masterha_manager --conf=/etc/masterha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/masterha/app1/manager.log 2>&1 & 
[1] 20022
[root@mha_manager ~]# masterha_check_status --conf=/etc/masterha/app1.cnf
app1 (pid:20022) is running(0:PING_OK), master:192.168.2.151