当前位置： 代码迷 >> 综合 >> 配置Haproxy + Keepalived +PXC 5.7
  详细解决方案

配置Haproxy + Keepalived +PXC 5.7

热度：50   发布时间：2023-12-14 22:56:49.0

MySQL读写分离高的方案很多，可谓五花八门。其中Haproxy + Keepalived + MySQL(Master Slave)是较为常用的一种选择。本文基于PXC 5.7集群环境来配置Haproxy以及结合Keepalived实现MySQL的读写分离，以下为主要配置方法及其步骤。

一、当前环境

Haproxy IP | keepalived IP（两台主机都部署haproxy以及keepalived)
192.168.81.149 centos7A
192.168.81.150 centos7B
192.168.81.138 vipPXC IP
192.168.81.142 node142.example.com node142
192.168.81.146 node146.example.com node146
192.168.81.147 node147.example.com node147# more /etc/redhat-release
CentOS Linux release 7.2.1511 (Core)# mysql -V
mysql Ver 14.14 Distrib 5.7.19-17, for Linux (x86_64) using 6.2

二、配置PXC节点监控

1、PXC 三节点安装配置xinetd服务

以下以第一节点为示例
[root@node142 ~]# yum install xinetd -y
[root@node142 ~]# echo 'mysqlchk 9200/tcp # mysqlchk' >> /etc/services
[root@node142 ~]# systemctl enable xinetd
[root@node142 ~]# systemctl start xinetd

2、创建状态检查用户(任意一PXC节点即可)

以下以第一节点为示例
[root@node142 ~]# locate clustercheck
/usr/bin/clustercheck
[root@node142 ~]# grep clustercheckuser /usr/bin/clustercheck
# GRANT PROCESS ON *.* TO 'clustercheckuser'@'localhost' IDENTIFIED BY 'clustercheckpassword!';[root@node142 ~]# mysql -uroot -p
mysql> GRANT PROCESS ON *.* TO 'clustercheckuser'@'localhost' IDENTIFIED BY 'clustercheckpassword!';
Query OK, 0 rows affected, 1 warning (0.54 sec)mysql> flush privileges;
Query OK, 0 rows affected (0.08 sec)

3、PXC三节点手工执行clusterchek，确保返回200

以下以第一节点为示例 
[root@node142 ~]# clustercheck
HTTP/1.1 200 OK
Content-Type: text/plain
Connection: close
Content-Length: 40
Percona XtraDB Cluster Node is synced.

4、PXC三节点防火墙配置

以下以第一节点为示例  
[root@node146 ~]# firewall-cmd --add-port=9200/tcp --permanent
[root@node146 ~]# firewall-cmd --reload所有PXC节点，Haproxy节点建议关闭selinux，如下示例，建议重启生效
# vim /etc/selinux/config
SELINUX=disabled

三、Haproxy安装与配置

1、安装配置haproxy

以下以192.168.81.149 centos7A节点示例
[root@centos7a ~]# yum install haproxy -y
[root@centos7a ~]# cp /etc/haproxy/haproxy.cfg{,.org}添加以下内容到haproxy.cfg配置文件
[root@centos7a ~]# vim /etc/haproxy/haproxy.cfg 
frontend pxc-front     ##前端监控配置名称，端口，协议，对应的后端名称
bind *:3306               
mode tcp     
default_backend pxc-backfrontend stats-front    ##web状态监控配置
bind *:8080
mode http
default_backend stats-backbackend pxc-back   ###后端配置
mode tcp
balance leastconn
option httpchk
server node142 192.168.81.142:3306 check port 9200 inter 12000 rise 3 fall 3
server node146 192.168.81.146:3306 check port 9200 inter 12000 rise 3 fall 3
server node147 192.168.81.147:3306 check port 9200 inter 12000 rise 3 fall 3backend stats-back    ###web监控访问
mode http
balance roundrobin
stats uri /haproxy/stats
stats refresh 5s
stats auth pxcstats:secret防火墙配置
[root@centos7a ~]# firewall-cmd --permanent --add-port=3306/tcp
[root@centos7a ~]# firewall-cmd --permanent --add-port=8080/tcp
[root@centos7a ~]# firewall-cmd --reloadhaproxy日志配置(根据需要，此步骤可省略)
[root@centos7a ~]# rpm -qa|grep rsyslog
rsyslog-mmjsonparse-7.4.7-12.el7.x86_64
rsyslog-7.4.7-12.el7.x86_64
[root@centos7a ~]# rpm -ql rsyslog |grep conf$
/etc/rsyslog.conf[root@centos7a ~]# cp /etc/rsyslog.conf{,.org}
[root@centos7a ~]# vim /etc/rsyslog.conf
$ModLoad imudp ###该行注释去掉
$UDPServerRun 514 ###该行注释去掉
local0.* /var/log/haproxy.log ###文件尾部添加改行[root@centos7a ~]# cp /etc/sysconfig/rsyslog{,.org}
[root@centos7a ~]# vim /etc/sysconfig/rsyslog
SYSLOGD_OPTIONS="-r -m 0 -c 2" ###修改该行[root@centos7a ~]# vim /etc/haproxy/haproxy.cfg
log 127.0.0.1 local0 ###添加该行到global段启动日志服务及haproxy服务
[root@centos7a ~]# systemctl restart rsyslog.service
[root@centos7a ~]# systemctl start haproxy.service
[root@centos7a ~]# netstat -nltp|grep haproxy
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 22468/haproxy
tcp 0 0 0.0.0.0:8080 0.0.0.0:* LISTEN 22468/haproxy添加用于测试的mysql用户
[root@node142 ~]# mysql -uroot -pxxx -hlocalhost
mysql>grant all privileges on *.* to 'robin'@'192.168.%' identified by 'xxx';在192.168.81.149 centos7A节点测试mysql请求
# for i in `seq 1 1000`;
> do mysql -hlocalhost -P3306 -urobin -pxxx --protocol=tcp -e "select now()";
> done

2、验证haproxy

这里写图片描述

四、安装及配置keepalived

1、安装keepalived (两节点)

haproxy两节点分别安装keepalived，以下以第一节点为例
[root@centos7a ~]# yum install keepalived
[root@centos7a ~]# rpm -qa|grep keepalived
keepalived-1.3.5-1.el7.x86_64

3、配置防火墙(两节点)

以下为在centos7a节点上演示，224.0.0.18为keepalived组播地址
[root@centos7a ~]# firewall-cmd --direct --permanent --add-rule ipv4 filter INPUT 0 \
> --in-interface eno16777728 --destination 224.0.0.18 --protocol vrrp -j ACCEPT

2、配置keepalived

节点1配置
节点1与节点2最大的差异就是state的值，一个为MASTER，一个为BACKUP
另外一个就是优先级别，具体可参考：http://blog.csdn.net/leshami/article/details/42010495
[root@centos7a ~]# nmcli con show
NAME UUID TYPE DEVICE
eno16777728 52ff246a-d965-4056-b34f-16e8f4df2c0a 802-3-ethernet eno16777728[root@centos7a ~]# cp /etc/keepalived/keepalived.conf{,.org}
[root@centos7a ~]# vim /etc/keepalived/keepalived.conf
vrrp_script chk_haproxy {script "/etc/keepalived/chk_haproxy.sh" # check the haproxy processinterval 2 # every 2 secondsweight 2 # add 2 points if OK
}vrrp_instance VI_1 {interface eno16777728 # interface to monitorstate MASTER # MASTER on centos7A, BACKUP on centos7Bvirtual_router_id 51priority 100 # 100 on centos7A, 90 on centos7Bvirtual_ipaddress {192.168.81.138 # virtual ip address}track_script {chk_haproxy}
}[root@centos7a ~]# vim /etc/keepalived/chk_haproxy.sh
#!/bin/bash
stat=`ps -C haproxy --no-header |wc -l`if [ $stat -eq 0 ];then/usr/bin/systemctl start haproxysleep 3if [ `ps -C haproxy --no-header |wc -l` -eq 0 ];then/usr/bin/systemctl stop keepalivedfi
fi[root@centos7a ~]# chmod u+x /etc/keepalived/chk_haproxy.sh
[root@centos7a ~]# scp /etc/keepalived/chk_haproxy.sh 192.168.81.150:/etc/keepalived/节点2配置
[root@centos7b ~]# vim /etc/keepalived/keepalived.conf
vrrp_script chk_haproxy {script "/etc/keepalived/chk_haproxy.sh" # check the haproxy processinterval 2 # every 2 seconds weight 2 # add 2 points if OK
}vrrp_instance VI_1 {interface eno16777728 # interface to monitorstate BACKUP # MASTER on centos7A, BACKUP on centos7Bvirtual_router_id 51priority 90 # 100 on centos7A, 90 on centos7Bvirtual_ipaddress {192.168.81.138 # virtual ip address}track_script {chk_haproxy}
}[root@centos7b ~]# chmod u+x /etc/keepalived/chk_haproxy.sh

3、测试keepalived

启动第一个节点上的keepalived
[root@centos7a ~]# systemctl enable keepalived.service
[root@centos7a ~]# systemctl start keepalived.service
[root@centos7a ~]# ip addr|grep eno16777728
2: eno16777728: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
inet 192.168.81.149/24 brd 192.168.81.255 scope global dynamic eno16777728
inet 192.168.81.138/32 scope global eno16777728
[root@centos7a ~]# systemctl status keepalived
[root@centos7a ~]# systemctl status keepalived
● keepalived.service - LVS and VRRP High Availability Monitor
Loaded: loaded (/usr/lib/systemd/system/keepalived.service; enabled; vendor preset: disabled)
Active: active (running) since Fri 2018-01-05 15:54:56 CST; 1min 2s ago
Process: 50513 ExecStart=/usr/sbin/keepalived $KEEPALIVED_OPTIONS (code=exited, status=0/SUCCESS)
Main PID: 50514 (keepalived)
CGroup: /system.slice/keepalived.service
├─50514 /usr/sbin/keepalived -D
├─50515 /usr/sbin/keepalived -D
└─50516 /usr/sbin/keepalived -D[root@centos7a ~]# tail -fn 50 /var/log/messages
Jan 5 15:54:56 centos7A systemd: Starting LVS and VRRP High Availability Monitor...
Jan 5 15:54:56 centos7A Keepalived[50513]: Starting Keepalived v1.3.5 (03/19,2017), git commit v1.3.5-6-g6fa32f2
Jan 5 15:54:56 centos7A Keepalived[50513]: Unable to resolve default script username 'keepalived_script' - ignoring
Jan 5 15:54:56 centos7A Keepalived[50513]: Opening file '/etc/keepalived/keepalived.conf'.
Jan 5 15:54:56 centos7A Keepalived[50514]: Starting Healthcheck child process, pid=50515
Jan 5 15:54:56 centos7A Keepalived[50514]: Starting VRRP child process, pid=50516
Jan 5 15:54:56 centos7A systemd: Started LVS and VRRP High Availability Monitor.
Jan 5 15:54:56 centos7A Keepalived_vrrp[50516]: Registering Kernel netlink reflector
Jan 5 15:54:56 centos7A Keepalived_vrrp[50516]: Registering Kernel netlink command channel
Jan 5 15:54:56 centos7A Keepalived_vrrp[50516]: Registering gratuitous ARP shared channel
Jan 5 15:54:56 centos7A Keepalived_vrrp[50516]: Opening file '/etc/keepalived/keepalived.conf'.
Jan 5 15:54:56 centos7A Keepalived_vrrp[50516]: Unable to load ipset library - libipset.so.3:cannot open shared object file: No such file or directory  ##，注，此为keepalived Bug
Jan 5 15:54:56 centos7A Keepalived_vrrp[50516]: VRRP_Instance(VI_1) removing protocol VIPs.
Jan 5 15:54:56 centos7A Keepalived_vrrp[50516]: SECURITY VIOLATION - scripts are being executed but script_security not enabled.
Jan 5 15:54:56 centos7A Keepalived_vrrp[50516]: Using LinkWatch kernel netlink reflector...
Jan 5 15:54:56 centos7A Keepalived_vrrp[50516]: VRRP sockpool: [ifindex(2), proto(112), unicast(0), fd(10,11)]
Jan 5 15:54:57 centos7A Keepalived_vrrp[50516]: VRRP_Script(chk_haproxy) succeeded
Jan 5 15:54:57 centos7A kernel: IPVS: Registered protocols (TCP, UDP, SCTP, AH, ESP)
Jan 5 15:54:57 centos7A kernel: IPVS: Connection hash table configured (size=4096, memory=64Kbytes)
Jan 5 15:54:57 centos7A kernel: IPVS: Creating netns size=2040 id=0
Jan 5 15:54:57 centos7A kernel: IPVS: ipvs loaded.
Jan 5 15:54:57 centos7A Keepalived_healthcheckers[50515]: Opening file '/etc/keepalived/keepalived.conf'.
Jan 5 15:54:57 centos7A Keepalived_vrrp[50516]: VRRP_Instance(VI_1) Transition to MASTER STATE
Jan 5 15:54:57 centos7A Keepalived_vrrp[50516]: VRRP_Instance(VI_1) Changing effective priority from 100 to 102
Jan 5 15:54:59 centos7A Keepalived_vrrp[50516]: VRRP_Instance(VI_1) Entering MASTER STATE
Jan 5 15:54:59 centos7A Keepalived_vrrp[50516]: VRRP_Instance(VI_1) setting protocol VIPs.
Jan 5 15:54:59 centos7A Keepalived_vrrp[50516]: Sending gratuitous ARP on eno16777728 for 192.168.81.138
Jan 5 15:54:59 centos7A Keepalived_vrrp[50516]: VRRP_Instance(VI_1)Sending/queueing gratuitous ARPs on eno16777728 for 192.168.81.138Keepalived 1.3.5 Bug链接，经笔者测试，该Bug也可以通过升级到1.4版解决
https://github.com/acassen/keepalived/pull/436/files[root@centos7a ~]# ip addr|grep eno16777728
2: eno16777728: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
inet 192.168.81.149/24 brd 192.168.81.255 scope global dynamic eno16777728
inet 192.168.81.138/32 scope global eno16777728启动第二个节点上的keepalived 
[root@centos7b ~]# systemctl enable keepalived
[root@centos7b ~]# systemctl start keepalivedvip漂移到备节点测试
[root@centos7a ~]# systemctl stop keepalived
[root@centos7a ~]# tail -fn 50 /var/log/messages
Jan 5 15:59:00 centos7A Keepalived[50514]: Stopping  ##Author : Leshami
Jan 5 15:59:00 centos7A Keepalived_healthcheckers[50515]: Stopped ## Blog : http://blog.csdn.net/leshami
Jan 5 15:59:00 centos7A Keepalived_vrrp[50516]: VRRP_Instance(VI_1) sent 0 priority
Jan 5 15:59:00 centos7A Keepalived_vrrp[50516]: VRRP_Instance(VI_1) removing protocol VIPs.
Jan 5 15:59:00 centos7A avahi-daemon[912]: Withdrawing address record for 192.168.81.138 on eno16777728.
Jan 5 15:59:00 centos7A systemd: Stopping LVS and VRRP High Availability Monitor...
Jan 5 15:59:01 centos7A Keepalived_vrrp[50516]: Stopped
Jan 5 15:59:01 centos7A Keepalived[50514]: Stopped Keepalived v1.3.5 (03/19,2017), git commit v1.3.5-6-g6fa32f2
Jan 5 15:59:01 centos7A systemd: Stopped LVS and VRRP High Availability Monitor.节点2上查看日志
[root@centos7b ~]# tail -fn 50 /var/log/messages
Jan 5 15:59:02 centos7B Keepalived_vrrp[102344]: VRRP_Instance(VI_1) Transition to MASTER STATE
Jan 5 15:59:03 centos7B Keepalived_vrrp[102344]: VRRP_Instance(VI_1) Entering MASTER STATE
Jan 5 15:59:03 centos7B Keepalived_vrrp[102344]: VRRP_Instance(VI_1) setting protocol VIPs.
Jan 5 15:59:03 centos7B Keepalived_vrrp[102344]: Sending gratuitous ARP on eno16777728 for 192.168.81.138
Jan 5 15:59:03 centos7B avahi-daemon[892]: Registering new address record for 192.168.81.138 on eno16777728.IPv4.[root@centos7b ~]# ip addr|grep eno16777728 ##此时节点2已经获得vip地址
2: eno16777728: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
inet 192.168.81.150/24 brd 192.168.81.255 scope global dynamic eno16777728
inet 192.168.81.138/32 scope global eno16777728vip漂移回切测试
启动节点1上的keepalived，将vip切回
[root@centos7a ~]# systemctl start keepalived.service
[root@centos7a ~]# tail -fn 50 /var/log/messages
Jan 5 16:01:06 centos7A systemd: Started LVS and VRRP High Availability Monitor.
Jan 5 16:01:06 centos7A Keepalived_vrrp[50883]: Unable to load ipset library - libipset.so.3:cannot open shared object file: No such file or directory
Jan 5 16:01:06 centos7A Keepalived_vrrp[50883]: VRRP_Instance(VI_1) removing protocol VIPs.
Jan 5 16:01:06 centos7A Keepalived_vrrp[50883]: SECURITY VIOLATION - scripts are being executed but script_security not enabled.
Jan 5 16:01:06 centos7A Keepalived_vrrp[50883]: Using LinkWatch kernel netlink reflector...
Jan 5 16:01:06 centos7A Keepalived_vrrp[50883]: VRRP sockpool: [ifindex(2), proto(112), unicast(0), fd(10,11)]
Jan 5 16:01:06 centos7A Keepalived_vrrp[50883]: VRRP_Script(chk_haproxy) succeeded
Jan 5 16:01:07 centos7A Keepalived_vrrp[50883]: VRRP_Instance(VI_1) Transition to MASTER STATE
Jan 5 16:01:07 centos7A Keepalived_vrrp[50883]: VRRP_Instance(VI_1) Changing effective priority from 100 to 102
Jan 5 16:01:08 centos7A Keepalived_vrrp[50883]: VRRP_Instance(VI_1) Entering MASTER STATE
Jan 5 16:01:08 centos7A Keepalived_vrrp[50883]: VRRP_Instance(VI_1) setting protocol VIPs.
Jan 5 16:01:08 centos7A Keepalived_vrrp[50883]: Sending gratuitous ARP on eno16777728 for 192.168.81.138[root@centos7a ~]# ip addr|grep eno16777728 ##节点1已经获得vip
2: eno16777728: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
inet 192.168.81.149/24 brd 192.168.81.255 scope global dynamic eno16777728
inet 192.168.81.138/32 scope global eno16777728[root@centos7b ~]# ip addr|grep eno16777728 ##节点2 vip已释放
2: eno16777728: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
inet 192.168.81.150/24 brd 192.168.81.255 scope global dynamic eno16777728说明：由于chk_haproxy.sh脚本中在当haproxy挂掉后，会自动将haproxy服务拉起来，不会触发vip漂移，因此这个实验省略。

4、结合MySQL测试Keepalived

以下将在第二个节点创建一个insert脚本，执行脚本，然后关闭第一个节点的keepalived
然后观察脚本执行过程以及haproxy的状态

[root@centos7a ~]# ip addr|grep eno16777728 ##vip位于第一节点
2: eno16777728: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
inet 192.168.81.149/24 brd 192.168.81.255 scope global dynamic eno16777728
inet 192.168.81.138/32 scope global eno16777728[root@centos7b ~]# mysql -urobin -ppass -h192.168.81.138 --protocol=tcp -e "create database tempdb"
[root@centos7b ~]# mysql -urobin -ppass -h192.168.81.138 --protocol=tcp -e "create table tempdb.tb(userId int)"[root@centos7b ~]# vim /tmp/insert_id.sh
#/bin/sh
cnt=1while [ $cnt -le 10000 ]
do
mysql -urobin -ppass -h192.168.81.138 --protocol=tcp -e "insert into tempdb.tb(userId) values($cnt)"
let cnt=$cnt+1
sleep 1
echo "Insert $cnt"
done[root@centos7b ~]# chmod u+x /tmp/insert_id.sh[root@centos7b ~]# /tmp/insert_id.sh
Insert 2
Insert 3
Insert 4
Insert 5
Insert 6............在节点1关闭keepalived
[root@centos7a ~]# systemctl stop keepalived

回到节点2观察insert_id.sh的执行并没有出现间断
以下为基于vip获取的haproxy状态
这里写图片描述

五、更多参考

基于CentOS 7 安装Percona XtraDB Cluster(PXC) 5.7
MySQL 5.7 时间显示修改(log_timestamps UTC)
MySQL PXC 5.7 invalid user‘@MYSQLD_USER@’

DBA牛鹏社(SQL/NOSQL/LINUX)

  相关解决方案

代码迷 - 您身边的软件异常，代码异常，编程异常助手（发现，解决，分享）
Copyright © 2009-2013 DAIMAMI.COM. All rights reserved.