MySQL5.7+MHA+Keepalived高可用配置的示例分析

2025-03-14 技术教程

这篇文章主要介绍了MySQL5.7+MHA+Keepalived高可用配置的示例分析，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让小编带着大家一起了解一下。

数据库架构：一主两从

master：192.168.8.57

slave1：192.168.8.58

slave2：192.168.8.59

manager：192.168.8.60

MHA工具包：

mha4mysql-manager-0.58.tar.gz

mha4mysql-node-0.58.tar.gz

keepalived-1.4.5.tar.gz

一、主从复制环境和MHA安装如下

http://blog.itpub.net/30135314/viewspace-2217566/

二、配置MHA相关文件

master_ip_failover

my$vip='192.168.8.88/24';my$key='1';my$ssh_start_vip="systemctlstartkeepalived";my$ssh_stop_vip="systemctlstopkeepalived";

master_ip_online_change

my$vip='192.168.8.88/24';my$key='1';my$ssh_start_vip="systemctlstartkeepalived";my$ssh_stop_vip="systemctlstopkeepalived";my$orig_master_ssh_port=22;my$new_master_ssh_port=22;my$ssh_user="root";my$new_master_password='mysql';my$orig_master_password='mysql';

三、添加非本机 IP 邦定支持

echo"net.ipv4.ip_nonlocal_bind=1">>/etc/sysctl.confsysctl-p

四、安装配置keepalived

mount/dev/cdrom/mntyuminstallkernel-developenssl-develpopt-devel-y

tarzxvfkeepalived-1.4.5.tar.gzcdkeepalived-1.4.5./configure--prefix=/usr/local/keepalived/make&&makeinstall

设置 keepalived 开机自动启动

systemctlenablekeepalived

配置keepalived

192.168.8.57

mkdir/etc/keepalived#vi/etc/keepalived/keepalived.conf!Configurationfileforkeepalivedglobal_defs{notification_email{zdd5503@163.com}router_id8.57notification_email_fromzdd5503@163.comsmtp_serverstmp.163.comsmtp_connect_timeout30}vrrp_instancev_mysql{stateBACKUPinterfaceenp0s3virtual_router_id200priority100advert_int1nopreemptauthentication{auth_typePASSauth_passmysql}virtual_ipaddress{192.168.8.88/24}}virtual_server192.168.8.573306{delay_loop2lb_algowrrlb_kindDRpersistence_timeout60protocolTCPreal_server192.168.8.573306{weight3notify_down/etc/keepalived/keepalived_stop.shTCP_CHECK{connect_timeout10nb_get_retry3delay_before_retry3connect_port3306}}}

echo"#!/bin/bash">/etc/keepalived/keepalived_stop.shecho"pkillkeepalived">>/etc/keepalived/keepalived_stop.shchmodu+x/etc/keepalived/keepalived_stop.sh

192.168.8.58

mkdir/etc/keepalived#vi/etc/keepalived/keepalived.conf!Configurationfileforkeepalivedglobal_defs{notification_email{zdd5503@163.com}router_id8.58notification_email_fromzdd5503@163.comsmtp_serverstmp.163.comsmtp_connect_timeout30}vrrp_instancev_mysql{stateBACKUPinterfaceenp0s3virtual_router_id200priority90advert_int1nopreemptauthentication{auth_typePASSauth_passmysql}virtual_ipaddress{192.168.8.88/24}}virtual_server192.168.8.583306{delay_loop2lb_algowrrlb_kindDRpersistence_timeout60protocolTCPreal_server192.168.8.583306{weight3notify_down/etc/keepalived/keepalived_stop.shTCP_CHECK{connect_timeout10nb_get_retry3delay_before_retry3connect_port3306}}}

echo"#!/bin/bash">/etc/keepalived/keepalived_stop.shecho"pkillkeepalived">>/etc/keepalived/keepalived_stop.shchmodu+x/etc/keepalived/keepalived_stop.sh

五、启动并查看keepalived

192.168.8.57和192.168.8.58

systemctldaemon-reloadsystemctlstartkeepalivedsystemctlenablekeepalivedps-ef|grepkeepalived

看到如下进程，即启动正常，否则，启动失败

#ps-ef|grepkeepalivedroot169161014:53?00:00:00/usr/local/keepalived/sbin/keepalived-Droot1691716916014:53?00:00:00/usr/local/keepalived/sbin/keepalived-Droot1691816916014:53?00:00:00/usr/local/keepalived/sbin/keepalived-Droot177729992016:17pts/200:00:00grep--color=autokeepalived

查看VIP

192.168.8.57

#ipa|grep88inet192.168.8.88/24scopeglobalsecondaryenp0s3

192.168.8.58看不到192.168.8.88/24

六、测试keepalived可用性（测试之前关闭MHA进程，避免故障切换造成干扰）

MySQL连接测试

查到目前VIP192.168.8.88在192.168.8.57这个机器

#mysql-uroot-pmysql-h292.168.8.88-e"showvariableslike'server_id'"mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.+---------------+-------+|Variable_name|Value|+---------------+-------+|server_id|57|+---------------+-------+

关闭keepalived测试

关闭192.168.8.57上keepalived进程，此时在此节点已查不到VIP

ipa1:lo:<LOOPBACK,UP,LOWER_UP>mtu65536qdiscnoqueuestateUNKNOWNgroupdefaultqlen1000link/loopback00:00:00:00:00:00brd00:00:00:00:00:00inet127.0.0.1/8scopehostlovalid_lftforeverpreferred_lftforeverinet6::1/128scopehostvalid_lftforeverpreferred_lftforever2:enp0s3:<BROADCAST,MULTICAST,UP,LOWER_UP>mtu1500qdiscpfifo_faststateUPgroupdefaultqlen1000link/ether08:00:27:4d:70:17brdff:ff:ff:ff:ff:ffinet192.168.8.58/24brd192.168.8.255scopeglobalnoprefixrouteenp0s3valid_lftforeverpreferred_lftforeverinet192.168.8.88/24scopeglobalsecondaryenp0s3valid_lftforeverpreferred_lftforeverinet6fe80::6a31:3e92:8b6f:83c0/64scopelinknoprefixroutevalid_lftforeverpreferred_lftforeverinet6fe80::5198:593b:cdc5:1f90/64scopelinktentativenoprefixroutedadfailedvalid_lftforeverpreferred_lftforever3:virbr0:<NO-CARRIER,BROADCAST,MULTICAST,UP>mtu1500qdiscnoqueuestateDOWNgroupdefaultqlen1000link/ether52:54:00:f4:55:bbbrdff:ff:ff:ff:ff:ffinet192.168.122.1/24brd192.168.122.255scopeglobalvirbr0valid_lftforeverpreferred_lftforever4:virbr0-nic:<BROADCAST,MULTICAST>mtu1500qdiscpfifo_fastmastervirbr0stateDOWNgroupdefaultqlen1000link/ether52:54:00:f4:55:bbbrdff:ff:ff:ff:ff:ff

可以看到VIP已经飘至192.168.8.58

此时启动192.168.8.57上的keepalived进程，VIP依然在192.168.8.58节点。

模拟宕机测试

查看VIP位置

mysql-uroot-pmysql-h292.168.8.88-e"showvariableslike'server_id'"mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.+---------------+-------+|Variable_name|Value|+---------------+-------+|server_id|57|+---------------+-------+

关闭192.168.8.57节点mysql进程

可以看到VIP已飘至192.168.8.58节点

七、修改keepalived日志位置

默认日志存放在系统日志：/var/log/messages 下

把日志单独存放

修改 vi /usr/local/keepalived/etc/sysconfig/keepalived

把 KEEPALIVED_OPTIONS="-D" 修改为：KEEPALIVED_OPTIONS="-D -d -S 0"

vi/usr/local/keepalived/etc/sysconfig/keepalived#Optionsforkeepalived.See`keepalived--help'outputandkeepalived(8)and#keepalived.conf(5)manpagesforalistofalloptions.Herearethemost#commonones:##--vrrp-POnlyrunwithVRRPsubsystem.#--check-COnlyrunwithHealth-checkersubsystem.#--dont-release-vrrp-VDontremoveVRRPVIPs&VROUTEsondaemonstop.#--dont-release-ipvs-IDontremoveIPVStopologyondaemonstop.#--dump-conf-dDumptheconfigurationdata.#--log-detail-DDetailedlogmessages.#--log-facility-S0-7Setlocalsyslogfacility(default=LOG_DAEMON)#KEEPALIVED_OPTIONS="-D-d-S0"

在/etc/rsyslog.conf 末尾添加

vi/etc/rsyslog.conflocal0.*/var/log/keepalived.log

RHEL6:

/etc/init.d/rsyslog restart

/etc/init.d/keepalived restart

RHEL7:

systemctl restart rsyslog

systemctl restart keepalived

查看keepalived日志

#tail-100f/var/log/keepalived.logOct2717:07:25masterKeepalived_vrrp[22697]:------<Globaldefinitions>------Oct2717:07:25masterKeepalived_vrrp[22697]:RouterID=8.57Oct2717:07:25masterKeepalived_vrrp[22697]:Smtpserverconnectiontimeout=30Oct2717:07:25masterKeepalived_vrrp[22697]:Emailnotificationfrom=zdd5503@163.comOct2717:07:25masterKeepalived_vrrp[22697]:Emailnotification=zdd5503@163.comOct2717:07:25masterKeepalived_vrrp[22697]:Defaultinterface=eth0Oct2717:07:25masterKeepalived_vrrp[22697]:LVSflush=falseOct2717:07:25masterKeepalived_vrrp[22697]:VRRPIPv4mcastgroup=224.0.0.18Oct2717:07:25masterKeepalived_vrrp[22697]:VRRPIPv6mcastgroup=ff02::12Oct2717:07:25masterKeepalived_vrrp[22697]:GratuitousARPdelay=5Oct2717:07:25masterKeepalived_vrrp[22697]:GratuitousARPrepeat=5Oct2717:07:25masterKeepalived_vrrp[22697]:GratuitousARPrefreshtimer=0Oct2717:07:25masterKeepalived_vrrp[22697]:GratuitousARPrefreshrepeat=1Oct2717:07:25masterKeepalived_vrrp[22697]:GratuitousARPlowerprioritydelay=5Oct2717:07:25masterKeepalived_vrrp[22697]:GratuitousARPlowerpriorityrepeat=5Oct2717:07:25masterKeepalived_vrrp[22697]:Sendadvertafterreceivelowerpriorityadvert=trueOct2717:07:25masterKeepalived_vrrp[22697]:Sendadvertafterreceivehigherpriorityadvert=falseOct2717:07:25masterKeepalived_vrrp[22697]:GratuitousARPinterval=0Oct2717:07:25masterKeepalived_vrrp[22697]:GratuitousNAinterval=0Oct2717:07:25masterKeepalived_vrrp[22697]:VRRPdefaultprotocolversion=2Oct2717:07:25masterKeepalived_vrrp[22697]:Iptablesinputchain=INPUTOct2717:07:25masterKeepalived_vrrp[22697]:VRRPcheckunicast_src=false

可以看到日志已经输出，位置已变。

感谢你能够认真阅读完这篇文章，希望小编分享的“MySQL5.7+MHA+Keepalived高可用配置的示例分析”这篇文章对大家有帮助，同时也希望大家多多支持亿速云，关注亿速云行业资讯频道，更多相关知识等着你来学习!