MYSQL 高可用集群搭建 ---MHA

paperroll

已于 2023-01-28 16:40:56 修改

阅读量1.1k

点赞数 1

分类专栏：中间件文章标签： mysql 数据库服务器

于 2022-11-18 00:36:00 首次发布

本文链接：https://blog.csdn.net/admin321123/article/details/127914348

版权

中间件专栏收录该内容

2 篇文章 0 订阅

订阅专栏

文章目录

1. 何为高可用
2. MHA介绍
3. 实验环境介绍
4. MHA 搭建之基于GTID 主从复制
5. 基于GTID 主从复制部署MHA高可用
6. 模拟主库故障，故障切换
- 6.1 模拟主库(192.168.221.133)故障
- 6.2

mysql5.7一主两从+MHA高可用架构部署配置
数据库之MHA高可用集群部署及故障切换原理

1. 何为高可用

2. MHA介绍

3. 实验环境介绍

192.168.221.128      master
192.168.221.153      node01
192.168.221.136      node02

4. MHA 搭建之基于GTID 主从复制

4.1 前期准备工作

4.1.1 MHA下载

mkdir /data && cd /data
wget https://github.com/yoshinorim/mha4mysql-manager/releases/download/v0.58/mha4mysql-manager-0.58.tar.gz
wget https://github.com/yoshinorim/mha4mysql-node/releases/download/v0.58/mha4mysql-node-0.58.tar.gz

4.1.2 配置三台机器免密通信

1、配置三台机器免密通信
所有节点都执行生成密钥操作，以主库示例，所有节点都要操作

cd /root/.ssh/
ssh-keygen -t dsa -P '' -f id_dsa
cat id_dsa.pub >> authorized_keys

2、在主库上接收slave上的密钥

scp 192.168.221.153:/root/.ssh/authorized_keys ./authorized_keys.3
scp 192.168.221.136:/root/.ssh/authorized_keys ./authorized_keys.4

3、在主库上执行合并密钥的命令

cat authorized_keys.3 >> authorized_keys
cat authorized_keys.4 >> authorized_keys

4、在主库上将合并后的密钥文件发给其他节点

scp authorized_keys 192.168.221.153:/root/.ssh/
scp authorized_keys 192.168.221.136:/root/.ssh/

1. 所有节点在/etc/hosts文件中写入本地解析

cat >> /etc/hosts << EOF
192.168.221.128      master
192.168.221.153      node01
192.168.221.136      node02
EOF

6、测试能够互相ssh且不要密码，即成功！

4.2 搭建主从环境

4.2.1 部署mysql (三台)

1、安装部署

wget -i -c http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm
yum -y install mysql57-community-release-el7-10.noarch.rpm
yum -y install mysql-community-server --nogpgcheck 

systemctl start mysqld
systemctl enable mysqld

安装失败则查看之前是否安装myssql，如有安装，则卸载

rpm -qa|grep mysql
rpm -e mysql57-community-release-el7-11.noarch

2、进入数据库，跳过密码【配置】

在 /etc/my.cnf  最后一行添加免密登陆   
在[mysqld]后面任意一行添加“skip-grant-tables”用来跳过密码验证的过程
然后重启服务 systemctl restart mysqld

3、创建主从复制号

create user 'gtid'@'192.168.221.%' identified by 'gtid123';
grant replication slave on *.* to 'gtid'@'192.168.221.%';
flush privileges;

4、创建管理账号

create user 'manage'@'192.168.221.%' identified by 'manage123';
grant all privileges on *.* to 'manage'@'192.168.221.%';
flush privileges;

5、修改mysql root密码

use mysql;
update mysql.user set authentication_string=password('123') where user='root';
flush privileges;

6、设置密码不过期策略，不然会报【1862】错误

        vi /etc/my.cnf
　　    [mysqld]
　　    skip-grant-tables
　　    :wq! #保存退出
        # mysql -u root -p
          use mysql
          select * from mysql.user where user='root' \G
		查看#password_expired 把Y修改为N
		  update user set password_expired='N' where user='root';
          flush privileges;
          quit
		然后把 /etc/my.cnf 的 skip-grant-tables 这行注释掉
		再次登录服务

4.2.2 开启bin_log二进制日志及GTID

4.2.2.1 开启bin_log二进制日志

master 节点

#编辑 /etc/my.cnf文件
#skip-grant-tables
server-id=1
log-bin=master-bin
expire_logs_days=3

node01 节点

#编辑 /etc/my.cnf文件
#skip-grant-tables
server-id=2
log-bin=master-bin
expire_logs_days=3

node02 节点

#编辑 /etc/my.cnf文件
#skip-grant-tables
server-id=3
log-bin=master-bin
expire_logs_days=3

重启三台服务

systemctl restart mysqld

4.2.2.2 开启GTID

GTID 的作用

    GTID复制的作用:
   主要保证主从复制中的高级特性。
    GTID在MySQL 5.6版本中引入的新特性，但默认并没有开启。
    GTID在MySQL 5.7版本中即使不开启，也有匿名的GTID记录。
    GTID的优势:
        (1)为主库的dump线程传输可以提供并行的解决方案;
        (2)为从库的SQL线程可以提供并发"回放";
        (3)配置主从时方便，无需手动定位主库二进制日志文件名称及位置信息，而是交由MySQL自动去定位;
    温馨提示:
        MySQL 5.7.17+的版本以后几乎都是GTID模式了。

开启GTID（三台）

mysql -uroot -p123
SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = WARN;

4.2.3 配置基于GTID主从复制，开启主从同步

4.2.3.1 主库上复制数据到所有从库，完成在某个时刻GTID的同步

mysqldump --single-transaction -uroot -p -A > all.sql
scp all.sql node01:/root/
scp all.sql node02:/root/

4.2.3.2 在各从库上恢复备份并配置主从复制，开启主从同步

从库执行

mysql -uroot -p < all.sql
mysql -uroot -p

4.2.3.3 查看主库bin_log日志信息，准备链接主库

mysql> show master status\G
*************************** 1. row ***************************
             File: master-bin.000001
         Position: 154
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

4.2.3.4 从库链接主库信息（node01\node02）

1、从库配置主库的链接信息并确认复制起点

CHANGE MASTER TO
MASTER_HOST='192.168.221.128',
MASTER_USER='gtid',
MASTER_PASSWORD='gtid123',
MASTER_PORT=3306,
MASTER_LOG_FILE='master-bin.000001',
MASTER_LOG_POS=4,
MASTER_CONNECT_RETRY=10;

2、从库开启专用的复制线程

> START SLAVE;

3、验证主从复制状态

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.221.128
                  Master_User: gtid
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: master-bin.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: node01-relay-bin.000002
                Relay_Log_Pos: 369
        Relay_Master_Log_File: master-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
....

重点看这两个线程

    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes

可能会遇到的报错及解决方法

Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
错误原因：链接时输入的bin_log二进制名错了
解决办法：主库 show master status \G ，查看二进制名
stop slave;
CHANGE MASTER TO
MASTER_HOST='192.168.221.128',
MASTER_USER='gtid',
MASTER_PASSWORD='gtid123',
MASTER_PORT=3306,
MASTER_LOG_FILE='master-bin.xxxxx',
MASTER_LOG_POS=4,
MASTER_CONNECT_RETRY=10;
start slave;

4.3 其他报错信息解决思路

4.3.1 首先看当时的报错信息，报错代码。比如

# 1. 数据库root密码不对或者密码过期时间导致
mysqldump: Got error: 1045: Access denied for user 'root'@'localhost' (using password: YES) when trying to connect
解决办法：修改数据库root密码
use mysql;
update mysql.user set authentication_string=password('123') where user='root';
flush privileges;
修改密码过期时间
update user set password_expired='N' where user='root';
# 2. 没有配置mysql 的server id
ERROR 1794 (HY000): Slave is not configured or failed to initialize properly. You must at least set --server-id to enable either a master or a slave. Additional e
解决办法：编辑/etc/my.cnf
修改三台的server id 为不同数值
# 3. 没有打开bin_log日志
Got fatal error 1236 from master when reading data from binary log: 'Binary log is not open'
解决办法:配置文件如上,添加bin_log日志并重启服务
等等错误

4.3.2 其次看mysql 日志文件

日志文件所有的报错信息都很清楚，例如：

#截取部分
cat  /var/log/mysqld.log
# bin_log 日志没有开启
2022-11-17T23:14:39.667951Z 5 [ERROR] Error reading packet from server for channel '': Binary log is not open (server_errno=1236)
2022-11-17T23:14:39.667966Z 5 [ERROR] Slave I/O for channel '': Got fatal error 1236 from master when reading data from binary log: 'Binary log is not open', Error_code: 1236
#
2022-11-17T23:07:11.531128Z 0 [ERROR] InnoDB: Table `mysql`.`innodb_table_stats` not found.
2022-11-17T23:07:11.531162Z 0 [ERROR] InnoDB: Fetch of persistent statistics requested for table `mysql`.`gtid_executed` but the required system tables mysql.innodb_table_stats and mysql.i

4.3.3 推荐报错信息速查链接

Mysql-error code汇总
 常见报错收集

5. 基于GTID 主从复制部署MHA高可用

5.1 在所有节点安装MHA-Node节点

#在所有节点上安装数据节点
#首先安装 MySQL 依赖的 perl 环境
yum install perl-DBD-MySQL.x86_64 -y
cd /data/mha/
#解压 mha4mysql-node 包，并安装 perl-cpan
tar -zxf mha4mysql-node-0.58.tar.gz 
cd mha4mysql-node-0.58/
yum install perl-CPAN* -y
perl Makefile.PL
make && make install

5.1.1 linux perl cpan 安装使用

参考：https://blog.huati365.com/9a3be0b240ae4a99

cpan>h                 #获取帮助

cpan>m　                  #获取模块

cpan[1]> i /DBI/       #匹配查找

cpan>install　DBI      #安装模块

cpan>q                 #退出安装

5.2 安装配置 MHA-Manager 管理节点

以下操作都是在node02（192.168.221.136）上完成的

安装环境需要的介质包：

注意：我的操作系统是rhel7，下载软件时注意软件版本问题
yum install perl-DBD-MySQL*

wget ftp://ftp.pbone.net/mirror/ftp5.gwdg.de/pub/opensuse/repositories/home:/matthewdva:/build:/RedHat:/RHEL-7/complete/x86_64/perl-Params-Validate-1.08-4.el7.x86_64.rpm
rpm -ivh perl-Params-Validate-1.08-4.el7.x86_64.rpm 

wget ftp://ftp.pbone.net/mirror/ftp5.gwdg.de/pub/opensuse/repositories/home:/csbuild:/Perl/RHEL_7/noarch/perl-Config-Tiny-2.20-1.2.noarch.rpm
rpm -ivh perl-Config-Tiny-2.20-1.2.noarch.rpm

wget ftp://ftp.pbone.net/mirror/ftp5.gwdg.de/pub/opensuse/repositories/home:/csbuild:/Perl/RHEL_7/noarch/perl-Log-Dispatch-2.41-2.2.noarch.rpm
rpm -ivh perl-Log-Dispatch-2.41-2.2.noarch.rpm

wget ftp://ftp.pbone.net/mirror/download.fedora.redhat.com/pub/fedora/epel/7/aarch64/Packages/p/perl-Parallel-ForkManager-1.18-2.el7.noarch.rpm
rpm -ivh perl-Parallel-ForkManager-1.18-2.el7.noarch.rpm

安装管理节点

cd /data/mha
tar -zxf mha4mysql-manager-0.58.tar.gz
cd mha4mysql-manager-0.58
perl Makefile.PL
make && make install

5.3 配置MHA

5.3.1 MHA配置文件

以下操作都是在node02（192.168.221.136）上完成的

mkdir /etc/mha
mkdir -p /usr/local/mha
cd /etc/mha/
vim mha.conf
#####################################################写入配置
[server default]
user=manage
password=manage123
manager_log=/usr/local/mha/manager.log
manager_workdir=/usr/local/mha
master_binlog_dir=/mvtech/mysql/logs
remote_workdir=/usr/local/mha
ssh_user=root
repl_user=gtid
repl_password=gtid123
master_ip_failover_script=/usr/local/scripts/master_ip_failover
master_ip_online_change_script=/usr/local/scripts/master_ip_online_change
ping_interval=1

[server1]
hostname=192.168.221.128
ssh_port=22
master_binlog_dir=/data/mysql
candidate_master=1
port=3306

[server2]
candidate_master=1
hostname=192.168.221.153
ssh_port=22
master_binlog_dir=/data/mysql
port=3306

[server3]
hostname=192.168.221.136
ssh_port=22
master_binlog_dir=/data/mysql
no_master=1
port=3306

5.3.2 编辑 failover 切换脚本

mkdir /usr/local/mha/scripts
cd /usr/local/mha/scripts
vim master_ip_failover
#脚本内部的VIP和网卡需要根据自己的实际要求更改
###############################################写入
#!/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.221.100/24';  # Virtual IP 这里需要根据自己的环境修改
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;  

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 "\n\n\n***************************************************************\n";  
            print "Disabling the VIP - $vip on old master: $orig_master_host\n";  
            print "***************************************************************\n\n\n\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 "\n\n\n***************************************************************\n";  
            print "Enabling the VIP - $vip on new master: $new_master_host \n";  
            print "***************************************************************\n\n\n\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=po  
rt –new_master_host=host –new_master_ip=ip –new_master_port=port\n";  
}

chmod +x master_ip_failover

5.3.3 编写 online_change 脚本

vim master_ip_online_change

#注意VIP
###############################################写入
#/bin/bash  
source /root/.bash_profile  

vip=`echo '192.168.221.100/24'`  # Virtual IP  
key=`echo '1'`  

command=`echo "$1" | awk -F = '{print $2}'`  
orig_master_host=`echo "$2" | awk -F = '{print $2}'`  
new_master_host=`echo "$7" | awk -F = '{print $2}'`  
orig_master_ssh_user=`echo "${12}" | awk -F = '{print $2}'`  
new_master_ssh_user=`echo "${13}" | awk -F = '{print $2}'`  

stop_vip=`echo "ssh root@$orig_master_host /sbin/ifconfig  eth0:$key  down"`  
start_vip=`echo "ssh root@$new_master_host /sbin/ifconfig  eth0:$key  $vip"`  

if [ $command = 'stop' ]  
   then  
   echo -e "\n\n\n***************************************************************\n"  
   echo -e "Disabling the VIP - $vip on old master: $orig_master_host\n"  
   $stop_vip  
   if [ $? -eq 0 ]  
      then  
      echo "Disabled the VIP successfully"  
   else  
      echo "Disabled the VIP failed"  
   fi  
   echo -e "***************************************************************\n\n\n\n"  
fi  

if [ $command = 'start' -o $command = 'status' ]  
   then  
   echo -e "\n\n\n***************************************************************\n"  
   echo -e "Enabling the VIP - $vip on new master: $new_master_host \n"  
   $start_vip  
   if [ $? -eq 0 ]  
      then  
      echo "Enabled the VIP successfully"  
   else  
      echo "Enabled the VIP failed"  
   fi  
   echo -e "***************************************************************\n\n\n\n"  
fi

chmod +x master_ip_online_change

5.4 检查所有主机的连通性&复制状态

5.4.1 检测所有主机的连通性

/usr/local/bin/masterha_check_ssh --conf=/etc/mha/mha.conf

检查各个节点的互信状态，如下所示，注意观察是否有"error"信息，如果有需要解决后再执行之后的操作，下面3个节点有6个"ok"说明是正常状态。
在这里插入图片描述

5.4.1.1 因为安装包没有导致的错误

问题原因：没有对于的安装包，例如：
Can't locate Package/Stash.pm in @INC 
解决办法：
以下是安装包下载路径,建议全部安装，减少报错

cd /data/mha
wget https://github.com/yoshinorim/mha4mysql-node/releases/download/v0.58/mha4mysql-node-0.58-0.el7.centos.noarch.rpm

wget https://github.com/yoshinorim/mha4mysql-manager/releases/download/v0.58/mha4mysql-manager-0.58-0.el7.centos.noarch.rpm

wget http://mirror.centos.org/centos/6/os/x86_64/Packages/compat-db43-4.3.29-17.el6.x86_64.rpm
rpm -ivh --force compat-db43-4.3.29-17.el6.x86_64.rpm --nodeps

wget http://mirror.centos.org/centos/7/os/x86_64/Packages/perl-Config-Tiny-2.14-7.el7.noarch.rpm
rpm -ivh --force perl-Config-Tiny-2.14-7.el7.noarch.rpm --nodeps

wget http://download-ib01.fedoraproject.org/pub/epel/7/x86_64/Packages/p/perl-Email-Date-Format-1.002-15.el7.noarch.rpm
rpm -ivh --force perl-Email-Date-Format-1.002-15.el7.noarch.rpm  --nodeps

wget http://download-ib01.fedoraproject.org/pub/epel/7/x86_64/Packages/p/perl-Mail-Sendmail-0.79-21.el7.noarch.rpm
rpm -ivh --force perl-Mail-Sendmail-0.79-21.el7.noarch.rpm  --nodeps

wget http://download-ib01.fedoraproject.org/pub/epel/7/x86_64/Packages/p/perl-MIME-Types-1.38-2.el7.noarch.rpm
rpm -ivh --force perl-MIME-Types-1.38-2.el7.noarch.rpm  --nodeps

wget http://download-ib01.fedoraproject.org/pub/epel/7/x86_64/Packages/p/perl-MIME-Lite-3.030-1.el7.noarch.rpm
rpm -ivh --force perl-MIME-Lite-3.030-1.el7.noarch.rpm  --nodeps

wget http://download-ib01.fedoraproject.org/pub/epel/7/x86_64/Packages/p/perl-Parallel-ForkManager-1.18-2.el7.noarch.rpm
rpm -ivh --force perl-Parallel-ForkManager-1.18-2.el7.noarch.rpm  --nodeps

wget http://mirror.centos.org/centos/7/os/x86_64/Packages/perl-TimeDate-2.30-2.el7.noarch.rpm
rpm -ivh --force perl-TimeDate-2.30-2.el7.noarch.rpm  --nodeps

wget http://mirror.centos.org/centos/7/os/x86_64/Packages/perl-Params-Util-1.07-6.el7.x86_64.rpm
rpm -ivh --force perl-Params-Util-1.07-6.el7.x86_64.rpm --nodeps

wget http://mirror.centos.org/centos/7/os/x86_64/Packages/perl-Sub-Install-0.926-6.el7.noarch.rpm
rpm -ivh --force perl-Sub-Install-0.926-6.el7.noarch.rpm --nodeps

wget http://mirror.centos.org/centos/7/os/x86_64/Packages/perl-Data-OptList-0.107-9.el7.noarch.rpm
rpm -ivh --force perl-Data-OptList-0.107-9.el7.noarch.rpm  --nodeps

wget http://mirror.centos.org/centos/7/os/x86_64/Packages/perl-Module-Runtime-0.013-4.el7.noarch.rpm
rpm -ivh --force perl-Module-Runtime-0.013-4.el7.noarch.rpm  --nodeps

wget http://mirror.centos.org/centos/7/os/x86_64/Packages/perl-Try-Tiny-0.12-2.el7.noarch.rpm
rpm -ivh --force perl-Try-Tiny-0.12-2.el7.noarch.rpm  --nodeps

wget http://mirror.centos.org/centos/7/os/x86_64/Packages/perl-Module-Implementation-0.06-6.el7.noarch.rpm
rpm -ivh --force perl-Module-Implementation-0.06-6.el7.noarch.rpm  --nodeps

wget http://mirror.centos.org/centos/7/os/x86_64/Packages/perl-Package-Stash-XS-0.26-3.el7.x86_64.rpm
rpm -ivh --force perl-Package-Stash-XS-0.26-3.el7.x86_64.rpm --nodeps

wget wget http://mirror.centos.org/centos/7/os/x86_64/Packages/perl-List-MoreUtils-0.33-9.el7.x86_64.rpm
rpm -ivh --force perl-List-MoreUtils-0.33-9.el7.x86_64.rpm  --nodeps

wget http://mirror.centos.org/centos/7/os/x86_64/Packages/perl-Package-DeprecationManager-0.13-7.el7.noarch.rpm
rpm -ivh --force perl-Package-DeprecationManager-0.13-7.el7.noarch.rpm  --nodeps

wget http://mirror.centos.org/centos/7/os/x86_64/Packages/perl-Package-Stash-0.34-2.el7.noarch.rpm
rpm -ivh --force perl-Package-Stash-0.34-2.el7.noarch.rpm --nodeps

wget http://mirror.centos.org/centos/7/os/x86_64/Packages/perl-Class-Load-0.20-3.el7.noarch.rpm
rpm -ivh --force  -- force perl-Class-Load-0.20-3.el7.noarch.rpm --nodeps

wget http://download-ib01.fedoraproject.org/pub/epel/7/x86_64/Packages/p/perl-Email-Date-Format-1.002-15.el7.noarch.rpm
rpm -ivh --force perl-Email-Date-Format-1.002-15.el7.noarch.rpm  --nodeps

wget http://mirror.centos.org/centos/7/os/x86_64/Packages/perl-Params-Validate-1.08-4.el7.x86_64.rpm
rpm -ivh --force perl-Params-Validate-1.08-4.el7.x86_64.rpm --nodeps

wget http://mirror.centos.org/centos/7/os/x86_64/Packages/perl-Sys-Syslog-0.33-3.el7.x86_64.rpm
rpm -ivh --force perl-Sys-Syslog-0.33-3.el7.x86_64.rpm --nodeps

wget http://mirror.centos.org/centos/7/os/x86_64/Packages/perl-Time-HiRes-1.9725-3.el7.x86_64.rpm
rpm -ivh --force perl-Time-HiRes-1.9725-3.el7.x86_64.rpm --nodeps

wget http://mirror.centos.org/centos/7/os/x86_64/Packages/perl-Net-SMTP-SSL-1.01-13.el7.noarch.rpm
rpm -ivh --force perl-Net-SMTP-SSL-1.01-13.el7.noarch.rpm --nodeps

wget http://mirror.centos.org/centos/7/os/x86_64/Packages/perl-Params-Validate-1.08-4.el7.x86_64.rpm
rpm -ivh --force perl-Params-Validate-1.08-4.el7.x86_64.rpm --nodeps

wget http://download-ib01.fedoraproject.org/pub/epel/7/x86_64/Packages/p/perl-Mail-Sender-0.8.23-1.el7.noarch.rpm
rpm -ivh --force perl-Mail-Sender-0.8.23-1.el7.noarch.rpm --nodeps

wget http://mirror.centos.org/centos/7/os/x86_64/Packages/perl-MailTools-2.12-2.el7.noarch.rpm
rpm -ivh --force perl-MailTools-2.12-2.el7.noarch.rpm --nodeps

wget http://download-ib01.fedoraproject.org/pub/epel/7/x86_64/Packages/p/perl-Log-Dispatch-2.41-1.el7.1.noarch.rpm
rpm -ivh --force perl-Log-Dispatch-2.41-1.el7.1.noarch.rpm --nodeps

wget http://download-ib01.fedoraproject.org/pub/epel/7/x86_64/Packages/p/perl-MIME-Lite-HTML-1.24-8.el7.noarch.rpm
rpm -ivh --force perl-MIME-Lite-HTML-1.24-8.el7.noarch.rpm --nodeps

5.4.1.2 其他报错

报错信息收集
1
机器互通报错

Compilation failed in require at /usr/local/share/perl5/MHA/SSHCheck.pm line 29.
BEGIN failed--compilation aborted at /usr/local/share/perl5/MHA/SSHCheck.pm line 29.
 1. 问题原因
 原因分析，程序需要从manage管理ssh连接，所以会从mysql-test3 ssh到 mysql-test 再ssh到 mysql-test2，问题出在第二次连接，需要输入key的密码，导致测试失败。所以全部机器都要相互做密钥登录。
 2. 解决办法
  按上述步骤做免密钥

语言环境缺失导致报错

Compilation failed in require at /usr/local/bin/masterha_check_ssh line 25.
BEGIN failed--compilation aborted at /usr/local/bin/masterha_check_ssh line 25.
 1. 问题原因
 出现这种问题是因为缺少perl-Mail-Sender和 perl-Log-Dispatch这两个语言环境
 2. 解决办法
yum install perl-Mail-Sender
yum install perl-Log-Dispatch

5.4.2 检测复制状态

/usr/local/bin/masterha_check_repl --conf=/etc/mha/mha.conf

检查主从的状态，如下所示，注意观察是否有"error"信息，如果有需要解决后再执行之后的操作，下面3个节点都是"Alive Servers"，其中"Alive Slaves","Dead Servers"都可以一目了然的看清楚，当然还有一些参数监控等。
#出现MySQL Replication Health is NOT OK!的，可以去看一下mysql服务器上的软链接是否少创建–>本文位置：修改三台MySQL服务器的主配置文件/etc/my.cnf，并创建命令软链接

5.4.2.1 MHA常见报错及解决方法

参考1：常见报错及解决方法
 参考2
MySQL 有关MHA搭建与切换的几个错误log

5.4.2.2 可能遇到的报错

查找错误：根据MasterMonitor.pm, ln *** 这个信息去查。
在这里插入图片描述

Failed to save binary log: Binlog not found from /data/mysql! If you got this error at MHA Manager, please set "master_binlog_dir=/path/to/binlog_directory_of_the_master" correctly in the MHA Manager's configuration file and try again.
#错误一：
Sun Nov 20 22:46:43 2022 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln161] Binlog setting check failed!
# 解决办法
分析：set global event_scheduler=off; 主从都要关闭. (特别提醒：从节点不关闭一样报错)
#错误二：
[error][/usr/local/share/perl5/MHA/ServerManager.pm, ln301] install_driver(mysql) failed: Attempt to reload DBD/mysql.pm aborted.
Compilation failed in require at (eval 37) line 3.
# 解决办法
缺少安装MHA依赖包：libdbd-mysql-perl
安装Perl模块依赖包

5.5 在主库上添加VIP

ip addr add 192.168.221.100/24 dev ens33

5.6 在管理节点启动MHA服务

nohup masterha_manager --conf=/etc/mha/mha.conf > /tmp/mha_manager.log < /dev/null 2>&1 &

5.6.1 检查MHA是否启动

masterha_check_status --conf=/etc/mha/mha.conf

6. 模拟主库故障，故障切换

6.1 模拟主库(192.168.221.133)故障

6.2

paperroll

关注

1
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
MYSQL 高可用集群搭建 ---MHA

mysql mha 高可用
复制链接

扫一扫

专栏目录