mysql【4】Haproxy+keepalived+mysql，实现MySQL多节点负载均衡

原创已于 2024-06-16 17:06:21 修改 · 1.1k 阅读

10 ·

本内容遵循CC 4.0 BY-SA版权协议

GEO检测

标签

#mysql #负载均衡 #数据库

于 2024-06-16 13:02:23 首次发布

由于Keeplived是基于VRRP的协议，所以用虚拟IP只能保证MYSQL主节点能访问，其它备用节点只有在主节点失效后，才能接受虚拟IP的MYSQL的请求，达不到每个MYSQL节点负载均衡的接受虚拟IP的MYSQL的请求，所以必须加入Haproxy的模块，才能达到负载均衡的效果！

好的，Haproxy如何安装，就不介绍了，yum install haproxy

如何设置keepalived 可参考mysql【3】keepalived+mysql双主同步，实现故障转移群集_mysql互为主从模式,keepalived实现故障切换-CSDN博客

./haproxymaster.sh #mysql主节点上的设置haproxy
./filetransferRemote.sh #mysql主节点向次节点发送用于设置haproxy的文件
./function.sh #mysql主节点生成公钥，并用于免密传送文件给次节点
./haproxyslave.sh #mysql次节点上的设置haproxy
./errlog.sh #查看日志或错误

在主节点上运行 sh ./haproxymaster.sh

haproxymaster.sh 的内容如下:

clear
find / -name "haproxy.cfg" -type f ! -path "$(pwd)/*"|awk '{print NR":"$0}'|tee tmp
echo -e "\033[33m 请按序号选择配置文件，如输入1 \033[0m" 
read -p "" n
cnf=`cat tmp|awk -F: 'NR=="'${n}'"{print $2}'`
echo 备份你的配置文件 ${cnf}
#cp  $cnf `date +%F`"[master]"${cnf##*/}

echo 配置你的日志
#yum list installed|grep rsyslog
rpm -qa|grep "rsyslog"
if [[ $? -ne 0 ]]; then
	yum install -y rsyslog  	
fi
log="/etc/rsyslog.conf"
#cp $log ${log%/*}/`date +%F`${log##*/}

sed -i "/\$UDPServerRun 514/d" $log
sed -i "/# Provides UDP/a\$UDPServerRun 514" /etc/rsyslog.conf
#echo '$UDPServerRun 514' >> $log
sed -i "/\$ModLoad imudp/d" $log
sed -i "/# Provides UDP/a\$ModLoad imudp" /etc/rsyslog.conf
#echo '$ModLoad imudp' >> $log
sed -i "/^local2.*/d" $log
sed -i "/#### RULES ####/alocal2.*                       /var/log/haproxy.log" $log
#systemctl restart rsyslog

echo -e "\033[37m 设置你的后端服务器个数\033[0m"
read -p "" n
(for (( i = 0; i < n; i++ )); do
	read -p "如192.168.31.16`expr $i + 1`:3306,请输入你第`expr $i + 1`个服务器的的IP和端口号" a
	echo "    server db$(expr $i + 1) ${a} weight 1 check inter 2000 rise 2 fall 3"	
done) >ser

echo -e "\033[36m 请设置服务器组名 \033[0m"
read -p "" sergroup
echo -e "\033[33m 请设置前端转发端口 \033[0m" 
read -p "" forwardport
echo -e "\033[35m 请设置WEB管理端口 \033[0m" 
read -p "" manageport
echo -e "\033[34m 设置WEB管理的用户名:密码, 如admin:123456 \033[0m" 
read -p "" usrpwd

echo -e "\033[31m 以下是haproxy.cfg的配置内容 \033[0m"
cat|tee $cnf <<eof
global
  #日志
  log 127.0.0.1 local0 info
  #最大连接数
  maxconn 10240
  daemon
  pidfile /usr/local/haproxy/logs/haproxy.pid
 
defaults
  #应用全局的日志配置
  log global
  mode http
  #超时配置
  timeout connect 5000
  timeout client 5000
  timeout server 5000
  timeout check 2000

frontend mysql-front
    bind *:$forwardport			# 这里绑定的端口就是后面使用 VIP 访问 mysql 时使用的端口
    mode tcp
    default_backend $sergroup
 
backend $sergroup
    mode tcp
    balance roundrobin
$(cat ser)
 
listen http_front #haproxy的客户页面
  bind 0.0.0.0:$manageport         #HAProxy自己的IP地址
  mode http
  option httplog
  stats uri /haproxy
  stats auth $usrpwd          #控制面板账号密码 账号：admin 
  stats refresh 5s
  stats enable
eof
haproxy -c -f $cnf

echo -e "\033[31m 开启你的haproxy服务 \033[0m"
systemctl restart rsyslog
systemctl enable haproxy
systemctl stop haproxy
systemctl start haproxy
systemctl status haproxy
cp $log ${log##*/}
cp $cnf ${cnf##*/}

等待服务重启完成，主节点的haproxy设置就完成了！
为了设置备用节点方便，我们直接将设置文件远程传输到此节点上

再主节点上运行 sh ./filetransferRemote.sh 即会将当前文件目录所有文件，自动传送到次节点对应的同文件目录下！

filetransferRemote.sh的内容如下

clear
if [[ ! -e ./haproxy.cfg ]]; then
	echo -e "\033[31m 没正确生成haproxy配置文件在当前目录！请重新执行haproxymaster.sh \033[0m" 
	exit 1	
fi
haproxy -c -f ./haproxy.cfg|grep -E "valid"
if [[ $? -ne 0  ]]; then
	echo -e "\033[31m 当前目录haproxy配置文件设置有误！请重新执行haproxymaster.sh \033[0m" 
	exit 1
fi

pf=$(pwd)
rpm -qa|grep "sshpass"
if [[ $? -ne 0 ]]; then
	yum install -y sshpass  	
fi
rpm -qa|grep "rsync"
if [[ $? -ne 0 ]]; then
	yum install -y rsync  	
fi
read -p "请输入远程服务器的用户,格式为 用户名@IP:" user_ip
read -p "请输入密码:" password
ip=${user_ip#*@}
user=${user_ip%@*}
sshpass="sshpass -p $password ssh -o StrictHostKeyChecking=no $user_ip mkdir -p ${pf};"
echo -e "\033[36m $sshpass \033[0m"
eval $sshpass

#加载函数
. ./function.sh
echo -e "\033[33m 加载免密连接远程服务器通过秘钥 \033[0m"
nopwdssh_bykey $user_ip
eval "$(ssh-agent -s)"
ssh-add
#tail /var/log/secure -n 20 查看日志
#ssh -vv root@192.168.31.168 date debug测试
echo -e "\033[37m 远程客户端已安装的秘钥 \033[0m"
ssh $ip  cat /root/.ssh/authorized_keys |grep -n -E ".*"

cpcmd="rsync -avzr --progress --delete $pf $ip:${pf%/*}" 
echo -e "\033[36m 复制文件到远端 $cpcmd \033[0m"
eval $cpcmd
echo -e "\033[36m 文件已传输! \r\n 请在远端服务器${ip}此目录下执行${pf}执行haproxyslave.sh \033[0m"

function.sh的代码

nopwdssh_bykey()
{
	#echo "$1"
	ssh-keygen
	key=`find ~/.ssh/ -name *id_rsa.pub|head -n 1`
	echo -e "\033[31m 连接远端服务器复制公钥 \033[0m"	
	ssh-copy-id -i ${key} ${1}
	#cat ~/.ssh/id_rsa.pub | ssh -p 端口号 用户名@主机名 "mkdir -p ~/.ssh && cat >> ~/.ssh/authorized_keys"
			
}

在远程次节点同文件目录下，执行sh ./haproxyslave.sh

haproxyslave.sh的代码如下

clear
find / -name "haproxy.cfg" -type f ! -path "$(pwd)/*"|awk '{print NR":"$0}'|tee tmp
echo -e "\033[33m 请按序号选择配置文件，如输入1 \033[0m" 
read -p "" n
cnf=`cat tmp|awk -F: 'NR=="'${n}'"{print $2}'`
echo 备份你的haproxy配置文件: ${cnf}
cp $cnf ${cnf%/*}/`date +%F`${cnf##*/}

#yum list installed|grep rsyslog
rpm -qa|grep "rsyslog"
if [[ $? -ne 0 ]]; then
	yum install -y rsyslog  	
fi
log="/etc/rsyslog.conf"
echo 备份你的rsyslog配置文件:${log}
cp $log ${log%/*}/`date +%F`${log##*/}

echo -e "\033[32m 拷贝配置文件 \033[0m"
cp ./haproxy.cfg ${cnf}
cp ./rsyslog.conf ${log}

echo -e "\033[31m 开启你的haproxy服务, 按任意键继续 \033[0m"
read -p "" pause 
systemctl restart rsyslog
systemctl enable haproxy
systemctl stop haproxy
systemctl start haproxy
systemctl status haproxy

至此，haproxy主次节点的设置完成，再任意节点上执行sh ./errlog.sh
查下错误日志和当前haproxy的状态！

errlog.sh 代码如下

clear
port=`grep  -A 3 -E "listen http_front" ./haproxy.cfg|grep -Po ":\d+"`
url=`awk 'BEGIN {FS=" "};$0~"stats uri" {print $3}' ./haproxy.cfg`
userpwd=`awk -F" " '/stats auth/{print $3}' ./haproxy.cfg`
echo -e "\033[35m 你可以通过浏览器访问\033[0m" 
find /etc/sysconfig/network-scripts -name "ifcfg*"|grep -v "bak" \
|xargs cat |awk -F"=" '/IPADDR/{print "http://"$2"'${port}${url}'" }'
echo -e "\033[33m 用户名:密码\033[0m" 
echo $userpwd

echo -e "\033[31m haproxy服务状态 \033[0m" 
systemctl status haproxy -l

echo -e "\033[37m haproxy当前系统日志 \033[0m"
tail -n 100 /var/log/haproxy.log # -f
journalctl -u haproxy -xel