配置Haproxy + Keepalived +PXC 5.7-阿里云开发者社区

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 vip

PXC 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-back

frontend stats-front    ##web状态监控配置
bind *:8080
mode http
default_backend stats-back

backend 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 3

backend 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 --reload

haproxy日志配置(根据需要，此步骤可省略)
[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

011901

四、安装及配置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 process
    interval 2 # every 2 seconds
    weight 2 # add 2 points if OK
}

vrrp_instance VI_1 {
    interface eno16777728 # interface to monitor
    state MASTER # MASTER on centos7A, BACKUP on centos7B
    virtual_router_id 51
    priority 100 # 100 on centos7A, 90 on centos7B

virtual_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 haproxy
        sleep 3

        if [ `ps -C haproxy --no-header |wc -l` -eq 0 ];then
            /usr/bin/systemctl stop keepalived
        fi
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 process
    interval 2 # every 2 seconds 
    weight 2 # add 2 points if OK
}

vrrp_instance VI_1 {
    interface eno16777728 # interface to monitor
    state BACKUP # MASTER on centos7A, BACKUP on centos7B
    virtual_router_id 51
    priority 90 # 100 on centos7A, 90 on centos7B

virtual_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.138

Keepalived 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 keepalived

vip漂移到备节点测试
[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 eno16777728

vip漂移回切测试
启动节点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=1

while [ $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状态
011902

配置Haproxy + Keepalived +PXC 5.7

一、当前环境

二、配置PXC节点监控

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

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

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

4、PXC三节点防火墙配置

三、Haproxy安装与配置

1、安装配置haproxy

2、验证haproxy

四、安装及配置keepalived

1、安装keepalived (两节点)

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

2、配置keepalived

3、测试keepalived

4、结合MySQL测试Keepalived

五、更多参考

热门文章

最新文章

相关课程

相关电子书

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

配置Haproxy + Keepalived +PXC 5.7

一、当前环境

二、配置PXC节点监控

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

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

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

4、PXC三节点防火墙配置

三、Haproxy安装与配置

1、安装配置haproxy

2、验证haproxy

四、安装及配置keepalived

1、安装keepalived (两节点)

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

2、配置keepalived

3、测试keepalived

4、结合MySQL测试Keepalived

五、更多参考

热门文章

最新文章

相关课程

相关电子书