HAProxy – Mysql cluster on Docker

HAProxy – Mysql cluster on Docker

In this tutorial I am going to setup a HAProxy based cluster (layer 4) in Docker which would load balance to a set of Mysql nodes (again running on Docker).

Before I jump into how to get this done, I would like to explain a little more about few important terms : –

  • Docker – well, everybody knows this. Excellent implementation of Microservices architecture. nearly everybody is going for it, whether Redhat, Oracle, Microsoft, Apple. it has potential to containerize on any platform.
  • HAProxy – stands for High Availability Proxy, is an open source software TCP/HTTP Load Balancer and Reverse proxy solution which can run on Linux, Solaris, and FreeBSD. Most common use case for this product is to improve the performance and availability of servers by distributing the workload across multiple servers (e.g. web, application, database (yes, even Database)). So why do we need HAProxy?  Availability / reliability / stability we have got all the reasons to have it, You may also use NGINX for this or a Network based Balancer like F5 devices.

Setup without Balancing would look more or less similar to below architecture :-

no-balancing

In the above example the users connect to the web server, and there is no load balancing. If this single web server goes down, the users will no longer get access to the web server eventually causing Service Disruption. Additionally, if many users are trying to access your app / Webserver simultaneously and it is unable to handle the load, they may get a slow experience or they may not be able to connect at all.
Layer 4 Load Balancing

A simple way to do load balancing to multiple servers is to use layer 4 (transport layer) load balancing. This way the Load Balancer will forward user traffic based on IP / IP range and port (i.e. if a request comes in for http://somedomain.com/something, the traffic will be forwarded to the backend that handles all the requests for somedomain.com). This won’t be intelligent enough to introspect the content headers  and redirect user to any other set of servers if need be for specific subdirectory of a domain.

Layer 7 Load Balancing

Another more flexible way to load balance network traffic is to use layer 7 (application layer) load balancing. Using layer 7 allows HAProxy to forward requests to different backend servers based on the content of the user’s request. This mode of load balancing allows to run multiple web application servers under the same domain and port. A example setup would like a Portal Server in backend fetching the Portal frames only being redirected based on “/portal” and another set of backend servers running the actual app which would receive traffic when Portal server invokes /app/someportlet.

Here is a diagram of a simple example of layer 7 load balancing:

load-balancing

  • Rsyslogd  – Something which is required for a HAProxy container to send the logs to. HAProxy doesn’t create logs in physical Files but depends on rsyslogd daemon.  since we don’t have Rsyslogd installed on official HAProxy image, we can use any other rsyslogd image and launch a container to receive the logs.
  • MySQL – of course your need at least 2 MySQL nodes setup in Master-Master replication setup to be able to have a Replicated  and fully available cluster. More details in my previous blog Mysql Master-Master Replication setup on Docker

So, How it would look like when we have built this HAProxy based MySql DB Cluster? . Here is how it should  : –

docker-containers-links

Lets start now : –

Step 1 – Master-Master replicated Docker containers 

Full tutorial is there in my previous article – Mysql Master-Master Replication setup on Docker.  However, quick steps here


#!/bin/bash
#title			: Launch 2 MYSQLnodes2
#description	        : This script creates 2 MySQL containers and launches them assuming the data /log/backup/conf.d are present in /opt2/mysql/<node_prefix><nodenumber> folders.
#author		 	: Avinash Barnwal
#date			: 22092016
#version		: 0.1
#usage			: bash Launch2MySqlnodes
#=============================================================================

DB_NAME=mydata
ROOT_PASS=roo235t
MYSQL_IMAGE='mysql:latest'
NODE_PREFIX=mysql

docker run --name ${NODE_PREFIX}1 \
       -e MYSQL_ROOT_PASSWORD=$ROOT_PASS \
       -e MYSQL_DATABASE=$DB_NAME -dit \
       -v /opt2/mysql/${NODE_PREFIX}1/conf.d:/etc/mysql/mysql.conf.d/ \
       -v /opt2/mysql/${NODE_PREFIX}1/data:/var/lib/mysql \
       -v /opt2/mysql/${NODE_PREFIX}1/log:/var/log/mysql \
       -v /opt2/mysql/${NODE_PREFIX}1/backup:/backup \
       -p 3306 \
       -h ${NODE_PREFIX}1 $MYSQL_IMAGE

NODE1_PORT=$(docker inspect --format='{{(index (index .NetworkSettings.Ports "3306/tcp") 0).HostPort}}' ${NODE_PREFIX}1)
 # https://docs.docker.com/engine/reference/commandline/inspect/

docker run --name ${NODE_PREFIX}2 \
       -e MYSQL_ROOT_PASSWORD=$ROOT_PASS \
       -e MYSQL_DATABASE=$DB_NAME -dit \
       --link ${NODE_PREFIX}1:${NODE_PREFIX}1cl \
       -v /opt2/mysql/${NODE_PREFIX}2/conf.d:/etc/mysql/mysql.conf.d/ \
       -v /opt2/mysql/${NODE_PREFIX}2/data:/var/lib/mysql \
       -v /opt2/mysql/${NODE_PREFIX}2/log:/var/log/mysql \
       -v /opt2/mysql/${NODE_PREFIX}2/backup:/backup \
       -p 3306 \
       -h ${NODE_PREFIX}2 $MYSQL_IMAGE

NODE2_IP=$(docker inspect --format '{{ .NetworkSettings.IPAddress }}' ${NODE_PREFIX}2)
# This would add the second node's IP in the Host file of mysql first node.
docker exec -i ${NODE_PREFIX}1 sh -c 'echo '$NODE2_IP ${NODE_PREFIX}2 ${NODE_PREFIX}2' >> /etc/hosts';

Step 2 – Launch the rSyslogD container.

I have taken a existing docker image on docker hub from here voxxit/rsyslog

pull the image & Launch it

docker pull voxxit/rsyslog
docker run --name haproxy-logger -dit -h haproxy-logger -v /opt2/mysql/logs:/var/log/ voxxit/rsyslog

Step 3 – Prepare HAProxy

I am going to use official HAProxy docker image from docker hub. As this image doesn’t have a mysql-client within, we would have to set it up as Layer 4 Load Balancer. If you are interested to set it up as a Layer 7 Load balancer you would need to build your own image from the official image with Mysql client installation.

Here is a configuration which I have used : –

global
    log haproxy-logger local0 notice
    # user haproxy
    # group haproxy
defaults
    log global
    retries 2
    timeout connect 3000
    timeout server 5000
    timeout client 5000
listen mysql-cluster
    bind 0.0.0.0:3306
    mode tcp
    #option mysql-check user haproxy_check  (This is not needed as for Layer 4 balancing)
    option tcp-check
    balance roundrobin
    # The below nodes would be hit on 1:1 ratio. If you want it to be 1:2 then add 'weight 2' just after the line.
    server mysql1 mysql1:3306 check
    server mysql2 mysql2:3306 check
# Enable cluster status
listen mysql-clusterstats
    bind 0.0.0.0:8080
    mode http
    stats enable
    stats uri /
    stats realm Strictly\ Private
    stats auth status:keypas5

Step 4 – Launch HAProxy container

We are going to launch the HAProxy Docker image with all the Volume mounts

docker run --name mysql-cluster -dit \
    -h mysql-cluster \
    --link mysql1:mysql1cl  --link mysql2:mysql2cl \
    --link haproxy-logger:haproxy-loggercl \
    -v /opt2/mysql/haproxy/haproxy.cfg:/usr/local/etc/haproxy/haproxy.cfg:ro \
    -p 33060:3306 -p 38080:8080 \
    haproxy:latest

# check the docker container status
# There should be 4 containers there
# mysql1, mysql2, mysql-cluster, haproxy-logger
docker ps -a
/opt2/mysql$ docker ps -a
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                                              NAMES
f1343662ce3f        haproxy:latest      "/docker-entrypoint.s"   9 minutes ago       Up 9 minutes        0.0.0.0:33060:3306/tcp, 0.0.0.0:38080->8080/tcp   mysql-cluster
c005e3275745        voxxit/rsyslog      "rsyslogd -n"            10 minutes ago      Up 10 minutes       514/tcp, 514/udp                                   haproxy-logger
59ed9026944d        mysql:latest        "docker-entrypoint.sh"   25 hours ago        Up 21 hours         0.0.0.0:9010->3306/tcp                             mysql2
1bff0bc121bc        mysql:latest        "docker-entrypoint.sh"   25 hours ago        Up 23 hours         0.0.0.0:9004->3306/tcp                             mysql1

If everything is good, you should be able to see all the docker containers live and HAPROXY listening 33060 for MySQL transport and 38080 for Status page.

Step 5- MySQL client verification 

If you are thinking to do verify Mysql connections using passwords, that’s a bad idea.  This is because, the MySQL 5.6 onward will spit out warnings when using passwords on command line parameters “Warning: Using a password on the command line interface can be insecure.

To keep your password secure and escape such warnings you can use mysql_config_editor tool like below : –


mysql_config_editor set --login-path=local --host=localhost --user=username --password

#Now you can use below
mysql --login-path=local -e "statement"
# old usage was
mysql -u username -p pass -e "statement"

Additionally, the MySQL client would try to connect on Unix Socket , which is not running on the Docker host so you need to override that behavior by using “–protocol tcp” and then provide the port as well”-P 33060″

To test your MySQL Cluster with about 10 connections repetitively, you may use the below script

$ mysql_config_editor set --login-path=local --host=localhost --user=root --password
# Password:
# Password Stored
$ for i in `seq 1 10`
  do
  mysql --login-path=local -P 33060 --protocol tcp -e "show variables like 'server_id'"
  done

Output would be like below : –

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 101   |
+---------------+-------+
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 102   |
+---------------+-------+
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 101   |
+---------------+-------+
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 102   |
+---------------+-------+
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 101   |
+---------------+-------+
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 102   |
+---------------+-------+
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 101   |
+---------------+-------+
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 102   |
+---------------+-------+
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 101   |
+---------------+-------+
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 102   |
+---------------+-------+

This shows that HAProxy is running and loading the traffic 1:1 between the 2 backend nodes.

If you need the Load balancing to be setup in a different ratio, you can set it up using ‘weight 2’ parameter in the haproxy.cfg when defining the backend MySQL nodes.

Here is a snapshot of the Status page. Note the LastChk column which says and stays success until the node is UP.

haproxy-status

Lets try to take one of the containers down and see what happens .

Step a – Stop Container 


$ docker stop mysql2

Step b – Check status Page 

The Status page shows MySQL node2 has gone down .

HAProxy Status failed.png

Step c – Verify the MySQL client way 

for i in `seq 1 10`
do
mysql --login-path=local -P 33060 --protocol tcp -e "show variables like 'server_id'"
done
# This would all go to MySQL&nbsp;1 showing server_id as 101

Bring the mysql2 node back would again allow HAProxy to load-balance between the nodes. Remember the mysql2 node would pick up all the changes from MYSQL1 Node as those nodes are setup in Master-Master replication.

Hope you enjoyed!  Happy learning !

Any feedback, you are more than welcome.

One thought on “HAProxy – Mysql cluster on Docker

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.