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`