(5.19)Mysql+Keepalived+HAproxy读负载均衡
【0】项目架构
相关概念参考:(5.17)mysql集群技术概述(LVS、Keepalived、HAproxy)
Keepalived,只需要在负载均衡机器上配置
而HAproxy需要在所有的4台机器上配置
OS:CentOS7.5
MySQL:5.7.26
Keepalived:1.4.5
描述 | IP | 主机名 |
HAproxy+Keepalived:主服务器1 | 192.168.1.201 | db |
HAproxy+Keepalived:从服务器2 | 192.168.1.202 | db1 |
HAproxy+Keepalived的虚拟地址IP(VIP) | 192.168.1.210 | db/db1来回切换 |
Mysql:从服务器1 | 192.168.1.203 | db2 |
Mysql:从服务器2 | 192.168.1.204 | db3 |
【1】keepalived 安装配置
【1.1】前置环境配置(允许IP非本地绑定支持、防火墙、路由转发)
#(1)添加非本机IP绑定支持 echo "net.ipv4.ip_nonlocal_bind=1" >> /etc/sysctl.conf sysctl -p #(2)配置iptables,添加VRRP通讯支持 -A INPUT -d 224.0.0.18 -j ACCEPT #允许组播地址访问本机,组播地址用于keepalived互相检测心跳 -A INPUT -s 192.168.1.0/24 -d 224.0.0.18 -j ACCEPT #允许组播地址通信 -A INPUT -s 192.168.1.0/24 -p vrrp -j ACCEPT #允许VRRP(虚拟路由器冗余协议)通信 -A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCPET #开发mysql3306端口 #(3)开启路由转发功能 #临时 echo "1">/proc/sys/net/ipv4/ip_forward #永久路由转发 echo "net.ipv4.ip_forward=1" >> /etc/sysctl.conf sysctl -p
【1.2】下载安装 Keepalived
#(1)下载 官网:https://www.keepalived.org/download.html wget https://www.keepalived.org/software/keepalived-1.4.5.tar.gz #(2)安装前置依赖包 mount /dev/cdrom /mnt #加载本地源,有网络直接配置也可(参考:yum源配置、epel源配置) yum install kernel-devel openssl-devel popt-devel -y #(3)安装 cd /soft/ tar -zxvf keepalived-1.3.3.tar.gz cd keepalived-1.3.4 ./configure --prefix=/usr/local/keepalived/ make && make install #(4)设置keepalived开机自启 systemctl enable keepalived
#默认日志在 /var/log/messages
------------下面(5)(6)可以忽略------------------------ #(5)如果不设置自定义目录,默认目录如下 #cp /usr/local/etc/rc.d/init.d/keepalived /etc/rc.d/init.d/ #cp /usr/local/etc/sysconfig/keepalived /etc/sysconfig/ #mkdir /etc/keepalived #cp /usr/local/etc/keepalived/keepalived.conf /etc/keepalived/ #cp /usr/local/sbin/keepalived /usr/sbin/ #(5)构建keepalived 配置文件目录与文件 mkdir /etc/keepalived cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/ #(6)基本配置文件复制 cp usr/local/keepalived/sbin/keepalived /usr/sbin/ cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
【1.3】修改 Keepalived 默认日志位置
#默认错误日志位置:/var/log/messages #(1)修改keepalived的文件 vim /usr/local/keepalived/etc/sysconfig/keepalived #左边改成右边 KEEPALIVED_OPTIONS="-D" =》 KEEPALIVED_OPTIONS="-D -d -S 0" # --dont-release-ipvs -I Dont remove IPVS topology on daemon stop. # --dump-conf -d Dump the configuration data. # --log-detail -D Detailed log messages. # --log-facility -S 0-7 Set local syslog facility (default=LOG_DAEMON) #(2)修改日志级别参数文件 vi /etc/rsyslog.conf #在最后一行加上 local0.* /var/log/keepalived.log #(3)重启日志服务
systemctl daemon-reload systemctl restart rsyslog
【2】HAproxy的安装与配置
【2.1】HAproxy下载与安装
#下载地址: https://src.fedoraproject.org/repo/pkgs/haproxy/ http://download.openpkg.org/components/cache/haproxy/haproxy-1.8.25.tar.gz : https://pan.baidu.com/s/1NzhZc85jxOJsx9plnXi6qQ #安装: tar -xvf haproxy-1.8.25.tar.gz
cd haproxy-1.8.25/ make TARGET=linux2628 ARCH=x86_64 PREFIX=/usr/local/haproxy make install PREFIX=/usr/local/haproxy
#核验: /usr/local/haproxy/sbin/haproxy -v
【2.2】HAproxy开启日志(如果主机性能差的话不建议开启)
#添加不能登录的用户 haproxy
useradd -r -s /sbin/nologin haproxy
#先创建好日志文件
touch /var/log/haproxy.log
chmod 755 /var/log/haproxy.log
#开启日志(新建这个文件)
vim /etc/rsyslog.conf
#文件内容
$ModLoad imudp #找到这2个选项,把它们打开UDP的
$UDPServerRun 514
local0.* /var/log/haproxy.log #这行是新家的,放到文末
#修改日志选项
vim /etc/sysconfig/rsyslog
=》内容改为
SYSLOGD_OPTIONS="-r -m 0 -c 2"
#重启系统日志
systemctl restart rsyslog
【3】整合keepalived 与 haproxy 的 keepalived.conf 文件配置
【3.1】编辑配置文件 keepalived.conf
mkdir /etc/keepalived
mkdir /etc/keepalived/scripts vim /etc/keepalived/keepalived.conf
vim /etc/keepalived/keepalived.conf
【参考代码】
global_defs {#全局定义 router_id HAproxy-HA #全局路由ID,主从需要相同 } vrrp_script check_haproxy {#VRRP自定义脚本,check_run自定义名称 script "/etc/keepalived/scripts/check_haproxy.sh" #调用的脚本文件URL interval 60 #运行间隔,单位为秒 } vrrp_sync_group VG1 {#vrrp同步组,VG1是自定义名称 group {#组内成员 VI_1 } } vrrp_instance VI_1 {#vrrp虚拟实例 state BACKUP #从 interface ens34 #针对监听的网络接口 virtual_router_id 51 #唯一路由ID,主从要一致 priority 90 #权限 advert_int 1 #广播频率,单位是秒 nopreempt #非抢占模式 authentication {#同一组keepalived的验证方式 auth_type PASS auth_pass 1234 } track_script {#定义跟踪使用脚本 check_run #这里就是我们上面自己定义的脚本 } notify_master /etc/keepalived/script/haproxy_master.sh #当本机换为Master状态时,会呼叫执行 notify_master notify_stop /etc/keepalived/script/haproxy_stop.sh #当本机停止keepalived 时,会呼叫执行 notify_stop notify_backup /etc/keepalived/script/haproxy_backup.sh #当本机进入Backup状态时,会呼叫执行 notify_backup notify_fault /etc/keepalived/script/haproxy_fault.sh #当本机发现异常情况进入Fault状态时,会呼叫执行 notify_fault virtual_ipaddress { #设置虚拟IP 192.168.1.210 } }
【我的实际】直接可用的代码
#master global_defs {#全局定义 router_id HAproxy-HA #全局路由ID,主从需要相同 } vrrp_script check_haproxy {#VRRP自定义脚本,check_run自定义名称 script "/etc/keepalived/scripts/check_haproxy.sh" #调用的脚本文件URL interval 60 #运行间隔,单位为秒 } vrrp_sync_group VG1 {#vrrp同步组,VG1是自定义名称 group {#组内成员 VI_1 } } vrrp_instance VI_1 {#vrrp虚拟实例 state BACKUP #从 interface ens34 #针对监听的网络接口 virtual_router_id 51 #唯一路由ID,主从要一致 priority 100 #权限 advert_int 1 #广播频率,单位是秒 nopreempt #非抢占模式 authentication {#同一组keepalived的验证方式 auth_type PASS auth_pass 1234 } track_script {#定义跟踪使用脚本 check_haproxy #这里就是我们上面自己定义的脚本 } notify_master /etc/keepalived/scripts/haproxy_master.sh #当本机换为Master状态时,会呼叫执行 notify_master notify_stop /etc/keepalived/scripts/haproxy_stop.sh #当本机停止keepalived 时,会呼叫执行 notify_stop notify_backup /etc/keepalived/scripts/haproxy_backup.sh #当本机进入Backup状态时,会呼叫执行 notify_backup notify_fault /etc/keepalived/scripts/haproxy_fault.sh #当本机发现异常情况进入Fault状态时,会呼叫执行 notify_fault virtual_ipaddress { #设置虚拟IP 192.168.1.210 } }
slave
#slave global_defs {#全局定义 router_id HAproxy-HA #全局路由ID,主从需要相同 } vrrp_script check_haproxy {#VRRP自定义脚本,check_run自定义名称 script "/etc/keepalived/scripts/check_haproxy.sh" #调用的脚本文件URL interval 60 #运行间隔,单位为秒 } vrrp_sync_group VG1 {#vrrp同步组,VG1是自定义名称 group {#组内成员 VI_1 } } vrrp_instance VI_1 {#vrrp虚拟实例 state BACKUP #从 interface ens34 #针对监听的网络接口 virtual_router_id 51 #唯一路由ID,主从要一致 priority 90 #权限 advert_int 1 #广播频率,单位是秒 nopreempt #非抢占模式 authentication {#同一组keepalived的验证方式 auth_type PASS auth_pass 1234 } track_script {#定义跟踪使用脚本 check_haproxy #这里就是我们上面自己定义的脚本 } notify_master /etc/keepalived/scripts/haproxy_master.sh #当本机换为Master状态时,会呼叫执行 notify_master notify_stop /etc/keepalived/scripts/haproxy_stop.sh #当本机停止keepalived 时,会呼叫执行 notify_stop notify_backup /etc/keepalived/scripts/haproxy_backup.sh #当本机进入Backup状态时/即从库状态,会呼叫执行 notify_backup notify_fault /etc/keepalived/scripts/haproxy_fault.sh #当本机发现异常情况进入Fault状态时,会呼叫执行 notify_fault virtual_ipaddress { #设置虚拟IP 192.168.1.210 } }
【3.2】-【3.6】在200、201这两台机器上都需要配置
【3.2】check_haproxy.sh
vim /etc/keepalived/scripts/check_haproxy.sh
#!/bin/bash STARTHAPROXY="/usr/local/haproxy/sbin/haproxy -f /usr/local/haproxy/haproxy.cfg" #STOPKEEPALIVED="systemctl stop keepalived" LOGFILE="/etc/keepalived/scripts/keepalived-haproxy-state.log" echo "[check_haproxy_status]" >> ${LOGFILE} A=`ps -C haproxy --no-header |wc -l` date "+%F_%T" >>${LOGFILE} if [ $A -eq 0 ];then echo ${STARTHAPROXY} >> ${LOGFILE} ${STARTHAPROXY} >> ${LOGFILE} 2>&1 sleep 5 fi if [ `ps -C haproxy --no-header |wc -l` -eq 0 ];then exit 1 else exit 0 fi
【3.3】haproxy_master.sh
vim /etc/keepalived/scripts/haproxy_master.sh
#!/bin/bash STARTHAPROXY=`/usr/local/haproxy/sbin/haproxy -f /usr/local/haproxy/haproxy.cfg` STOPHAPROXY=`ps -ef|grep sbin/haproxy|grep -v grep|awk '{print $2}'|xargs kill -s 9` LOGFILE="/etc/keepalived/scripts/keepalived-haproxy-state.log" echo "[master]">>${LOGFILE} date "+%F_%T">>${LOGFILE} echo "Beging master......" >> ${LOGFINLE} 2>&1 echo "Stop haproxy......" >>${LOGFILE} 2>&1 ${STOPHAPROXY} >>${LOGFILE} 2>&1 echo "Start haproxy">>${LOGFILE} 2>&1 ${STARTHAPROXY} >> ${LOGFILE} 2>&1 if [ $? -eq 0 ];then echo "haproxy started......">>${LOGFILE} 2>&1 else echo "[error] haproxy start failed......">>${LOGFILE} 2>&1 fi
【3.4】haproxy_backup.sh
vim /etc/keepalived/scripts/haproxy_backup.sh
#!/bin/bash STARTHAPROXY=`/usr/local/haproxy/sbin/haproxy -f /usr/local/haproxy/haproxy.cfg` STOPHAPROXY=`ps -ef|grep sbin/haproxy|grep -v grep|awk '{print $2}'|xargs kill -s 9` echo "[master=》backup]">>${LOGFILE} date "+%F_%T">>${LOGFILE} echo "Beging master......" >> ${LOGFINLE} 2>&1 echo "Stop haproxy......" >>${LOGFILE} 2>&1 ${STOPHAPROXY} >>${LOGFILE} 2>&1 echo "Start haproxy">>${LOGFILE} 2>&1 ${STARTHAPROXY} >> ${LOGFILE} 2>&1 if [ $? -eq 0 ];then echo "haproxy started......">>${LOGFILE} 2>&1 else echo "[error] haproxy start failed......">>${LOGFILE} 2>&1 fi
【3.5】haproxy_fault.sh
vim /etc/keepalived/scripts/haproxy_fault.sh
#!/bin/bash LOGFILE="/etc/keepalived/scripts/keepalived-haproxy-state.log" echo "[fault]" >>${LOGFILE} date "+%F_%T">>${LOGFILE}
【3.6】haproxy_stop.sh
vim /etc/keepalived/scripts/haproxy_stop.sh
#!/bin/bash LOGFILE="/etc/keepalived/scripts/keepalived-haproxy-state.log" echo "[stop]" >>${LOGFILE} date "+%F_%T">>${LOGFILE} chmod 755 /etc/keepalived/scripts/*
【4】Haproxy配置(200.201都需要)
【4.1】参数文件 haproxy.cfg
vim /usr/local/haproxy/haproxy.cfg
global #全局配置参数 log 127.0.0.1 local0 notice #设置定义日志,notice通知级别 #user haproxy #group haproxy daemon #以后台形式运行haproxy #quiet nbproc 1 #后台运行的进程数量,可以设置多个进程提高性能(根据CPU) pidfile /usr/local/haproxy/haproxy.pid #haproxy的pid进程文件存放路径 defaults #一些默认参数 log global #全局日志 retries 3 #最多重试次数,3次连接失败,就认为服务器不可用 option dontlognull #保证haproxy不记录上次负载均衡发送过来的,用于检测存活状态但没有实际数据的心跳包。 option redispatch #serverid 对应的服务器挂掉以后,强制执行到其他健康的服务器 maxconn 2000 #默认最大连接数 timeout queue 1m #等待最大时长 timeout http-request 10s #客户端建立连接但又不请求数据的时候,超过10s,就关闭该客户端连接 timeout connect 10s #haproxy将客户端请求转发至后端服务器所等待的超时时长 timeout server 1m #服务端非活动状态的超时时间 timeout client 1m #客户端非活动状态的超时时间 timeout http-keep-alive 10s #设置 http-keep-alived的超时时间 timeout check 10s #健康监测、心跳检测的超时时间 balance roundrobin #负载均衡算法方式(默认就是这个RR,即轮询),(保存会话session的话,使用 源地址/cookies,针对web应用) #定义负载均衡配置 listen mysql_slave_wgpt_lb1 bind 192.168.1.210:3306 #绑定ip和端口(官方建议:bind *:3306,即本机所有IP通过3306均可访问负载均衡服务器) mode tcp #模式是TCP(还可以7层http,4层tcp,如果是mysql用tcp,如果是weblogic/was/tomcat之类的就用http) option mysql-check user haproxy_check #使用haproxy_check用户去检测mysql是否存活/是否可以访问,mysql-check参数有其自己的一套检测方案,无需担心。 stats hide-version #隐藏统计页面上的haproxy的版本信息 balance roundrobin #服务器定义,check inter 2000 检测心跳评率(单位是毫秒,即2s一次),rise 2(检测2次,2次均为正常则可用),fall 5(5次失败,认为服务器不可用),maxconn 300(服务器的最大连接数300个) server mysql202 192.168.1.202:3306 weight 1 check inter 2000 rise 2 fall 5 maxconn 300 server mysql203 192.168.1.203:3306 weight 1 check inter 2000 rise 2 fall 5 maxconn 300 #定义自带的监控服务器页面参数配置 listen haproxy_stats mode http bind *:8888 #使用本机任意IP地址及8888端口可以访问 option httplog #定义http日志 stats refresh 5s #定义数据状态5s刷新一次 stats uri /haproxy-stats stats realm haproxy monitor #页面上显示的主页文本 haproxy monitor stats realm Haproxy Manager #同上 stats auth haproxy:haproxy #进入监控页面的账户密码
【4.2】为配置文件里的配置创建mysql用户
在mysql的master执行,比如我们这里主从是202/203,所以我们在202上执行,会通过复制传递到从库上去的
create user haproxy_check@'192.168.1.%'; grant usage on *.* to haproxy_check@'192.168.1.%'; flush privileges;
-- 查看账户
select user,host from mysql.user;
【4.3】启动、查看 Keepalived
systemctl daemon-reload #重新装载服务 systemctl start keepalived #启动服务 systemctl enable keepalived #设置服务开机自启 systemctl status keepalived #查看服务状态,有没有报错最快可以看这里 ps -ef|grep keepalived|grep -v "grep" #查看服务进程
![](https://img2020.cnblogs.com/i1/1302413/202005/1302413-20200504200807401-40415911.png)
【4.4】测试负载均衡
mysql -uroot -p123456 -h192.168.1.210 -e"select @@hostname;"
mysql -uroot -p123456 -h192.168.1.210 -e"show variables like 'server_id';"
![](https://img2020.cnblogs.com/i1/1302413/202005/1302413-20200504204534657-1918150628.png)
![](https://img2020.cnblogs.com/i1/1302413/202005/1302413-20200504204627036-720024837.png)
【4.5】通过web方式查看haproxy状态
http://192.168.1.210:8888/haproxy-stats
【4.6】宕机之后连接会定向到存活机器
我把202主库机器的Mysql服务停掉之后,比LVS好多了,LVS坑的一批
【5】keepalived状态检测脚本(防止keepalived死掉之后,切换后,原主keepalived程序不启动,导致无法再次故障转移)
【5.1】keepcheck.sh(200.201都需要)
vim /etc/keepalived/keepcheck.sh
#!/bin/bash while [ 1 -eq 1 ] do keepalivedcheck=`ps -C keepalived --no-header |wc -l` if [ ${keepalivedcheck} -eq 0 ];then systemctl start keepalived fi sleep 5 done
【5.2】添加到开启自动启动
#注意,如果用/etc/init.d/keepalived start 或者 systemctl start keepalived 起不来,可以使用 /usr/local/keepalived/sbin/keepalived start 方式来启动脚本 #启动脚本 chmod +x /etc/keepalived/keepcheck.sh nohup /etc/keepalived/keepcheck.sh & #写入开机自动启动 echo "nohup /etc/keepalived/keepcheck.sh &" >>/etc/rc.local
(可忽略)【6】定义haproxy手动启动脚本(我们之前在keepalived里已经定义启动了,这里只是方便单独使用)
【6.1】haproxy
vim /etc/rc.d/init.d/haproxy
#!/bin/sh set -e PATH=/sbin:/bin:/usr/sbin:/usr/bin:/usr/local/haproxy/sbin PROGDIR=/usr/local/haproxy PROGNAME=haproxy DAEMON=$PROGDIR/sbin/$PROGNAME CONFIG=$PROGDIR/${PROGNAME}.cfg PIDFILE=$PROGDIR/$PROGNAME.pid DESC="HAproxy daemon" SCRIPTNAME=/etc/init.d/$PROGNAME #Gracefully exit if the package has been removed test -x $DAEMON || exit 0 start() { echo -n "Starting ${DESC}:${PROGNAME}" $DAEMON -f $CONFIG echo "." } stop() { echo -n "stopping ${DESC}:${PROGNAME}" haproxy_id=`cat ${PIDFILE}` kill -9 ${haproxy_id} echo "." } restart() { echo -n "restarting ${DESC}:${PROGNAME}" $DAEMON -f $CONFIG -p $PIDFILE -sf $(cat ${PIDFILE}) echo "." } case "$1" in start) start ;; stop) stop ;; restart) restart ;; *) echo "Usage:$SCRIPTNAME {start|stop|restart}" >&2 exit 1 ;; esac exit 0
【6.2】授权、添加到开机启动脚本
chmod +x /etc/rc.d/init.d/haproxy echo "nohup /etc/rc.d/init.d/haproxy start &" >> /etc/rc.d/rc.local