配置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

四、安装及配置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状态

五、更多参考

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

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏iOS Developer

FFmpeg-iOS推流器的简单封装

1834
来自专栏FFmpeg

FFmpeg简单转码程序--视频剪辑

学习了雷神的文章,慕斯人分享精神,感其英年而逝,不胜唏嘘。他有分享一个转码程序《最简单的基于FFMPEG的转码程序》其中使用了filter(参考了ffmpeg....

4939
来自专栏杨建荣的学习笔记

11g备库无法开启ADG的原因分析 (r7笔记第62天)

今天碰到一个有些奇怪的问题,但是奇怪的现象背后都是有本质的因果。 下午在做一个环境的检查时,发现备库是在mount阶段,这可是一个11gR2的库,没有ADG实在...

3844
来自专栏C++

FFmpeg4.0笔记:file2rtmp

1636
来自专栏施炯的IoT开发专栏

VS2008下使用托管代码控制Windows Mobile Device Emulators

  在听黎波老师的Webcast-Visual Studio 2008 移动开发新体验时,听他提到过可以使用代码来控制Device Emulator。今天在ms...

1908
来自专栏北京马哥教育

Kubernetes网络部署方案

现在网络上流传很多Kubernetes的部署和搭建的文档,其中比较出名就是Kubernetes The Hard Way (https://github.com...

4508
来自专栏前端说吧

一些时间的处理

2905
来自专栏散尽浮华

MFS+Keepalived双机高可用热备方案操作记录

基于MFS的单点及手动备份的缺陷,考虑将其与Keepalived相结合以提高可用性。在Centos下MooseFS(MFS)分布式存储共享环境部署记录这篇文档部...

4707
来自专栏IT笔记

SpringBoot开发案例之微信小程序录音上传

前言 书接上回的《SpringBoot开发案例之微信小程序文件上传》,正常的业务流程是,口语测评需要学生通过前端微信小程序录入一段音频,通过调用第三方音频处理服...

1.3K8
来自专栏Java学习123

转 svn: E170001报错的原因以及解决方案

1K7

扫码关注云+社区

领取腾讯云代金券