东莞网站制作与网站建设,交互设计好的网站,网站空格键代码,企业网站分为哪四类目录
自动failover
MHA#xff1a;
MHA 服务
项目#xff1a;搭建Mysql主从复制、MHA高可用架构
实验项目IP地址配置#xff1a;
MHA下载地址
项目步骤#xff1a;
一、修改主机名
二、编写一键安装mha node脚本和一键安装mha mangaer脚本#xff0c;并执行安装…目录
自动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 MHAMaster HA是一款开源的 MySQL 的高可用程序它为 MySQL主从复制架构提供了 automating master failover 自动化主故障转移功能。MHA 在监控到 master 节点故障时会 提升其中拥有最新数据的 slave 节点成为新的master 节点在此期间MHA 会通过于其它从节 点获取额外信息来避免一致性方面的问题。MHA 还提供了 master 节点的在线切换功能即按需 切换 master/slave 节点。 参考MYSQL高可用架构之MHA实战一 数据库主从配置真实可用_51CTO博客_mysql数据库主从搭建
基于mycat2mhakeepalived的半同步主从复制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 才能顺利的连接进去实现主从切换功能。 [rootmysql-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 --usermysql --log-error/data/mysql/mysql_error.log --open-files-limit8192 --pid-file/data/mysql/localhost.localdomain.pid --socket/data/mysql/mysql.sock --port3306
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 --usermysql --log-error/data/mysql/mysql_error.log --open-files-limit8192 --pid-file/data/mysql/mysql-1.pid --socket/data/mysql/mysql.sock --port3306
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 --usermysql --log-error/data/mysql/mysql_error.log --open-files-limit8192 --pid-file/data/mysql/mysql-1.pid --socket/data/mysql/mysql.sock --port3306
root 21340 0.0 0.0 112824 988 pts/1 R 04:22 0:00 grep --colorauto mysql项目搭建Mysql主从复制、MHA高可用架构
实验项目IP地址配置 mha_manager: manager192.168.2.141 #用于监控管理 vip192.168.2.227 mha_node: master192.168.2.150 #开启 bin-log relay-log slave-1192.168.2.151 #开启 bin-log relay-log slave-2192.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?pwdz52d 提取码z52d
[rootmha_manager ~]# ls
anaconda-ks.cfg mha4mysql-manager-0.58.tar.gz mha4mysql-node-0.58.tar.gz
[rootmha_manager ~]# 项目步骤
一、修改主机名
[rootweb-3 ~]# hostnamectl set-hostname mha_manager
[rootweb-3 ~]# su -
上一次登录五 8月 11 13:28:25 CST 2023从 192.168.2.7pts/0 上
[rootmha_manager ~]# 二、编写一键安装mha node脚本和一键安装mha mangaer脚本并执行安装
一键安装mha node脚本
[rootmha_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脚本
[rootmha_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主从复制集群
[rootmha_manager ~]# scp onekey_install_mha_node.sh root192.168.2.150:~
The authenticity of host 192.168.2.150 (192.168.2.150) cant 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.
root192.168.2.150s password:
onekey_install_mha_node.sh 100% 481 745.0KB/s 00:00
[rootmha_manager ~]# scp onekey_install_mha_node.sh root192.168.2.151:~
The authenticity of host 192.168.2.151 (192.168.2.151) cant 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.
root192.168.2.151s password:
onekey_install_mha_node.sh 100% 481 287.5KB/s 00:00
[rootmha_manager ~]# scp onekey_install_mha_node.sh root192.168.2.152:~
The authenticity of host 192.168.2.152 (192.168.2.152) cant 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.
root192.168.2.152s password:
onekey_install_mha_node.sh 100% 481 397.8KB/s 00:00
[rootmha_manager ~]# 4.2、mha_manger上需要配置有mha4mysql-manager-0.58.tar.gz和mha4mysql-node-0.58.tar.gz安装包和对应的一键安装脚本首先执行一键安装mha4mysql-node-0.58的脚本
[rootmha_manager ~]# bash onekey_install_mha_node.sh 再执行一键安装mha4mysql-manager-0.58的脚本
[rootmha_manager ~]# bash onekey_install_mha_manager.sh 最后输出 如下所示 表示成功
Appending installation info to /root/perl5/lib/perl5/x86_64-linux-thread-multi/perllocal.pod4.3、mysql主从复制集群运行一键安装mha4mysql-node-0.58的脚本
[rootmysql-1 ~]# bash onekey_install_mha_node.sh [rootmysql-2 ~]# bash onekey_install_mha_node.sh [rootmysql-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服务器建立免密通道
[rootmha_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/C0gk59hqkGi1cn0/cA rootmha_manager
The keys randomart image is:
---[RSA 2048]----
| |
| . o |
| . |
| o o. o|
| oS o.Eoo*|
| o o.ooo.*|
| . ..*.|
| .oBoo.|
| .o..o|
----[SHA256]-----
[rootmha_manager .ssh]# ssh-copy-id root192.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
root192.168.2.150s password: Number of key(s) added: 1Now try logging into the machine, with: ssh root192.168.2.150
and check to make sure that only the key(s) you wanted were added.[rootmha_manager .ssh]# [rootmha_manager .ssh]# ssh-copy-id root192.168.2.151[rootmha_manager .ssh]# ssh-copy-id root192.168.2.1525.2、master对slave1、slave2建立免密通道
[rootmysql-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:rB6Rg0nbJCHYxWxuBafl4HDB81RuuOpHC9/5LYRTAI rootmysql-1
The keys randomart image is:
---[RSA 2048]----
| ooBoE |
|. .*B.. . |
| oo..o. |
| .oB.o |
| . .Soo |
| .. |
| . . |
| o.o . |
| oo.. |
----[SHA256]-----
[rootmysql-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
root192.168.2.151s 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.[rootmysql-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
root192.168.2.152s 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.[rootmysql-1 ~]# 5.3、slave1对master、slave2建立免密通道
[rootmysql-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 rootmysql-2
The keys randomart image is:
---[RSA 2048]----
|o* |
|B .. |
|o .. o . |
|o. . o o o |
|.o . . S E ..|
| ....|
|.o o . o.|
|o . . . o.|
| . ..B|
----[SHA256]-----
[rootmysql-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) cant 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
root192.168.2.150s 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.[rootmysql-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) cant 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
root192.168.2.152s password:
Permission denied, please try again.
root192.168.2.152s 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.[rootmysql-2 ~]# 5.4、slave2对master、slave1建立免密通道
[rootmysql-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:m6F9WyLFkNnweKy2ERj3LflPDHqU5ZULS8FpCbXhtw rootmysql-3
The keys randomart image is:
---[RSA 2048]----
| . o . o|
| X .o |
| . * X E..|
| * O o.|
| S o o..|
| B . o. .|
| . * o . .. |
| o |
| . |
----[SHA256]-----
[rootmysql-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) cant 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
root192.168.2.150s 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.[rootmysql-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) cant 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
root192.168.2.151s 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.[rootmysql-3 ~]# 六、在Mysql的主从复制服务器里配置mha相关信息
6.1、所有mysql服务器master、slave1、slave2将mysql命令和mysqlbinlog二进制文件操作命令软链接到/usr/sbin方便manager管理节点因为/usr/sbin/ 目录下可以被直接调用。
[rootmysql-1 ~]# ln -s /usr/local/mysql/bin/mysql /usr/sbin/
[rootmysql-1 ~]# ln -s /usr/local/mysql/bin/mysqlbinlog /usr/sbin/[rootmysql-2 ~]# ln -s /usr/local/mysql/bin/mysql /usr/sbin/
[rootmysql-2 ~]# ln -s /usr/local/mysql/bin/mysqlbinlog /usr/sbin/[rootmysql-3 ~]# ln -s /usr/local/mysql/bin/mysql /usr/sbin/
[rootmysql-3 ~]# ln -s /usr/local/mysql/bin/mysqlbinlog /usr/sbin/6.2、所有mysql服务器新建允许manager访问的授权用户mha密码123456
root(none) 17:21 mysqlgrant all on *.* to mha192.168.2.% identified by 123456;
Query OK, 0 rows affected, 1 warning (0.01 sec)root(none) 17:21 mysqlgrant all on *.* to mha192.168.2.150 identified by 123456;
Query OK, 0 rows affected, 1 warning (0.00 sec)root(none) 17:22 mysqlgrant all on *.* to mha192.168.2.151 identified by 123456;
Query OK, 0 rows affected, 1 warning (0.00 sec)root(none) 17:22 mysqlgrant all on *.* to mha192.168.2.152 identified by 123456;
Query OK, 0 rows affected, 1 warning (0.00 sec)root(none) 17:22 mysqlselect 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下
[rootmha_manager ~]# cp -rp /root/mha4mysql-manager-0.58/samples/scripts/ /usr/local/bin/
[rootmha_manager ~]# cd /usr/local/bin/
[rootmha_manager bin]# ls
scripts
[rootmha_manager bin]#
[rootmha_manager bin]# cd scripts/
[rootmha_manager scripts]# ls
master_ip_failover master_ip_online_change power_manager send_report
[rootmha_manager scripts]# 7.2、复制自动切换时vip管理的脚本到/usr/local/bin下
[rootmha_manager scripts]# cp /usr/local/bin/scripts/master_ip_failover /usr/local/bin/
[rootmha_manager scripts]# ls
master_ip_failover master_ip_online_change power_manager send_report
[rootmha_manager scripts]# cd ..7.3、修改master_ip_failover文件内容配置vip只配置vip(192.168.2.227)相关参数其他默认不修改
[rootmha_manager bin]# ls
master_ip_failover scripts
[rootmha_manager bin]# /usr/local/bin/master_ip_failover #清空文件内容复制以下内容
[rootmha_manager bin]# vim master_ip_failover
[rootmha_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(
commands \$command,
ssh_users \$ssh_user,
orig_master_hosts \$orig_master_host,
orig_master_ips \$orig_master_ip,
orig_master_porti \$orig_master_port,
new_master_hosts \$new_master_host,
new_master_ips \$new_master_ip,
new_master_porti \$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 --commandstart|stop|stopssh|status --orig_master_hosthost --orig_master_ipip --orig_master_portport --new_master_hosthost --new_master_ipip --new_master_portport\n;
}[rootmha_manager bin]# 7.4、创建 MHA 软件目录并复制配置文件使用app1.cnf配置文件来管理 mysql 节点服务器配置文件一般放在/etc/目录下 注意注释只是提示用编辑配置文件时最好不要加注释否则很可能会出错 [rootmha_manager bin]# mkdir /etc/masterha
[rootmha_manager bin]# cp /root/mha4mysql-manager-0.58/samples/conf/app1.cnf /etc/masterha/
[rootmha_manager bin]# cd /etc/masterha/
[rootmha_manager masterha]# ls
app1.cnf
[rootmha_manager masterha]# app1.cnf #清空原有内容
[rootmha_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 #设置手动切换时候的切换脚本
usermha #设置监控用户mha
password123456 #设置mysql中mha用户的密码这个密码是前文中创建监控用户的那个密码
ping_interval1 #设置监控主库发送ping包的时间间隔1秒默认是3秒尝试三次没有回应的时候自动进行failover
remote_workdir/tmp #设置远端mysql在发生切换时binlog的保存位置
repl_userslave #设置复制用户的用户slave
repl_password123456 #设置复制用户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_userroot #设置ssh的登录用户名[server1]
#master
hostname192.168.2.150
port3306[server2]
#slave1
hostname192.168.2.151
port3306
candidate_master1
#设置为候选master设置该参数以后发生主从切换以后将会将此从库提升为主库即使这个主库不是集群中最新的slavecheck_repl_delay0
#默认情况下如果一个slave落后master 超过100M的relay logs的话MHA将不会选择该slave作为一个新的master 因为对于这个slave的恢复需要花费很长时间通过设置check_repl_delay0MHA触发切换在选择一个新的master的时候将会忽略复制延时这个参数对于设置了candidate_master1的主机非常有用因为这个候选主在切换的过程中一定是新的master[server3]
#slave2
hostname192.168.2.152
port3306
7.5、master服务器上手工开启vip
[rootmysql-1 ~]# ifconfig ens33:1 192.168.2.227/24
[rootmysql-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
[rootmysql-1 ~]# 7.6、测试manager节点上测试ssh免密通道如果正常最后会输出successfully
[rootmha_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 字样说明正常。
[rootmha_manager masterha]# masterha_check_repl -conf/etc/masterha/app1.cnf
MySQL Replication Health is OK.
7.7、manager节点后台开启MHA
[rootmha_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 21
[1] 5085
查看 MHA 状态可以看到当前的 master 是 Mysql1 节点。
[rootmha_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
[rootmha_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进程
[rootmha_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 --colorauto manager八、故障转移效果测试模拟matser宕机指定slave1成为新的master
manager节点监控日志记录实时监控
[rootmha_manager bin]# tail -f /var/log/masterha/app1/manager.log
8.1、模拟master宕机停掉master
[rootmysql-1 mysql]# service mysqld stop
8.2、查看自动故障检测的效果
查看vip是否漂移到了slave1 查看日志信息 日志显示master已经切换到了192.168.2.151slave1 slave2也已经选择slave1作为master 8.3、查看/etc/masterha/app1.cnf文件是否发生改变 发现原来的server1配置被删除了
8.4、再来看看slave2的master_info信息确定master服务转移到了salve1上 九、原master故障修复原master转为slave指向slave1
9.1、原master开启mysqld
[rootmysql-1 ~]# service mysqld start
9.2、修复主从原master修改master_info指向新的master原slave1
在mysql-2slave1上进行操作
root(none) 17:18 mysqlchange master to master_host192.168.2.151,master_userslave,master_password123456,master_port3306,master_auto_position1;
root(none) 17:19 mysqlstart slave;
root(none) 17:19 mysqlshow 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
password123456
ping_interval1
remote_workdir/tmp
repl_password123456
repl_userslave
secondary_check_script/usr/local/bin/masterha_secondary_check -s 192.168.2.151 -s 192.168.2.152
shutdown_script
ssh_userroot
usermha[server1]
hostname192.168.2.151 #原slave1的IP地址
port3306[server2]
candidate_master1
check_repl_delay0
hostname192.168.2.150 #原master的IP地址
port3306[server3]
hostname192.168.2.152 #原slave2的IP地址
port3306 9.4、重启mha manager并检查此时的master
[rootmha_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 21
[rootmha_manager ~]# nohup masterha_manager --conf/etc/masterha/app1.cnf --remove_dead_master_conf --ignore_last_failover /dev/null /var/log/masterha/app1/manager.log 21
[1] 20022
[rootmha_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就算搭建成功了
