MySQL5.7+keepalived+LVS从库负载均衡搭建

2025-04-09 技术教程

数据库架构：一主三从

master：192.168.8.57

slave1：192.168.8.58

slave2：192.168.8.59

slave3：192.168.8.61

manager：192.168.8.60

工具包：

mha4mysql-manager-0.58.tar.gz

mha4mysql-node-0.58.tar.gz

keepalived-1.4.5.tar.gz

此项目数据库架构中，master和slave1利用MHA和keepalived实现故障自动切换，slave2和slave3利用keepalived和LVS实现负载均衡，本文着重讲解负载均衡的搭建过程。

一、安装ipvsadm

在slave2和slave3安装

mount/dev/cdrom/mediayuminstall-yipvsadm

二、安装keepalived

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

设置开机自动启动

systemctlenablekeepalived

三、更改keepalived日志位置

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"

重启服务

systemctlrestartrsyslog

四、修改keepalived配置文件

192.168.8.59

vi /etc/keepalived/keepalived.conf

!ConfigurationFileforkeepalivedglobal_defs{router_id859notification_email{zdd5503@163.com}notification_email_fromzdd5503@163.comsmtp_serverstmp.163.comsmtp_connect_timeout30}vrrp_instancev_mysql_slave_wgpt1{stateMASTERinterfaceenp0s3virtual_router_id98priority100advert_int1nopreemptauthentication{auth_typePASSauth_passsteven}virtual_ipaddress{192.168.8.98/24}}virtual_server192.168.8.983306{delay_loop2lb_algorrlb_kindDRpersistence_timeout0protocolTCPreal_server192.168.8.593306{weight1TCP_CHECK{connect_timeout3nb_get_retry3delay_before_retry3connect_port3306}}real_server192.168.8.613306{weight1TCP_CHECK{connect_timeout3nb_get_retry3delay_before_retry3connect_port3306}}}

192.168.8.61

vi /etc/keepalived/keepalived.conf

!ConfigurationFileforkeepalivedglobal_defs{router_id861notification_email{zdd5503@163.com}notification_email_fromzdd5503@163.comsmtp_serverstmp.163.comsmtp_connect_timeout30}vrrp_instancev_mysql_slave_wgpt1{stateBACKUPinterfaceenp0s3virtual_router_id98priority80advert_int1nopreemptauthentication{auth_typePASSauth_passsteven}virtual_ipaddress{192.168.8.98/24}}virtual_server192.168.8.983306{delay_loop2lb_algorrlb_kindDRpersistence_timeout0protocolTCPreal_server192.168.8.593306{weight1TCP_CHECK{connect_timeout3nb_get_retry3delay_before_retry3connect_port3306}}real_server192.168.8.613306{weight1TCP_CHECK{connect_timeout3nb_get_retry3delay_before_retry3connect_port3306}}}

systemctl daemon-reload

systemctl start keepalived

systemctl enable keepalived

ps -ef |grep keepalived

ip a

五、lo:0绑定VIP地址、抑制ARP广播

192.168.8.59

vi/etc/rc.d/init.d/lvsmysql.sh

#!/bin/bash#./etc/rc.d/init.d/functionsVIP1=$1case"$2"instart)echo"StartLVSofMySQLSlaveREALServer"/sbin/ifconfiglo:0$VIP1broadcast$VIP1netmask255.255.255.255up/sbin/routeadd-host$VIP1devlo:0echo"1">/proc/sys/net/ipv4/conf/lo/arp_ignoreecho"2">/proc/sys/net/ipv4/conf/lo/arp_announceecho"1">/proc/sys/net/ipv4/conf/all/arp_ignoreecho"2">/proc/sys/net/ipv4/conf/all/arp_announcesysctl-p>/dev/null2>&1;;stop)/sbin/ifconfiglo:0down/sbin/routedel$VIP1>/dev/null2>&1echo"StopedLVSofMySQLSlaveDirectorserver"echo"0">/proc/sys/net/ipv4/conf/lo/arp_ignoreecho"0">/proc/sys/net/ipv4/conf/lo/arp_announceecho"0">/proc/sys/net/ipv4/conf/all/arp_ignoreecho"0">/proc/sys/net/ipv4/conf/all/arp_announce;;status)isLoOn=`/sbin/ifconfiglo:0|grep"$VIP"`isRoOn=`/bin/netstat-rn|grep"$VIP"`if["$isLoON"==""-a"$isRoOn"==""];thenecho"LVS-DRrealserverhasrunyet."elseecho"LVS-DRrealserverisrunning."fiexit3;;*)echo"Usage:$0{start|stop|status}"exit1esacexit0

192.168.8.61

vi/etc/rc.d/init.d/lvsmysql.sh

六、测试负载均衡

/etc/rc.d/init.d/lvsmysql.sh 192.168.8.59 start

/etc/rc.d/init.d/lvsmysql.sh 192.168.8.59 stop

/etc/rc.d/init.d/lvsmysql.sh 192.168.8.61 start

/etc/rc.d/init.d/lvsmysql.sh 192.168.8.61 stop

echo "/etc/rc.d/init.d/lvsmysql.sh 192.168.1.65 start" >>

/etc/rc.d/rc.local

echo "ipvsadm --set 15 5 15" >> /etc/rc.d/rc.local

ifconfig

ipvsadm -L