一，MHA简介

1，MHA概述

2，MHA集群架构

3，MHA工作过程

二，MHA高可用配置

1，环境准备

2，主从同步

3，安装MHA

4，故障模拟

一，MHA简介

1，MHA概述

一套优秀的MySQL高可用环境下故障切换和主从复制软件

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

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

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

MHA的组成：

MHA Manager （管理节点）

MHA Node （数据节点）

MHA的特点：

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

使用半同步复制，可以大大降低数据丢失的风险

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

MHA：Master High Availability，对主节点进行监控，可实现自动故障转移至其它节点；通过提升某一节点为新的主节点，基于主从复制实现，还需要客户端配合实现。

2，MHA集群架构

3，MHA工作过程

1，从宕机崩溃的master保存二进制日志事件（binlog events）

2，识别含有最新更新的slave

3，应用差异的中继日志（relay log）到其他的slave

4，应用从master保存二进制事件（binlog events）

5，提升一个slave为新的master

6，是其他的slave连接新的master进行复制

注：为了尽可能的减少主库硬件损坏宕机造成的数据丢失，因此在配置MHA的同时建议配置成MySQL的半同步复制

二，MHA高可用配置

在管理节点上安装两个包mha4mysql-manager和mha4mysql-node

说明：mha4mysql-manager-0.56-0.el6.noarch.rpm不支持centos8，只支持centos7以下版本

说明：mha4mysql-manager-0.58-0.el7.cetos.noarch.rpm,支持mysql5.7，但和centos8版本上的Mariadb-10.3.17不谦容

1，环境准备

master服务器：192.168.18.100 mysql5.7

slave1服务器：192.168.18.91 mysql5.7

slave2服务器：192.168.18.93 mysql5.7

MHA manager（管理节点服务器）：192.168.18.90

关闭防火墙和selinux（所有机器都需要）

[root@localhost ~]# systemctl stop firewalld.service
[root@localhost ~]# setenforce 0

为了便于操作修改 Master、Slave1、Slave2 节点的主机名

[root@localhost ~]# hostnamectl set-hostname master
[root@localhost ~]# su
[root@master ~]#

[root@localhost ~]# hostnamectl set-hostname slave1
[root@localhost ~]# su
[root@slave1 ~]#

[root@localhost ~]# hostnamectl set-hostname slave2
[root@localhost ~]# su
[root@slave2 ~]#

每一台节点服务器添加域名解析

[root@master ~]# vim /etc/hosts

127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.18.100  master
192.168.18.91   slave1
192.168.18.93   slave2

[root@slave1 ~]# vim /etc/hosts

127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.18.100  master
192.168.18.91   slave1
192.168.18.93   slave2

[root@slave2 ~]# vim /etc/hosts

127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.18.100  master
192.168.18.91   slave1
192.168.18.93   slave2

2，主从同步

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

三台服务器的 server-id 不能一样

master节点配置

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

prompt=(\u@\h) [\d]>\_

[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
server-id = 1
log_bin = master-bin
log-slave-updates = true


：wq

[root@master ~]# systemctl restart mysqld.service   ##重启mysql服务

slave1节点配置

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


[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
server-id = 2
log_bin = master-bin
relay-log = relay-log-bin
relay-log-index = slave-relay-bin.index
  

：wq
 
[root@slave1 ~]# systemctl restart mysqld.service  ##重启mysql服务

slave2节点配置

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


[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
server-id = 3
log_bin = master-bin
relay-log = relay-log-bin
relay-log-index = slave-relay-bin.index

：wq

[root@slave2 ~]# systemctl restart mysqld.service  #重启mysql服务

做软链接设置每一台节点服务器都需要做

[root@master ~]# ln -s /usr/local/mysql/bin/{mysql,mysqlbinlog} /usr/sbin/
[root@master ~]# ls /usr/sbin/mysql*
/usr/sbin/mysql  /usr/sbin/mysqlbinlog
[root@master ~]#

[root@slave1 ~]# ln -s /usr/local/mysql/bin/{mysql,mysqlbinlog} /usr/sbin/
[root@slave1 ~]# ls /usr/sbin/mysql*
/usr/sbin/mysql  /usr/sbin/mysqlbinlog
[root@slave1 ~]#

[root@slave2 ~]# ln -s /usr/local/mysql/bin/{mysql,mysqlbinlog} /usr/sbin/
[root@slave2 ~]# ls /usr/sbin/mysql*
/usr/sbin/mysql  /usr/sbin/mysqlbinlog
[root@slave2 ~]#

登录数据库授权主从用户（每一台节点服务器都需要操作）

[root@master ~]# mysql -uroot -p123123    ##登录数据库
mysql: [Warning] Using a password on the command line interface can be insecure.

                                     ##授权主从
(root@localhost) [(none)]> grant replication slave on *.* to 'myslave'@'192.168.18.%' identified by '123123';
Query OK, 0 rows affected, 1 warning (0.01 sec)

(root@localhost) [(none)]> grant all privileges on *.* to 'mha'@'192.168.18.%' identified by 'manager';
Query OK, 0 rows affected, 1 warning (0.01 sec)

(root@localhost) [(none)]> grant all privileges on *.* to 'mha'@'master' identified by 'manager';
Query OK, 0 rows affected, 1 warning (0.00 sec)

(root@localhost) [(none)]> grant all privileges on *.* to 'mha'@'slave1' identified by 'manager';
Query OK, 0 rows affected, 1 warning (0.01 sec)

(root@localhost) [(none)]> grant all privileges on *.* to 'mha'@'slave2' identified by 'manager';
Query OK, 0 rows affected, 1 warning (0.00 sec)
(root@localhost) [(none)]> flush privileges;   ##刷新配置
Query OK, 0 rows affected (0.00 sec)

查看主节点

(root@localhost) [(none)]> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 |     1745 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

配置从节点（两台从节点配置相同）

mysql> help change master to  ##查看帮助

CHANGE MASTER TO
  MASTER_HOST='master2.mycompany.com',     ##格式
  MASTER_USER='replication',
  MASTER_PASSWORD='bigs3cret',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='master2-bin.001',
  MASTER_LOG_POS=4,
  MASTER_CONNECT_RETRY=10;

配置从节点
 mysql> change master to 
    -> master_host='192.168.18.100',master_user='myslave',mmaster_password
='123123',master_log_file='master-bin.0000011',master_log_pos=1745;
Query OK, 0 rows affected, 2 warnings (0.05 sec)

开启从节点（两台）

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

查看从节点（两台）

 Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.18.100
                  Master_User: myslave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000001
          Read_Master_Log_Pos: 1745
               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   ##  两项都是yes就行了
              Replicate_Do_DB: 
          Replicate_Ignore_DB:

设置从节点只读（两台）

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

验证主从复制

在主服务器上添加库文件

[root@master ~]# cd /data
[root@master data]# ls
[root@master data]# rz -E
rz waiting to receive.
[root@master data]# ls
test.sql

(root@localhost) [(none)]> source /data/test.sql   ##source文件位置
(root@localhost) [hellodb]> show tables;   ##进到了hellodb库查看表
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| teachers          |
| toc               |
+-------------------+
7 rows in set (0.00 sec)

在从服务器slave1上查看

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb            |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

mysql> use hellodb;

Database changed
mysql> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| teachers          |
| toc               |
+-------------------+
7 rows in set (0.00 sec)

在从服务器slave2上查看

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

mysql> use hellodb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| teachers          |
| toc               |
+-------------------+
7 rows in set (0.00 sec)

以上操作说明主从复制主从复制已经完成

3，安装MHA

#所有服务器上都安装 MHA 依赖的环境，首先安装 epel 源

[root@master ~]# yum install epel-release --nogpgcheck -y
[root@master ~]# 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.6选择 0.57 版本。
#在所有服务器上必须先安装 node 组件，最后在 MHA-manager 节点上安装 manager 组件，因为 manager 依赖 node 组件。

在主从服务器上安装node 组件：

[root@master opt]# ls
[root@master opt]# rz -E    ##将安装包拖进目录
rz waiting to receive.
[root@master opt]# tar zxf mha4mysql-node-0.57.tar.gz    ##解压安装包
[root@master opt]# ls
mha4mysql-node-0.57  mha4mysql-node-0.57.tar.gz
[root@master opt]# cd mha4mysql-node-0.57                ##进入编译目录

[root@master mha4mysql-node-0.57]# perl Makefile.PL
*** Module::AutoInstall version 1.06
*** Checking for Perl dependencies...
[Core Features]
- DBI        ...loaded. (1.627)
- DBD::mysql ...loaded. (4.023)

[root@master mha4mysql-node-0.57]# make && make install

在 MHA-manager 节点上安装 manager 组件，

[root@localhost mha4mysql-node-0.57]# cd /opt
[root@localhost opt]# ls
mha4mysql-manager-0.57.tar.gz  mha4mysql-node-0.57.tar.gz
mha4mysql-node-0.57
[root@localhost opt]# tar zxf mha4mysql-manager-0.57.tar.gz 
[root@localhost opt]# cd mha4mysql-manager-0.57/

[root@localhost mha4mysql-manager-0.57]# perl Makefile.PL

[root@localhost mha4mysql-manager-0.57]# make && make install
cp lib/MHA/ManagerUtil.pm blib/lib/MHA/ManagerUtil.pm
cp lib/MHA/Config.pm blib/lib/MHA/Config.pm
cp lib/MHA/HealthCheck.pm blib/lib/MHA/HealthCheck.pm
cp lib/MHA/ServerManager.pm blib/lib/MHA/ServerManager.pm

查看

[root@localhost mha4mysql-manager-0.57]# cd /usr/local/bin[root@localhost bin]# ls
apply_diff_relay_logs  masterha_master_monitor
filter_mysqlbinlog     masterha_master_switch
masterha_check_repl    masterha_secondary_check
masterha_check_ssh     masterha_stop
masterha_check_status  purge_relay_logs
masterha_conf_host     save_binary_logs
masterha_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

#node 组件安装后也会在/usr/local/bin 下面会生成几个脚本（这些工具通常由 MHAManager 的脚本触发，无需人为操作）主要如下：
save_binary_logs 保存和复制 master 的二进制日志
apply_diff_relay_logs 识别差异的中继日志事件并将其差异的事件应用于其他的 slave
filter_mysqlbinlog 去除不必要的 ROLLBACK 事件（MHA 已不再使用这个工具）
purge_relay_logs 清除中继日志（不会阻塞 SQL 线程）

在所有的服务器上配置无密码验证

在 manager 节点上配置到所有数据库节点的无密码认证

[root@localhost bin]# ssh-keygen -t rsa      ##指定加密算法
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa):  ##回车
Created directory '/root/.ssh'.
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:h6JanpQdKx+SidD2/47cwGHU7Wbpsk3OyRsWwkx+T9A root@localhost.localdomain
The key's randomart image is:
+---[RSA 2048]----+
|                 |
|       . . .     |
|      . o o E    |
| .   . = o o     |
|. o   = S O .    |
| o o O = B +     |
|  . @ * . = .    |
|   = B = X o     |
|  . o =o= B.     |
+----[SHA256]-----+        ##这样就生成免密认证密码

[root@localhost bin]# ssh-copy-id 192.168.18.100    
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/root/.ssh/id_rsa.pub"
The authenticity of host '192.168.18.100 (192.168.18.100)' can't be established.
ECDSA key fingerprint is SHA256:SoTXgxKaYDjMEOAjN5scxKxp5tLnyAipIWvbQ3etP68.
ECDSA key fingerprint is MD5:54:4c:73:fe:2a:a3:bc:d4:ae:22:1d:17:27:c0:27:a4.
Are you sure you want to continue connecting (yes/no)? yes   ##输入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.18.100's password:  ###192.168.18.100主机的密码  

Number of key(s) added: 1

Now try logging into the machine, with:   "ssh '192.168.18.100'"
and check to make sure that only the key(s) you wanted were added.
这样就做好了登录192.168.18.100的免密登录

验证免密登录

[root@localhost bin]# ssh 192.168.18.100
Last login: Tue Dec  7 22:11:18 2021 from 192.168.18.1
[root@master ~]# exit
登出
Connection to 192.168.18.100 closed.
[root@localhost bin]#

上述同样的方法做其余的免密登录

[root@localhost bin]# ssh-copy-id 192.168.18.91
[root@localhost bin]# ssh-copy-id 192.168.18.93

数据库服务器之间相互做免密码认证

主服务器

[root@master mha4mysql-node-0.57]# ssh-keygen -t rsa 
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:nKrCiHvL9iccQtTUUrezeOlVusvRAu8ISYrzupQipCo root@master
The key's randomart image is:
+---[RSA 2048]----+
|   o.o. .        |
|  . o .. .       |
| .   .  o   .    |
|  .    o = o     |
| o    o S o      |
|o .o.o = + o     |
|=o=o..+ . = .    |
|E+=oo... + +     |
|++=*+o  . +      |
+----[SHA256]-----+

[root@master mha4mysql-node-0.57]# ssh-copy-id 192.168.18.91
[root@master mha4mysql-node-0.57]# ssh-copy-id 192.168.18.93

从服务器1

[root@slave1 mha4mysql-node-0.57]# ssh-keygen -t rsa 
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:aFctxMlnr+aF7RvGCjZPXoeq68VWjbN85QPshXFd7zk root@slave1
The key's randomart image is:
+---[RSA 2048]----+
|         o..    .|
|         .+.o   +|
|          oo.o .o|
|       . . .. =+.|
|      o S    B+E+|
|     . .   .=+==o|
|          +o=+O.+|
|         . O.=.+.|
|         .+o= .. |
+----[SHA256]-----+

[root@slave1 mha4mysql-node-0.57]# ssh-copy-id 192.168.18.100
[root@slave1 mha4mysql-node-0.57]# ssh-copy-id 192.168.18.93

从服务器2

[root@slave2 mha4mysql-node-0.57]# ssh-keygen -t rsa
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:12b/skVhvDU96xpUW6OyLMIxkvm6vRPZ4ReiLnj9KZk root@slave2
The key's randomart image is:
+---[RSA 2048]----+
|                 |
|              . .|
|               O+|
|      o  o o  + @|
|     + oS +.=o +.|
|      ++o+.++... |
|    . o++..o ....|
|   . ooE. o   +o |
|    .ooo=o   ..o.|
+----[SHA256]-----+

[root@slave2 mha4mysql-node-0.57]# ssh-copy-id 192.168.18.100
[root@slave2 mha4mysql-node-0.57]# ssh-copy-id 192.168.18.91

在manager节点上操作

在 manager 节点上复制相关脚本到/usr/local/bin 目录

[root@localhost bin]# cp -rp /opt/mha4mysql-manager-0.57/samples/scripts /usr/local/bin
[root@localhost bin]# ll /usr/local/bin/s
save_binary_logs  scripts/          
[root@localhost bin]# ll /usr/local/bin/scripts/
总用量 32
-rwxr-xr-x. 1 1001 1001  3648 5月  31 2015 master_ip_failover
-rwxr-xr-x. 1 1001 1001  9870 5月  31 2015 master_ip_online_change
-rwxr-xr-x. 1 1001 1001 11867 5月  31 2015 power_manager
-rwxr-xr-x. 1 1001 1001  1360 5月  31 2015 send_report

master_ip_failover         #自动切换时 VIP 管理的脚本
master_ip_online_change    #在线切换时 vip 的管理
power_manager                #故障发生后关闭主机的脚本
send_report                #因故障切换后发送报警的脚本

[root@localhost bin]# cd /usr/local/bin/
[root@localhost bin]# cp scripts/* ./     ##将脚本添加全局变量
[root@localhost bin]# ls
apply_diff_relay_logs    masterha_secondary_check
filter_mysqlbinlog       masterha_stop
masterha_check_repl      master_ip_failover
masterha_check_ssh       master_ip_online_change
masterha_check_status    power_manager
masterha_conf_host       purge_relay_logs
masterha_manager         save_binary_logs
masterha_master_monitor  scripts
masterha_master_switch   send_report

修改脚本文件

[root@localhost 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.91.188';									
my $brdc = '192.168.91.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;											
#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 "nnIN script TEST====$ssh_stop_vip==$ssh_start_vip===nn";

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=portn";
}

添加内容解释

#############################添加内容部分#############################
my $vip = '192.168.18.156';                                   #指定vip的地址
my $brdc = '192.168.18.255';                               #指定vip的广播地址
my $ifdev = 'ens33';                                       #指定vip绑定的网卡
my $key = '1';                                               #指定vip绑定的虚拟网卡序列号
my $ssh_start_vip = "/sbin/ifconfig ens33:$key $vip";       #代表此变量值为ifconfig ens33:1 192.168.18.156
my $ssh_stop_vip = "/sbin/ifconfig ens33:$key down";       #代表此变量值为ifconfig ens33:1 192.168.18.156 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";

[root@localhost bin]# cd /opt/mha4mysql-manager-0.57/samples/
[root@localhost samples]# cd conf/     ##切换目录到配置文件位置
[root@localhost conf]# ls
app1.cnf  masterha_default.cnf
[root@localhost conf]# mkdir /etc/masterha     ##创建文件夹 
[root@localhost conf]# cp app1.cnf /etc/masterha/    ##复制文件到指定位置
[root@localhost conf]# cd /etc/masterha/             ##切换目录
[root@localhost masterha]# ls
app1.cnf
[root@localhost masterha]# vim /etc/masterha/app1.cnf   ###编辑配置文件

[root@localhost masterha]# 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_failovermaster_ip_online_change_script=/usr/local/bin/master_ip_online_change
password=manager
ping_interval=1
remote_workdir=/tmp

repl_password=123123
repl_user=myslave
secondary_check_script=/usr/local/bin/masterha_secondary_check -s 192.168.18.91 -s 192.168.18.93shutdown_script=""ssh_user=root
user=mha

[server1]
hostname=192.168.18.100
port=3306

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

[server3]
hostname=192.168.18.93
port=3306

配置文件个字段注释

[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的路径一致，以便MHA能找到
master_ip_failover_script=/usr/local/bin/master_ip_failover　　#设置自动failover时候的切换脚本，也就是上面的那个脚本
master_ip_online_change_script=/usr/local/bin/master_ip_online_change　　#设置手动切换时候的切换脚本
password=manager           #设置mysql中root用户的密码，这个密码是前文中创建监控用户的那个密码
ping_interval=1               #设置监控主库，发送ping包的时间间隔，默认是3秒，尝试三次没有回应的时候自动进行failover
remote_workdir=/tmp           #设置远端mysql在发生切换时binlog的保存位置
repl_password=123123           #设置复制用户的密码
repl_user=myslave           #设置复制用户的用户
report_script=/usr/local/send_report　　　　　#设置发生切换后发送的报警的脚本
secondary_check_script=/usr/local/bin/masterha_secondary_check -s 192.168.18.91 -s 192.168.18.93 #指定检查的从服务器IP地址
shutdown_script=""           #设置故障发生后关闭故障主机脚本（该脚本的主要作用是关闭主机防止发生脑裂,这里没有使用）
ssh_user=root               #设置ssh的登录用户名
user=mha                   #设置监控用户root

[server1]
hostname=192.168.18.100
port=3306

[server2]
hostname=192.168.18.91
port=3306
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

[server3]
hostname=192.168.18.93
port=3306

在主节点开启虚拟IP

[root@master /]# ifconfig ens33:1 192.168.18.156/24   ##开启虚拟ip
[root@master /]# ifconfig      ##查看
ens33: flags=4163  mtu 1500
        inet 192.168.18.100  netmask 255.255.255.0  broadcast 192.168.18.255
        inet6 fe80::dfcf:a827:5049:4ee0  prefixlen 64  scopeid 0x20
        ether 00:0c:29:41:2f:c4  txqueuelen 1000  (Ethernet)
        RX packets 137270  bytes 143739781 (137.0 MiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 60516  bytes 10056357 (9.5 MiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

ens33:1: flags=4163  mtu 1500
        inet 192.168.18.156  netmask 255.255.255.0  broadcast 192.168.18.255
        ether 00:0c:29:41:2f:c4  txqueuelen 1000  (Ethernet)

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

[root@localhost masterha]# masterha_check_ssh -conf=/etc/masterha/app1.cnf
Wed Dec  8 17:16:22 2021 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Wed Dec  8 17:16:22 2021 - [info] Reading application default configuration from /etc/masterha/app1.cnf..
Wed Dec  8 17:16:22 2021 - [info] Reading server configuration from 
Wed Dec  8 17:16:23 2021 - [debug]   ok.
Wed Dec  8 17:16:24 2021 - [debug] 
Wed Dec  8 17:16:23 2021 - [debug]  Connecting via SSH from root@192.168.18.93(192.168.18.93:22) to root@192.168.18.100(192.168.18.100:22)..
Wed Dec  8 17:16:23 2021 - [debug]   ok.
Wed Dec  8 17:16:23 2021 - [debug]  Connecting via SSH from root@192.168.18.93(192.168.18.93:22) to root@192.168.18.91(192.168.18.91:22)..
Wed Dec  8 17:16:24 2021 - [debug]   ok.
Wed Dec  8 17:16:24 2021 - [info] All SSH connection tests passed successfully.
最后出现successfully说明正确

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

[root@localhost masterha]# masterha_check_repl -conf=/etc/masterha/app1.cnf
Wed Dec  8 17:22:31 2021 - [warning] Global configuration file /etc/masterha_default.cnf 
.................
Wed Dec  8 17:22:36 2021 - [info]  OK.
Wed Dec  8 17:22:36 2021 - [warning] shutdown_script is not defined.
Wed Dec  8 17:22:36 2021 - [info] Got exit code 0 (Not master dead).

MySQL Replication Health is OK.
最后显示MySQL Replication Health is OK.说明正确
如果mysql数据库中字符集设置了中文有可能会MySQL Replication Health is not OK.

在 manager 节点上启动 MHA

[root@localhost 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] 7370

查看 MHA 状态，

[root@localhost masterha]# masterha_check_status --conf=/etc/masterha/app1.cnf
app1 (pid:7370) is running(0:PING_OK), master:192.168.18.100  ##master是主节点

查看 MHA 日志

[root@localhost masterha]# cat /var/log/masterha/app1/manager.log | grep "current master"
Wed Dec  8 17:28:02 2021 - [info] Checking SSH publickey authentication settings on the current master..
192.168.18.100(192.168.18.100:3306) (current master) ##master是主节点

若要关闭 manager 服务，可以使用如下命令。
masterha_stop --conf=/etc/masterha/app1.cnf
或者可以直接采用 kill 进程 ID 的方式关闭。

4，故障模拟

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

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

IN script TEST====/sbin/ifconfig ens33:1 down==/sbin/ifconfig ens33:1 192.168.91.188===

Checking the Status of the script.. OK 
Wed Dec  8 17:28:03 2021 - [info]  OK.
Wed Dec  8 17:28:03 2021 - [warning] shutdown_script is not defined.
Wed Dec  8 17:28:03 2021 - [info] Set master ping interval 1 seconds.
Wed Dec  8 17:28:03 2021 - [info] Set secondary check script: /usr/local/bin/masterha_secondary_check -s 192.168.18.91 -s 192.168.18.93
Wed Dec  8 17:28:03 2021 - [info] Starting ping health check on 192.168.18.100(192.168.18.100:3306)..
Wed Dec  8 17:28:03 2021 - [info] Ping(SELECt) succeeded, waiting until MySQL doesn't respond..

在 Master 节点 Mysql1 上停止mysql服务

[root@master /]# systemctl stop mysqld.service

查看manager日志

Master 192.168.18.100(192.168.18.100:3306) is down!    ###master宕机

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

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

查看slave1 是否接管 VIP

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

[root@slave1 mha4mysql-node-0.57]# ifconfig 
ens33: flags=4163  mtu 1500
        inet 192.168.18.91  netmask 255.255.255.0  broadcast 192.168.18.255
        inet6 fe80::8dcb:1449:ff97:2031  prefixlen 64  scopeid 0x20
        ether 00:0c:29:ce:bf:09  txqueuelen 1000  (Ethernet)
        RX packets 80356  bytes 74983394 (71.5 MiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 36438  bytes 5565958 (5.3 MiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

ens33:1: flags=4163  mtu 1500
        inet 192.168.18.156  netmask 255.255.255.0  broadcast 192.168.18.156
            ##接管了VIP192.168.18.156

故障切换备选主库的算法：
1．一般判断从库的是从（position/GTID）判断优劣，数据有差异，最接近于master的slave，成为备选主。
2．数据一致的情况下，按照配置文件顺序，选择备选主库。
3．设定有权重（candidate_master=1），按照权重强制指定备选主。
（1）默认情况下如果一个slave落后master 100M的relay logs的话，即使有权重，也会失效。
（2）如果check_repl_delay=0的话，即使落后很多日志，也强制选择其为备选主。

故障修复

1，修复mastermysql

[root@master /]# systemctl restart mysqld.service

2．修复主从
#在现主库服务器 slave1 查看二进制文件和同步点

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

在原主库服务器 master执行同步操作

(root@localhost) [(none)]> change master to 
    -> master_host='192.168.18.91',master_user='myslave',master_passwordd='123123',master_log_file='master-bin.000001',master_log_pos=1745;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

(root@localhost) [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

(root@localhost) [(none)]> show slave statusG
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.18.91
                  Master_User: myslave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000001
          Read_Master_Log_Pos: 1745
               Relay_Log_File: master-relay-bin.000002
                Relay_Log_Pos: 321
        Relay_Master_Log_File: master-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

3．在 manager 节点上修改配置文件app1.cnf

[root@localhost masterha]# vi /etc/masterha/app1.cnf

...........
secondary_check_script=/usr/local/bin/masterha_secondary_check -s 192.168.18.100 -s 192.168.18.93
shutdown_script=""
ssh_user=root
user=mha


[server1]
hostname=192.168.18.91
port=3306

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

[server3]
hostname=192.168.18.93
port=3306

4．在 manager 节点上启动 MHA

[root@localhost 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] 12724

查看现在的主192.168.18.91

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

就此模拟故障就完成了，假如主节点宕掉，那么它会跳到另一台节点上去，作为主节点，宕掉的服务，就会成为从节点

MySQL高可用集群——MHA高可用配置

一，MHA简介

2，MHA集群架构

Linux相关栏目本月热门文章