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
