HAPRoxy for Percona or Galera on CentOS. Its configuration and monitoring in Zabbix

A very short article about how HAProxy can be used as a balancer for multi-master MySQL servers, such as Percona or Galera.

I want to note that this instruction was born in the process of implementing Zabbix in the walls of Acronis .
In the process of examination and the research I conducted, she proved her right to life and safely serves us faithfully day by day.
')
For those who are not familiar with HAProxy, a quote about the purpose of the product:

When increasing the load or attendance of the project, sooner or later vertical scaling (increase in server resources, such as memory, disk speed, etc.) rests on a certain limit and does not give a noticeable increase. In this case, horizontal scaling is used - adding new servers with load redistribution between them.
In addition to increasing power, horizontal scaling adds reliability to the system — if one of the servers fails, the load will be balanced between those working and the application will live.

From words to business, installation and configuration are very simple:




In the previous article, I described in detail what preliminary operations I did on pure CentOS 6.4, my recommendations are up-to-date and here, all the packages will be indicated taking into account the recommendations in the previous material :

Repositories are connected, the system is up to date, go to the installation of HAProxy:

# We put haproxy

yum install haproxy mariadb-client php-mysql php-cli -y

# Writing configs

mv /etc/haproxy/haproxy.cfg /etc/haproxy/haproxy.cfg.old

[root @ rs-haproxy ~] # nano /etc/haproxy/haproxy.cfg

global
daemon
maxconn 4096

# If you want to see debug information, then this item should be commented out.
quiet

# And this is opposite, uncomment
# debug

user haproxy
group haproxy

# We will use this socket for monitoring in zabbix
stats socket / var / run / haproxy

pidfile /var/run/haproxy.pid

defaults
mode http
option tcplog
log global
option dontlognull retries 3 option redispatch
maxconn 2000
contimeout 5000
clitimeout 50,000
srvtimeout 50,000
option tcplog

# Indicate the valid address on the server and the port on which our haproxy will listen
listen MySQL address-on-which we listen-: 3306

mode tcp

# roundrobin - consistently writes to all servers, it is good in read mode but can cause problems if it is written to the database
# balance roundrobin

# leastconn this mode is great when using haproxy as a failover proxy, using the last working server and only it
balance leastconn

# httpchk makes haproxy check the server for its readiness before sending each of the requests
option httpchk

# Below is a list of servers, their port and the port to which the connection will be made to test viability
server address address: 3306 check port 50005 inter 12000 rise 3 fall 3
server address address: 3306 check port 50005 inter 12000 rise 3 fall 3

# Servers with backup flag are used only if other servers are not available.
server address address: 3306 check port 50005 inter 12000 rise 3 fall 3 backup

Now let's go to our percona or galera servers

# We configure the mechanism for checking our databases, for this we need xinetd

yum install -y xinetd

[root @ xtrabackup-node-01 ~] # nano /etc/xinetd.d/mysqlchk

# default: on
# description: mysqlchk
service mysqlchk
{
disable = no
flags = REUSE
socket_type = stream

# verification port that we specified above
port = 50005

wait = no
user = nobody
server = / usr / bin / clustercheck
log_on_failure + = USERID

# Addresses with which MySQL service status checking is allowed
only_from = 10.100.100.0/24

per_source = UNLIMITED
}

# It is very important to add this entry, otherwise it will not work.
root @ xtrabackup-node-01 ~] # nano / etc / services

mysqlchk 50005 / tcp # mysqlchk

# Add service to autoload and run it

chkconfig xinetd on
/etc/init.d/xinetd start

# Allow access to port verification

iptables -A INPUT -p tcp -m state --state NEW -m tcp --dport 50005 -j ACCEPT
service iptables save

# Add permissions for clustercheck access

GRANT PROCESS ON *. * TO 'clustercheckuser' @ 'localhost' IDENTIFIED BY 'clustercheckpassword!';
flush privileges;

# We check everything we did, it should work out something like
[root @ xtrabackup-node-01 ~] # / usr / bin / clustercheck

HTTP / 1.1 200 OK
Content-Type: text / plain
Connection: close
Content-Length: 40

Percona XtraDB Cluster Node is synced.

# Now let's check work with our haproxy host

[root @ rs-haproxy ~] # telnet address 50005
Trying the address ...
Connected to address.
Escape character is '^]'.
HTTP / 1.1 200 OK
Content-Type: text / plain
Connection: close
Content-Length: 40

Percona XtraDB Cluster Node is synced.
Connection closed by foreign host.

Done! need to do this operation on all the nodes of percona / galera

Monitoring HAProxy for MYSQL in Zabbix

# This is the main tool for our script, install it

yum install -y socat

# Configure sudo for zabbix if it hasn’t been done before

usermod -s / bin / bash zabbix
echo 'zabbix ALL = (ALL) NOPASSWD: ALL' >> / etc / sudoers
sed -i 's / Defaults \ requireti / # Defaults \ requiretty / g' / etc / sudoers

# Create a folder for our scripts

mkdir -p / etc / zabbix / scripts /
chmod 750 / etc / zabbix / scripts /

# Actually the script itself
rm -f /etc/zabbix/scripts/haproxy.mysql
nano /etc/zabbix/scripts/haproxy.mysql

if [[ -z $1 || -z $2 ]]; then servers=`echo "show stat" | sudo socat /var/run/haproxy stdio | sed 's/,/\ /g' | awk '{print $2}' | grep -v -e "pxname" -e '^$'` if [[ -n ${servers} ]]; then JSON="{ \"data\":[" for DEV in ${servers}; do JSON=${JSON}"{ \"{#SRV}\":\"${DEV}\"}," done JSON=${JSON}"]}" echo ${JSON} fi exit 0 else server="$2" # echo $server if [ ${1} = "qcur" ]; then # echo $1 echo "show stat" | sudo socat /var/run/haproxy stdio | grep "MySQL,${server}"| sed 's/,/\ /g' | awk '{print $3}' exit 0 fi if [ ${1} = "qmax" ]; then echo "show stat" | sudo socat /var/run/haproxy stdio | grep "MySQL,${server}" | sed 's/,/\ /g' | awk '{print $4}' exit 0 fi if [ ${1} = "scur" ]; then echo "show stat" | sudo socat /var/run/haproxy stdio | grep "MySQL,${server}" | sed 's/,/\ /g' | awk '{print $5}' exit 0 fi if [ ${1} = "smax" ]; then echo "show stat" | sudo socat /var/run/haproxy stdio | grep "MySQL,${server}" | sed 's/,/\ /g' | awk '{print $6}' exit 0 fi if [ ${1} = "econ" ]; then echo "show stat" | sudo socat /var/run/haproxy stdio | grep "MySQL,${server}" | sed 's/,/\ /g' | awk '{print $14}' exit 0 fi if [ ${1} = "qlimit" ]; then echo "show stat" | sudo socat /var/run/haproxy stdio | grep "MySQL,${server}" | sed 's/,/\ /g' | awk '{print $26}' exit 0 fi fi 

The script itself detects the available servers and transfers them to zabbix
Check it out:

[root @ rs-haproxy ~] # echo “show stat” | sudo socat / var / run / haproxy stdio | sed 's /, / \ / g' | awk '{print $ 2}' | grep -v -e "pxname" -e '^ $'
FRONTEND
10.100.100.246
BACKEND

# Minimal settings for our zabbix-agent

echo Timeout = 30 >> /etc/zabbix/zabbix_agentd.conf
echo Include = / etc / zabbix / zabbix_agentd.d / >> /etc/zabbix/zabbix_agentd.conf

# Apply rights to execute a new script

chown zabbix: zabbix -R / etc / zabbix / scripts /
chmod + x /etc/zabbix/scripts/haproxy.mysql

# We pass our UserParameter zabbix

mkdir -p /etc/zabbix/zabbix_agentd.d/
rm -f /etc/zabbix/zabbix_agentd.d/haproxy.mysql.conf
touch /etc/zabbix/zabbix_agentd.d/haproxy.mysql.conf

echo 'UserParameter = haproxy.mysql [*], / etc / zabbix / scripts / haproxy.mysql "$ 1" "$ 2"' >> /etc/zabbix/zabbix_agentd.d/haproxy.mysql.conf

Now check if our script works correctly:

su zabbix

This is how our automatic server detection works.
bash-4.1 $ /etc/zabbix/scripts/haproxy.mysql

{"Data": [{"{#SRV}": "FRONTEND"}, {"{#SRV}": "10.100.100.246"}, {"{#SRV}": "BACKEND"},]}

# Ask any data

bash-4.1 $ /etc/zabbix/scripts/haproxy.mysql qcur FRONTEND
115

It seems that everything is in order! Restart the service and admire the logs

/etc/init.d/zabbix-agent restart && tail -f -n 100 /var/log/zabbix/zabbix_agentd.log

This is how our haproxy template looks like.


Here you can download the template for import into zabbix

Thanks for attention!