共计 11774 个字符，预计需要花费 30 分钟才能阅读完成。
本篇内容主要讲解“怎么用 MySQL+Keepalived+haproxy 实现负载均衡”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让丸趣 TV 小编来带大家学习“怎么用 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.conf
echo net.ipv4.ip_forward=1 /etc/sysctl.conf
sysctl -pnet.ipv4.ip_nonlocal_bind=1 意思是启动 haproxy 的时候，允许忽视 VIP 的存在
net.ipv4.ip_forward = 1 haproxy 代理服务器同时也要打开内核的转发功能
二、安装 keepalived 和 haproxy
mount /dev/cdrom /media
yum install kernel-devel openssl-devel popt-devel -y
cd /software
tar zxvf keepalived-1.4.5.tar.gz
cd keepalived-1.4.5
./configure --prefix=/usr/local/keepalived/
make make install
systemctl enable keepalivedvi /usr/local/keepalived/etc/sysconfig/keepalived
KEEPALIVED_OPTIONS= -D -d -S 0vi /etc/rsyslog.conf
local0.* /var/log/keepalived.logsystemctl restart rsyslogcd /software
tar zxvf haproxy-1.8.9.tar.gz
cd haproxy-1.8.9
make TARGET=linux2628 PREFIX=/usr/local/haproxy
make install PREFIX=/usr/local/haproxy
/usr/local/haproxy/sbin/haproxy -v
useradd -r -s /sbin/nologin haproxy
touch /var/log/haproxy.log
chmod 755 /var/log/haproxy.log修改日志位置
vi /etc/rsyslog.conf
local0.* /var/log/haproxy.log
vi /etc/sysconfig/rsyslog
SYSLOGD_OPTIONS= -r -m 0 -c 2
systemctl restart rsyslog
三、配置 keepalived
192.168.8.59
/etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
router_id mysql-slave
notification_email {
zdd5503@163.com
notification_email_from zdd5503@163.com
smtp_server stmp.163.com
smtp_connect_timeout 30
vrrp_script chk_haproxy {
script /etc/keepalived/scripts/check_haproxy.sh
interval 2
weight 2
vrrp_instance v_mysql_slave_zdd01 {
interface enp0s3
state MASTER
virtual_router_id 98
priority 200
nopreempt
virtual_ipaddress {
192.168.8.98/24
track_script {
chk_haproxy
notify_master /etc/keepalived/scripts/haproxy_master.sh
notify_backup /etc/keepalived/scripts/haproxy_backup.sh
notify_fault /etc/keepalived/scripts/haproxy_fault.sh
notify_stop /etc/keepalived/scripts/haproxy_stop.sh
}192.168.8.61
/etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
router_id mysql-slave
notification_email {
zdd5503@163.com
notification_email_from zdd5503@163.com
smtp_server stmp.163.com
smtp_connect_timeout 30
vrrp_script chk_haproxy {
script /etc/keepalived/scripts/check_haproxy.sh
interval 2
weight 2
vrrp_instance v_mysql_slave_zdd01 {
interface enp0s3
state BACKUP
virtual_router_id 98
priority 150
nopreempt
virtual_ipaddress {
192.168.8.98/24
track_script {
chk_haproxy
notify_master /etc/keepalived/scripts/haproxy_master.sh
notify_backup /etc/keepalived/scripts/haproxy_backup.sh
notify_fault /etc/keepalived/scripts/haproxy_fault.sh
notify_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/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`
echo [check_haproxy status] $LOGFILE
date $LOGFILE
if [ $A -eq 0 ];then
echo $STARTHAPROXY $LOGFILE
$STARTHAPROXY $LOGFILE 2 1
sleep 5
if [ `ps -C haproxy --no-header |wc -l` -eq 0 ];then
exit 0
exit 1
fivi /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 $LOGFILE
echo Being master.... $LOGFILE 2 1
echo stop haproxy.... $LOGFILE 2 1
$STOPHAPROXY $LOGFILE 2 1
echo start haproxy.... $LOGFILE 2 1
$STARTHAPROXY $LOGFILE 2 1
echo haproxy stared ... $LOGFILEvi /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`
LOGFILE= /etc/keepalived/scripts/keepalived-haproxy-state.log
echo [master] $LOGFILE
date $LOGFILE
echo Being master.... $LOGFILE 2 1
echo stop haproxy.... $LOGFILE 2 1
$STOPHAPROXY $LOGFILE 2 1
echo start haproxy.... $LOGFILE 2 1
$STARTHAPROXY $LOGFILE 2 1
echo haproxy stared ... $LOGFILEvi /etc/keepalived/scripts/haproxy_fault.sh
#!/bin/bash
LOGFILE=/etc/keepalived/scripts/keepalived-haproxy-state.log
echo [fault] $LOGFILE
date $LOGFILEvi /etc/keepalived/scripts/haproxy_stop.sh
#!/bin/bash
LOGFILE=/etc/keepalived/scripts/keepalived-haproxy-state.log
echo [stop] $LOGFILE
date $LOGFILE更改脚本属性
chmod 755 /etc/keepalived/scripts/*配置 haproxy
192.168.8.59
vi /usr/local/haproxy/haproxy.cfg
global # 全局配置参数
log 127.0.0.1 local0 notice # 日志
#user haproxy
#group haproxy
daemon # 以后台形式运行 haproxy
#quiet
nbproc 1 # 进程数量，可以设置多个进程提高性能
pidfile /usr/local/haproxy/haproxy.pid #haproxy 的 pid 存放路径
defaults # 一些默认参数
log global # 全局日志
retries 3 # 3 次连接失败就认为服务器不可用
option dontlognull # 保存 haproxy 不记录上次负载均衡发送过来的用于检测状态没有数据的心跳包
option redispatch # 对应的服务器挂掉后，强制指向到其它正常的服务器
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-alive 超时时长
timeout check 10s # 健康状态监测时的超时时间
balance roundrobin # 负载均衡方式，轮询方式（保持会话 session: 源地址 /cookies, 针对 web 应用服务器）
#定义负载均衡的配置
listen mysql_slave_wgpt_lb1
bind 192.168.8.98:3307 # 绑定的 IP 与端口 (建议 bind *:3306)
mode tcp # 模式是 TCP，（7 层 http,4 层 tcp，如果是 mysql 用 tcp，如果是 weblogic/was/tomcat 之类的就用 http）option mysql-check user haproxy_check # 通过 mysql 连接去检测 mysql 是否可以访问
stats hide-version # 隐藏统计页上的 haproxy 的版本信息
balance roundrobin # 负载均衡方式，轮询方式
server slave2 192.168.8.59:3306 weight 1 check inter 2000 rise 2 fall 5 maxconn 300
server slave3 192.168.8.61:3306 weight 1 check inter 2000 rise 2 fall 5 maxconn 300
# 服务器定义,check inter 2000 检测心跳频率，rise 2 2 次正确认为服务器可用，fall 5 5 次失败认为服务器不可用， #maxconn 300 最大连接数 300
#自带的监控服务器的配置
listen haproxy_stats
mode http
bind *:8888
option httplog
stats refresh 5s
stats uri /haproxy-stat
stats realm www.zdd.com monitor
stats realm Haproxy Manager
stats auth haproxy:haproxy192.168.8.61
vi /usr/local/haproxy/haproxy.cfg
global # 全局配置参数
log 127.0.0.1 local0 notice # 日志
#user haproxy
#group haproxy
daemon # 以后台形式运行 haproxy
#quiet
nbproc 1 # 进程数量，可以设置多个进程提高性能
pidfile /usr/local/haproxy/haproxy.pid #haproxy 的 pid 存放路径
defaults # 一些默认参数
log global # 全局日志
retries 3 # 3 次连接失败就认为服务器不可用
option dontlognull # 保存 haproxy 不记录上次负载均衡发送过来的用于检测状态没有数据的心跳包
option redispatch # 对应的服务器挂掉后，强制指向到其它正常的服务器
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-alive 超时时长
timeout check 10s # 健康状态监测时的超时时间
balance roundrobin # 负载均衡方式，轮询方式（保持会话 session: 源地址 /cookies, 针对 web 应用服务器）
#定义负载均衡的配置
listen mysql_slave_wgpt_lb1
bind 192.168.8.98:3307 # 绑定的 IP 与端口 (建议 bind *:3306)
mode tcp # 模式是 TCP，（7 层 http,4 层 tcp，如果是 mysql 用 tcp，如果是 weblogic/was/tomcat 之类的就用 http）option mysql-check user haproxy_check # 通过 mysql 连接去检测 mysql 是否可以访问
stats hide-version # 隐藏统计页上的 haproxy 的版本信息
balance roundrobin # 负载均衡方式，轮询方式
server slave2 192.168.8.59:3306 weight 1 check inter 2000 rise 2 fall 5 maxconn 300
server slave3 192.168.8.61:3306 weight 1 check inter 2000 rise 2 fall 5 maxconn 300
# 服务器定义,check inter 2000 检测心跳频率，rise 2 2 次正确认为服务器可用，fall 5 5 次失败认为服务器不可用， #maxconn 300 最大连接数 300
#自带的监控服务器的配置
listen haproxy_stats
mode http
bind *:8888
option httplog
stats refresh 5s
stats uri /haproxy-stat
stats realm www.zdd.com monitor
stats realm Haproxy Manager
stats auth haproxy:haproxy五、添加 haproxy_check 用户
drop user haproxy_check@ %
create user haproxy_check@ 192.168.8.59
create user haproxy_check@ 192.168.8.61
create user haproxy_check@ 192.168.8.98
grant usage on *.* to haproxy_check@ 192.168.8.59
grant usage on *.* to haproxy_check@ 192.168.8.61
grant usage on *.* to haproxy_check@ 192.168.8.98mysql select user,host from mysql.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
systemctl daemon-reload
systemctl enable keepalived
systemctl start keepalivedip a |grep 98
ps -ef |grep keepalived七、配置 keepalived 监控脚本
两个节点都做如下配置：
vi /etc/keepalived/keepcheck.sh
#!/bin/bash
while :
keepalivedcheck=`ps -C keepalived --no-header | wc -l`
if [ $keepalivedcheck -eq 0 ];then
systemctl start keepalived
echo keepalived is running
sleep 5
donechmod +x /etc/keepalived/keepcheck.sh
nohup /etc/keepalived/keepcheck.sh
echo nohup /etc/keepalived/keepcheck.sh /etc/rc.d/rc.local八、配置 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_pid=`cat $PIDFILE`
kill -9 $haproxy_pid
echo .
restart()
echo -n Restarting $DESC: $PROGNAME
$DAEMON -f $CONFIG -p $PIDFILE -sf $(cat $PIDFILE)
echo .
case $1 in
start)
start
stop)
restart)
restart
echo Usage: $SCRIPTNAME {start|stop|restart} 2
exit 1
exit 0chmod +x /etc/rc.d/init.d/haproxy
echo nohup /etc/rc.d/init.d/haproxy start /etc/rc.d/rc.localsystemctl stop keepalived
systemctl start keepalived
tail -f /var/log/keepalived.log/etc/rc.d/init.d/haproxy start
/etc/rc.d/init.d/haproxy stop
tail -f /var/log/haproxy.loghaproxy 控制台访问地址：
http://192.168.8.98:8888/haproxy-stat
九、测试负载均衡
[root@slave3 ~]# mysql -uroot -pmysql -h292.168.8.98 -P3307 -e show variables like server_id
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 59 |
+---------------+-------+
[root@slave3 ~]# mysql -uroot -pmysql -h292.168.8.98 -P3307 -e show variables like server_id
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 61 |
+---------------+-------+
[root@slave3 ~]# mysql -uroot -pmysql -h292.168.8.98 -P3307 -e show variables like server_id
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 59 |
+---------------+-------+
[root@slave3 ~]# mysql -uroot -pmysql -h292.168.8.98 -P3307 -e show variables like server_id
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 61 |
+---------------+-------+
[root@slave3 ~]# mysql -uroot -pmysql -h292.168.8.98 -P3307 -e show variables like server_id
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 59 |
+---------------+-------+
[root@slave3 ~]# mysql -uroot -pmysql -h292.168.8.98 -P3307 -e show variables like server_id
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 61 |
+---------------+-------+因为我们在 haproxy 配置文件中配置了权重为 1：1，所以会话将交替分发到两个节点上边。
到此，相信大家对“怎么用 MySQL+Keepalived+haproxy 实现负载均衡”有了更深的了解，不妨来实际操作一番吧！这里是丸趣 TV 网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！
正文完
