Mysql 搭建MHA高可用架构,实现自动failover,完成主从切换

目录

自动failover

MHA:

MHA 服务

项目:搭建Mysql主从复制、MHA高可用架构

实验项目IP地址配置:

MHA下载地址

项目步骤: 

一、修改主机名

二、编写一键安装mha node脚本和一键安装mha mangaer脚本,并执行安装

三、搭建Mysql主从复制集群(注意所有的Mysql主从复制机器都需要打开二进制日志,可以实现自动故障切换)

四、将安装包 mha4mysql-node和 脚本一键安装mha node脚本传输给Mysql主从复制集群,并运行脚本安装(下载依赖的时候最好翻墙下载)

4.1、mha_manger发送一键安装mha_node的脚本给mysql主从复制集群

4.2、mha_manger上需要配置有mha4mysql-manager-0.58.tar.gz和mha4mysql-node-0.58.tar.gz安装包和对应的一键安装脚本,首先执行一键安装mha4mysql-node-0.58的脚本

 4.3、mysql主从复制集群运行一键安装mha4mysql-node-0.58的脚本

五、所有服务器互相建立免密通道

5.1、mha manager对所有mysql服务器建立免密通道

5.2、master对slave1、slave2建立免密通道

5.3、slave1对master、slave2建立免密通道

 5.4、slave2对master、slave1建立免密通道

六、在Mysql的主从复制服务器里,配置mha相关信息

6.1、所有mysql服务器(master、slave1、slave2)将mysql命令和mysqlbinlog二进制文件操作命令软链接到/usr/sbin,方便manager管理节点,因为/usr/sbin/ 目录下可以被直接调用。

6.2、所有mysql服务器新建允许manager访问的授权用户mha,密码123456

七、在mha manager节点上配置好相关脚本、管理节点服务器

7.1、mha manager节点上复制相关脚本到/usr/local/bin下

7.2、复制自动切换时vip管理的脚本到/usr/local/bin下

7.3、修改master_ip_failover文件内容,配置vip(只配置vip(192.168.2.227)相关参数,其他默认不修改)

7.4、创建 MHA 软件目录并复制配置文件,使用app1.cnf配置文件来管理 mysql 节点服务器,配置文件一般放在/etc/目录下

7.5、master服务器上手工开启vip

7.6、测试:manager节点上测试ssh免密通道,如果正常最后会输出successfully

7.7、manager节点后台开启MHA

八、故障转移效果测试,模拟matser宕机,指定slave1成为新的master

8.1、模拟master宕机,停掉master

8.2、查看自动故障检测的效果

8.3、查看/etc/masterha/app1.cnf文件是否发生改变

8.4、再来看看slave2的master_info信息(确定master服务转移到了salve1上)

九、原master故障修复(原master转为slave,指向slave1)

9.1、原master开启mysqld

9.2、修复主从,原master修改master_info指向新的master(原slave1)

9.3、在 manager 节点上修改配置文件/etc/masterha/app1.cnf(再把这个记录添加进去,因为master宕机后原来的server1会被自动删除)

9.4、重启mha manager,并检查此时的master


自动failover

自动故障切换(Automatic Failover)是一种系统设计和配置策略,旨在在出现故障时自动将服务从一个失败的节点转移到另一个健康的节点,以保持系统的可用性。自动故障切换通常用于分布式系统、数据库集群、高可用性架构等场景,以减少系统停机时间并确保业务连续性。

MHA:

MHA(Master HA)是一款开源的 MySQL 的高可用程序,它为 MySQL主从复制架构提供了 automating master failover (自动化主故障转移)功能MHA 在监控到 master 节点故障时,会 提升其中拥有最新数据的 slave 节点成为新的master 节点,在此期间,MHA 会通过于其它从节 点获取额外信息来避免一致性方面的问题。MHA 还提供了 master 节点的在线切换功能,即按需 切换 master/slave 节点。  

参考:MYSQL高可用架构之MHA实战一 数据库主从配置(真实可用)_51CTO博客_mysql数据库主从搭建

基于mycat2+mha+keepalived的半同步主从复制MySQL cluster_mb643815027e44d的技术博客_51CTO博客

MHA 服务

MHA 服务有两种角色, MHA Manager(管理节点)和 MHA Node(数据节点):

MHA Manager: 通常单独部署在一台独立机器上管理多个 master/slave 集群(组),每个 master/slave 集群称作一个 application,用来管理统筹整个集群。

MHA node: 运行在每台 MySQL 服务器上(master/slave/manager),它通过监控具备解析和清理

logs 功能的脚本来加快故障转移: 主要是接收管理节点所发出指令的代理,代理需要运行在每一个 mysql 节点上。简单讲 node 就是用来收集从节点服务器上所生成的 bin-log对比打算提升为新的主节点之上的从节点的是否拥有并完成操作,如果没有发给新主节点在本地应用后提升为主节点。

在MHA自动故障切换过程中,MHA试图从宕机的主服务器上保存二进制日志,最大程度的 保证数据的不丢失,但这并不总是可行的。例如,如果主服务器硬件故障或无法通过ssh访 问,MHA没法保存二进制日志,只进行故障转移而丢失了最新的数据。使用MySQL 5.7的半同步复制,可以大大降低数据丢失的风险。MHA可以与半同步复制结合起来。如果只有 一个slave已经收到了最新的二进制日志,MHA可以将最新的二进制日志应用于其他所有的 slave服务器上,因此可以保证所有节点的数据一致性。


由上图我们可以看出,每个复制组内部和 Manager 之间都需要ssh实现无密码互连,只有这样, 在 Master 出故障时, Manager 才能顺利的连接进去,实现主从切换功能。 

[root@mysql-1 ~]# ps aux|grep mysql
root       3269  0.0  0.1 115536  1688 ?        S    8月12   0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql --pid-file=/data/mysql/localhost.localdomain.pid
mysql      3506  0.6 27.1 1603328 270316 ?      Sl   8月12   5:28 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/mysql/mysql_error.log --open-files-limit=8192 --pid-file=/data/mysql/localhost.localdomain.pid --socket=/data/mysql/mysql.sock --port=3306
root      20706  0.0  0.1 115536  1700 ?        S    04:21   0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql --pid-file=/data/mysql/mysql-1.pid
mysql     20945  1.7 19.2 975992 191756 ?       Sl   04:21   0:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/mysql/mysql_error.log --open-files-limit=8192 --pid-file=/data/mysql/mysql-1.pid --socket=/data/mysql/mysql.sock --port=3306
root      21042  0.0  0.1 115408  1648 ?        Ss   04:22   0:00 /bin/sh /etc/rc.d/init.d/mysqld start
root      21047  0.0  0.1 115536  1700 ?        S    04:22   0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql --pid-file=/data/mysql/mysql-1.pid
mysql     21286  2.2 19.2 975992 191820 ?       Sl   04:22   0:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/mysql/mysql_error.log --open-files-limit=8192 --pid-file=/data/mysql/mysql-1.pid --socket=/data/mysql/mysql.sock --port=3306
root      21340  0.0  0.0 112824   988 pts/1    R+   04:22   0:00 grep --color=auto mysql

项目:搭建Mysql主从复制、MHA高可用架构

实验项目IP地址配置:

mha_manager:

manager:192.168.2.141    #用于监控管理

vip:192.168.2.227

mha_node:

master:192.168.2.150    #开启 bin-log relay-log

slave-1:192.168.2.151   #开启 bin-log relay-log

slave-2:192.168.2.152   #开启 bin-log relay-log

MHA下载地址

mha4mysql-manager-0.58.tar.gz:

wget https://github.com/yoshinorim/mha4mysql-manager/releases/download/v0.58/mha4mysql-manager-0.58.tar.gz

mha4mysql-node-0.58.tar.gz:

wget https://github.com/yoshinorim/mha4mysql-node/releases/download/v0.58/mha4mysql-node-0.58.tar.gz

mha4mysql安装包集合

链接:https://pan.baidu.com/s/1cyM1syv8NjwOW8ExR0E21Q?pwd=z52d 
提取码:z52d

[root@mha_manager ~]#  ls
anaconda-ks.cfg  mha4mysql-manager-0.58.tar.gz  mha4mysql-node-0.58.tar.gz
[root@mha_manager ~]# 

项目步骤: 

一、修改主机名

[root@web-3 ~]# hostnamectl set-hostname mha_manager
[root@web-3 ~]# su -
上一次登录:五 8月 11 13:28:25 CST 2023从 192.168.2.7pts/0 上
[root@mha_manager ~]# 

二、编写一键安装mha node脚本和一键安装mha mangaer脚本,并执行安装

一键安装mha node脚本

[root@mha_manager ~]# cat onekey_install_mha_node.sh 
#查看可以安装或者已安装的rpm包,并且作缓存
yum list#下载epel源
yum install epel-release --nogpgcheck -y#下载依赖包
yum install -y perl-DBD-MySQL \
perl-Config-Tiny \
perl-Log-Dispatch \
perl-Parallel-ForkManager \
perl-ExtUtils-CBuilder \
perl-ExtUtils-MakeMaker \
perl-CPAN#软件包mha4mysql-node-0.58.tar.gz放入/root目录下
cd ~
tar zxvf mha4mysql-node-0.58.tar.gz
cd mha4mysql-node-0.58#编译安装
perl Makefile.PL
make && make install

一键安装mha mangaer脚本

[root@mha_manager ~]# cat onekey_install_mha_manager.sh 
#查看可以安装或者已安装的rpm包,并且作缓存
yum list#下载epel源
yum install epel-release --nogpgcheck -y#下载依赖包
yum install -y perl-DBD-MySQL \
perl-Config-Tiny \
perl-Log-Dispatch \
perl-Parallel-ForkManager \
perl-ExtUtils-CBuilder \
perl-ExtUtils-MakeMaker \
perl-CPAN#软件包mha4mysql-manager-0.58.tar.gz放入/root目录下
cd ~
tar zxvf mha4mysql-manager-0.58.tar.gz
cd mha4mysql-manager-0.58#编译安装
perl Makefile.PL
make && make install

三、搭建Mysql主从复制集群(注意所有的Mysql主从复制机器都需要打开二进制日志,可以实现自动故障切换

参考:Mysql - 配置Mysql主从复制-keepalived高可用-读写分离集群_Claylpf的博客-CSDN博客

四、将安装包 mha4mysql-node和 脚本一键安装mha node脚本传输给Mysql主从复制集群,并运行脚本安装(下载依赖的时候最好翻墙下载)

4.1、mha_manger发送一键安装mha_node的脚本给mysql主从复制集群

[root@mha_manager ~]# scp onekey_install_mha_node.sh root@192.168.2.150:~
The authenticity of host '192.168.2.150 (192.168.2.150)' can't be established.
ECDSA key fingerprint is SHA256:rUDllK9IdVfMva40nDGHGyHLkpuXrHJyRHRPuLbkkv8.
ECDSA key fingerprint is MD5:6d:46:aa:d1:48:87:92:8b:14:ca:d2:18:af:3b:89:51.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.2.150' (ECDSA) to the list of known hosts.
root@192.168.2.150's password: 
onekey_install_mha_node.sh                                                                                                                                 100%  481   745.0KB/s   00:00    
[root@mha_manager ~]# scp onekey_install_mha_node.sh root@192.168.2.151:~
The authenticity of host '192.168.2.151 (192.168.2.151)' can't be established.
ECDSA key fingerprint is SHA256:3SsW//YjcK0UTRAlQkOUcqMcFMaQEhZ1xRSUgHRs/JQ.
ECDSA key fingerprint is MD5:58:8e:3f:27:fb:f5:4e:83:56:70:e6:fd:f7:d0:9d:17.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.2.151' (ECDSA) to the list of known hosts.
root@192.168.2.151's password: 
onekey_install_mha_node.sh                                                                                                                                 100%  481   287.5KB/s   00:00    
[root@mha_manager ~]# scp onekey_install_mha_node.sh root@192.168.2.152:~
The authenticity of host '192.168.2.152 (192.168.2.152)' can't be established.
ECDSA key fingerprint is SHA256:t7FSFcUpEOJYIGkZo1HvvfqhsezGEz7WEScc4KTgQDU.
ECDSA key fingerprint is MD5:7c:68:1c:c3:aa:a5:34:b7:f7:4b:18:0b:93:fb:a6:76.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.2.152' (ECDSA) to the list of known hosts.
root@192.168.2.152's password: 
onekey_install_mha_node.sh                                                                                                                                 100%  481   397.8KB/s   00:00    
[root@mha_manager ~]# 

4.2、mha_manger上需要配置有mha4mysql-manager-0.58.tar.gz和mha4mysql-node-0.58.tar.gz安装包和对应的一键安装脚本,首先执行一键安装mha4mysql-node-0.58的脚本

[root@mha_manager ~]# bash onekey_install_mha_node.sh 

再执行一键安装mha4mysql-manager-0.58的脚本

[root@mha_manager ~]# bash onekey_install_mha_manager.sh 

最后输出 如下所示 表示成功

Appending installation info to /root/perl5/lib/perl5/x86_64-linux-thread-multi/perllocal.pod

 4.3、mysql主从复制集群运行一键安装mha4mysql-node-0.58的脚本

[root@mysql-1 ~]# bash onekey_install_mha_node.sh [root@mysql-2 ~]# bash onekey_install_mha_node.sh [root@mysql-3 ~]# bash onekey_install_mha_node.sh 

注:下载依赖包perl的时候如果无法成功下载,可以尝试转换源为:The Comprehensive Perl Archive Network - www.cpan.org ,并翻墙下载

五、所有服务器互相建立免密通道

参考:Linux - SSH服务 - SCP - 免密通道建立_linux ssh服务状态_Claylpf的博客-CSDN博客

5.1、mha manager对所有mysql服务器建立免密通道

[root@mha_manager .ssh]# ssh-keygen
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa): 
/root/.ssh/id_rsa already exists.
Overwrite (y/n)? y
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:36631NGvhLwX3HXPFgkfo8t/C0g+k59hqkGi1cn0/cA root@mha_manager
The key's randomart image is:
+---[RSA 2048]----+
|                 |
|             . o |
|         .    + +|
|        + o o. =o|
|       oS= o.Eoo*|
|      o o.ooo==.*|
|     .   ..*=+++.|
|          .oBo=o.|
|         .o++=..o|
+----[SHA256]-----+
[root@mha_manager .ssh]# ssh-copy-id root@192.168.2.150
/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/root/.ssh/id_rsa.pub"
/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/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.2.150's password: Number of key(s) added: 1Now try logging into the machine, with:   "ssh 'root@192.168.2.150'"
and check to make sure that only the key(s) you wanted were added.[root@mha_manager .ssh]# [root@mha_manager .ssh]# ssh-copy-id root@192.168.2.151[root@mha_manager .ssh]# ssh-copy-id root@192.168.2.152

5.2、master对slave1、slave2建立免密通道

[root@mysql-1 ~]# ssh-keygen
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:rB6Rg0nbJCHYxWxuBafl4HDB8+1RuuOpHC9/5LYRTAI root@mysql-1
The key's randomart image is:
+---[RSA 2048]----+
| oo=BoE          |
|. .=*B..  .      |
|   o=+o..o.      |
|   .oB.o++       |
|   .+ =.Soo      |
|       ++ ..     |
|      +. =.      |
|     o.+o +.     |
|      ++oo..     |
+----[SHA256]-----+
[root@mysql-1 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub 192.168.2.151
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/root/.ssh/id_rsa.pub"
/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.2.151's password: Number of key(s) added: 1Now try logging into the machine, with:   "ssh '192.168.2.151'"
and check to make sure that only the key(s) you wanted were added.[root@mysql-1 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub 192.168.2.152
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/root/.ssh/id_rsa.pub"
/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.2.152's password: Number of key(s) added: 1Now try logging into the machine, with:   "ssh '192.168.2.152'"
and check to make sure that only the key(s) you wanted were added.[root@mysql-1 ~]# 

5.3、slave1对master、slave2建立免密通道

[root@mysql-2 ~]# ssh-keygen
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:MMCE8STghhwmha65CVG/w3/9k8/T96sFfcr75CFMTGs root@mysql-2
The key's randomart image is:
+---[RSA 2048]----+
|o*+=+            |
|B ++..           |
|o= .. o      .   |
|o.  .  o    o o  |
|.o . .  S    E ..|
|+   +       +....|
|.o   o   .   +o+.|
|o     . . . o.+++|
|       .   ..+=+B|
+----[SHA256]-----+
[root@mysql-2 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub 192.168.2.150
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/root/.ssh/id_rsa.pub"
The authenticity of host '192.168.2.150 (192.168.2.150)' can't be established.
ECDSA key fingerprint is SHA256:rUDllK9IdVfMva40nDGHGyHLkpuXrHJyRHRPuLbkkv8.
ECDSA key fingerprint is MD5:6d:46:aa:d1:48:87:92:8b:14:ca:d2:18:af:3b:89:51.
Are you sure you want to continue connecting (yes/no)? 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.2.150's password: Number of key(s) added: 1Now try logging into the machine, with:   "ssh '192.168.2.150'"
and check to make sure that only the key(s) you wanted were added.[root@mysql-2 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub 192.168.2.152
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/root/.ssh/id_rsa.pub"
The authenticity of host '192.168.2.152 (192.168.2.152)' can't be established.
ECDSA key fingerprint is SHA256:t7FSFcUpEOJYIGkZo1HvvfqhsezGEz7WEScc4KTgQDU.
ECDSA key fingerprint is MD5:7c:68:1c:c3:aa:a5:34:b7:f7:4b:18:0b:93:fb:a6:76.
Are you sure you want to continue connecting (yes/no)? 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.2.152's password: 
Permission denied, please try again.
root@192.168.2.152's password: Number of key(s) added: 1Now try logging into the machine, with:   "ssh '192.168.2.152'"
and check to make sure that only the key(s) you wanted were added.[root@mysql-2 ~]# 

 5.4、slave2对master、slave1建立免密通道

[root@mysql-3 ~]# ssh-keygen
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:m6F9WyLFkNnweKy2ERj3LflPDHqU5ZUL+S8FpCbXhtw root@mysql-3
The key's randomart image is:
+---[RSA 2048]----+
|      . o    .+ o|
|       + X + @.o |
|      . * X @ E..|
|         * O + o.|
|        S + o o..|
|       + B . o. .|
|      . * o . .. |
|         o +     |
|          .      |
+----[SHA256]-----+
[root@mysql-3 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub 192.168.2.150
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/root/.ssh/id_rsa.pub"
The authenticity of host '192.168.2.150 (192.168.2.150)' can't be established.
ECDSA key fingerprint is SHA256:rUDllK9IdVfMva40nDGHGyHLkpuXrHJyRHRPuLbkkv8.
ECDSA key fingerprint is MD5:6d:46:aa:d1:48:87:92:8b:14:ca:d2:18:af:3b:89:51.
Are you sure you want to continue connecting (yes/no)? 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.2.150's password: Number of key(s) added: 1Now try logging into the machine, with:   "ssh '192.168.2.150'"
and check to make sure that only the key(s) you wanted were added.[root@mysql-3 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub 192.168.2.151
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/root/.ssh/id_rsa.pub"
The authenticity of host '192.168.2.151 (192.168.2.151)' can't be established.
ECDSA key fingerprint is SHA256:3SsW//YjcK0UTRAlQkOUcqMcFMaQEhZ1xRSUgHRs/JQ.
ECDSA key fingerprint is MD5:58:8e:3f:27:fb:f5:4e:83:56:70:e6:fd:f7:d0:9d:17.
Are you sure you want to continue connecting (yes/no)? 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.2.151's password: Number of key(s) added: 1Now try logging into the machine, with:   "ssh '192.168.2.151'"
and check to make sure that only the key(s) you wanted were added.[root@mysql-3 ~]# 

六、在Mysql的主从复制服务器里,配置mha相关信息

6.1、所有mysql服务器(master、slave1、slave2)将mysql命令和mysqlbinlog二进制文件操作命令软链接到/usr/sbin,方便manager管理节点,因为/usr/sbin/ 目录下可以被直接调用。

[root@mysql-1 ~]# ln -s /usr/local/mysql/bin/mysql /usr/sbin/
[root@mysql-1 ~]# ln -s /usr/local/mysql/bin/mysqlbinlog /usr/sbin/[root@mysql-2 ~]# ln -s /usr/local/mysql/bin/mysql /usr/sbin/
[root@mysql-2 ~]# ln -s /usr/local/mysql/bin/mysqlbinlog /usr/sbin/[root@mysql-3 ~]# ln -s /usr/local/mysql/bin/mysql /usr/sbin/
[root@mysql-3 ~]# ln -s /usr/local/mysql/bin/mysqlbinlog /usr/sbin/

6.2、所有mysql服务器新建允许manager访问的授权用户mha,密码123456

root@(none) 17:21  mysql>grant all on *.* to 'mha'@'192.168.2.%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.01 sec)root@(none) 17:21  mysql>grant all on *.* to 'mha'@'192.168.2.150' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)root@(none) 17:22  mysql>grant all on *.* to 'mha'@'192.168.2.151' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)root@(none) 17:22  mysql>grant all on *.* to 'mha'@'192.168.2.152' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)root@(none) 17:22  mysql>select user,host from mysql.user;
+---------------+---------------+
| user          | host          |
+---------------+---------------+
| claylpf       | %             |
| sc_slave      | %             |
| mha           | 192.168.2.%   |
| mha           | 192.168.2.150 |
| mha           | 192.168.2.151 |
| mha           | 192.168.2.152 |
| mysql.session | localhost     |
| mysql.sys     | localhost     |
| root          | localhost     |
+---------------+---------------+
9 rows in set (0.00 sec)root@(none) 17:22  mysql>

七、在mha manager节点上配置好相关脚本、管理节点服务器

7.1、mha manager节点上复制相关脚本到/usr/local/bin下

[root@mha_manager ~]# cp -rp /root/mha4mysql-manager-0.58/samples/scripts/ /usr/local/bin/
[root@mha_manager ~]# cd /usr/local/bin/
[root@mha_manager bin]# ls
scripts
[root@mha_manager bin]# 
[root@mha_manager bin]# cd scripts/
[root@mha_manager scripts]# ls
master_ip_failover  master_ip_online_change  power_manager  send_report
[root@mha_manager scripts]# 

7.2、复制自动切换时vip管理的脚本到/usr/local/bin下

[root@mha_manager scripts]# cp /usr/local/bin/scripts/master_ip_failover /usr/local/bin/
[root@mha_manager scripts]# ls
master_ip_failover  master_ip_online_change  power_manager  send_report
[root@mha_manager scripts]# cd ..

7.3、修改master_ip_failover文件内容,配置vip(只配置vip(192.168.2.227)相关参数,其他默认不修改)

[root@mha_manager bin]# ls
master_ip_failover  scripts
[root@mha_manager bin]# >/usr/local/bin/master_ip_failover  #清空文件内容,复制以下内容
[root@mha_manager bin]# vim master_ip_failover 
[root@mha_manager bin]# cat 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.2.227';								#指定vip的地址,自己指定
my $brdc = '192.168.2.255';								#指定vip的广播地址
my $ifdev = 'ens33';										#指定vip绑定的网卡
my $key = '1';												#指定vip绑定的虚拟网卡序列号
my $ssh_start_vip = "/sbin/ifconfig ens33:$key $vip";		#代表此变量值为ifconfig ens33:1 192.168.2.227
my $ssh_stop_vip = "/sbin/ifconfig ens33:$key down";		#代表此变量值为ifconfig ens33:1 192.168.2.227 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";
##################################################################################
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" ) {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=port\n";
}[root@mha_manager bin]# 

7.4、创建 MHA 软件目录并复制配置文件,使用app1.cnf配置文件来管理 mysql 节点服务器,配置文件一般放在/etc/目录下

注意:注释只是提示用,编辑配置文件时最好不要加注释,否则很可能会出错

[root@mha_manager bin]# mkdir /etc/masterha
[root@mha_manager bin]# cp /root/mha4mysql-manager-0.58/samples/conf/app1.cnf /etc/masterha/
[root@mha_manager bin]# cd /etc/masterha/
[root@mha_manager masterha]# ls
app1.cnf
[root@mha_manager masterha]# >app1.cnf #清空原有内容
[root@mha_manager masterha]# vim app1.cnf [server default]
manager_log=/var/log/masterha/app1/manager.log       #manager日志
manager_workdir=/var/log/masterha/app1.log    		#manager工作目录
master_binlog_dir=/data/mysql/          #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  #设置手动切换时候的切换脚本
user=mha					#设置监控用户mha
password=123456			#设置mysql中mha用户的密码,这个密码是前文中创建监控用户的那个密码
ping_interval=1				#设置监控主库,发送ping包的时间间隔1秒,默认是3秒,尝试三次没有回应的时候自动进行failover
remote_workdir=/tmp			#设置远端mysql在发生切换时binlog的保存位置
repl_user=slave			#设置复制用户的用户slave
repl_password=123456		#设置复制用户slave的密码
report_script=/usr/local/send_report     #设置发生切换后发送的报警的脚本
secondary_check_script=/usr/local/bin/masterha_secondary_check -s 192.168.2.151 -s 192.168.2.152	#指定检查的从服务器IP地址
shutdown_script=""			#设置故障发生后关闭故障主机脚本(该脚本的主要作用是关闭主机防止发生脑裂,这里没有使用)
ssh_user=root				#设置ssh的登录用户名[server1]
#master
hostname=192.168.2.150 
port=3306[server2]
#slave1
hostname=192.168.2.151
port=3306
candidate_master=1
#设置为候选master,设置该参数以后,发生主从切换以后将会将此从库提升为主库,即使这个主库不是集群中最新的slavecheck_repl_delay=0
#默认情况下如果一个slave落后master 超过100M的relay logs的话,MHA将不会选择该slave作为一个新的master, 因为对于这个slave的恢复需要花费很长时间;通过设置check_repl_delay=0,MHA触发切换在选择一个新的master的时候将会忽略复制延时,这个参数对于设置了candidate_master=1的主机非常有用,因为这个候选主在切换的过程中一定是新的master[server3]
#slave2
hostname=192.168.2.152
port=3306

7.5、master服务器上手工开启vip

[root@mysql-1 ~]# ifconfig ens33:1 192.168.2.227/24
[root@mysql-1 ~]# ip add
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00inet 127.0.0.1/8 scope host lovalid_lft forever preferred_lft foreverinet6 ::1/128 scope host valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000link/ether 00:0c:29:61:50:77 brd ff:ff:ff:ff:ff:ffinet 192.168.2.150/24 brd 192.168.2.255 scope global noprefixroute ens33valid_lft forever preferred_lft foreverinet 192.168.2.227/24 brd 192.168.2.255 scope global secondary ens33:1valid_lft forever preferred_lft foreverinet6 fe80::20c:29ff:fe61:5077/64 scope link valid_lft forever preferred_lft forever
[root@mysql-1 ~]# 

7.6、测试:manager节点上测试ssh免密通道,如果正常最后会输出successfully

[root@mha_manager masterha]# masterha_check_ssh -conf=/etc/masterha/app1.cnf
Mon May  8 11:50:00 2023 - [info] All SSH connection tests passed successfully.

注意是否每台mysql间都建立了ssh免密通道,否则会报错

如果报错,思考是否软链接建立好了?或者主从复制搭建正确了

在 manager 节点上测试 mysql 主从连接情况,最后出现 MySQL Replication Health is OK 字样说明正常。

[root@mha_manager masterha]# masterha_check_repl -conf=/etc/masterha/app1.cnf
MySQL Replication Health is OK.

7.7、manager节点后台开启MHA

[root@mha_manager 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] 5085

查看 MHA 状态,可以看到当前的 master 是 Mysql1 节点。

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

查看MHA日志,看到当前matser是192.168.2.150

[root@mha_manager masterha]# cat /var/log/masterha/app1/manager.log | grep "current master"
Mon May  14 11:57:07 2023 - [info] Checking SSH publickey authentication settings on the current master..
192.168.2.150(192.168.2.150:3306) (current master)

查看mha进程

[root@mha_manager bin]# ps aux|grep manager
root       5085  0.1  4.5 299656 21992 pts/0    S    11:57   0:12 perl /usr/local/bin/masterha_manager --conf=/etc/masterha/app1.cnf --remove_dead_master_conf --ignore_last_failover
root      14939  0.0  0.2 112824   984 pts/0    S+   14:39   0:00 grep --color=auto manager

八、故障转移效果测试,模拟matser宕机,指定slave1成为新的master

manager节点监控日志记录(实时监控)

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

8.1、模拟master宕机,停掉master

[root@mysql-1 mysql]# service mysqld stop

8.2、查看自动故障检测的效果

查看vip是否漂移到了slave1

基于mycat2+mha+keepalived的半同步主从复制MySQL cluster_MySQL_11

查看日志信息

基于mycat2+mha+keepalived的半同步主从复制MySQL cluster_MySQL_12

日志显示,master已经切换到了192.168.2.151(slave1)

slave2也已经选择slave1作为master

8.3、查看/etc/masterha/app1.cnf文件是否发生改变

基于mycat2+mha+keepalived的半同步主从复制MySQL cluster_mysql_13

发现原来的server1配置被删除了

8.4、再来看看slave2的master_info信息(确定master服务转移到了salve1上)

基于mycat2+mha+keepalived的半同步主从复制MySQL cluster_MySQL_14

九、原master故障修复(原master转为slave,指向slave1)

9.1、原master开启mysqld

[root@mysql-1 ~]# service mysqld start

9.2、修复主从,原master修改master_info指向新的master(原slave1)

在mysql-2(slave1)上进行操作

root@(none) 17:18  mysql>change master to master_host='192.168.2.151',master_user='slave',master_password='123456',master_port=3306,master_auto_position=1;
root@(none) 17:19  mysql>start slave;
root@(none) 17:19  mysql>show slave status\G;

9.3、在 manager 节点上修改配置文件/etc/masterha/app1.cnf(再把这个记录添加进去,因为master宕机后原来的server1会被自动删除)

[server default]
manager_log=/var/log/masterha/app1/manager.log
manager_workdir=/var/log/masterha/app1
master_binlog_dir=/data/mysql/
master_ip_failover_script=/usr/local/bin/master_ip_failover
master_ip_online_change_script=/usr/local/bin/master_ip_online_change
password=123456
ping_interval=1
remote_workdir=/tmp
repl_password=123456
repl_user=slave
secondary_check_script=/usr/local/bin/masterha_secondary_check -s 192.168.2.151 -s 192.168.2.152
shutdown_script=""
ssh_user=root
user=mha[server1]
hostname=192.168.2.151   #原slave1的IP地址
port=3306[server2]
candidate_master=1
check_repl_delay=0
hostname=192.168.2.150   #原master的IP地址
port=3306[server3]
hostname=192.168.2.152   #原slave2的IP地址
port=3306 

基于mycat2+mha+keepalived的半同步主从复制MySQL cluster_mysql_16

9.4、重启mha manager,并检查此时的master

[root@mha_manager ~]# masterha_stop --conf=/etc/masterha/app1.cnf
Stopped app1 successfully.
[1]+  退出 1                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
[root@mha_manager ~]# 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] 20022
[root@mha_manager ~]# masterha_check_status --conf=/etc/masterha/app1.cnf
app1 (pid:20022) is running(0:PING_OK), master:192.168.2.151

master已经从192.168.2.150切换到了192.168.2.151

并且原来的192.168.2.150(原master)变成了slave1,并从192.168.2.151(原slave1)拿二进制日志了

至此,mha就算搭建成功了!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.rhkb.cn/news/91228.html

如若内容造成侵权/违法违规/事实不符,请联系长河编程网进行投诉反馈email:809451989@qq.com,一经查实,立即删除!

相关文章

让光存在,探索光耦继电器的魔力

光耦合器继电器是电路中的无名英雄&#xff0c;正在改变我们实现电气安全和控制的方式。这些卓越的设备&#xff08;也称为光电耦合器继电器&#xff09;由于其在电气隔离电路上传输信号和功率的独特能力而在各个行业中广受欢迎。今天&#xff0c;我们深入探讨光耦合器继电器背…

AI一键生成数字人

AI一键生成数字人 阅读时长&#xff1a;10分钟 本文内容&#xff1a; 结合开源AI&#xff0c;一键生成短视频发布到常见的某音&#xff0c;某手平台&#xff0c;狠狠赚一笔 前置知识&#xff1a; 基本的 python 编程知识Jupyter Notebook 使用过Linux 使用过 先上源码,colab一键…

如何快速便捷收集市场信息?电商API来帮你

电商API&#xff08;Application Programming Interface&#xff0c;应用程序编程接口&#xff09;是为了促进不同电商平台之间数据共享和交互而设计的一种技术。通过使用电商API&#xff0c;可以快速便捷地收集市场信息&#xff0c;提升电商运营效率&#xff0c;增加竞争力。 …

Flutter 混合架构方案探索

得益于 Flutter 优秀的跨平台表现&#xff0c;混合开发在如今的 App 中随处可见&#xff0c;如最近微信公布的小程序新渲染引擎 Skyline 发布正式版也在底层渲染上使用了 Flutter&#xff0c;号称渲染速度提升50%。 在现有的原生 App 中引入 Flutter 来开发不是一件简单的事&a…

Bitcoin 加速交易操作示例

这里以 Bitcoin Ordinals NFT 为例&#xff0c; 进行加速交易演示 第1步&#xff1a;新建子账户 温馨提示&#xff1a;如果有多条鱼未确认&#xff0c;也只需1个账户即可&#xff0c;不必搞多个子账户 第2步&#xff1a;切换回到老地址&#xff08;Account 1&#xff09; 第3步…

【Unity每日一记】进行发射,位置相关的方法总结

&#x1f468;‍&#x1f4bb;个人主页&#xff1a;元宇宙-秩沅 &#x1f468;‍&#x1f4bb; hallo 欢迎 点赞&#x1f44d; 收藏⭐ 留言&#x1f4dd; 加关注✅! &#x1f468;‍&#x1f4bb; 本文由 秩沅 原创 &#x1f468;‍&#x1f4bb; 收录于专栏&#xff1a;uni…

【设计模式——学习笔记】23种设计模式——状态模式State(原理讲解+应用场景介绍+案例介绍+Java代码实现)

文章目录 案例引入介绍基本介绍登场角色应用场景 案例实现案例一类图实现 案例二&#xff1a;借贷平台源码剖析传统方式实现分析状态修改流程类图实现 案例三&#xff1a;金库警报系统系统的运行逻辑伪代码传统实现方式使用状态模式 类图实现分析问题问题一问题二 总结文章说明…

基于飞桨图学习框架实现的城市地点动态关系挖掘

李双利 飞桨开发者技术专家&#xff08;PPDE&#xff09;&#xff0c;百度研究院商业智能实验室研究实习生&#xff0c;中国科学技术大学在读博士生。 主要进行时空数据挖掘和图深度学习的相关研究工作。曾获2021年百度研究院年度优秀实习生&#xff0c;有多篇基于飞桨完成的…

error_Network Error

此页面为订单列表&#xff0c;是混合开发(页面嵌入在客户端中) 此页面为订单列表&#xff0c;此需求在开发时后端先将代码发布在测试环境&#xff0c;我在本地调试时调用的后端接口进行联调没有任何问题。 此后我将代码发布在测试环境&#xff0c;在app中打开页面&#xff0c…

smardaten实战丨谁说无代码不能开发出漂亮的门户首页?

一、需求背景 门户首页对于一个公司或组织来说是一个极其重要的网站页面&#xff0c;它可以作为访问者了解和获取相关信息的入口&#xff0c;同时也是展示品牌形象和吸引目标受众的重要工具。 开发一个门户首页需要开发团队在向访问者展示关于公司或组织基本信息的基础上&…

Qt自定义对话框

介绍 自定义框主要通过对现有对话框QDialog类的派生&#xff0c;根据需求编写成员函数、重载信号函数、槽函数&#xff0c;进而实现在主QWidget中点击某个按钮后&#xff0c;一个对话框的弹出 流程 简化创建派生类 最后点击完成即可。 自定义ui界面&#xff0c;编写成员函数…

Netty:用ByteBufUtil的函数将字节数组、或者ByteBuf的内容转换为十六进制表示的字符串

用ByteBufUtil的hexDump(byte[] array)函数将字节数组的内容转换为十六进制表示的字符串 package com.thb;import io.netty.buffer.ByteBufUtil;public class Demo {public static void main(String[] args) {byte[] b new byte[] {0x68, 0x16, 0x03, 0x04, (byte)0xae};Stri…

ASR 语音识别接口封装和分析

这个文档主要是介绍一下我自己封装了 6 家厂商的短语音识别和实时流语音识别接口的一个包&#xff0c;以及对这些接口的一个对比。分别是&#xff0c;阿里&#xff0c;快商通&#xff0c;百度&#xff0c;腾讯&#xff0c;科大&#xff0c;字节。 zxmfke/asrfactory (github.c…

前后端分离------后端创建笔记(07)表单验证

1、我输入数据&#xff0c;然后关闭&#xff0c;重新打开会发现残存的数据仍然保留着 2、点了这个x号&#xff0c;数据就全部被清理了 3、点这三个地方&#xff0c;数据全部都清理掉 4、这里先写一个方法 4.1 定义一个方法 4.2 这里表单的数据在哪里&#xff0c;就是这个 4.3 …

Qt扫盲-QWidget理论使用总结

QWidget理论使用总结 一、概述二、顶层 控件 和子 控件三、复合控件四、自定义控件和绘制五、大小提示和大小策略六、事件七、一组函数和属性八、QWidget样式表九、透明度和双缓冲十、创建半透明窗口 一、概述 widget 是用户界面的最小单位&#xff1a;它从window系统接收鼠标…

Unity游戏源码分享-精品即时战略游戏_官网60美刀素材

Unity游戏源码分享-精品即时战略游戏_官网60美刀素材 下载地址&#xff1a;https://download.csdn.net/download/Highning0007/88204017

Jmeter-压测时接口按照顺序执行-临界部分控制器

文章目录 临界部分控制器存在问题 临界部分控制器 在进行压力测试时&#xff0c;需要按照顺序进行压测&#xff0c;比如按照接口1、接口2、接口3、接口4 进行执行 查询结果是很混乱的&#xff0c;如果请求次数少&#xff0c;可能会按照顺序执行&#xff0c;但是随着次数增加&a…

《零基础实践深度学习》(第2版)学习笔记,(二)机器学习和深度学习综述

文章目录 1. 人工智能、机器学习、深度学习的关系2. 机器学习2.1 实现原理2.2 如何实施 3. 深度学习神经网络核心概念 1. 人工智能、机器学习、深度学习的关系 **人工智能&#xff08;Artificial Intelligence&#xff0c;AI&#xff09;**是研发用于模拟、延伸和扩展人的智能…

helm安装harbor + nerdctl 制作push 镜像

参考 文章&#xff1a;Helm部署Harbor_helm harbor_风向决定发型丶的博客-CSDN博客 安装好后使用 nerd containerd对接harbor_containerd 容器 insecure-registries 配置_柠是柠檬的檬的博客-CSDN博客 推送镜像 Containerd 对接私有镜像仓库 Harbor - 知乎 接下来我们来…

“一日之际在于晨”,欢迎莅临WAVE SUMMIT上午场:Arm 虚拟硬件早餐交流会

8月16日&#xff0c;盛夏的北京将迎来第九届WAVE SUMMIT深度学习开发者大会。在峰会主论坛正式开启前&#xff0c;让我们先用一份精美的元气早餐&#xff0c;和一场“Arm虚拟硬件交流会”&#xff0c;唤醒各位开发小伙伴的开发魂&#xff01; 8月16日&#xff0c;WAVE SUMMIT大…