Oct 172014

Idea is to create haproxy -configuration where it’ll try to make sure to always use a working backend server. Usually all the tutorials suggest using haproxy with “option mysql-check” – which is not enough this time, because MariaDB with Galera might answer correctly for this poll but still be unavailable in DONOR -state.


  • Working MariaDB Galera Cluster
  • Haproxy installed on the application server
  • xinetd installed on the database server


In the application server, we create haproxy frontend which is listening at for database connections, has weighted loadbalancing and is checks database servers in their port 9200, and has an administrative interface (for debug) in port 9600. And always check configuration and scripts, never just copy & paste from the internet.

# cat /etc/haproxy/haproxy.cfg

log /dev/log local0
log /dev/log local1 notice
chroot /var/lib/haproxy
stats socket /run/haproxy/admin.sock mode 660 level admin
stats timeout 30s
user haproxy
group haproxy
node APP1
description APP1.macronet.fi
maxconn 40000
spread-checks 3

log global
mode tcp
option tcp-smart-accept
option tcp-smart-connect
option dontlognull
option redispatch
timeout check 3500
timeout queue 3500
timeout connect 3500
timeout client 10000
timeout server 10000

userlist STATS
group admin users admin
user admin insecure-password adminpass
user stats insecure-password userpass

listen adminpage
bind *:9600
mode http
stats enable
stats refresh 60s
stats uri /
acl AuthOkay_ReadOnly http_auth(STATS)
acl AuthOkay_Admin http_auth_group(STATS) admin

listen galeracluster
mode tcp
balance leastconn
timeout client 60000
timeout server 60000
option tcpka
option httpchk
option allbackups
server DB1 check port 9200 weight 128
server DB2 check port 9200 weight 64
server DB3 check port 9200 weight 32


In database server, we need to create a solution which listens in port 9200 and tells HAProxy that it’s OK. Theres quite many scripts already going around the ‘net, this is just one mix and match of ’em.  wsrep_local_state = 4  == SYNCED  == OK!

# cat /opt/galeracheck.sh

# This script checks if a mysql server is healthy running on localhost. It will
# return:
# "HTTP/1.x 200 OK\r" (if mysql is running smoothly)
# - OR -
# "HTTP/1.x 500 Internal Server Error\r" (else)
# The purpose of this script is make haproxy capable of monitoring mysql properly

CHECK_QUERY="show global status where variable_name='wsrep_local_state'"
for I in "$TMP_FILE" "$ERR_FILE"; do
if [ -f "$I" ]; then
if [ ! -w $I ]; then
echo -e "HTTP/1.1 503 Service Unavailable\r\n"
echo -e "Content-Type: Content-Type: text/plain\r\n"
echo -e "\r\n"
echo -e "Cannot write to $I\r\n"
echo -e "\r\n"
exit 1
echo -e "HTTP/1.1 200 OK\r\n"
echo -e "Content-Type: text/html\r\n"
echo -e "Content-Length: 43\r\n"
echo -e "\r\n"
echo -e "<html><body>MariaDB Galera is OK!</body></html>\r\n"
echo -e "\r\n"
exit 0
echo -e "HTTP/1.1 503 Service Unavailable\r\n"
echo -e "Content-Type: text/html\r\n"
echo -e "Content-Length: 42\r\n"
echo -e "\r\n"
echo -e "<html><body>MariaDB Galera is *down*!</body></html>\r\n"
sed -e 's/\n$/\r\n/' $ERR_FILE
echo -e "\r\n"
exit 1
if [ -f "$FORCE_FAIL" ]; then
echo "$FORCE_FAIL found" > $ERR_FILE
if [ $? -ne 0 ]; then
status=`cat $TMP_FILE | awk '{print $2;}'`

if [ $status -ne 4 ]; then



As you must have noticed, it needs permissions on the database server:

MariaDB [(none)]> GRANT USAGE *.* to 'HAProxy' IDENTIFIED BY 'HAProxyPassword';


To make this listen in a port, use something like the following xinetd -service:

# cat /etc/xinetd.d/galeracheck
# default: on
# description: galeracheck
service galeracheck
flags = REUSE
socket_type = stream
port = 9200
wait = no
user = nobody
server = /opt/galeracheck.sh
log_on_failure += USERID
disable = no
# only_from =
# recommended to put the IPs that need to connect exclusively (security purposes)
per_source = UNLIMITED


And you’ll need to add the service to /etc/services and restart xinetd:

echo "galeracheck 9200/tcp # Galera clustercheck" >> /etc/services


After all of this, your result should be something like this:

# nc 9200
HTTP/1.1 200 OK

Content-Type: text/html

Content-Length: 43

<html><body>MariaDB Galera is OK!</body></html>

And your application should be able to query the database backend successfully through HAProxy.

Oct 162014

SSLv3 is currently disabled in all of our services, if you’re still using Windows XP and Internet Explorer 6: sorry

Apache (grep -i SSLProtocol -R /etc/apache2/*):
SSLProtocol all -SSLv2
SSLProtocol all -SSLv2 -SSLv3

Nginx (grep -i ssl_protocols -R /etc/nginx/*) :
ssl_protocols SSLv3 TLSv1 TLSv1.1 TLSv1.2;
ssl_protocols TLSv1 TLSv1.1 TLSv1.2;

Postfix (grep -i sslv -R /etc/postfix/* – if not found, add):

Dovecot (grep -i sslv -R /etc/dovecot/* – might be commented by default):
ssl_protocols = !SSLv2
ssl_protocols = !SSLv2 !SSLv3

HAProxy v1.5 (add to your bind :443 -line):

Oct 142014

Usually one of the first things we want to do with a new server is to restrict access to SSH -service.

So far it seems that everyone advices “disable firewallD, install iptables service and use it like you’ve always used” but how about trying to get along with this new tech?

Restricting access to SSH isn’t as hard as it might seem at the first glance. First we check what services are allowed in public (usually the default) and internal -zones:

# firewall-cmd --zone=internal --list-services
dhcpv6-client ipp-client mdns samba-client ssh
# firewall-cmd --zone=public --list-services
dhcpv6-client ssh

Then we add our admin-IP to internal -zone:

# firewall-cmd --permanent --zone=internal --add-source=<admin-ip>

Remove access to SSH-service from public:

# firewall-cmd --permanent --zone=public --remove-service=ssh

And reload the changes into use:

# firewall-cmd --reload

–permanent makes changes which stay over reboot/reload, but they aren’t active immediately – without –permanent the changes are active immediately but are lost on reload/reboot

Service definitions can be found (in RHEL/CentOS 7) at /etc/firewalld/services/ – if you create a new one -> use –reload to make it active.

Oct 092014

Nowadays LVM has  a cache feature, where we can bolt an SSD as a cache-device to a logical volume.

Let’s imagine we have the following setup:

  •  4x 2TB SATA disks in RAID10 configuration, /dev/md0
  • 2x 120GB SSD disks in RAID1 configuration, /dev/md1

First we’ll create the logical volume which we’ll be working with:

# pvcreate /dev/md0
# vgcreate storage /dev/md0
# lvcreate -n volume -L 4TB storage /dev/md0

Next we’ll bolt the cache-device (which should be RAID1-mirrored in case of disk failure) to the volume, first we’ll extend the volume group to contain the SSD-device:

# vgextend storage /dev/md1

Then we’ll create a cache volume and a metadata volume (there’s 1GB free on purpose):

# lvcreate -n metadata -L 1GB storage /dev/md1
# lvcreate -n cache -L 118GB storage /dev/md1

Now we’ll convert these into a cache pool (this will fail if there isn’t at least the same amount free what’s used for metadata, 1GB, because it’s used for failure recovery):

# lvconvert --type cache-pool --poolmetadata storage/metadata storage/cache

Then all what’s left is attaching the cache to a logical volume:

# lvconvert --type cache --cachepool storage/cache storage/volume

It should say “storage/volume is now cached” and lvs output should look something like this:

# lvs
LV VG Attr LSize Pool Origin Data% Meta% Move Log Cpy%Sync Convert
cache storage Cwi---C--- 118.00g
volume storage Cwi-a-C--- 4.0t cache [storage_corig]

Oh, and if you want the cache to survive a reboot, youll need a package which provides /usr/sbin/cache_check -binary. In Debian that’s “thin-provisioning-tools”, and in RHEL/CentOS/derivatives the package is device-mapper-persistent-data.
Tests were performed on Debian testing Jessie and CentOS 7.0.1406 Core in 10/2014. Official documentation can be found here.