Configure haproxy + preserved + PXC 5.7

There are many ways to separate reading from writing in MySQL. Haproxy + preserved + MySQL (master slave) is a common choice. This paper configures haproxy based on PXC 5.7 cluster environment and implements read-write separation of MySQL with Keepalived. The following are the main configuration methods and steps.

1, Current environment

Haproxy IP | keepalived IP(Both hosts deployed haproxy as well as 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

2, Configure PXC node monitoring

1. PXC three node installation configuration xinetd service

The following is an example of the first node
[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. Create status check user (any PXC node is OK)

The following is an example of the first node
[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. The PXC three nodes manually execute clusterchek to ensure that 200 is returned

The following is an example of the first node 
[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 three node firewall configuration

The following is an example of the first node  
[root@node146 ~]# firewall-cmd --add-port=9200/tcp --permanent
[root@node146 ~]# firewall-cmd --reload

For all PXC nodes and Haproxy nodes, it is recommended to turn off selinux, as shown in the following example, and restart is recommended to take effect
# vim /etc/selinux/config
SELINUX=disabled

3, Haproxy installation and configuration

1. Install and configure haproxy

Below192.168.81.149 centos7A Node example
[root@centos7a ~]# yum install haproxy -y
[root@centos7a ~]# cp /etc/haproxy/haproxy.cfg{,.org}

//Add the following to the haproxy.cfg configuration file
[root@centos7a ~]# vim /etc/haproxy/haproxy.cfg  
frontend pxc-front     ##Front end monitoring configuration name, port, protocol, corresponding back-end name
bind *:3306               
mode tcp     
default_backend pxc-back

frontend stats-front    ##web status monitoring configuration
bind *:8080
mode http
default_backend stats-back

backend pxc-back   ###Back end configuration
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 monitoring access
mode http
balance roundrobin
stats uri /haproxy/stats
stats refresh 5s
stats auth pxcstats:secret

//Firewall configuration
[root@centos7a ~]# firewall-cmd --permanent --add-port=3306/tcp
[root@centos7a ~]# firewall-cmd --permanent --add-port=8080/tcp
[root@centos7a ~]# firewall-cmd --reload

haproxy Log configuration(This step can be omitted as needed)
[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 ###The line is commented out
$UDPServerRun 514 ###The line is commented out
local0.* /var/log/haproxy.log ###Add a new line at the end of the file

[root@centos7a ~]# cp /etc/sysconfig/rsyslog{,.org}
[root@centos7a ~]# vim /etc/sysconfig/rsyslog
SYSLOGD_OPTIONS="-r -m 0 -c 2" ###Modify this line

[root@centos7a ~]# vim /etc/haproxy/haproxy.cfg
log 127.0.0.1 local0 ###Add this line to the global segment

//Start log service and haproxy service
[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

//Add mysql user for testing
[root@node142 ~]# mysql -uroot -pxxx -hlocalhost
mysql>grant all privileges on *.* to 'robin'@'192.168.%' identified by 'xxx';

//stay192.168.81.149 centos7A Node test mysql request
# for i in `seq 1 1000`;
> do mysql -hlocalhost -P3306 -urobin -pxxx --protocol=tcp -e "select now()";
> done

2. Verify haproxy

4, Install and configure keepalived

1. Install keepalived (two nodes)

haproxy Two nodes are installed separately keepalived,Take the first node as an example
[root@centos7a ~]# yum install keepalived
[root@centos7a ~]# rpm -qa|grep keepalived
keepalived-1.3.5-1.el7.x86_64

3. Configure firewall (two nodes)

The following is a demonstration on the centos7a node. 224.0.0.18 is the maintained multicast address
[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. Configure keepalived

node1To configure
//node1And node2The biggest difference is state Value of, one for MASTER,One for BACKUP
//The other is the priority level. For details, please refer to: 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/

//node2To configure
[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. Test kept

Start on the first node 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  ##, note, this is kept 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 Link, tested by the author, the Bug You can also upgrade to1.4Version solution
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

//Start keepalived on the second node 
[root@centos7b ~]# systemctl enable keepalived
[root@centos7b ~]# systemctl start keepalived

vip Drift to standby node test
[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.

//node2View log on
[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   ##Now node 2 has obtained the vip address
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 Drift cut back test
//Start node1Upper keepalived,take vip Cut back
[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  ##Node 1 has obtained 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 ##Node 2 vip released
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

//Note: in the chk_haproxy.sh script, when the haproxy is hung up, it will automatically pull up the haproxy service and will not trigger the vip drift, so this experiment is omitted.

4. Test preserved with MySQL

Next, create an insert script in the second node, execute the script, and then close the keepalived of the first node
Then observe the script execution process and the state of haproxy

[root@centos7a ~]# ip addr|grep eno16777728  ##vip at first node
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
 ............

//At node1Close keepalived
[root@centos7a ~]# systemctl stop keepalived

Go back to node 2 and observe that there is no interruption in the execution of insert_id.sh
The following is the haproxy status obtained based on vip

5, More references

Install Percona XtraDB Cluster(PXC) 5.7 based on CentOS 7
Log "timestamps UTC
MySQL PXC 5.7 invalid user'@MYSQLD_USER@'

Tags: MySQL firewall vim yum

Posted on Fri, 01 May 2020 00:49:09 -0700 by FeeBle