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:

Install MySQL on the master host.

$ sudo apt-get update && sudo apt-get install --assume-yes mysql-server
Hit:1 http://archive.ubuntu.com/ubuntu jammy InRelease
##### snipped #####
Setting up mysql-server-8.0 (8.0.xx-0ubuntu0.22.04.x) ...

Install MySQL on each replica host.

$ sudo apt-get update && sudo apt-get install --assume-yes mysql-server

Set replication options on the master host in /etc/mysql/mysql.conf.d/mysqld.cnf.
```
[mysqld]
server-id = 1
log_bin = mysql-bin
binlog_format = ROW
bind-address = <MASTER_PRIVATE_IP>
```
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 systemctl restart mysql
```

Confirm binary logging is active on the master host.

$ sudo mysql -e "SHOW MASTER STATUS\\G"
*************************** 1. row ***************************
             File: mysql-bin.000003
         Position: 157
     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.

$ sudo mysql
mysql> CREATE USER 'repl'@'10.0.0.%' IDENTIFIED BY '<REPL_PASSWORD>';
mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'10.0.0.%';
mysql> FLUSH PRIVILEGES;
mysql> EXIT;

Avoid '%' as the host pattern for the replication user; restrict by replica IPs or a private subnet.

Create a consistent seed dump on the master host.
```
$ sudo mysqldump --single-transaction --routines --triggers --events --master-data=2 --all-databases > /tmp/master.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.

$ scp /tmp/master.sql <REPLICA1_HOST>:/tmp/master.sql

Set replica options on each replica host in /etc/mysql/mysql.conf.d/mysqld.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 systemctl restart mysql
```
Import the seed dump on each replica host.
```
$ sudo mysql < /tmp/master.sql
```
Extract the master binary log coordinates from the dump header.
```
$ grep -m 1 "MASTER_LOG_FILE" /tmp/master.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=157;
```
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.

$ sudo mysql
mysql> CHANGE REPLICATION SOURCE TO
    -> SOURCE_HOST='<MASTER_PRIVATE_IP>',
    -> SOURCE_USER='repl',
    -> SOURCE_PASSWORD='<REPL_PASSWORD>',
    -> SOURCE_LOG_FILE='mysql-bin.000003',
    -> SOURCE_LOG_POS=157,
    -> GET_SOURCE_PUBLIC_KEY=1;
Query OK, 0 rows affected (0.01 sec)

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

Start replication on each replica host.
```
mysql> START REPLICA;
Query OK, 0 rows affected (0.01 sec)
```
Older MySQL versions use START SLAVE instead of START REPLICA.

Check the replication status on each replica host.

mysql> SHOW REPLICA STATUS\G
*************************** 1. row ***************************
             Replica_IO_Running: Yes
            Replica_SQL_Running: Yes
           Seconds_Behind_Source: 0
                  Last_IO_Error:
                 Last_SQL_Error:
##### snipped #####

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.

$ sudo mysql
mysql> CREATE USER 'lbcheck'@'<HAPROXY_PRIVATE_IP>' IDENTIFIED BY '<LB_PASSWORD>';
mysql> GRANT USAGE ON *.* TO 'lbcheck'@'<HAPROXY_PRIVATE_IP>';
mysql> FLUSH PRIVILEGES;
mysql> EXIT;

Install HAProxy on the load balancer host.

$ sudo apt-get update && sudo apt-get install --assume-yes haproxy

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

listen mysql_write
    bind *:3306
    mode tcp
    option tcplog
    option tcp-check
    server mysql-master <MASTER_PRIVATE_IP>:3306 check

listen mysql_read
    bind *:3307
    mode tcp
    option tcplog
    balance roundrobin
    option tcp-check
    server mysql-replica-1 <REPLICA1_PRIVATE_IP>:3306 check
    server mysql-replica-2 <REPLICA2_PRIVATE_IP>: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.

Validate the HAProxy configuration before reloading.

$ sudo haproxy -c -f /etc/haproxy/haproxy.cfg
Configuration file is valid

Restart haproxy to apply the new configuration.
```
$ sudo systemctl restart haproxy
```
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.
```
$ sudo systemctl enable haproxy
```

Confirm haproxy is listening on the write and read ports.

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

Test the write endpoint through HAProxy.

$ mysql --host <HAPROXY_PRIVATE_IP> --port 3306 --user lbcheck --password -e "SELECT @@hostname AS backend, @@read_only AS read_only;"
Enter password:
+----------+-----------+
| backend  | read_only |
+----------+-----------+
| master01 |         0 |
+----------+-----------+

Test the read endpoint through HAProxy.

$ mysql --host <HAPROXY_PRIVATE_IP> --port 3307 --user lbcheck --password -e "SELECT @@hostname AS backend, @@read_only AS read_only;"
Enter password:
+-----------+-----------+
| backend   | read_only |
+-----------+-----------+
| replica02 |         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.