How to set up a MySQL load balancer with HAProxy and replication

A load-balanced MySQL topology improves availability for read-heavy workloads by spreading read traffic across replicas while keeping writes on a single primary node. That reduces pressure on the primary, smooths traffic spikes, and provides extra database nodes that can keep serving reads during maintenance windows.

MySQL replication works by recording changes on the primary in the binary log, then having replica servers connect and apply those changes to stay in sync. HAProxy operates as a TCP proxy for MySQL connections, so balancing occurs per connection rather than per SQL statement, which makes role separation (writes vs reads) a configuration problem instead of an inspection problem.

Replication is typically asynchronous, so replicas can lag behind the primary and serve slightly stale reads during bursts or network issues. This setup does not provide automatic primary failover, so planned promotion tooling (or an orchestrator) is still required for seamless write continuity, and network exposure of port 3306 must be tightly restricted to avoid turning the database into a public suggestion box.

Steps to set up load-balanced MySQL with HAProxy and replication:

Confirm the MySQL primary and replicas are running.

$ docker ps --filter name=sg-mysql-primary --filter name=sg-mysql-replica --filter name=sg-mysql-replica-2 --format 'table {{.Names}}\t{{.Image}}\t{{.Status}}'
NAMES                IMAGE       STATUS
sg-mysql-replica-2   mysql:8.0   Up 7 minutes
sg-mysql-replica     mysql:8.0   Up 9 minutes
sg-mysql-primary     mysql:8.0   Up 9 minutes

On non-container hosts, install MySQL on each node and ensure the service is running.

Set replication options on the master host in /etc/mysql/conf.d/replication.cnf.
```
[mysqld]
server-id = 1
log_bin = mysql-bin
binlog_format = ROW
bind-address = 192.0.2.40
```
Setting bind-address to a reachable interface exposes MySQL on the network; restrict inbound 3306 to replica and proxy IPs with firewall rules or security groups.

Restart the mysql service on the master host.

$ sudo docker restart sg-mysql-primary
sg-mysql-primary

Confirm binary logging is active on the master host.

$ mysql -u root -p -e "SHOW MASTER STATUS\\G"
*************************** 1. row ***************************
             File: mysql-bin.000002
         Position: 2323
     Binlog_Do_DB:
 Binlog_Ignore_DB:

If File and Position are empty, binary logging is not enabled, and replicas cannot follow the master.

Create a user for replication on the master host.

$ mysql -u root -p -e "CREATE USER 'repl'@'192.0.2.41' IDENTIFIED BY 'ReplPass123!'; CREATE USER 'repl'@'192.0.2.42' IDENTIFIED BY 'ReplPass123!'; GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'192.0.2.41'; GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'192.0.2.42'; FLUSH PRIVILEGES;"

Prefer replica-specific hosts (IP or DNS) instead of '%' to limit where the replication credentials can be used.

Create a consistent seed dump on the master host.
```
$ mysqldump --single-transaction --routines --triggers --events --master-data=2 --all-databases > /root/sg-work/replication/lb-seed.sql
```
Importing a full dump into a non-empty replica can overwrite objects or fail on conflicts; use a fresh instance or a verified backup/restore strategy.

Copy the seed dump to each replica host.

$ docker cp sg-mysql-primary:/root/sg-work/replication/lb-seed.sql /root/sg-work/replication/
$ docker cp /root/sg-work/replication/lb-seed.sql sg-mysql-replica:/root/sg-work/replication/lb-seed.sql
$ docker cp /root/sg-work/replication/lb-seed.sql sg-mysql-replica-2:/root/sg-work/replication/lb-seed.sql

Set replica options on each replica host in /etc/mysql/conf.d/replica.cnf.
```
[mysqld]
server-id = 2
relay_log = relay-bin
read_only = ON
super_read_only = ON
```
Every replica requires a unique server-id, and enabling super_read_only helps prevent accidental writes through misrouted connections.

Restart MySQL on each replica host.

$ sudo docker restart sg-mysql-replica sg-mysql-replica-2
sg-mysql-replica
sg-mysql-replica-2

Import the seed dump on each replica host.

$ mysql -u root -p < /root/sg-work/replication/lb-seed.sql

Extract the master binary log coordinates from the dump header.
```
$ grep -m 1 "MASTER_LOG_FILE" /root/sg-work/replication/lb-seed.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=2323;
```
The dump includes coordinates for a consistent start point, which avoids guessing a safe Position value.

Configure replication on each replica host using the extracted file and position.

$ mysql -u root -p -e "CHANGE REPLICATION SOURCE TO SOURCE_HOST='192.0.2.40', SOURCE_USER='repl', SOURCE_PASSWORD='ReplPass123!', SOURCE_LOG_FILE='mysql-bin.000002', SOURCE_LOG_POS=2323, GET_SOURCE_PUBLIC_KEY=1;"

Older MySQL versions use CHANGE MASTER TO instead of CHANGE REPLICATION SOURCE TO.

Start replication on each replica host.
```
$ mysql -u root -p -e "START REPLICA;"
```
Older MySQL versions use START SLAVE instead of START REPLICA.

Check the replication status on each replica host.

$ mysql -u root -p -e "SHOW REPLICA STATUS\\G" | grep -E "Replica_IO_Running|Replica_SQL_Running|Seconds_Behind_Source|Last_IO_Error|Last_SQL_Error"
Replica_IO_Running: Yes
Replica_SQL_Running: Yes
Seconds_Behind_Source: 0
Last_IO_Error:
Last_SQL_Error:

On older versions, run SHOW SLAVE STATUS\\G and confirm Slave_IO_Running and Slave_SQL_Running are Yes.

Create a low-privilege MySQL user on the master host for proxy connection tests.

$ mysql -u root -p -e "CREATE USER 'lbcheck'@'192.0.2.50' IDENTIFIED BY 'LbPass123!'; GRANT USAGE ON *.* TO 'lbcheck'@'192.0.2.50'; FLUSH PRIVILEGES;"

Add MySQL TCP listeners to /usr/local/etc/haproxy/haproxy.cfg for writes and reads.

listen mysql_write
    bind *:3306
    mode tcp
    option tcplog
    option tcp-check
    server mysql-master 192.0.2.40:3306 check

listen mysql_read
    bind *:3307
    mode tcp
    option tcplog
    balance roundrobin
    option tcp-check
    server mysql-replica-1 192.0.2.41:3306 check
    server mysql-replica-2 192.0.2.42:3306 check

HAProxy does not inspect SQL, so read/write separation is by port (3306 for writes, 3307 for reads) rather than by query type.

Start HAProxy with a mounted configuration file.

$ docker run -d --name sg-haproxy-mysql --restart unless-stopped -v /root/sg-work/haproxy/haproxy.cfg:/usr/local/etc/haproxy/haproxy.cfg:ro haproxy:2.9

Validate the HAProxy configuration before reloading.

$ docker exec sg-haproxy-mysql haproxy -c -V -f /usr/local/etc/haproxy/haproxy.cfg
Configuration file is valid

Restart haproxy to apply the new configuration.
```
$ docker restart sg-haproxy-mysql
sg-haproxy-mysql
```
A single HAProxy node is a single point of failure; use a redundant pair (VRRP/keepalived) or a managed load balancer for high availability.

Enable haproxy to start on boot.

$ docker update --restart unless-stopped sg-haproxy-mysql
sg-haproxy-mysql

Confirm haproxy is listening on the write and read ports.

$ docker exec sg-haproxy-mysql ss -lntp | grep haproxy
LISTEN 0      4096         0.0.0.0:3306      0.0.0.0:*    users:(("haproxy",pid=8,fd=7))
LISTEN 0      4096         0.0.0.0:3307      0.0.0.0:*    users:(("haproxy",pid=8,fd=8))

Test the write endpoint through HAProxy.

$ mysql --host 192.0.2.50 --port 3306 --user lbcheck --password -e "SELECT @@hostname AS backend, @@read_only AS read_only;"
+--------------+-----------+
| backend      | read_only |
+--------------+-----------+
| primary01    |         0 |
+--------------+-----------+

Test the read endpoint through HAProxy.

$ mysql --host 192.0.2.50 --port 3307 --user lbcheck --password -e "SELECT @@hostname AS backend, @@read_only AS read_only;"
+--------------+-----------+
| backend      | read_only |
+--------------+-----------+
| replica01    |         1 |
+--------------+-----------+

Repeated connections to the read endpoint can rotate across replicas, because balancing occurs per TCP connection.

Configure application write traffic to use the HAProxy write port.

Target <HAPROXY_HOSTNAME>:3306 for write connections so statements requiring the primary never land on a read-only replica.
Configure application read traffic to use the HAProxy read port.

Target <HAPROXY_HOSTNAME>:3307 for read-only traffic, and plan for replica lag when strong read-after-write consistency is required.

Author: Mohd Shakir Zakaria
Mohd Shakir Zakaria is a cloud architect with deep roots in software development and open-source advocacy. Certified in AWS, Red Hat, VMware, ITIL, and Linux, he specializes in designing and managing robust cloud and on-premises infrastructures.

Discuss the article:

Comment anonymously. Login not required.