Percona’s Clustercheck Script for MySQL Galera

Percona clustercheck script is a script distributed as part of the Percona XtraDB Cluster. It creates HAProxy to monitor Galera Cluster nodes and perform health checks on backend servers. The clustercheck shell script accepts HTTP requests and checks MySQL on incoming connections. If the Galera Cluster node is okay, it will give HTTP code 200 OK response; otherwise it gives 503 response implying that the service is unavailable, i.e. the node should not receive MySQL traffic.

Most recently, Datavail released a whitepaper, Why Choose Galera-Based Clustering Solution for MySQL, which outlines the Galera Cluster’s initial configuration and default communication ports and discusses the relevance of Percona’s clustercheck script. This blog post goes into the nitty-gritty details of how to set up and use Percona’s clustercheck script in a MySQL Galera Cluster. (Percona clustercheck script can be downloaded from GitHub.)

Applications of clustercheck script

HAProxy configuration

Clustercheck script is configured with HAProxy to check for MySQL connectivity via HTTP on port 9200. This is the port number that’s commonly used but you are free to change it by editing the mysqlchk service script (explained further in the section “How to set up clustercheck script”).

Disable node for maintenance

You can manually disable a node during maintenance when clustercheck is used in conjunction with a proxy server or load balancer, by using /var/tmp/clustercheck.disabled to force clustercheck to return a 503 error.

How to set up clustercheck script

Setup with xinetd

The clustercheck script is set to listen on port 9200 using xinetd and report status of the nodes in the Galera Cluster. Here are the steps of the setup process:

Download the Percona script from GitHub, copy it to the/usr/local/bin or /usr/bin/ directory and make it executable by using the following commands:

$ git clone https://github.com/olafz/percona-clustercheck

$ cp percona-clustercheck/clustercheck /usr/bin

$ chmod a+x /usr/bin/clustercheck

Daemonize the script using xinetd as follows:

(If not installed, install xinetd.)

For Centos

# yum install –y xinetd

For Ubuntu

# apt-get install xinetd

Create the following configuration file in /etc/xinet.d/ to configure the mysqlchk service:

# vi /etc/xinet.d/mysqlchk

Enter the text below:

# default: on

# description: mysqlchk

service mysqlchk

{

   disable = no

   flags = REUSE

   socket_type = stream

   port = 9200

   wait = no

   user = nobody

   server = /usr/bin/clustercheck

   log_on_failure += USERID

   only_from = 0.0.0.0/0

   per_source = UNLIMITED

}

Add the mysqlchk service to /etc/services file as follows:

xinetd   9098/tcp # ...

mysqlchk 9200/tcp # MySQL check <--- Add this line

git   9418/tcp # Git Version Control System

Note: Ensure the ports 9200, 9098, 9418, and 3306 are open for clustercheck script, xinetd and mysqlchk service communication with nodes in the cluster.

Restart xinetd:

# service xinetd restart

Now clustercheck script will listen on port 9200 and HAProxy is ready to check MySQL via HTTP. You can test this by running the command:

$ telnet localhost 9200
Trying 127.0.0.1…
Connected to localhost.
Escape character is ‘^]’.
HTTP/1.1 200 OK
Content-Type: text/plain
Connection: close
Content-Length: 40

Galera Cluster Node is synced.
Connection closed by

This means that the cluster is synced and the instance is ready to handle traffic.

In one of the nodes, create clustercheckuser that will be doing the checks

Log into Galera Cluster:

$ mysql –u username –p

Enter the password in the prompt, then enter:

mysql> GRANT PROCESS ON *.* TO 'clustercheckuser'@'localhost' IDENTIFIED BY 'clustercheckpassword!'

mysql> FLUSH PRIVILEGES;

The MySQL Galera Cluster will replicate the statement to the other nodes.

Make sure to change the clustercheckuser username and password in the clustercheck script to be the same as the ones created in the DDL statements above.

Setup with Shell Return Values

A different option from setting up with xinetd is executing clustercheck on commandline and checking the return value.

Download the Percona script from GitHub, copy it to the/usr/local/bin or /usr/bin/ directory and make it executable. Then create clustercheckuser which will be doing the checks.

After that, run the clustercheck script as follows:

# /usr/bin/clustercheck > /dev/null

# echo $?

For a syncd node, the output will be “0” and for an un-synced node the output will be “1”. This return value can be used with server and network-monitoring tools like Zabbix.

This clustercheck script comes in handy when monitoring the health status of the nodes within MySQL Galera Cluster and setting up proxy load balancer configuration. It also helps when disabling a node for maintenance purpose.

Datavail Script: Terms & Conditions

By using this software script (“Script”), you are agreeing to the following terms and condition, as a legally enforceable contract, with Datavail Corporation (“Datavail”). If you do not agree with these terms, do not download or otherwise use the Script. You (which includes any entity whom you represent or for whom you use the Script) and Datavail agree as follows:

1. CONSIDERATION. As you are aware, you did not pay a fee to Datavail for the license to the Script. Consequently, your consideration for use of the Script is your agreement to these terms, including the various waivers, releases and limitations of your rights and Datavail’s liabilities, as setforth herein.

2. LICENSE. Subject to the terms herein, the Script is provided to you as a non-exclusive, revocable license to use internally and not to transfer, sub-license, copy, or create derivative works from the Script, not to use the Script in a service bureau and not to disclose the Script to any third parties. No title or other ownership of the Script (or intellectual property rights therein) is assigned to you.

3. USE AT YOUR OWN RISK; DISCLAIMER OF WARRANTIES. You agree that your use of the Script and any impacts on your software, databases, systems, networks or other property or services are solely and exclusively at your own risk. Datavail does not make any warranties, and hereby expressly disclaims any and all warranties, implied or express, including without limitation, the following: (1) performance of or results from the Script, (2) compatibility with any other software or hardware, (3) non-infringement or violation of third party’s intellectual property or other property rights, (4) fitness for a particular purpose, or (5) merchantability.

4. LIMITATION ON LIABILITY; RELEASE. DATAVAIL SHALL HAVE NO, AND YOU WAIVE ANY, LIABILITY OR DAMAGES UNDER THIS AGREEMENT.

You hereby release Datavail from any claims, causes of action, losses, damages, costs and expenses resulting from your downloading or other use of the Script.

5. AGREEMENT. These terms and conditions constitute your complete and exclusive legal agreement between you and Datavail.