MySQL health check

for mysql health check will use xinetd whith bash scrip that will be select from mysql db
install xinetd
$ aptitude install xinetd
create configure file for xinetd in witch define script for select from db

$ vim /etc/xinetd.d/mysqlchk

service mysqlchk
{
  disable = no
  flags           = REUSE
  socket_type     = stream
  port            = 9200
  wait            = no
  user            = root
  server          = /root/mysqlcheck.sh
  log_on_failure  += USERID
  only_from       = 0.0.0.0/0
  per_source      = UNLIMITED
}

create bash script
$ vim /root/mysqlcheck.sh

#!/bin/bash
#
# Script to make a proxy (ie HAProxy) capable of monitoring MySQL Cluster
MYSQL_USERNAME=”health_check”
MYSQL_PASSWORD=”password”
ERR_FILE=”/dev/null”
#
# Perform the query to check the wsrep_local_state
#
WSREP_STATUS=`mysql –user=${MYSQL_USERNAME} –password=${MYSQL_PASSWORD} -e “select id from healthcheck.healthcheck limit 1;” 2>${ERR_FILE} | wc -l`
if [[ “${WSREP_STATUS}” -gt 0 ]]
then
/bin/echo -en “HTTP/1.1 200 OK\r\n”
/bin/echo -en “Content-Type: text/plain\r\n”
/bin/echo -en “\r\n”
/bin/echo -en “Service is available.\r\n”
/bin/echo -en “\r\n”
else
/bin/echo -en “HTTP/1.1 503 Service Unavailable\r\n”
/bin/echo -en “Content-Type: text/plain\r\n”
/bin/echo -en “\r\n”
/bin/echo -en “Service Unavailable.\r\n”
/bin/echo -en “\r\n”
fi

in mysql need to create user & db,table with select writes to it

create database healthcheck;
use healthcheck;
create table healthcheck (id MEDIUMINT NOT NULL AUTO_INCREMENT, date VARCHAR (255) NOT NULL, PRIMARY KEY (id));
INSERT INTO healthcheck (id, date) VALUES (“1″,”all if fine”);
select id from healthcheck.healthcheck limit 1;
CREATE USER ‘health_check’@’localhost’ IDENTIFIED BY ‘password’;
GRANT SELECT ON healthcheck.healthcheck TO ‘health_check’@’localhost’;

$ vim /etc/services
add lines

# Local services                                                                                                    
mysqlchk        9200/tcp

$ stop xinetd; start xinetd

Now on port 9200 of the server we have http request status 200 for good working service & 503 for failure.
This configuration can be use for haproxy capable of monitoring mysql cluster.

listen  mysqldb *:3306
       mode    tcp
       option tcpka
       maxconn 1650
       timeout client 3601s
       timeout server 3601s
 server db1 db1.server.com:3306 maxconn 900 check port 9200 inter 12000 rise 3 fall 3
 server db2 db2.server.com:3306 maxconn 900 check port 9200 inter 12000 rise 3 fall 3 backup

Leave a comment