open source

High Availability with HAproxy , Apache2 and MariaDB Galera Cluster Ubuntu 18.04 LTS & Centos 7

Christopher Simpson

Dec 3, 2018 • 9 min read

In this post we create a highly available web & database cluster using a compbination of:

MariaDB Galera Cluster of three nodes on Ubuntu 18.04
HAproxy for load balancing (requests enter at this endpoint) on Centos 7
Two Apache2 instancesfor web server(s)

The config was used in a real world scenario for a UK television show Dragons Den, where we kept one of the contestants websites functioning smoothly whilst sustaining over 133,000 visits in the space of 15 minutes. The next contestant wasn't so prepared!

MariaDB Galera Cluster

You need a minimum of three instances/servers to form a Galera Cluster.

If you're using a VPS provider, consider putting your MariaDB instances on their own virtual private network. Also, locality is important here to reduce latency. The closer togeather, the less latency- at the cost of avalability.

The latest versions of MariaDB include the needeed wsrep API for Galera Cluster. There's no need to install the wsrep wrapper seperatly.

Create three mariadb instances

If you need a VPS provider, you can deploy this entire config on Vultr, and create three instances from there for your MariaDB Galera Cluster following this.

Install MariaDB on all your MariaDB Instances

On all three MariaDB instances, install on configure MariaDB.

Update & upgrade your new instance:

sudo apt update; apt upgrade -y

Install MariaDB Server:

sudo apt install -y mariadb-server mariadb-client

Set MariaDB Cluster client config

Uncomment bind_address in /etc/mysql/mariadb.conf.d/50-server.cnf:

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address           = 127.0.0.1

This will is replaced by the bind_address in /etc/mysql/conf.d/cluster.cnf which is not exposed to the internet (only the load balancer will be).

Why? this is because our /etc/mysql/conf.d/cluster.cnf config will ask MariaDB to bind to an internal nework address (by default Mariadb will only listen on localhost).

On each MariaDB instance, enter the following config.

Changing the:

bind-address
wsrep_cluster_name (the same on all instances)
wsrep_cluster_address (comma seperated list of each MariaDB instance in your cluster)
wsrep_node_address
wsrep_node_name

vim /etc/mysql/conf.d/cluster.cnf

[mysqld]
query_cache_size=0
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
query_cache_type=0
bind-address=<this_name_ip>

#Galera Provider Configuration
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
#wsrep_provider_options="gcache.size=32G"

#Galera Cluster Configuration
wsrep_cluster_name="our_cluster"
wsrep_cluster_address="gcomm://first_ip,second_ip,third_ip"

#Galera Synchronization Congifuration
wsrep_sst_method=rsync
#wsrep_sst_auth=user:pass

#Galera Node Configuration
wsrep_node_address="this_node_ip"
wsrep_node_name="maria1"

Netplan Networking Config Maria1,2 and 3

vim sudo vim /etc/netplan/10-ens3.yaml

network:
  version: 2
  renderer: networkd
  ethernets:
    ens7:
      match:
        macaddress: <mac-address>
      mtu: 1450
      dhcp4: no
      addresses: [ip-address/mask]

Apply netplan configuration.

netplan apply

Run mysql_secure_installation

mysql_secure_installation

Allow access from apache2 instances

In preperation for apache, give access to the database instances from apache.

Be aware than the Mariadb mysql.User table cannot be replicated because of the storage engine it uses (this might make you think that user account creation cannot be replicated- but they can!). At a higher level, DDL statements are replicated accross the cluster, so statements like CREATE USER are replicatee, whereas statements like INSERT INTO mysql.User are not. For the full story read: "User changes not replicating"" at: http://galeracluster.com/documentation-webpages/userchanges.html

For example, on your private network:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'10.8.96.%' 
  IDENTIFIED BY 'your-password' WITH GRANT OPTION;

Remember your individual database users will need to have their own user, and grant permissions. For example, a user tom with a database called tomdb:

CREATE DATABASE tom; 
CREATE USER 'tom'@'10.8.96.%' IDENTIFIED BY 'your-password';
GRANT ALL ON tomdb.* TO 'tom'@'10.8.96.%';

The % allows any nodes on the 10.8.96.0/20 network to connect to the database instances (note this is an application layer rule, you still need to be able to ping the database, and have them listening on the correct interface using the bind-address setting above).

Verify access

SELECT User, Host FROM mysql.user WHERE Host <> 'localhost';

Configure Ports

Enable the ufw firewall:

ufw enable

Allow the following ports:

ufw allow 22/tcp
ufw allow 3306/tcp
ufw allow 4444/tcp
ufw allow 4567/tcp
ufw allow 4568/tcp
ufw allow 4567/udp

For a complete list of MariaDB ports see: http://galeracluster.com/documentation-webpages/firewallsettings.html

What are these port for?

3306 For MySQL client connections and State Snapshot Transfer that use the mysqldump method.
4567 For Galera Cluster replication traffic, multicast replication uses both UDP transport and TCP on this port.
4568 For Incremental State Transfer.
4444 For all other State Snapshot Transfer.

Create Swapfile

Memory requirements for Galera Cluster are difficult to predict with any precision. The particular amount of memory it uses can vary significantly, depending upon the load the given node receives. In the event that Galera Cluster attempts to use more memory than the node has available, the mysqld instance crashes.

The way to protect your node from such crashing is to ensure that you have sufficient swap space available on the server, either in the form of a swap partition or swap files. Docs

(Check you have enough space) then:

fallocate -l 10G /swapfile
chmod 600 /swapfile
mkswap /swapfile
swapon /swapfile

Add the swap file to /etc/fstab to make it permanent:

vi /etc/fstab

Add the swap file to your /etc/fstab file:

echo "/swapfile none swap defaults 0 0" >> /etc/fstab

Verify swap is on:

swapon --summary

Start the Galera database cluster

Stop all mysql services on all nodes

sudo systemctl stop mysql

On node1 only

Because Ubuntu 18.04 uses systemd (and not Upstart init) there MariaDB provides the upstart scrpt galera_new_cluster which is stored in /usr/bin/galera_new_cluster by default.

On your first database instance only, issue:

galera_new_cluster

Verify the initial first node (currently primary) is OK:

mysql -u root -p -e "SHOW STATUS LIKE 'wsrep_cluster_size';"

+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 1     |
+--------------------+-------+

You should see value 1.

Now you can start mariadb on the remaining nodes, and they will attempt to join the cluster.

On each remaining node (1 & 2):

systemctl start mysql

You will see:

+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 2     |
+--------------------+-------+

& Then finally:

+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 3     |
+--------------------+-------+

Check your logs if you don't see this. `tail -f /var/log/mysql/error.log`

Set up apache2 instances

We're going to create two apache instances, and use HAproxy to load balance between them.

Set-up apache instances

Update & upgrade each apache instance:

sudo apt update; apt upgrade -y

Install apache2 and mariadb-client in order to access the cluster:

sudo apt update; sudo apt upgrade -y; apt install apache2 mariadb-client -y;

Exable apache modules:

sudo a2enmod ssl rewrite; systemctl restart apache2;

Install PHP if needed:

sudo apt install -y php libapache2-mod-php php-mysql; systemctl restart apache2.service;

Each apache instance will of course need the same virtual hosts configured on each machine. You might use rsync for this, or even more simple simply scp -r /var/www root@ip-of-other-machine:/var/ to copy over your web files.

In addition, you'll need to copy over virtual host definitions e.g.
scp -r /etc/apache2/sites-available/ root@ip-of-other-machine:/etc/apache2/sites-available/

Finally, make sure each virtual host you want to load balance is enabled using a2ensite. e.g. sudo a2ensite /etc/apache2/sites-available/example.conf

Setup HAproxy

Centos 7

yum install haproxy -y

Configure IP address.

Your HAproxy needs to be able to connect to the apache cluster, so via another virtual interface:

cp /etc/sysconfig/network-scripts/ifcfg-eth0 /etc/sysconfig/network-scripts/ifcfg-eth1


DEVICE="eth1"
BOOTPROTO="static"
ONBOOT="yes"
TYPE="Ethernet"
NM_CONTROLLED="no"
IPADDR=<internal-net-address>
NETMASK=<netmask>
NOZEROCONF="yes"
ZONE=public
MTU=1450

Open port 80 up on your Centos box:

sudo firewall-cmd --permanent --zone=public --add-service=http

Open up port 443 on your Centos box (for ssl):

sudo firewall-cmd --permanent --zone=public --add-service=https

Reload Centos firewall:

sudo firewall-cmd --reload

Turn on HAproxy logging on Centos 7
https://www.ostechnix.com/setup-centralized-rsyslog-server-centos-7/

Configure SSL on HAproxy using letsencrypt

yum -y install yum-utils
yum-config-manager --enable rhui-REGION-rhel-server-extras rhui-REGION-rhel-server-optional
sudo yum install -y certbot
systemctl stop haproxy
sudo certbot certonly --standalone -d example.co.uk -d www.example.co.uk
systemctl start haproxy

Auto renew SLL for HAproxy on Centos 7

This is inspired by this tutorial.

Create renew-cert.sh in the root directory.

Add --dry-run to the command to test your script.

vim /root/renew-cert.sh

#!/bin/bash

/usr/bin/certbot renew --pre-hook "systemctl stop haproxy" --post-hook "systemctl start haproxy"
DOMAIN='example.co.uk' sudo -E bash -c 'cat /etc/letsencrypt/live/$DOMAIN/fullchain.pem /etc/letsencrypt/live/$DOMAIN/privkey.pem > /etc/haproxy/certs/$DOMAIN.pem'

Add as a cron entry:

crontab -e

Add the following:

* * */89 * * /root/renew-cert.sh

Wordpress Specific SSL Redirects

Edit your wp-config.php file to include:

define('FORCE_SSL_ADMIN', true);
define('FORCE_SSL_LOGIN', true);
if ($_SERVER['HTTP_X_FORWARDED_PROTO'] == 'https')
  $_SERVER['HTTPS']='on';

Example HAproxy config

On centos the default location of the HAproxy config is: /etc/haproxy/haproxy.cfg

This example config:

#---------------------------------------------------------------------
# Global settings
#---------------------------------------------------------------------
global
    # to have these messages end up in /var/log/haproxy.log you will
    # need to:
    #
    # 1) configure syslog to accept network log events.  This is done
    #    by adding the '-r' option to the SYSLOGD_OPTIONS in
    #    /etc/sysconfig/syslog
    #
    # 2) configure local2 events to go to the /var/log/haproxy.log
    #   file. A line like the following can be added to
    #   /etc/sysconfig/syslog
    #
    #    local2.*                       /var/log/haproxy.log
    #
    log         127.0.0.1 local2

    chroot      /var/lib/haproxy
    pidfile     /var/run/haproxy.pid
    maxconn     4000
    user        haproxy
    group       haproxy
    daemon
    tune.ssl.default-dh-param 2048

    # turn on stats unix socket
    stats socket /var/lib/haproxy/stats
    
    #---------------------------------------------------------------------
    # common defaults that all the 'listen' and 'backend' sections will
    # use if not designated in their block
    #---------------------------------------------------------------------
    defaults
        mode                    http
        log                     global
        option                  httplog
        option                  dontlognull
        option http-server-close
        option forwardfor       except 127.0.0.0/8
        option                  redispatch #Allow proxy to redistribute persistent session to a working server.
        retries                 3
        timeout http-request    5s
        timeout queue           1m
        timeout connect         10s
        timeout client          1m # recommended client timeout equal to server timeout to avoid complex debug.
        timeout server          1m
        timeout http-keep-alive 10s
        timeout check           10s
        maxconn                 3000
        errorfile 408 /dev/null # https://www.haproxy.com/blog/haproxy-and-http-errors-408-in-chrome/
        
#---------------------------------------------------------------------
# main frontend which proxys to the backends
#---------------------------------------------------------------------
frontend www-http
    # Redirect HTTP to HTTPS
    bind *:80
    # Adds http header to end of end of the HTTP request
    reqadd X-Forwarded-Proto:\ http
    # Sets the default backend to use which is defined below with name 'app'
    default_backend www-backend

frontend main
    bind *:443 ssl crt /etc/haproxy/certs/example.co.uk.pem
    # set X-SSL in case of ssl_fc <- explained below
    http-request set-header X-SSL %[ssl_fc]
    # set x-forward to https
    reqadd X-Forwarded-Proto:\ https
    default_backend             www-backend
    
    #---------------------------------------------------------------------
# round robin balancing between the various backends
#---------------------------------------------------------------------
backend www-backend
    # Redirect with code 301 so the browser understands it is a redirect. If it's not SSL_FC.
    # ssl_fc: Returns true when the front connection was made via an SSL/TLS transport
    # layer and is locally deciphered. This means it has matched a socket declared
    # with a "bind" line having the "ssl" option.
    redirect scheme https code 301 if !{ ssl_fc }
    balance     roundrobin
    server  apache1 10.8.96.6:80 check maxconn 15
    server  apache2 10.8.96.7:80 check maxconn 15

Performance tune HAproxy with apache2

With one change to our current HAproxy config we can improve the longest request time from 31.71 secconds to 1.645. How?

Perhaps counterintuitively, by reducing the number of max connections (mancon) to a lower number. E.g. maxconn 10. The reason for this is rather than have apache struggle to manage many concurrent requests (the time is takes apache to respond is proporiatate to the number of concurrent requests it must serve) by reducing this number to ten, we can ensure that each apache instance is only ever handling ten requests at a time, additional connections get qeued up at HAProxy. Consider how many requests your site will need to load it's most complicated page with many embeded images for example. This might influence your maxconn number.

The meaning is too prevent too many requests to be forwarded to an application server, by adding a limit for simultaneous requests for each server of the backend. Fortunately, HAProxy would not reject any request over the limit, unlike some other load balancer does.

For the full story read: https://www.haproxy.com/blog/play_with_maxconn_avoid_server_slowness_or_crash/

Here's an example output of an apache benchmark of adjusting just the maxconn value for each apache instance from 30 to 10 with the haproxy config:

With maxconn 30:

ab -i -n 100 -c 10 example.co.uk

This is ApacheBench, Version 2.3 <$Revision: 1706008 $>
Copyright 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/
Licensed to The Apache Software Foundation, http://www.apache.org/

Benchmarking example.co.uk (be patient).....done

...

Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:      103  862 1744.3    380   15173
Processing:   247 5405 6890.9   1267   30117
Waiting:      246 5350 6881.1   1266   30117
Total:        349 6267 7147.7   2462   31710

Percentage of the requests served within a certain time (ms)
  50%   2462
  66%   5759
  75%  11289
  80%  13220
  90%  17158
  95%  21414
  98%  23157
  99%  31710
 100%  31710 (longest request)

With maxconn 10:

Notiche the apache bench call is exactly the same, we've simply improved our HAproxy config / HAproxy performance turning specific to our two apache instances: