MHA高可用配置及故障切换

知识点分析

1.MHA概述
一套优秀的MySQL高可用环境下故障切换和主从复制的软件
MySQL故障过程中，MHA能够做到0-30秒内自动完成故障切换
2.MHA的组成
MHA Manager（管理节点）
MHA Node（数据节点）
3.MHA特点
自动故障切换过程中，MHA试图从宕机的主服务器上保存二进制日志，最大程度的保证数据不丢失
使用半同步复制，可以大大降低数据丢失的风险
目前MHA支持一主多从架构，最少三台服务，即一主两从
4.MHA缺点
需要编写脚本或利用第三方工具来实现vip的配置
MHA启动后只会对数据库进行监控，需要基于ssh免认证配置，存在一定的安全隐患
没有提供从服务器的读负载均很的功能

案例部署

1、MHA架构
数据库安装
一主两从
MHA搭建
2、故障模拟
主库失效
备选主库成为主库
从库2将备选主库指向为主库
3、故障修复
坏库修复，启动
在修复好的库上建立新主从关系
修改manager配置文件，添加修好的库的记录
重启mha
在三台 MySQL 节点上分别安装数据库 MySQL 版本请使用 5.6.36，cmake 版本请使用 2.8.6
主服务器
yum -y install ncurses-devel gcc-c++ perl-Module-Install
tar zxvf cmake-2.8.6.tar.gz
cd cmake-2.8.6
./configure
gmake && gmake install
cd
tar zxvf mysql-5.6.36.tar.gz
cd mysql-5.6.36
cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_EXTRA_CHARSETS=all -DSYSCONFDIR=/etc
make && make install
cp support-files/my-default.cnf /etc/my.cnf
cp support-files/mysql.server /etc/rc.d/init.d/mysqld
chmod +x /etc/rc.d/init.d/mysqld
chkconfig --add mysqld
echo “PATH=$PATH:/usr/local/mysql/bin” >> /etc/profile
source /etc/profile
groupadd mysql
useradd -M -s /sbin/nologin mysql -g mysql
chown -R mysql.mysql /usr/local/mysql
mkdir -p /data/mysql
/usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --user=mysql

vi /etc/my.cnf
[client]
port = 3306
socket = /usr/local/mysql/mysql.sock
[mysql]
port = 3306
socket = /usr/local/mysql/mysql.sock

[mysqld]
user = mysql
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306
pid-file = /usr/local/mysql/mysqld.pid
socket = /usr/local/mysql/mysql.sock
server-id = 1
log_bin = master-bin
log-slave-updates = true

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

ln -s /usr/local/mysql/bin/mysql /usr/sbin/
ln -s /usr/local/mysql/bin/mysqlbinlog /usr/sbin/
systemctl start mysqld
netstat -anpt | grep 3306
从服务器1
yum -y install ncurses-devel gcc-c++ perl-Module-Install
tar zxvf cmake-2.8.6.tar.gz
cd cmake-2.8.6
./configure
gmake && gmake install
cd
tar zxvf mysql-5.6.36.tar.gz
cd mysql-5.6.36
cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_EXTRA_CHARSETS=all -DSYSCONFDIR=/etc
make && make install
cp support-files/my-default.cnf /etc/my.cnf
cp support-files/mysql.server /etc/rc.d/init.d/mysqld
chmod +x /etc/rc.d/init.d/mysqld
chkconfig --add mysqld
echo “PATH=$PATH:/usr/local/mysql/bin” >> /etc/profile
source /etc/profile
groupadd mysql
useradd -M -s /sbin/nologin mysql -g mysql
chown -R mysql.mysql /usr/local/mysql
mkdir -p /data/mysql
/usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --user=mysql

vi /etc/my.cnf
[client]
port = 3306
socket = /usr/local/mysql/mysql.sock
[mysql]
port = 3306
socket = /usr/local/mysql/mysql.sock
[mysqld]
user = mysql
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306
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

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

ln -s /usr/local/mysql/bin/mysql /usr/sbin/
ln -s /usr/local/mysql/bin/mysqlbinlog /usr/sbin/
systemctl start mysqld
netstat -anpt | grep 3306

从服务器2
yum -y install ncurses-devel gcc-c++ perl-Module-Install
tar zxvf cmake-2.8.6.tar.gz
cd cmake-2.8.6
./configure
gmake && gmake install
cd
tar zxvf mysql-5.6.36.tar.gz
cd mysql-5.6.36
cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_EXTRA_CHARSETS=all -DSYSCONFDIR=/etc
make && make install
cp support-files/my-default.cnf /etc/my.cnf
cp support-files/mysql.server /etc/rc.d/init.d/mysqld
chmod +x /etc/rc.d/init.d/mysqld
chkconfig --add mysqld
echo “PATH=$PATH:/usr/local/mysql/bin” >> /etc/profile
source /etc/profile
groupadd mysql
useradd -M -s /sbin/nologin mysql -g mysql
chown -R mysql.mysql /usr/local/mysql
mkdir -p /data/mysql
/usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --user=mysql

vi /etc/my.cnf
[client]
port = 3306
socket = /usr/local/mysql/mysql.sock
[mysql]
port = 3306
socket = /usr/local/mysql/mysql.sock
[mysqld]
user = mysql
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306
pid-file = /usr/local/mysql/mysqld.pid
socket = /usr/local/mysql/mysql.sock
server-id = 3
relay-log = relay-log-bin
relay-log-index = slave-relay-bin.index

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

ln -s /usr/local/mysql/bin/mysql /usr/sbin/
ln -s /usr/local/mysql/bin/mysqlbinlog /usr/sbin/
systemctl start mysqld
netstat -anpt | grep 3306

配置MySQL一主两从

1、MySQL 主从配置相对比较简单，需要注意的是授权。步骤如下：在所有数据库节点上授权两个用户，一个是从库同步使用，另外一个是 manager 使用。
mysql> grant replication slave on . to ‘myslave’@‘20.0.0.%’ identified by ‘123’;
mysql> grant all privileges on . to ‘mha’@‘20.0.0.%’ identified by ‘manager’;
mysql> flush privileges

2、在主服务器上查看二进制文件和同步点
mysql> show master status;

3、接下来在从1 和 从2 分别执行同步,查看 IO 和 SQL 线程都是 yes 代表同步是否正常
mysql> change master to master_host=‘20.0.0.10’,master_user=‘myslave’,master_password=‘123’,master_log_file=‘master-bin.000001’,master_log_pos=608;
mysql> start slave;
mysql> show slave status\G;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

I/O线程显示为NO: 主库与从库网络不通、主库未授权给从库
SQL线程显示为NO：从库日志和位置点与主不同步
若从库查看连接主库I/0线程状态为conneting，一直是这个状态，考虑双方的防火墙是否开启。

4、必须设置两个从库为只读模式
mysql> set global read_only=1;

5、在主库插入两条数据，测试是否同步
mysql> create database tanwenlong;
mysql> use tanwenlong;
mysql> create table test(id int（4））;
mysql> insert into test(id) values (1);
mysql> select * from test;
6、在两个从库分别查询如下所示说明主从同步正常
mysql> select * from tanwenlong.test;

安装MHA软件

1、在所有服务器上都安装 MHA 依赖的环境，每个服务器上都需要两个源（epel.repo和CentOS7-Base-163.repo）,将这两个源放入/etc/yum.repos.d的目录下，在主服务器上操作演示安装
yum install epel-release --nogpgcheck
yum install -y perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker perl-CPAN
2、在所有服务器上必须先安装 node 组件，最后在 MHA-manager 节点上安装 manager 组件，因为 manager 依赖 node 组件

tar zxvf mha4mysql-node-0.57.tar.gz
cd mha4mysql-node-0.57
perl Makefile.PL
make && make install

3、在mha-manager 服务器上安装 manager 组件

tar zxvf mha4mysql-manager-0.57.tar.gz
cd mha4mysql-manager-0.57
perl Makefile.PL
make && make install

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 下面会生成几个脚本（这些工具通常由 MHA-Manager 的脚本触发，无需人为操作）主要如下：
save_binary_logs 保存和复制 master 的二进制日志
apply_diff_relay_logs 识别差异的中继日志事件并将其差异的事件应用于其他的 slave
filter_mysqlbinlog 去除不必要的 ROLLBACK 事件（MHA 已不再使用这个工具）
purge_relay_logs 清除中继日志（不会阻塞 SQL 线程）

5.无密码认证
在manager服务器配置到所有节点的无密码认证
所有的都要设置
ssh-keygen -t rsa
ssh-copy-id 20.0.0.22
ssh-copy-id 20.0.0.23
ssh-copy-id 20.0.0.24

在主服务器上配置到数据库节点的无密码认证
ssh-keygen -t rsa
ssh-copy-id 20.0.0.23
ssh-copy-id 20.0.0.24

从1
ssh-keygen -t rsa
ssh-copy-id 20.0.0.22
ssh-copy-id 20.0.0.24

从2
ssh-keygen -t rsa
ssh-copy-id 20.0.0.22
ssh-copy-id 20.0.0.23

6.配置MHA
1、在 manager 节点上复制相关脚本到/usr/local/bin 目录,复制上述的自动切换时 VIP 管理的脚本到/usr/local/bin 目录，这里使用脚本管理 VIP

cp -ra mha4mysql-manager-0.57/samples/scripts /usr/local/bin
ll /usr/local/bin/scripts/
cp /usr/local/bin/scripts/master_ip_failover /usr/local/bin
vi /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 = ‘20.0.0.200’;
my $brdc = ‘20.0.0.255’;
my $ifdev = ‘ens33’;
my $key = ‘1’;
my $ss{h}_{s}tar{t}_{v}ip="/sbin/ifconfigens33:$key $vip";
my $ss{h}_{s}to{p}_{v}ip="/sbin/ifconfigens33:$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 “\n\nIN SCRIPT TEST====$ss{h}_{s}to{p}_{v}ip==$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();
KaTeX parse error: Expected 'EOF', got '}' at position 16: exit_code = 0; }̲; if (@) {
warn “Got Error: $@\n”;
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq “start” ) {
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 \";
}

拷贝后会有四个执行文件，文件的含义如下：
master_ip_failover #自动切换时 VIP 管理的脚本
master_ip_online_change #在线切换时 vip 的管理
power_manager #故障发生后关闭主机的脚本
send_report #因故障切换后发送报警的脚本

2、创建 MHA 软件目录并拷贝配置文件
mkdir /etc/masterha
cp mha4mysql-manager-0.57/samples/conf/app1.cnf /etc/masterha/
vim /etc/masterha/app1.cnf
[server default]
manager_workdir=/var/log/masterha/app1
manager_log=/var/log/masterha/app1/manager.log
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=manager
user=mha
ping_interval=1
remote_workdir=/tmp
repl_password=123
repl_user=myslave
secondary_check_script= /usr/local/bin/masterha_secondary_check -s 20.0.0.23 -s 20.0.0.24
shutdown_script=""
ssh_user=root
[server1]
hostname=20.0.0.22
port=3306
[server2]
hostname=20.0.0.23
port=3306
candidate_master=1
check_repl_delay=0
[server3]
hostname=20.0.0.24
port=3306

3、测试 ssh 无密码认证，如果正常最后会输出 successfully
masterha_check_ssh -conf=/etc/masterha/app1.cnf
… #省略
Sun Nov 1 15:54:42 2020 - [debug] ok.
Sun Nov 1 15:54:42 2020 - [debug] Connecting via SSH from root@20.0.0.22(20.0.0.22:22) to root@20.0.0.24(20.0.0.24:22)…
Sun Nov 1 15:54:43 2020 - [debug] ok.
Sun Nov 1 15:54:44 2020 - [debug]
Sun Nov 1 15:54:42 2020 - [debug] Connecting via SSH from root@20.0.0.24(20.0.0.24:22) to root@20.0.0.22(20.0.0.22:22)…
Sun Nov 1 15:54:43 2020 - [debug] ok.
Sun Nov 1 15:54:43 2020 - [debug] Connecting via SSH from root@20.0.0.24(20.0.0.24:22) to root@20.0.0.23(20.0.0.23:22)…
Sun Nov 1 15:54:44 2020 - [debug] ok.
Sun Nov 1 15:54:44 2020 - [debug]
Sun Nov 1 15:54:42 2020 - [debug] Connecting via SSH from root@20.0.0.23(20.0.0.23:22) to root@20.0.0.22(20.0.0.22:22)…
Sun Nov 1 15:54:43 2020 - [debug] ok.
Sun Nov 1 15:54:43 2020 - [debug] Connecting via SSH from root@20.0.0.23(20.0.0.23:22) to root@20.0.0.24(20.0.0.24:22)…
Sun Nov 1 15:54:43 2020 - [debug] ok.
Sun Nov 1 15:54:44 2020 - [info] All SSH connection tests passed successfully.
4、测试 MySQL 主从连接情况，最后出现 MySQL Replication Health is OK 字样说明正常

masterha_check_repl -conf=/etc/masterha/app1.cnf
… #省略内容
IN SCRIPT TEST====/sbin/ifconfig ens33:1 down==/sbin/ifconfig ens33:1 20.0.0.200===

Checking the Status of the script… OK
Sun Nov 1 15:54:59 2020 - [info] OK.
Sun Nov 1 15:54:59 2020 - [warning] shutdown_script is not defined.
Sun Nov 1 15:54:59 2020 - [info] Got exit code 0 (Not master dead).

MySQL Replication Health is OK.

5、启动 MHA
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 &

6、查看 MHA 状态，可以看到当前的 master 是主服务器节点

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

7、查看 MHA 日志，也以看到当前的 master 是 20.0.0.22
cat /var/log/masterha/app1/manager.log

8、第一次配置vip的时候，需要在主服务器上创建虚拟IP地址

ifconfig ens33:1 20.0.0.200/24