怎么用MySQL+Keepalived+haproxy实现负载均衡

2025-03-20 技术教程

本篇内容主要讲解“怎么用MySQL+Keepalived+haproxy实现负载均衡”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“怎么用MySQL+Keepalived+haproxy实现负载均衡”吧!

数据库环境：

slave2：192.168.8.59

slave3：192.168.8.61

工具包：

keepalived-1.4.5.tar.gz

haproxy-1.8.9.tar.gz

此次实验中，slave2和slave3利用keepalived和haproxy实现负载均衡，本文着重讲解负载均衡的搭建过程。

一、修改内核参数

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

net.ipv4.ip_nonlocal_bind=1 意思是启动haproxy的时候，允许忽视VIP的存在

net.ipv4.ip_forward = 1 haproxy代理服务器同时也要打开内核的转发功能

二、安装keepalived和haproxy

mount/dev/cdrom/mediayuminstallkernel-developenssl-develpopt-devel-ycd/softwaretarzxvfkeepalived-1.4.5.tar.gzcdkeepalived-1.4.5./configure--prefix=/usr/local/keepalived/make&&makeinstallsystemctlenablekeepalived

vi /usr/local/keepalived/etc/sysconfig/keepalived

KEEPALIVED_OPTIONS="-D-d-S0"

vi /etc/rsyslog.conf

local0.*/var/log/keepalived.log

systemctlrestartrsyslog

cd/softwaretarzxvfhaproxy-1.8.9.tar.gzcdhaproxy-1.8.9makeTARGET=linux2628PREFIX=/usr/local/haproxymakeinstallPREFIX=/usr/local/haproxy/usr/local/haproxy/sbin/haproxy-vuseradd-r-s/sbin/nologinhaproxytouch/var/log/haproxy.logchmod755/var/log/haproxy.log

修改日志位置

vi/etc/rsyslog.conflocal0.*/var/log/haproxy.logvi/etc/sysconfig/rsyslogSYSLOGD_OPTIONS="-r-m0-c2"systemctlrestartrsyslog

三、配置keepalived

192.168.8.59

/etc/keepalived/keepalived.conf

!ConfigurationFileforkeepalivedglobal_defs{router_idmysql-slavenotification_email{zdd5503@163.com}notification_email_fromzdd5503@163.comsmtp_serverstmp.163.comsmtp_connect_timeout30}vrrp_scriptchk_haproxy{script"/etc/keepalived/scripts/check_haproxy.sh"interval2weight2}vrrp_instancev_mysql_slave_zdd01{interfaceenp0s3stateMASTERvirtual_router_id98priority200nopreemptvirtual_ipaddress{192.168.8.98/24}track_script{chk_haproxy}notify_master/etc/keepalived/scripts/haproxy_master.shnotify_backup/etc/keepalived/scripts/haproxy_backup.shnotify_fault/etc/keepalived/scripts/haproxy_fault.shnotify_stop/etc/keepalived/scripts/haproxy_stop.sh}

192.168.8.61

/etc/keepalived/keepalived.conf

!ConfigurationFileforkeepalivedglobal_defs{router_idmysql-slavenotification_email{zdd5503@163.com}notification_email_fromzdd5503@163.comsmtp_serverstmp.163.comsmtp_connect_timeout30}vrrp_scriptchk_haproxy{script"/etc/keepalived/scripts/check_haproxy.sh"interval2weight2}vrrp_instancev_mysql_slave_zdd01{interfaceenp0s3stateBACKUPvirtual_router_id98priority150nopreemptvirtual_ipaddress{192.168.8.98/24}track_script{chk_haproxy}notify_master/etc/keepalived/scripts/haproxy_master.shnotify_backup/etc/keepalived/scripts/haproxy_backup.shnotify_fault/etc/keepalived/scripts/haproxy_fault.shnotify_stop/etc/keepalived/scripts/haproxy_stop.sh}

四、配置haproxy

192.168.8.59和192.168.8.61都做如下脚本配置：

mkdir -p /etc/keepalived/scripts

vi /etc/keepalived/scripts/check_haproxy.sh

#!/bin/bashSTARTHAPROXY="/usr/local/haproxy/sbin/haproxy-f/usr/local/haproxy/haproxy.cfg"#STOPKEEPALIVED="systemctlstopkeepalived"LOGFILE="/etc/keepalived/scripts/keepalived-haproxy-state.log"echo"[check_haproxystatus]">>$LOGFILEA=`ps-Chaproxy--no-header|wc-l`echo"[check_haproxystatus]">>$LOGFILEdate>>$LOGFILEif[$A-eq0];thenecho$STARTHAPROXY>>$LOGFILE$STARTHAPROXY>>$LOGFILE2>&1sleep5fiif[`ps-Chaproxy--no-header|wc-l`-eq0];thenexit0elseexit1fi

vi /etc/keepalived/scripts/haproxy_master.sh

#!/bin/bashSTARTHAPROXY=`/usr/local/haproxy/sbin/haproxy-f/usr/local/haproxy/haproxy.cfg`STOPHAPROXY=`ps-ef|grepsbin/haproxy|grep-vgrep|awk'{print$2}'|xargskill-s9`LOGFILE="/etc/keepalived/scripts/keepalived-haproxy-state.log"echo"[master]">>$LOGFILEdate>>$LOGFILEecho"Beingmaster....">>$LOGFILE2>&1echo"stophaproxy....">>$LOGFILE2>&1$STOPHAPROXY>>$LOGFILE2>&1echo"starthaproxy....">>$LOGFILE2>&1$STARTHAPROXY>>$LOGFILE2>&1echo"haproxystared...">>$LOGFILE

vi /etc/keepalived/scripts/haproxy_backup.sh

vi/etc/keepalived/scripts/haproxy_fault.sh

#!/bin/bashLOGFILE=/etc/keepalived/scripts/keepalived-haproxy-state.logecho"[fault]">>$LOGFILEdate>>$LOGFILE

vi /etc/keepalived/scripts/haproxy_stop.sh

#!/bin/bashLOGFILE=/etc/keepalived/scripts/keepalived-haproxy-state.logecho"[stop]">>$LOGFILEdate>>$LOGFILE

更改脚本属性

chmod755/etc/keepalived/scripts/*

配置haproxy

192.168.8.59

vi /usr/local/haproxy/haproxy.cfg

global#全局配置参数log127.0.0.1local0notice#日志#userhaproxy#grouphaproxydaemon#以后台形式运行haproxy#quietnbproc1#进程数量，可以设置多个进程提高性能pidfile/usr/local/haproxy/haproxy.pid#haproxy的pid存放路径defaults#一些默认参数logglobal#全局日志retries3#3次连接失败就认为服务器不可用optiondontlognull#保存haproxy不记录上次负载均衡发送过来的用于检测状态没有数据的心跳包optionredispatch#对应的服务器挂掉后，强制指向到其它正常的服务器maxconn2000#默认最大连接数timeoutqueue1m#等待最大时长timeouthttp-request10s#客户端建立连接又不请求数据的时候超过10S，就关闭客户端连接timeoutconnect10s#定义haproxy将客户端请求转发至后端服务器所等待的超时时长timeoutserver1m#服务端非活动状态的超时时长timeoutclient1m#客户端非活动状态的超时时长timeouthttp-keep-alive10s#设置http-keep-alive超时时长timeoutcheck10s#健康状态监测时的超时时间balanceroundrobin#负载均衡方式，轮询方式（保持会话session:源地址/cookies,针对web应用服务器）#定义负载均衡的配置listenmysql_slave_wgpt_lb1bind192.168.8.98:3307#绑定的IP与端口(建议bind*:3306)modetcp#模式是TCP，（7层http,4层tcp，如果是mysql用tcp，如果是weblogic/was/tomcat之类的就用http）optionmysql-checkuserhaproxy_check#通过mysql连接去检测mysql是否可以访问statshide-version#隐藏统计页上的haproxy的版本信息balanceroundrobin#负载均衡方式，轮询方式serverslave2192.168.8.59:3306weight1checkinter2000rise2fall5maxconn300serverslave3192.168.8.61:3306weight1checkinter2000rise2fall5maxconn300#服务器定义,checkinter2000检测心跳频率，rise22次正确认为服务器可用，fall55次失败认为服务器不可用，#maxconn300最大连接数300#自带的监控服务器的配置listenhaproxy_statsmodehttpbind*:8888optionhttplogstatsrefresh5sstatsuri/haproxy-statstatsrealmwww.zdd.commonitorstatsrealmHaproxyManagerstatsauthhaproxy:haproxy

192.168.8.61

vi /usr/local/haproxy/haproxy.cfg

五、添加 haproxy_check 用户

dropuserhaproxy_check@'%';createuserhaproxy_check@'192.168.8.59';createuserhaproxy_check@'192.168.8.61';createuserhaproxy_check@'192.168.8.98';grantusageon*.*tohaproxy_check@'192.168.8.59';grantusageon*.*tohaproxy_check@'192.168.8.61';grantusageon*.*tohaproxy_check@'192.168.8.98';

mysql>selectuser,hostfrommysql.user;+---------------+--------------+|user|host|+---------------+--------------+|repl|192.168.8.%||root|192.168.8.%||haproxy_check|192.168.8.59||haproxy_check|192.168.8.61||haproxy_check|192.168.8.98||mysql.session|localhost||mysql.sys|localhost||root|localhost|+---------------+--------------+

六、启动 keepalived

systemctldaemon-reloadsystemctlenablekeepalivedsystemctlstartkeepalived

ipa|grep98ps-ef|grepkeepalived

七、配置keepalived监控脚本

两个节点都做如下配置：

vi /etc/keepalived/keepcheck.sh

#!/bin/bashwhile:dokeepalivedcheck=`ps-Ckeepalived--no-header|wc-l`if[$keepalivedcheck-eq0];thensystemctlstartkeepalivedelseecho"keepalivedisrunning"fisleep5done

chmod+x/etc/keepalived/keepcheck.shnohup/etc/keepalived/keepcheck.sh&echo"nohup/etc/keepalived/keepcheck.sh&">>/etc/rc.d/rc.local

八、配置haproxy 启动脚本

vim /etc/rc.d/init.d/haproxy

#!/bin/shset-ePATH=/sbin:/bin:/usr/sbin:/usr/bin:/usr/local/haproxy/sbinPROGDIR=/usr/local/haproxyPROGNAME=haproxyDAEMON=$PROGDIR/sbin/$PROGNAMECONFIG=$PROGDIR/$PROGNAME.cfgPIDFILE=$PROGDIR/$PROGNAME.pidDESC="HAProxydaemon"SCRIPTNAME=/etc/init.d/$PROGNAME#Gracefullyexitifthepackagehasbeenremoved.test-x$DAEMON||exit0start(){echo-n"Starting$DESC:$PROGNAME"$DAEMON-f$CONFIGecho"."}stop(){echo-n"Stopping$DESC:$PROGNAME"haproxy_pid=`cat$PIDFILE`kill-9$haproxy_pidecho"."}restart(){echo-n"Restarting$DESC:$PROGNAME"$DAEMON-f$CONFIG-p$PIDFILE-sf$(cat$PIDFILE)echo"."}case"$1"instart)start;;stop)stop;;restart)restart;;*)echo"Usage:$SCRIPTNAME{start|stop|restart}">&2exit1;;esacexit0

chmod+x/etc/rc.d/init.d/haproxyecho"nohup/etc/rc.d/init.d/haproxystart&">>/etc/rc.d/rc.local

systemctlstopkeepalivedsystemctlstartkeepalivedtail-f/var/log/keepalived.log

/etc/rc.d/init.d/haproxystart/etc/rc.d/init.d/haproxystoptail-f/var/log/haproxy.log

haproxy控制台访问地址：

http://192.168.8.98:8888/haproxy-stat

九、测试负载均衡

[root@slave3~]#mysql-uroot-pmysql-h292.168.8.98-P3307-e"showvariableslike'server_id';"mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.+---------------+-------+|Variable_name|Value|+---------------+-------+|server_id|59|+---------------+-------+[root@slave3~]#mysql-uroot-pmysql-h292.168.8.98-P3307-e"showvariableslike'server_id';"mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.+---------------+-------+|Variable_name|Value|+---------------+-------+|server_id|61|+---------------+-------+[root@slave3~]#mysql-uroot-pmysql-h292.168.8.98-P3307-e"showvariableslike'server_id';"mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.+---------------+-------+|Variable_name|Value|+---------------+-------+|server_id|59|+---------------+-------+[root@slave3~]#mysql-uroot-pmysql-h292.168.8.98-P3307-e"showvariableslike'server_id';"mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.+---------------+-------+|Variable_name|Value|+---------------+-------+|server_id|61|+---------------+-------+[root@slave3~]#mysql-uroot-pmysql-h292.168.8.98-P3307-e"showvariableslike'server_id';"mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.+---------------+-------+|Variable_name|Value|+---------------+-------+|server_id|59|+---------------+-------+[root@slave3~]#mysql-uroot-pmysql-h292.168.8.98-P3307-e"showvariableslike'server_id';"mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.+---------------+-------+|Variable_name|Value|+---------------+-------+|server_id|61|+---------------+-------+

因为我们在haproxy配置文件中配置了权重为1：1，所以会话将交替分发到两个节点上边。

到此，相信大家对“怎么用MySQL+Keepalived+haproxy实现负载均衡”有了更深的了解，不妨来实际操作一番吧！这里是亿速云网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！