Using HAProxy to Load Balance MySQL Slave Servers

Overview

In this tutorial you will be shown how to scale your MySQL database, and balance traffic across all slave nodes.

Your application load has grown too large for your backend database server, and you need a solution to do so that his highly available and balanced.

You could use DNS round-robin load balancing, however, your DNS service is unlikely to know the state your backend service. If an endpoint goes down, DNS would continue directing traffic to it.

A better solution is to use a TCP proxy service, such as HAProxy.

Getting Started

Install HAProxy

Installing HAProxy on Ubuntu 16

sudo apt update
sudo apt-get install -y haproxy

Installing HAProxy on Ubuntu 18

sudo apt install -y haproxy

Deploy MySQL Servers

Creating a new MySQL cluster is not covered in this post. However, the instructions are available in a previous post called How to Create MySQL Master Slave Clusters.

You will need at least one master node and one slave node to get started.

Define the Backend Service in HAProxy

The first step is to create a service proxy for the backend read-only database servers. The service is what your the application we target as the database read endpoints.

An example haproxy configuration is shown below. The configuration in the example creates a frontend service called app1_read_db. We bind it to a network interface and port on the haproxy host.

The second part is to define our backend service, which is where we place the read-only databases. We create a server entry for each, give it a name, and set its network endpoint.

Round Robin MySQL Load Balancing

Robin-robin load balancing a simple model for balancing traffic across your backend servers. Traffic will will rotate between each server in sequential order. For example, if you have three servers defined as your backend, then the first connection would go to server 1, the second connection would go to server 2, and the third connection would go to server 3.

Use Case: The round-robin model works best in environments where all servers are of the same spec.

Cons: HAProxy will not monitor the amount of open connections to your servers. Since not all database connections are equal, there is a chance the one or more of your servers become overwhelmed with traffic, while others sit idle.

global
        user haproxy
        group haproxy
        daemon
        maxconn 4096

defaults
        mode    tcp
        balance roundrobin
        timeout client      30000ms
        timeout server      30000ms
        timeout connect      3000ms
        retries 3

frontend app1_read_db
        bind 0.0.0.0:3306
        default_backend mysql_slaves_group1

backend mysql_slaves_group1
        server db-slave-01 192.168.1.10:3306 maxconn 2048
        server db-slave-02 192.168.1.11:3306 maxconn 2048
        server db-slave-03 192.168.1.12:3306 maxconn 2048 

Least Connections MySQL Load Balancing

The least connections model will attempt to priorities backend servers with the least amount of connections. HAProxy will monitor how many open connections each database server has. New traffic will be directed to servers with the least amount of connections first.

global
        user haproxy
        group haproxy
        daemon
        maxconn 4096

defaults
        mode    tcp
        balance leastconn
        timeout client      30000ms
        timeout server      30000ms
        timeout connect      3000ms
        retries 3

frontend app1_read_db
        bind 0.0.0.0:3306
        default_backend mysql_slaves_group1

backend mysql_slaves_group1
        server db-slave-01 192.168.1.10:3306 maxconn 2048
        server db-slave-02 192.168.1.11:3306 maxconn 2048
        server db-slave-03 192.168.1.12:3306 maxconn 2048

Simplifying Configuration using Listen

In the two examples above there is a frontend and a backend configuration for each service. We can combine these two into a single listener configuration. This is a common practice for proxying TCP network services.

 global
        user haproxy
        group haproxy
        daemon
        maxconn 4096

defaults
        mode    tcp
        balance leastconn
        timeout client      30000ms
        timeout server      30000ms
        timeout connect      3000ms
        retries 3

listener mysql_slaves 0.0.0.0:3306
        mode tcp
        balance leastconn
        server db-slave-01 192.168.1.10:3306
        server db-slave-02 192.168.1.11:3306
        server db-slave-03 192.168.1.12:3306