部署mysql的高可用MHA（manager high available）集群架构和MHA故障恢复

简介

        mha也是第三方软件或者插件，用于部署mysql高可用的一种方式，主要采取故障转移的策略，当主节点发生故障时，mha就会将主节点的master身份赋予给子节点slave，被指定的slave节点就会升级为master节点，赋予的策略默认就是按照接受到MHA的master机器的binlog日志最新的机器，当然，也可以使用权重来设置，自我感觉最原始的比较好，毕竟数据一致性很重要，最新的数据最贴近一致性。

        跟zookeeper里面的机制差不多，不过zookeeper里面的机制是采用选举，这里采用最新日志状态

MHA环境架构

MHA环境部署

第一步

首先master+slave1+slave2下载好mysql 统一密码为1234, 注意：8.0之后有密码安全限制，需要改变一些变量
set global validate_password.length=4;
set global validate_password.policy=LOW;
#这里是临时改变，密码最低位数只能设置成4

第二步

#配置免密登录，后面的MHA管理子节点的时候，需要用到。
1. 每个机器都先执行以下ssh-keygen，生成.ssh目录和密钥
2. 每台机器都执行ssh-copy-id user@IP 到本地和另外三台机器上，该命令就是将自己的公钥放在对面机器的.ssh目录下，达到免密登录的效果
#这里机器比较少，可以一步步执行，如果机器多的话可以写一个脚本

第三步

#搭建MHA需要的环境，MYSQL异步复制主从架构
#解释：等搭建好MHA后，他就会变成半同步复制主从架构

#master机器
[root@master ~]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html

[mysqld]
server-id=1
log-bin=/log/mysql-bin
read-only=0
validate_password.length=4
validate_password.policy=LOW


datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

#创建log-bin的目录
mkdir /log
chown mysql:mysql /log
systemctl --now enable mysqld

#slave1机器
[root@slave1 ~]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html

[mysqld]
server-id=2
log-bin=/log/mysql-bin
read-only=1
relay-log=mysql-relay
validate_password.length=4
validate_password.policy=LOW

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

#创建log-bin的目录
mkdir /log
chown mysql:mysql /log
systemctl --now enable mysqld

#slave2机器
[root@slave2 ~]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html

[mysqld]
server-id=3
log-bin=/log/mysql-bin
relay-log=mysql-relay
read-only=1
validate_password.length=4
validate_password.policy=LOW

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

#创建log-bin的目录
mkdir /log
chown mysql:mysql /log
systemctl --now enable mysqld

#master机器配置
[root@master ~]# mysql -uroot -p1234

#创建从机复制的用户
mysql> create user 'slaves'@'192.168.10.%' identified with mysql_native_password by '1234';
Query OK, 0 rows affected (0.13 sec) 

#授权给该用户权限
mysql> grant replication slave on *.* to 'slaves'@'192.168.10.%';
Query OK, 0 rows affected (0.54 sec)

#刷新一下权限
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> show master logs;
+------------------+-----------+-----------+
| Log_name         | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 |      157 | No        |
+------------------+-----------+-----------+
1 row in set (0.01 sec)

#slave1操作和slave2操作一样
[root@slave1 ~]# mysql -uroot -p

mysql> 
#作为从机，连接主机的的binlog日志
mysql> CHANGE MASTER TO MASTER_HOST='192.168.10.16',MASTER_USER='slaves',MASTER_PASSWORD='1234',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=157;

Query OK, 0 rows affected, 8 warnings (0.61 sec)

mysql> start slave;
Query OK, 0 rows affected, 1 warning (1.39 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 192.168.10.16
                  Master_User: slaves
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 1373
               Relay_Log_File: mysql-relay.000002
                Relay_Log_Pos: 326
        Relay_Master_Log_File: mysql-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: 1373
              Relay_Log_Space: 532
              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: 39a78029-4311-11ee-987d-000c29124967
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Replica 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: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set, 1 warning (0.01 sec)

ERROR: 
No query specified

#测试：
测试前一定两边的数据要一样，如果不一样，则执行某些从机无法执行的命令，那么连接就会断开，show slave status\G里面可以看到某个线程会断开，并且下面会有错误说明。
解决方法：目前初始化机器，也没什么binlog重要日志，则直接主机操作reset master，从机操作stop slave,reset slave,start slave,然后通过show slave status\G查看一下状态
如果master有重要的日志，可以刷新一下，生成一个新的binlog日志就好了

第四步

#部署MHA manager和MHA node组件
#操作MHA服务器
#在各个服务器上传提前下好的组件
#根据下面这张图上传

[root@mha ~]# ll
total 1115328
-rw-------. 1 root root       1495 Jul 19 11:37 anaconda-ks.cfg
-rw-r--r--  1 root root      81024 Aug 30 17:20 mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
-rw-r--r--  1 root root      36328 Aug 30 17:20 mha4mysql-node-0.58-0.el7.centos.noarch.rpm
[root@mha ~]# yum install -y mha4mysql-*

[root@master ~]# ll
total 56
-rw-------. 1 root root  1495 Jul 19 11:37 anaconda-ks.cfg
-rw-r--r--  1 root root 36328 Aug 30 17:20 mha4mysql-node-0.58-0.el7.centos.noarch.rpm
[root@master ~]# yum install -y mha4mysql-*

[root@slave1 ~]# ll
total 56
-rw-------. 1 root root  1495 Jul 19 11:37 anaconda-ks.cfg
-rw-r--r--  1 root root 36328 Aug 30 17:20 mha4mysql-node-0.58-0.el7.centos.noarch.rpm
[root@slave1 ~]# yum install -y mha4mysql-*

[root@slave2 ~]# ll
total 56
-rw-------. 1 root root  1495 Jul 19 11:37 anaconda-ks.cfg
-rw-r--r--  1 root root 36328 Aug 30 17:20 mha4mysql-node-0.58-0.el7.centos.noarch.rpm
[root@slave2 ~]# yum install -y mha4mysql-*

# 2. 在master主节点上，创建MHA要用到的用户
[root@master ~]# mysql -uroot -p
#创建MHA的管理用户
mysql> create user 'MHA'@'192.168.10.%' identified with mysql_native_password by '1234';
Query OK, 0 rows affected (0.01 sec)

#授权给该用户管理员权限，所有all
mysql> grant all on *.* to 'MHA'@'192.168.10.%';
Query OK, 0 rows affected (0.10 sec)

#刷新一下权限
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

#2.1 配置master节点的VIP，这个VIP跟下面设置mha节点的VIP一样
[root@master ~]# ifconfig eth0:1 192.168.10.100/24

# 3.部署mha管理节点的配置

#3.1 在mha管理节点创建配置文件，该文件用于管理mysql节点服务器，默认必须指定为app1.cnf

[root@mha /etc/mastermha]# vim app1.cnf
[root@mha /etc/mastermha]# cat app1.cnf 
[server default]
user=MHA
password=1234
manager_workdir=/data/mastermha/app1/
manager_log=/data/mastermha/app1/manager.log
remote_workdir=/data/mastermha/app1/
ssh_user=root
repl_user=slaves
repl_password=1234
ping_interval=1
master_ip_failover_script=/usr/local/bin/master_ip_failover
report_script=/usr/local/bin/sendmail.sh
check_repl_delay=0
master_binlog_dir=/log
[server1]
hostname=192.168.10.16
candidate_master=1
[server2]
hostname=192.168.10.26
[server3]
hostname=192.168.10.27

# 3.2 配置email服务
[root@mha /etc/mastermha]# yum install -y mailx
[root@mha /etc/mastermha]# vim /etc/mail.rc  #在该文件末尾追加一下内容
set from=1964941064@qq.com
set smtp=smtp.qq.com
set smtp-auth-user=1964941064@qq.com
set smtp-auth-password=jxmiztynqcuadchb
set smtp-auth=login
#测试
[root@mha /etc/mastermha]# echo hello |mail -s "kk" 1964941064@qq.com

#3.3 配置关于mha主配置文件/etc/mastermh/app1.cnf中引用的各个脚本

#email发送脚本
[root@mha /etc/mastermha]# cat /usr/local/bin/sendmail.sh 
#!/bin/bash
echo "MySQL is down" | mail -s "MHA Warning" 1964941064@qq.com

[root@mha /etc/mastermha]# chmod +x /usr/local/bin/sendmail.sh 

#3.4 配置临时VIP
[root@mha /etc/mastermha]# ifconfig eth0:1 192.168.10.100/24

#3.5 配置调用临时VIP的脚本
[root@mha /etc/mastermha]# cat /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.10.100/24';
my $gateway = '192.168.10.2';
my $interface = 'eth0';
my $key = "1";
my $ssh_start_vip = "/sbin/ifconfig $interface:$key $vip;/sbin/arping -I
$interface -c 3 -s $vip $gateway >/dev/null 2>&1";
my $ssh_stop_vip = "/sbin/ifconfig $interface:$key down";
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" ) {
# $orig_master_host, $orig_master_ip, $orig_master_port are passed.
# If you manage master ip address at global catalog database,
# invalidate orig_master_ip here.
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" ) {
# all arguments are passed.
# If you manage master ip address at global catalog database,
# activate new_master_ip here.
# You can also grant write access (create user, set read_only=0, etc) here.
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";
`ssh $ssh_user\@$orig_master_host \" $ssh_start_vip \"`;
exit 0; }
else {
&usage();
exit 1; }}
# A simple system call that enable the VIP on the new master
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@mha /etc/mastermha]# chmod +x /usr/local/bin/master_ip_failover


#检查环境
[root@mha-manager ~]#masterha_check_ssh --conf=/etc/mastermha/app1.cnf #检查ssh免密
[root@mha-manager ~]#masterha_check_repl --conf=/etc/mastermha/app1.cnf #检查是否是主从复制机制

#前台开启mha
masterha_manager --conf=/etc/mastermha/app1.cnf
#后台开启mha
nohup masterha_manager --conf=/etc/mastermha/app1.cnf &> /dev/null

[root@mha-manager ~]# masterha_check_status --conf=/etc/mastermha/app1.cnf #查看mha状态

#测试
systemctl stop mysqld #干掉主mysql服务器

#查看另外两台服务器，是否VIP飘逸到了某一台，则该台为主服器

MHA故障恢复

#将挂掉的服务器修复好，重新启动mysqld服务
#在主服务器机器上，查看目前主服务器的binlog日志的position
show master logs;

#在恢复的机器上，执行以下命令，这时候的MASTER_HOST为现在的主服务器，而不是宕机恢复的机器，MASTER_LOG_FILE和MASTER_LOG_POS也要更改变化
CHANGE MASTER TO MASTER_HOST='192.168.10.16',MASTER_USER='slaves',MASTER_PASSWORD='1234',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=731;

#开启slave
start slave
#这时候的以前宕机的主服务器，恢复后，作为从机去连接主机
show slave status\G #查看是否连接成功

#接下来就是在主服务测试是否会同步数据
......

#然后在mha机器上删除一个文件，只有删除该文件，才能再次启动MHA
[root@mha /etc/mastermha]# rm -rf /data/mastermha/app1/app1.failover.complete

#启动MHA
#前台开启mha
masterha_manager --conf=/etc/mastermha/app1.cnf

#日志在这里，如果是后台运行，可以看该日志排错
/data/mastermha/app1/manager.log 
#致辞结束，点个赞吧