DEV Community

Cover image for 🟩How to Route DB Connections to Multiple Database Servers Using HAProxy Port Forwarding
Latchu@DevOps
Latchu@DevOps

Posted on

🟩How to Route DB Connections to Multiple Database Servers Using HAProxy Port Forwarding

#mysql #haproxy #devops #cloud

Managing multiple MySQL databases across several servers can be challenging—especially when you want users to connect seamlessly without needing to know which server hosts which database.

In this guide, I’ll walk you through a clean, production-friendly setup where:

  • You have 3 MySQL servers (each hosting one database)
  • You have 1 client server
  • You install HAProxy on the client server
  • Users connect to different ports, and HAProxy forwards them to the correct MySQL server

This approach is simple, scalable, and ideal for separating workloads or multi-tenant MySQL environments.

🟦 What is HAProxy?

HAProxy (High Availability Proxy) is a fast, open-source TCP and HTTP load balancer widely used in production environments.

Key features:

  • Load Balancing
  • Reverse Proxying
  • Health Checks
  • High Availability
  • SSL Termination
  • TCP/UDP routing

For MySQL, HAProxy acts as a TCP proxy, routing database connections based on rules you define.

🟦 What is HAProxy Port Forwarding?

HAProxy port forwarding means:

  • You expose different ports on the HAProxy server
  • Each port forwards traffic to a specific backend server

In our case:

Port Goes To Database
3301 MySQL Server 1 db1
3302 MySQL Server 2 db2
3303 MySQL Server 3 db3

So, when a user connects to:

mysql -h haproxy-server -P 3302 -u dbuser -p
Enter fullscreen mode Exit fullscreen mode

They are transparently forwarded to MySQL Server 2.

This method keeps things simple and avoids complex routing logic.

🟩 Architecture Diagram 

                +---------------------+
                |     Client User     |
                +----------+----------+
                           |
                           | MySQL request on port 3301/3302/3303
                           |
                     +-----+------+
                     |  HAProxy   |
                     |  (10.0.0.14)|
                     +-----+------+
         3301 -----------| |------------- 3302
                         | |
        +----------------+ +----------------+
        |                                   |
+-------+--------+                 +--------+-------+
| MySQL Server 1 |                 | MySQL Server 2 |
|  (10.0.0.11)   |                 |  (10.0.0.12)   |
|     db1        |                 |     db2        |
+----------------+                 +----------------+

                    +-----------------------+
                    |   MySQL Server 3      |
                    |     (10.0.0.13)       |
                    |         db3           |
                    +-----------------------+
Enter fullscreen mode Exit fullscreen mode

🟩 Server Setup Overview 

| Server Role            | Example IP  |
| ---------------------- | ----------- |
| MySQL Server 1 (db1)   | `10.0.0.11` |
| MySQL Server 2 (db2)   | `10.0.0.12` |
| MySQL Server 3 (db3)   | `10.0.0.13` |
| HAProxy + MySQL client | `10.0.0.14` |
Enter fullscreen mode Exit fullscreen mode

Replace IPs with your own.

🟦 Step 1 — Install MySQL on Each Server

Run on all three MySQL servers:

sudo apt update
sudo apt install mysql-server -y
sudo systemctl enable mysql
sudo systemctl start mysql
Enter fullscreen mode Exit fullscreen mode

🟦 Step 2 — Secure MySQL Installation 

sudo mysql_secure_installation
Enter fullscreen mode Exit fullscreen mode

Recommended answers:

  • Remove anonymous users: Yes
  • Disallow remote root login: Yes
  • Remove test DB: Yes

🟦 Step 3 — Create Databases and MySQL User

✔ Server 1 (10.0.0.11)

sudo mysql
Enter fullscreen mode Exit fullscreen mode 
CREATE DATABASE db1;
CREATE USER 'dbuser'@'%' IDENTIFIED BY 'StrongPass@123';
GRANT ALL PRIVILEGES ON db1.* TO 'dbuser'@'%';
FLUSH PRIVILEGES;
Enter fullscreen mode Exit fullscreen mode

✔ Server 2 (10.0.0.12)

CREATE DATABASE db2;
CREATE USER 'dbuser'@'%' IDENTIFIED BY 'StrongPass@123';
GRANT ALL PRIVILEGES ON db2.* TO 'dbuser'@'%';
Enter fullscreen mode Exit fullscreen mode

✔ Server 3 (10.0.0.13)

CREATE DATABASE db3;
CREATE USER 'dbuser'@'%' IDENTIFIED BY 'StrongPass@123';
GRANT ALL PRIVILEGES ON db3.* TO 'dbuser'@'%';
Enter fullscreen mode Exit fullscreen mode

🟦 Step 4 — Allow Remote Connections

Edit MySQL config on each database server:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
Enter fullscreen mode Exit fullscreen mode

Change:

bind-address = 127.0.0.1
Enter fullscreen mode Exit fullscreen mode

To:

bind-address = 0.0.0.0
Enter fullscreen mode Exit fullscreen mode

Restart:

sudo systemctl restart mysql
Enter fullscreen mode Exit fullscreen mode

🟦 Step 5 — Install HAProxy on the Client Server

Run on 10.0.0.14:

sudo apt update
sudo apt install haproxy -y
Enter fullscreen mode Exit fullscreen mode

🟦 Step 6 — Configure HAProxy for MySQL Port Forwarding

Edit config:

global
    log /dev/log local0
    maxconn 4096
    daemon

defaults
    mode tcp
    option tcplog
    timeout connect 5s
    timeout client  1m
    timeout server  1m

# -----------------
# FRONTENDS
# -----------------

frontend mysql_db1
    bind *:3301
    default_backend backend_db1

frontend mysql_db2
    bind *:3302
    default_backend backend_db2

frontend mysql_db3
    bind *:3303
    default_backend backend_db3

# -----------------
# BACKENDS
# -----------------

backend backend_db1
    server db1 10.0.0.11:3306 check

backend backend_db2
    server db2 10.0.0.12:3306 check

backend backend_db3
    server db3 10.0.0.13:3306 check
Enter fullscreen mode Exit fullscreen mode

Restart HAProxy:

sudo systemctl restart haproxy
sudo systemctl enable haproxy
Enter fullscreen mode Exit fullscreen mode

🟦 Step 7 — Test HAProxy Routing

✔ Test DB1

mysql -u dbuser -p -h 127.0.0.1 -P 3301 db1
Enter fullscreen mode Exit fullscreen mode

✔ Test DB2

mysql -u dbuser -p -h 127.0.0.1 -P 3302 db2
Enter fullscreen mode Exit fullscreen mode

✔ Test DB3

mysql -u dbuser -p -h 127.0.0.1 -P 3303 db3
Enter fullscreen mode Exit fullscreen mode

If it connects successfully, HAProxy routing works!

🟩 How Users Will Connect

Tell users:

| Database | Port     |
| -------- | -------- |
| db1      | **3301** |
| db2      | **3302** |
| db3      | **3303** |
Enter fullscreen mode Exit fullscreen mode

Example:

mysql -h <haproxy-ip> -P 3302 -u dbuser -p db2
Enter fullscreen mode Exit fullscreen mode

🟩 Why This Architecture Works Well

✔ Simple — no need for DNS tricks or MySQL router
✔ Easy to scale — add more ports/backends anytime
✔ Secure — MySQL servers are never exposed directly
✔ Transparent to users — they just pick a port
✔ Good for multi-tenant setups

🟩 Conclusion

Using HAProxy as a port-based MySQL router is one of the cleanest and most maintainable setups for environments where different databases reside on different servers.

By forwarding unique ports to unique backend servers, you avoid complexity while providing a flexible system that users can easily interact with.

Top comments (0)