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

    文章目录


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

    1. 何为高可用

    2. MHA介绍

    3. 实验环境介绍

    192.168.221.128      master
192.168.221.153      node01
192.168.221.136      node02

    • 1
    • 2
    • 3

    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

    • 1
    • 2
    • 3

    4.1.2 配置三台机器免密通信

    • 1、配置三台机器免密通信
      所有节点都执行生成密钥操作，以主库示例，所有节点都要操作
    cd /root/.ssh/
ssh-keygen -t dsa -P '' -f id_dsa
cat id_dsa.pub >> authorized_keys

    • 1
    • 2
    • 3
    • 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

    • 1
    • 2
    • 3、在主库上执行合并密钥的命令
    cat authorized_keys.3 >> authorized_keys
cat authorized_keys.4 >> authorized_keys

    • 1
    • 2
    • 4、在主库上将合并后的密钥文件发给其他节点
    scp authorized_keys 192.168.221.153:/root/.ssh/
scp authorized_keys 192.168.221.136:/root/.ssh/

    • 1
    • 2
      1. 所有节点在/etc/hosts文件中写入本地解析
    cat >> /etc/hosts << EOF
192.168.221.128      master
192.168.221.153      node01
192.168.221.136      node02
EOF

    • 1
    • 2
    • 3
    • 4
    • 5
    • 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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

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

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

    • 1
    • 2
    • 2、进入数据库，跳过密码【配置】
    在 /etc/my.cnf  最后一行添加免密登陆   
在[mysqld]后面任意一行添加“skip-grant-tables”用来跳过密码验证的过程
然后重启服务 systemctl restart mysqld

    • 1
    • 2
    • 3
    • 3、创建主从复制号
    create user 'gtid'@'192.168.221.%' identified by 'gtid123';
grant replication slave on *.* to 'gtid'@'192.168.221.%';
flush privileges;

    • 1
    • 2
    • 3
    • 4、创建管理账号
    create user 'manage'@'192.168.221.%' identified by 'manage123';
grant all privileges on *.* to 'manage'@'192.168.221.%';
flush privileges;

    • 1
    • 2
    • 3
    • 5、修改mysql root密码
    use mysql;
update mysql.user set authentication_string=password('123') where user='root';
flush privileges;

    • 1
    • 2
    • 3
    • 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 这行注释掉
		再次登录服务

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13

    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

    • 1
    • 2
    • 3
    • 4
    • 5
    • node01 节点
    #编辑 /etc/my.cnf文件
#skip-grant-tables
server-id=2
log-bin=master-bin
expire_logs_days=3

    • 1
    • 2
    • 3
    • 4
    • 5
    • node02 节点
    #编辑 /etc/my.cnf文件
#skip-grant-tables
server-id=3
log-bin=master-bin
expire_logs_days=3

    • 1
    • 2
    • 3
    • 4
    • 5
    • 重启三台服务
    systemctl restart mysqld

    • 1
    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模式了。

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 开启GTID（三台）
    mysql -uroot -p123
SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = WARN;

    • 1
    • 2

    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/

    • 1
    • 2
    • 3

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

    • 从库执行
    mysql -uroot -p < all.sql
mysql -uroot -p

    • 1
    • 2

    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)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    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;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 2、从库开启专用的复制线程
    > START SLAVE;

    • 1
    • 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
....

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15

    重点看这两个线程

        Slave_IO_Running: Yes
    Slave_SQL_Running: Yes

    • 1
    • 2
    • 可能会遇到的报错及解决方法
    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;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13

    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日志并重启服务
等等错误

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16

    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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    5.1.1 linux perl cpan 安装使用

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

    cpan>h                 #获取帮助

cpan>m　                  #获取模块

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

cpan>install　DBI      #安装模块

cpan>q                 #退出安装

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 安装管理节点
    cd /data/mha
tar -zxf mha4mysql-manager-0.58.tar.gz
cd mha4mysql-manager-0.58
perl Makefile.PL
make && make install

    • 1
    • 2
    • 3
    • 4
    • 5

    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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39

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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 76
    • 77
    • 78
    • 79
    • 80
    • 81
    • 82
    • 83
    • 84
    • 85
    • 86
    • 87
    • 88
    • 89
    • 90
    • 91
    • 92
    • 93
    • 94
    • 95
    • 96
    • 97
    • 98
    • 99
    • 100
    • 101
    • 102
    • 103
    chmod +x master_ip_failover

    • 1

    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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    chmod +x master_ip_online_change

    • 1

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

    5.4.1 检测所有主机的连通性

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

    • 1

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

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

    • 1
    • 2
    • 3
    • 4
    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


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 76
    • 77
    • 78
    • 79
    • 80
    • 81
    • 82
    • 83
    • 84
    • 85
    • 86
    • 87
    • 88
    • 89
    • 90
    • 91
    • 92
    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. 解决办法
  按上述步骤做免密钥

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 语言环境缺失导致报错
    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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    5.4.2 检测复制状态

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

    • 1

    1. 检查主从的状态，如下所示，注意观察是否有"error"信息，如果有需要解决后再执行之后的操作，下面3个节点都是"Alive Servers"，其中"Alive Slaves","Dead Servers"都可以一目了然的看清楚，当然还有一些参数监控等。

    2. #出现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模块依赖包




    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14

    5.5 在主库上添加VIP

    ip addr add 192.168.221.100/24 dev ens33

    • 1

    5.6 在管理节点启动MHA服务

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

    • 1

    5.6.1 检查MHA是否启动

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

    • 1

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

    6.1 模拟主库(192.168.221.133)故障

    6.2

  • 相关阅读:
    mysql与jdbc笔记
    深入理解XGBoost：集成学习与堆叠模型
    Linux上将进程、线程与CPU核绑定
    中国这么多 Java 开发者，应该诞生出生态级应用开发框架
    【vue】在vue项目中按顺序动态24个英文字母选项：A B C D E F......
    [多态设计模式]枚举
    【2013NOIP普及组】T2. 表达式求值 试题解析
    前端​Vue与uni-app中的九宫格、十二宫格和十五宫格菜单组件实现
    Java计算机毕业设计电影评分网站源码+系统+数据库+lw文档
    敏感型人格的特征，怎么改变敏感型性格？
  • 原文地址：https://blog.csdn.net/admin321123/article/details/127914348