配置Haproxy + Keepalived +PXC 5.7

柔光的暖阳◎ 2022-06-01 03:58 185阅读 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 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 ###

![这里写图片描述][SouthEast]

## 四、安装及配置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状态  
![这里写图片描述][SouthEast 1]

## 五、更多参考 ##

[基于CentOS 7 安装Percona XtraDB Cluster(PXC) 5.7][CentOS 7 _Percona XtraDB Cluster_PXC_ 5.7]  
[MySQL 5.7 时间显示修改(log\_timestamps UTC)][MySQL 5.7 _log_timestamps UTC]  
[MySQL PXC 5.7 invalid user‘@MYSQLD\_USER@’][MySQL PXC 5.7 invalid user_MYSQLD_USER]

[![DBA牛鹏社(SQL/NOSQL/LINUX)][DBA_SQL_NOSQL_LINUX]][DBA_SQL_NOSQL_LINUX_DBA_SQL_NOSQL_LINUX]

[SouthEast]: /images/20220601/6ca4991d7b5f4fd49c727e46c1cc1439.png
[SouthEast 1]: /images/20220601/346c0777706544a497a5df97d1a37320.png
[CentOS 7 _Percona XtraDB Cluster_PXC_ 5.7]: http://blog.csdn.net/leshami/article/details/78962190
[MySQL 5.7 _log_timestamps UTC]: http://blog.csdn.net/leshami/article/details/78952842
[MySQL PXC 5.7 invalid user_MYSQLD_USER]: http://blog.csdn.net/leshami/article/details/78919830
[DBA_SQL_NOSQL_LINUX]: http://pub.idqqimg.com/wpa/images/group.png
[DBA_SQL_NOSQL_LINUX_DBA_SQL_NOSQL_LINUX]: /images/20220601/d0bcbe659da64a598bce5c373d0ca1b2.png