MHA高可用配置及故障切換

MHA高可用配置及故障切換

一、案例概述
二、案例前置知識點

1. MHA概述
2. MHA的組成

（1）MHA Manager（管理節點）
（2）MHA Node（資料節點）

3. MHA特點
4. MHA切換過程

三、案例環境

1. 實驗思路
2. 伺服器、主機名、系統、IP以及軟體版本配置
3. 各伺服器環境配置

四、案例實施

1. 搭建MySQL MHA

（1）Master、Slave1、Slave2節點上安裝mysql5.7
（2）修改各伺服器節點的主機名
（3）修改Master、Slave1、Slave2節點的MySQL主配置檔案
（4）在Master、Slave1、Slave2節點上都建立兩個軟連結
（5）配置mysql一主兩從

①所有資料庫節點進行mysql授權
②在Master節點檢視二進制檔案和同步點
③在Slave1、Slave2節點執行同步操作
④在Slave1、Slave2節點檢視資料同步結果
⑤兩個從庫必須設定為隻讀模式
⑥插入資料測試資料庫同步

（6）安裝MHA軟體

①所有伺服器上都安裝MHA依賴的環境，首先安裝epel源
②安裝MHA軟體包，必須在所有伺服器上先安裝node元件
③在MHA manager節點上安裝manager元件
④node元件工具
⑤manager元件工具

（7）在所有伺服器上配置無密碼認證

①在manager節點上配置到所有資料庫節點的無密碼認證
②在mysql1上配置到資料庫節點mysql2和mysql3的無密碼認證
③在mysql2上配置到資料庫節點mysql1和mysql3的無密碼認證
④在mysql3上配置到資料庫節點mysql1和mysql2的無密碼認證

（8）在manager節點上配置MHA

①在manager節點上複制相關腳本到/usr/local/bin目錄
②複制上述的自動切換時VIP的管理腳本到/usr/local/bin目錄
③修改内容如下（修改VIP相關參數）：
④建立MHA軟體目錄并拷貝配置檔案

（9）第一次配置需要在master節點上手動開啟虛拟IP
（10）在manager節點上測試ssh無密碼認證
（11）在manager節點上測試mysql主從連接配接情況
（12）在manager節點上啟動MHA
（13）檢視MHA狀态，可以看到目前的master是MySQL1節點
（14）檢視MHA日志，也可以看到目前的master
（15）檢視MySQL1de VIP位址192.168.122.200是否存在，這個VIP位址不會因為manager節點停止MHA服務而消失
（16）關閉manager服務

2. 故障模拟與修複

（1）故障模拟

①在manager節點上監控觀察日志記錄
②在Master節點mysql1上停止mysql服務
③MHA快速調整新Master為mysql2節點伺服器
④檢視manager的app1.cnf檔案
⑤檢視mysql2是否接管VIP

（2）故障切換備選主庫的算法
（3）故障修複步驟

①修複mysql
②修複主從
③在manager節點上修改配置檔案app1.cnf
④在manager節點上啟動MHA

一、案例概述

傳統的MySQL主從架構存在的問題

● 單點故障

為了解決單點故障所帶來的的影響，我們可以使用MHA高可用架構。

二、案例前置知識點

1. MHA概述

● MHA（Master High Availability）是一套優秀的MySQL高可用環境下故障切換和主從複制的軟體。

● MHA的出現就是解決MySQL單點的問題。

● MySQL故障切換過程中，MHA能做到0-30秒内自動完成故障切換。

● MHA能在最大程度上保證資料的一緻性，以達到真正意義上的高可用。

2. MHA的組成

（1）MHA Manager（管理節點）

MHA Manager可以單獨部署在一台獨立的機器上，管理多個master-slave叢集；也可以部署在一台slave節點上。

MHA Manager會定時探測叢集中的master節點。當master出現故障時，它可以自動将最新資料的slave提升為新的master，然後将所有的slave重新指向新的master，整個故障轉移過程對應用程式完全透明。

（2）MHA Node（資料節點）

MHA Node運作在每台MySQL伺服器上。

3. MHA特點

● 自動故障切換過程中，MHA試圖從當機的主伺服器上儲存二進制日志，最大程度的保證資料不丢失。

● 使用半同步複制，可以大大降低資料丢失的風險，如果隻有一個slave已經收到了最新的二進制日志，MHA可以将最新的二進制日志應用于其他所有的slave伺服器上，是以可以保證所有節點的資料一緻性。

● 目前MHA支援一主多從的架構，最少三台服務，即一主兩從

4. MHA切換過程

（1）從當機崩潰的master儲存二進制日志事件（binlog events）；

（2）識别含有最新更新的slave；

（3）應用差異的中繼日志（relay log）到其他 slave；

（4）應用從master儲存的二進制日志事件（binlog events）；

（5）提升一個slave為新master；

（6）使其他的slave連接配接新的master進行複制。

三、案例環境

1. 實驗思路

MHA架構

● 資料庫安裝

● 一主兩從

● MHA搭建
故障模拟

● 主庫失效

● 備選主庫成為主庫

● 原故障主庫恢複重新加入到MHA成為從庫

2. 伺服器、主機名、系統、IP以及軟體版本配置

伺服器	主機名	系統	IP	軟體及版本
MHA manager節點伺服器	manager	CentOS7.4(64位)	192.168.122.100	安裝MHA node和manager元件
Master節點伺服器	mysql1		192.168.122.10	安裝mysql5.7、MHA node元件
Slave1節點伺服器	mysql2		192.168.122.11
Slave2節點伺服器	mysql3		192.168.122.12

3. 各伺服器環境配置

systemctl stop firewalld
systemctl disable firewalld
setenforce 0

四、案例實施

1. 搭建MySQL MHA

（1）Master、Slave1、Slave2節點上安裝mysql5.7

刷下列腳本，過程忽略

#!/bin/bash
systemctl stop firewalld
systemctl disable firewalld
setenforce 0

#--------mysql--------

#安裝依賴包

yum -y install gcc gcc-c++ ncurses ncurses-devel bison cmake

#配置軟體子產品

cd /opt/
tar zxvf mysql-5.7.17.tar.gz
tar zxvf boost_1_59_0.tar.gz
mv boost_1_59_0 /usr/local/boost

cd /opt/mysql-5.7.17/
cmake \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock \
-DSYSCONFDIR=/etc \
-DSYSTEMD_PID_DIR=/usr/local/mysql \
-DDEFAULT_CHARSET=utf8  \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_EXTRA_CHARSETS=all \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_PERFSCHEMA_STORAGE_ENGINE=1 \
-DMYSQL_DATADIR=/usr/local/mysql/data \
-DWITH_BOOST=/usr/local/boost \
-DWITH_SYSTEMD=1


#編譯安裝

make -j 2 && make install

#建立mysql使用者

useradd -M -s /sbin/nologin  mysql

#修改mysql 配置檔案

echo '[client]
port = 3306
default-character-set=utf8
socket=/usr/local/mysql/mysql.sock

[mysql]
port = 3306
default-character-set=utf8
socket = /usr/local/mysql/mysql.sock
auto-rehash

[mysqld]
user = mysql
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
port = 3306
character-set-server=utf8
pid-file = /usr/local/mysql/mysqld.pid
socket=/usr/local/mysql/mysql.sock
bind-address = 0.0.0.0
skip-name-resolve
max_connections=2048
default-storage-engine=INNODB
max_allowed_packet=16M
server-id = 1

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,PIPES_AS_CONCAT,ANSI_QUOTES' > /etc/my.cnf


#更改mysql安裝目錄和配置檔案的屬主屬組

chown -R mysql:mysql /usr/local/mysql/
chown mysql:mysql /etc/my.cnf

#設定路徑環境變量

echo 'export PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH' >> /etc/profile
source /etc/profile

#初始化資料庫

cd /usr/local/mysql/bin/
./mysqld \
--initialize-insecure \
--user=mysql \
--basedir=/usr/local/mysql \
--datadir=/usr/local/mysql/data

#添加mysqld系統服務

cp /usr/local/mysql/usr/lib/systemd/system/mysqld.service /usr/lib/systemd/system/
systemctl daemon-reload
systemctl start mysqld.service
systemctl enable mysqld

yum -y install expect
mima () {
passwd=$1
/usr/bin/expect <<-EOF
spawn mysqladmin -u root -p password $passwd
expect "Enter password:"
send "\r"
expect eof
EOF
}
mima "123456"

dl () {
/usr/bin/expect <<-EOF
spawn mysql -u root -p
expect "Enter password:" {send "123456\r"}
expect "mysql>" {send "grant all privileges on *.* to 'root'@'%' identified by '123456';\r"}
expect "mysql>" {send "quit\r"}
expect eof
EOF
}
dl

（2）修改各伺服器節點的主機名

Master(192.168.122.10)

[root@localhost opt]# hostnamectl set-hostname mysql1

Slave1(192.168.122.11)

[root@localhost opt]# hostnamectl set-hostname mysql2

Slave2(192.168.122.12)

[root@localhost opt]# hostnamectl set-hostname mysql3

MHA manager（192.168.122.100）

[root@localhost ~]# hostnamectl set-hostname manager

（3）修改Master、Slave1、Slave2節點的MySQL主配置檔案

[root@mysql1 ~]# vim /etc/my.cnf

##mysqld項下添加參數
[mysqld]
server-id = 1
log_bin = master-bin
log-slave-updates = true

[root@mysql1 ~]# systemctl restart mysqld

[root@mysql2 ~]# vim /etc/my.cnf

##mysqld項下添加參數
[mysqld]
server-id = 2
log_bin = master-bin
relay-log = relay-log-bin
relay-log-index = slave-relay-bin.index

[root@mysql2 ~]# systemctl restart mysqld

[root@mysql3 ~]# vim /etc/my.cnf

##mysqld項下添加參數
[mysqld]
server-id = 3
log_bin = master-bin
relay-log = relay-log-bin
relay-log-index = slave-relay-bin.index

[root@mysql3 ~]# systemctl restart mysqld

（4）在Master、Slave1、Slave2節點上都建立兩個軟連結

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

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

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

（5）配置mysql一主兩從

①所有資料庫節點進行mysql授權

[root@mysql1 ~]# mysql -u root -p
Enter password: 

mysql> grant replication slave on *.* to 'myslave'@'192.168.122.%' identified by '123456';
##從資料庫同步使用
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> grant all privileges on *.* to 'mha'@'192.168.122.%' identified by '123456';
##manager使用
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> grant all privileges on *.* to 'mha'@'mysql1' identified by '123456';
##防止從庫通過主機名連接配接不上主庫
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> grant all privileges on *.* to 'mha'@'mysql2' identified by '123456';
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> grant all privileges on *.* to 'mha'@'mysql3' identified by '123456';
Query OK, 0 rows affected, 2 warnings (0.00 sec)

[root@mysql2 ~]# mysql -u root -p
Enter password: 

mysql> grant replication slave on *.* to 'myslave'@'192.168.122.%' identified by '123456';
##從資料庫同步使用
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> grant all privileges on *.* to 'mha'@'192.168.122.%' identified by '123456';
##manager使用
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> grant all privileges on *.* to 'mha'@'mysql1' identified by '123456';
##防止從庫通過主機名連接配接不上主庫
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> grant all privileges on *.* to 'mha'@'mysql2' identified by '123456';
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> grant all privileges on *.* to 'mha'@'mysql3' identified by '123456';
Query OK, 0 rows affected, 2 warnings (0.00 sec)

[root@mysql3 ~]# mysql -u root -p
Enter password: 

mysql> grant replication slave on *.* to 'myslave'@'192.168.122.%' identified by '123456';
##從資料庫同步使用
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> grant all privileges on *.* to 'mha'@'192.168.122.%' identified by '123456';
##manager使用
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> grant all privileges on *.* to 'mha'@'mysql1' identified by '123456';
##防止從庫通過主機名連接配接不上主庫
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> grant all privileges on *.* to 'mha'@'mysql2' identified by '123456';
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> grant all privileges on *.* to 'mha'@'mysql3' identified by '123456';
Query OK, 0 rows affected, 2 warnings (0.00 sec)

②在Master節點檢視二進制檔案和同步點

mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 |     1595 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

③在Slave1、Slave2節點執行同步操作

mysql> change master to
    -> master_host='192.168.122.10',
    -> master_user='myslave',
    -> master_password='123456',
    -> master_log_file='master-bin.000001',
    -> master_log_pos=1595;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> change master to
    -> master_host='192.168.122.10',
    -> master_user='myslave',
    -> master_password='123456',
    -> master_log_file='master-bin.000001',
    -> master_log_pos=1595;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

④在Slave1、Slave2節點檢視資料同步結果

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.122.10
                  Master_User: myslave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000001
          Read_Master_Log_Pos: 1595
               Relay_Log_File: relay-log-bin.000002
                Relay_Log_Pos: 321
        Relay_Master_Log_File: master-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1595
              Relay_Log_Space: 526
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 00430ab9-0f02-11ec-ad15-000c2959bebe
             Master_Info_File: /usr/local/mysql/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.122.10
                  Master_User: myslave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000001
          Read_Master_Log_Pos: 1595
               Relay_Log_File: relay-log-bin.000002
                Relay_Log_Pos: 321
        Relay_Master_Log_File: master-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1595
              Relay_Log_Space: 526
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 00430ab9-0f02-11ec-ad15-000c2959bebe
             Master_Info_File: /usr/local/mysql/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

確定IO和SQL線程都是Yes，代表同步正常。

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

⑤兩個從庫必須設定為隻讀模式

mysql> set global read_only=1;
Query OK, 0 rows affected (0.00 sec)

mysql> set global read_only=1; 
Query OK, 0 rows affected (0.00 sec)

⑥插入資料測試資料庫同步

在Master主庫插入一條資料，測試是否同步

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> create table test.test(id int,name char(20));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test.test values(1,'master1');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test.test;
+------+---------+
| id   | name    |
+------+---------+
|    1 | master1 |
+------+---------+
1 row in set (0.00 sec)

mysql> select * from test.test;
+------+---------+
| id   | name    |
+------+---------+
|    1 | master1 |
+------+---------+
1 row in set (0.00 sec)

mysql> select * from test.test;
+------+---------+
| id   | name    |
+------+---------+
|    1 | master1 |
+------+---------+
1 row in set (0.00 sec)

（6）安裝MHA軟體

①所有伺服器上都安裝MHA依賴的環境，首先安裝epel源

[root@mysql1 ~]# yum install epel-release --nogpgcheck -y
[root@mysql1 ~]# yum install -y perl-DBD-MySQL \
> perl-Config-Tiny \
> perl-Log-Dispatch \
> perl-Parallel-ForkManager \
> perl-ExtUtils-CBuilder \
> perl-ExtUtils-MakeMaker \
> perl-CPAN

[root@mysql2 ~]# yum install epel-release --nogpgcheck -y
[root@mysql2 ~]# yum install -y perl-DBD-MySQL \
> perl-Config-Tiny \
> perl-Log-Dispatch \
> perl-Parallel-ForkManager \
> perl-ExtUtils-CBuilder \
> perl-ExtUtils-MakeMaker \
> perl-CPAN

[root@mysql3 ~]# yum install epel-release --nogpgcheck -y
[root@mysql3 ~]# yum install -y perl-DBD-MySQL \
> perl-Config-Tiny \
> perl-Log-Dispatch \
> perl-Parallel-ForkManager \
> perl-ExtUtils-CBuilder \
> perl-ExtUtils-MakeMaker \
> perl-CPAN

[root@manager ~]# yum install epel-release --nogpgcheck -y
[root@manager ~]# yum install -y perl-DBD-MySQL \
> perl-Config-Tiny \
> perl-Log-Dispatch \
> perl-Parallel-ForkManager \
> perl-ExtUtils-CBuilder \
> perl-ExtUtils-MakeMaker \
> perl-CPAN

②安裝MHA軟體包，必須在所有伺服器上先安裝node元件

對于每個作業系統版本不一樣，這裡CentOS7.4必須選擇0.57版本。

在所有伺服器上必須先安裝node元件，最後在MHA-manager節點上安裝manager元件，因為manager依賴node元件。

這裡統一将安裝包上傳到/opt目錄中。

[root@mysql1 ~]# cd /opt
##傳入安裝包
[root@mysql1 opt]# tar zxvf mha4mysql-node-0.57.tar.gz
[root@mysql1 opt]# cd mha4mysql-node-0.57/
[root@mysql1 mha4mysql-node-0.57]# perl Makefile.PL
[root@mysql1 mha4mysql-node-0.57]# make && make install

[root@mysql2 ~]# cd /opt
##傳入安裝包
[root@mysql2 opt]# tar zxvf mha4mysql-node-0.57.tar.gz
[root@mysql2 opt]# cd mha4mysql-node-0.57/
[root@mysql2 mha4mysql-node-0.57]# perl Makefile.PL
[root@mysql2 mha4mysql-node-0.57]# make && make install

[root@mysql3 ~]# cd /opt
##傳入安裝包
[root@mysql3 opt]# tar zxvf mha4mysql-node-0.57.tar.gz
[root@mysql3 opt]# cd mha4mysql-node-0.57/
[root@mysql3 mha4mysql-node-0.57]# perl Makefile.PL
[root@mysql3 mha4mysql-node-0.57]# make && make install

[root@manager ~]# cd /opt
##傳入安裝包
[root@manager opt]# tar zxvf mha4mysql-node-0.57.tar.gz
[root@manager opt]# cd mha4mysql-node-0.57/
[root@manager mha4mysql-node-0.57]# perl Makefile.PL
[root@manager mha4mysql-node-0.57]# make && make install

③在MHA manager節點上安裝manager元件

[root@manager mha4mysql-node-0.57]# cd ../
[root@manager opt]# tar zxvf mha4mysql-manager-0.57.tar.gz 
[root@manager opt]# cd mha4mysql-manager-0.57
[root@manager mha4mysql-manager-0.57]# perl Makefile.PL
[root@manager mha4mysql-manager-0.57]# make && make install

④node元件工具

node元件安裝後會在/usr/local/bin下面生成幾個腳本檔案（這些工具通常由MHA Manager的腳本觸發，無需人為操作）主要如下：

● save_binary_logs

儲存和複制master的二進制日志

● apply_diff_relay_logs

識别差異的中繼日志事件并将其差異的事件應用于其他的slave

● filter_mysqlbinlog

去除不必要的ROLLBACK事件（MHA已不再使用這個工具）

● purge_relay_logs

清除中繼日志（不會阻塞SQL線程）

⑤manager元件工具

manager元件安裝後在/usr/local/bin下面會生成幾個工具，主要包括以下幾個：

● masterha_check_ssh

檢查MHA的SSH配置狀況

● masterha_check_repl

檢查MySQL複制狀況

● masterha_manger

啟動manager的腳本

● masterha_check_status

檢測目前MHA運作狀态

● masterha_master_monitor

檢測 master是否當機

● masterha_master_switch

控制故障轉移（自動或者手動）

● masterha_conf_host

添加或删除配置的server資訊

● masterha_stop

關閉manager

（7）在所有伺服器上配置無密碼認證

①在manager節點上配置到所有資料庫節點的無密碼認證

[root@manager mha4mysql-manager-0.57]# ssh-keygen -t rsa
#一直按回車
[root@manager mha4mysql-manager-0.57]# ssh-copy-id 192.168.122.10
#輸入yes，輸入密碼
[root@manager mha4mysql-manager-0.57]# ssh-copy-id 192.168.122.11
#輸入yes，輸入密碼
[root@manager mha4mysql-manager-0.57]# ssh-copy-id 192.168.122.12
#輸入yes，輸入密碼

②在mysql1上配置到資料庫節點mysql2和mysql3的無密碼認證

[root@mysql1 mha4mysql-manager-0.57]# ssh-keygen -t rsa
#一直按回車
[root@mysql1 mha4mysql-manager-0.57]# ssh-copy-id 192.168.122.11
#輸入yes，輸入密碼
[root@mysql1 mha4mysql-manager-0.57]# ssh-copy-id 192.168.122.12
#輸入yes，輸入密碼

③在mysql2上配置到資料庫節點mysql1和mysql3的無密碼認證

[root@mysql2 mha4mysql-manager-0.57]# ssh-keygen -t rsa
#一直按回車
[root@mysql2 mha4mysql-manager-0.57]# ssh-copy-id 192.168.122.10
#輸入yes，輸入密碼
[root@mysql2 mha4mysql-manager-0.57]# ssh-copy-id 192.168.122.12
#輸入yes，輸入密碼

④在mysql3上配置到資料庫節點mysql1和mysql2的無密碼認證

[root@mysql3 mha4mysql-manager-0.57]# ssh-keygen -t rsa
#一直按回車
[root@mysql3 mha4mysql-manager-0.57]# ssh-copy-id 192.168.122.10
#輸入yes，輸入密碼
[root@mysql3 mha4mysql-manager-0.57]# ssh-copy-id 192.168.122.11
#輸入yes，輸入密碼

（8）在manager節點上配置MHA

①在manager節點上複制相關腳本到/usr/local/bin目錄

[root@manager mha4mysql-manager-0.57]# cp -rp /opt/mha4mysql-manager-0.57/samples/scripts /usr/local/bin
[root@manager mha4mysql-manager-0.57]# cd samples/scripts/
[root@manager scripts]# ll
總用量 32
-rwxr-xr-x 1 1001 1001  3648 5月  31 2015 master_ip_failover
#自動切換時VIP的管理腳本
-rwxr-xr-x 1 1001 1001  9870 5月  31 2015 master_ip_online_change
#線上切換時VIP的管理腳本
-rwxr-xr-x 1 1001 1001 11867 5月  31 2015 power_manager
#故障發生後關閉主機的腳本
-rwxr-xr-x 1 1001 1001  1360 5月  31 2015 send_report
#因故障切換後發送報警的腳本

②複制上述的自動切換時VIP的管理腳本到/usr/local/bin目錄

這裡使用master_ip_failover腳本來管理VIP和故障切換

[root@manager scripts]# cp /usr/local/bin/scripts/master_ip_failover /usr/local/bin

③修改内容如下（修改VIP相關參數）：

[root@manager scripts]# vim /usr/local/bin/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.122.200';                #指定vip的位址
my $brdc = '192.168.122.255';               #指定vip的廣播位址
my $ifdev = 'ens33';                    #指定vip綁定的網卡
my $key = '1';                        #指定vip綁定的虛拟網卡序列号
my $ssh_start_vip = "/sbin/ifconfig ens33:$key $vip";   #代表此變量值為ifconfig ens33:1 192.168.122.200
my $ssh_stop_vip = "/sbin/ifconfig ens33:$key down";    #代表此變量值為ifconfig ens33:1 192.168.122.200 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";
}

④建立MHA軟體目錄并拷貝配置檔案

這裡使用app1.cnf配置檔案來管理mysql節點伺服器

[root@manager scripts]# mkdir /etc/masterha
[root@manager scripts]# cp /opt/mha4mysql-manager-0.57/samples/conf/app1.cnf /etc/masterha
[root@manager scripts]# vim /etc/masterha/app1.cnf

[server default]
manager_log=/var/log/masterha/app1/manager.log
##manager日志路徑
manager_workdir=/var/log/masterha/app1
##manager工作目錄
master_binlog_dir=/usr/local/mysql/data
##master儲存binlog的位置，這裡的路徑要與master裡配置的binlog的路徑一緻，以便MHAzhaodao 
master_ip_failover_script=/usr/local/bin/master_ip_failover
##設定自動failover時候的切換腳本，也就是上一步修改的腳本
master_ip_online_change_script=/usr/local/bin/master_ip_online_change
##設定手動切換時候的切換腳本
password=123456
##設定mysql中root使用者的密碼，這個密碼是前文中建立監控使用者的那個密碼
ping_interval=1
##設定監控主庫，發送ping包的時間間隔，預設是3秒，嘗試三次沒有回應的時候自動進行failover
remote_workdir=/tmp
##設定遠端mysql在發生切換時binlog的儲存位置
repl_password=123456
##設定複制使用者的密碼
repl_user=myslave
##設定複制使用者的使用者
report_script=/usr/local/send_report
##設定發生切換後發送的報警的腳本
secondary_check_script=/usr/local/bin/masterha_secondary_check -s 192.168.122.11 -s 192.168.122.12
##指定檢查的從伺服器IP位址
shutdown_script=""
##設定故障發生後關閉故障主機腳本（該腳本的主要作用是關閉主機防止發生腦裂，這裡沒有指定）
ssh_user=root
##設定ssh的登入使用者名
user=mha
##設定監控使用者

[server1]
hostname=192.168.122.10
port=3306

[server2]
candidate_master=1
##設定為候選master，設定該參數以後，發生主從切換以後将會将此從庫提升為主庫，即使這個從庫不是叢集中最新的slave
check_repl_delay=0
##預設情況下如果有一個slave落後master超過100M的relay logs的話，MHA将不會選擇該slave作為一個新的master，因為對于這個slave的恢複需要花費很長時間；通過設定check_repl_delay=0，MHA觸發切換在選擇一個新的master的時候将會忽略複制延時，這個參數對于設定了candidate_master=1的主機非常有用，因為這個候選主機在切換的過程中一定是新的master
hostname=192.168.122.11
port=3306

[server3]
hostname=192.168.122.12
port=3306

（9）第一次配置需要在master節點上手動開啟虛拟IP

[root@mysql1 mha4mysql-node-0.57]# /sbin/ifconfig ens33:1 192.168.122.200/24

（10）在manager節點上測試ssh無密碼認證

如果正常最後會輸出successfully

[root@manager scripts]# masterha_check_ssh -conf=/etc/masterha/app1.cnf
Tue Sep  7 01:05:53 2021 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Tue Sep  7 01:05:53 2021 - [info] Reading application default configuration from /etc/masterha/app1.cnf..
Tue Sep  7 01:05:53 2021 - [info] Reading server configuration from /etc/masterha/app1.cnf..
Tue Sep  7 01:05:53 2021 - [info] Starting SSH connection tests..
Tue Sep  7 01:05:54 2021 - [debug] 
Tue Sep  7 01:05:53 2021 - [debug]  Connecting via SSH from [email protected](192.168.122.10:22) to [email protected](192.168.122.11:22)..
Tue Sep  7 01:05:54 2021 - [debug]   ok.
Tue Sep  7 01:05:54 2021 - [debug]  Connecting via SSH from [email protected](192.168.122.10:22) to [email protected](192.168.122.12:22)..
Tue Sep  7 01:05:54 2021 - [debug]   ok.
Tue Sep  7 01:05:55 2021 - [debug] 
Tue Sep  7 01:05:54 2021 - [debug]  Connecting via SSH from [email protected](192.168.122.11:22) to [email protected](192.168.122.10:22)..
Tue Sep  7 01:05:54 2021 - [debug]   ok.
Tue Sep  7 01:05:54 2021 - [debug]  Connecting via SSH from [email protected](192.168.122.11:22) to [email protected](192.168.122.12:22)..
Tue Sep  7 01:05:55 2021 - [debug]   ok.
Tue Sep  7 01:05:56 2021 - [debug] 
Tue Sep  7 01:05:54 2021 - [debug]  Connecting via SSH from [email protected](192.168.122.12:22) to [email protected](192.168.122.10:22)..
Tue Sep  7 01:05:55 2021 - [debug]   ok.
Tue Sep  7 01:05:55 2021 - [debug]  Connecting via SSH from [email protected](192.168.122.12:22) to [email protected](192.168.122.11:22)..
Tue Sep  7 01:05:55 2021 - [debug]   ok.
Tue Sep  7 01:05:56 2021 - [info] All SSH connection tests passed successfully.

（11）在manager節點上測試mysql主從連接配接情況

最後出現MySQL Replication Health is OK 字樣說明正常。

[root@manager scripts]# masterha_check_repl -conf=/etc/masterha/app1.cnf
[root@manager scripts]# masterha_check_repl -conf=/etc/masterha/app1.cnf
Tue Sep  7 03:10:16 2021 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Tue Sep  7 03:10:16 2021 - [info] Reading application default configuration from /etc/masterha/app1.cnf..
Tue Sep  7 03:10:16 2021 - [info] Reading server configuration from /etc/masterha/app1.cnf..
Tue Sep  7 03:10:16 2021 - [info] MHA::MasterMonitor version 0.57.
Tue Sep  7 03:10:17 2021 - [info] GTID failover mode = 0
Tue Sep  7 03:10:17 2021 - [info] Dead Servers:
Tue Sep  7 03:10:17 2021 - [info] Alive Servers:
Tue Sep  7 03:10:17 2021 - [info]   192.168.122.10(192.168.122.10:3306)
Tue Sep  7 03:10:17 2021 - [info]   192.168.122.11(192.168.122.11:3306)
Tue Sep  7 03:10:17 2021 - [info]   192.168.122.12(192.168.122.12:3306)
Tue Sep  7 03:10:17 2021 - [info] Alive Slaves:
Tue Sep  7 03:10:17 2021 - [info]   192.168.122.11(192.168.122.11:3306)  Version=5.7.17-log (oldest major version between slaves) log-bin:enabled
Tue Sep  7 03:10:17 2021 - [info]     Replicating from 192.168.122.10(192.168.122.10:3306)
Tue Sep  7 03:10:17 2021 - [info]     Primary candidate for the new Master (candidate_master is set)
Tue Sep  7 03:10:17 2021 - [info]   192.168.122.12(192.168.122.12:3306)  Version=5.7.17-log (oldest major version between slaves) log-bin:enabled
Tue Sep  7 03:10:17 2021 - [info]     Replicating from 192.168.122.10(192.168.122.10:3306)
Tue Sep  7 03:10:17 2021 - [info] Current Alive Master: 192.168.122.10(192.168.122.10:3306)
Tue Sep  7 03:10:17 2021 - [info] Checking slave configurations..
Tue Sep  7 03:10:17 2021 - [info]  read_only=1 is not set on slave 192.168.122.11(192.168.122.11:3306).
Tue Sep  7 03:10:17 2021 - [warning]  relay_log_purge=0 is not set on slave 192.168.122.11(192.168.122.11:3306).
Tue Sep  7 03:10:17 2021 - [info]  read_only=1 is not set on slave 192.168.122.12(192.168.122.12:3306).
Tue Sep  7 03:10:17 2021 - [warning]  relay_log_purge=0 is not set on slave 192.168.122.12(192.168.122.12:3306).
Tue Sep  7 03:10:17 2021 - [info] Checking replication filtering settings..
Tue Sep  7 03:10:17 2021 - [info]  binlog_do_db= , binlog_ignore_db= 
Tue Sep  7 03:10:17 2021 - [info]  Replication filtering check ok.
Tue Sep  7 03:10:17 2021 - [info] GTID (with auto-pos) is not supported
Tue Sep  7 03:10:17 2021 - [info] Starting SSH connection tests..
Tue Sep  7 03:10:20 2021 - [info] All SSH connection tests passed successfully.
Tue Sep  7 03:10:20 2021 - [info] Checking MHA Node version..
Tue Sep  7 03:10:20 2021 - [info]  Version check ok.
Tue Sep  7 03:10:20 2021 - [info] Checking SSH publickey authentication settings on the current master..
Tue Sep  7 03:10:20 2021 - [info] HealthCheck: SSH to 192.168.122.10 is reachable.
Tue Sep  7 03:10:20 2021 - [info] Master MHA Node version is 0.57.
Tue Sep  7 03:10:20 2021 - [info] Checking recovery script configurations on 192.168.122.10(192.168.122.10:3306)..
Tue Sep  7 03:10:20 2021 - [info]   Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/usr/local/mysql/data --output_file=/tmp/save_binary_logs_test --manager_version=0.57 --start_file=master-bin.000001 
Tue Sep  7 03:10:20 2021 - [info]   Connecting to [email protected](192.168.122.10:22).. 
  Creating /tmp if not exists..    ok.
  Checking output directory is accessible or not..
   ok.
  Binlog found at /usr/local/mysql/data, up to master-bin.000001
Tue Sep  7 03:10:20 2021 - [info] Binlog setting check done.
Tue Sep  7 03:10:20 2021 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..
Tue Sep  7 03:10:20 2021 - [info]   Executing command : apply_diff_relay_logs --command=test --slave_user='mha' --slave_host=192.168.122.11 --slave_ip=192.168.122.11 --slave_port=3306 --workdir=/tmp --target_version=5.7.17-log --manager_version=0.57 --relay_log_info=/usr/local/mysql/data/relay-log.info  --relay_dir=/usr/local/mysql/data/  --slave_pass=xxx
Tue Sep  7 03:10:20 2021 - [info]   Connecting to [email protected](192.168.122.11:22).. 
  Checking slave recovery environment settings..
    Opening /usr/local/mysql/data/relay-log.info ... ok.
    Relay log found at /usr/local/mysql/data, up to relay-log-bin.000006
    Temporary relay log file is /usr/local/mysql/data/relay-log-bin.000006
    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 Sep  7 03:10:21 2021 - [info]   Executing command : apply_diff_relay_logs --command=test --slave_user='mha' --slave_host=192.168.122.12 --slave_ip=192.168.122.12 --slave_port=3306 --workdir=/tmp --target_version=5.7.17-log --manager_version=0.57 --relay_log_info=/usr/local/mysql/data/relay-log.info  --relay_dir=/usr/local/mysql/data/  --slave_pass=xxx
Tue Sep  7 03:10:21 2021 - [info]   Connecting to [email protected](192.168.122.12:22).. 
  Checking slave recovery environment settings..
    Opening /usr/local/mysql/data/relay-log.info ... ok.
    Relay log found at /usr/local/mysql/data, up to relay-log-bin.000006
    Temporary relay log file is /usr/local/mysql/data/relay-log-bin.000006
    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 Sep  7 03:10:21 2021 - [info] Slaves settings check done.
Tue Sep  7 03:10:21 2021 - [info] 
192.168.122.10(192.168.122.10:3306) (current master)
 +--192.168.122.11(192.168.122.11:3306)
 +--192.168.122.12(192.168.122.12:3306)

Tue Sep  7 03:10:21 2021 - [info] Checking replication health on 192.168.122.11..
Tue Sep  7 03:10:21 2021 - [info]  ok.
Tue Sep  7 03:10:21 2021 - [info] Checking replication health on 192.168.122.12..
Tue Sep  7 03:10:21 2021 - [info]  ok.
Tue Sep  7 03:10:21 2021 - [info] Checking master_ip_failover_script status:
Tue Sep  7 03:10:21 2021 - [info]   /usr/local/bin/master_ip_failover --command=status --ssh_user=root --orig_master_host=192.168.122.10 --orig_master_ip=192.168.122.10 --orig_master_port=3306 


IN SCRIPT TEST====/sbin/ifconfig ens33:1 down==/sbin/ifconfig ens33:1 192.168.122.200===

Checking the Status of the script.. OK 
Tue Sep  7 03:10:21 2021 - [info]  OK.
Tue Sep  7 03:10:21 2021 - [warning] shutdown_script is not defined.
Tue Sep  7 03:10:21 2021 - [info] Got exit code 0 (Not master dead).

MySQL Replication Health is OK.

實驗中出現報錯，删除slave節點伺服器中mysql主配置檔案中的“default-character-set=utf8”後重新開機mysqld服務。

（12）在manager節點上啟動MHA

[root@manager scripts]# 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] 9321

● --remove_dead_master_conf：該參數代表當發生主從切換後，老的主庫的 ip 将會從配置檔案中移除。

● --manager_log：日志存放位置。

● --ignore_last_failover：在預設情況下，如果 MHA 檢測到連續發生當機，且兩次當機間隔不足 8 小時的話，則不會進行 Failover，之是以這樣限制是為了避免 ping-pong 效應。該參數代表忽略上次 MHA 觸發切換産生的檔案，預設情況下，MHA 發生切換後會在日志記目錄，也就是上面設定的日志app1.failover.complete檔案，下次再次切換的時候如果發現該目錄下存在該檔案将不允許觸發切換，除非在第一次切換後收到删除該檔案，為了友善，這裡設定為--ignore_last_failover。

（13）檢視MHA狀态，可以看到目前的master是MySQL1節點

[root@manager scripts]# masterha_check_status --conf=/etc/masterha/app1.cnf
app1 (pid:9321) is running(0:PING_OK), master:192.168.122.10

（14）檢視MHA日志，也可以看到目前的master

[root@manager scripts]# cat /var/log/masterha/app1/manager.log | grep "current master"
Tue Sep  7 03:22:07 2021 - [info] Checking SSH publickey authentication settings on the current master..
192.168.122.10(192.168.122.10:3306) (current master)

（15）檢視MySQL1de VIP位址192.168.122.200是否存在，這個VIP位址不會因為manager節點停止MHA服務而消失

[root@mysql1 mha4mysql-node-0.57]# ifconfig
ens33: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 192.168.122.10  netmask 255.255.255.0  broadcast 192.168.122.255
        inet6 fe80::7350:3abe:ece6:290d  prefixlen 64  scopeid 0x20<link>
        ether 00:0c:29:59:be:be  txqueuelen 1000  (Ethernet)
        RX packets 730534  bytes 1038497504 (990.3 MiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 346113  bytes 35339346 (33.7 MiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

ens33:1: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 192.168.122.200  netmask 255.255.255.0  broadcast 192.168.122.255
        ether 00:0c:29:59:be:be  txqueuelen 1000  (Ethernet)

（16）關閉manager服務

若要關閉 manager 服務，可以使用如下指令。

masterha_stop --conf=/etc/masterha/app1.cnf

[root@manager scripts]# 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

或者可以直接采用 kill 程序 ID 的方式關閉。

[root@manager scripts]# 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 &
##啟動manager
[1] 10387
[root@manager scripts]# kill 10387
##殺死程序
[root@manager scripts]# masterha_check_status --conf=/etc/masterha/app1.cnf
##檢視狀态為已停止
app1 is stopped(2:NOT_RUNNING).
[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

2. 故障模拟與修複

（1）故障模拟

①在manager節點上監控觀察日志記錄

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

IN SCRIPT TEST====/sbin/ifconfig ens33:1 down==/sbin/ifconfig ens33:1 192.168.122.200===

Checking the Status of the script.. OK 
Tue Sep  7 03:39:32 2021 - [info]  OK.
Tue Sep  7 03:39:32 2021 - [warning] shutdown_script is not defined.
Tue Sep  7 03:39:32 2021 - [info] Set master ping interval 1 seconds.
Tue Sep  7 03:39:32 2021 - [info] Set secondary check script: /usr/local/bin/masterha_secondary_check -s 192.168.122.11 -s 192.168.122.12
Tue Sep  7 03:39:32 2021 - [info] Starting ping health check on 192.168.122.10(192.168.122.10:3306)..
Tue Sep  7 03:39:32 2021 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond..

②在Master節點mysql1上停止mysql服務

[root@mysql1 mha4mysql-node-0.57]# systemctl stop mysqld
或
[root@mysql1 mha4mysql-node-0.57]# pkill -9 mysql

③MHA快速調整新Master為mysql2節點伺服器

Master 192.168.122.10(192.168.122.10:3306) is down!

Check MHA Manager logs at manager:/var/log/masterha/app1/manager.log for details.

Started automated(non-interactive) failover.
Invalidated master IP address on 192.168.122.10(192.168.122.10:3306)
The latest slave 192.168.122.11(192.168.122.11:3306) has all relay logs for recovery.
Selected 192.168.122.11(192.168.122.11:3306) as a new master.
192.168.122.11(192.168.122.11:3306): OK: Applying all logs succeeded.
192.168.122.11(192.168.122.11:3306): OK: Activated master IP address.
192.168.122.12(192.168.122.12:3306): This host has the latest relay log events.
Generating relay diff files from the latest slave succeeded.
192.168.122.12(192.168.122.12:3306): OK: Applying all logs succeeded. Slave started, replicating from 192.168.122.11(192.168.122.11:3306)
192.168.122.11(192.168.122.11:3306): Resetting slave info succeeded.
Master failover to 192.168.122.11(192.168.122.11:3306) completed successfully.

④檢視manager的app1.cnf檔案

[root@manager scripts]# vim /etc/masterha/app1.cnf

[server default]
manager_log=/var/log/masterha/app1/manager.log
manager_workdir=/var/log/masterha/app1
master_binlog_dir=/usr/local/mysql/data
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=myslave
secondary_check_script=/usr/local/bin/masterha_secondary_check -s 192.168.122.11 -s 192.168.122.12
shutdown_script=""
ssh_user=root
user=mha

[server2]
candidate_master=1
check_repl_delay=0
hostname=192.168.122.11
port=3306

[server3]
hostname=192.168.122.12
port=3306

正常自動切換一次後，MHA 程序會退出。HMA 會自動修改 app1.cnf 檔案内容，将當機的 mysql1 節點删除。

⑤檢視mysql2是否接管VIP

[root@mysql2 mha4mysql-node-0.57]# ifconfig
ens33: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 192.168.122.11  netmask 255.255.255.0  broadcast 192.168.122.255
        inet6 fe80::ed0e:8bf:3608:6ab9  prefixlen 64  scopeid 0x20<link>
        ether 00:0c:29:94:63:f1  txqueuelen 1000  (Ethernet)
        RX packets 172230  bytes 214197465 (204.2 MiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 80091  bytes 18553598 (17.6 MiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

ens33:1: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 192.168.122.200  netmask 255.255.255.0  broadcast 192.168.122.255
        ether 00:0c:29:94:63:f1  txqueuelen 1000  (Ethernet)

稱為新master後成功接管VIP

（2）故障切換備選主庫的算法

一般判斷從庫的是從（position/GTID）判斷優劣，資料有差異，最接近于master的slave，成為備選主。
資料一緻的情況下，按照配置檔案順序，選擇備選主庫。
設定有權重（candidate_master=1），按照權重強制指定備選主。

● 預設情況下如果一個slave落後master 100M的relay logs的話，即使有權重，也會失效。

● 如果check_repl_delay=0的話，即使落後很多日志，也強制選擇其為備選主。

（3）故障修複步驟

①修複mysql

[root@mysql1 mha4mysql-node-0.57]# systemctl restart mysqld

②修複主從

在現主庫伺服器mysql2檢視二進制檔案和同步點

[root@mysql2 mha4mysql-node-0.57]# mysql -uroot -p
Enter password: 

mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000003 |      154 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

在原主庫伺服器mysql1執行同步操作

[root@mysql1 mha4mysql-node-0.57]# mysql -u root -p
Enter password: 

mysql> change master to
    -> master_host='192.168.122.11',
    -> master_user='myslave',
    -> master_password='123456',
    -> master_log_file='master-bin.000003',
    -> master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

③在manager節點上修改配置檔案app1.cnf

将mysql1的記錄添加回去，因為測試故障時已被自動删除

[root@manager scripts]# vim /etc/masterha/app1.cnf

......
secondary_check_script=/usr/local/bin/masterha_secondary_check -s 192.168.122.10 -s 192.168.122.12
......

##添加server1并寫入mysql2的ip
[server1]
hostname=192.168.122.11
port=3306

##修改server2資訊為mysql1的ip
[server2]
candidate_master=1
check_repl_delay=0
hostname=192.168.122.10
port=3306

[server3]
hostname=192.168.122.12
port=3306

④在manager節點上啟動MHA

[root@manager scripts]# 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] 12884
[root@manager scripts]# masterha_check_status --conf=/etc/masterha/app1.cnf
app1 monitoring program is now on initialization phase(10:INITIALIZING_MONITOR). Wait for a while and try checking again.
[root@manager scripts]# masterha_check_status --conf=/etc/masterha/app1.cnf
app1 (pid:12884) is running(0:PING_OK), master:192.168.122.11

此時master仍是mysql2節點，如若需要讓mysql1重新成為master，停止mysql2節點的mysql服務即可。

mysql1獲得mater地位以及VIP後，重新啟動mysql2的mysql服務，并添加進slave叢集中。