Body
MySQL HA Lab MySQL HA Lab · MySQL · MySQL Router

MySQL HA Lab, Lesson 5: Highly Available MySQL Router with HAProxy

A single MySQL Router is a single point of failure. Run two routers behind HAProxy, understand every health-check setting, and test router failures alone and during a database failover.

MySQL HA Lab Lesson 5: Highly Available MySQL Router with HAProxy
📘
This post is part of the MySQL HA Lab series: a hands-on MySQL 8.4 InnoDB Cluster on Docker. Code for this lesson: branch 03-multi-router on GitHub. Previous: Lesson 4: Monitoring with Prometheus and Grafana.

A single MySQL Router is a single point of failure: the database survives losing a node, but if the router dies, no client can reach any of them. This lesson runs two routers behind HAProxy.

flowchart TB
    c(["Clients"]) -->|"mysql-router:6446 / :6447"| h["HAProxy (mysql-router)<br/>TCP health checks · stats :8404"]
    h -->|"leastconn"| r1["mysql-router1"]
    h -->|"leastconn"| r2["mysql-router2"]
    r1 & r2 -->|"writes"| p[("PRIMARY")]
    r1 & r2 -.->|"reads"| s[("SECONDARIES")]
Two MySQL Routers behind HAProxy: HAProxy picks a healthy router, the router picks the MySQL node.

Naming trick: the HAProxy container keeps the name mysql-router. Everything that connected to the single router (monitoring-init, the helpers) keeps working, now load-balanced.

Ways to make routing highly available

ApproachHowWhen
Router as sidecarOne router on every client host; clients connect to localhostOracle's recommended pattern. A dead router only affects its own host.
Routers behind a load balancer (this lesson)HAProxy or a cloud LB in front of 2+ routersOne shared address for many clients
Client-side failoverThe connection string lists several router addresses, tried in orderSimple, but every client must be configured

Files in this lesson

docker-compose.yml       # x-router anchor, router1, router2, router-lb
haproxy/haproxy.cfg      # load balancer config
monitoring/prometheus.yml  # + haproxy scrape job
monitoring/grafana/dashboards/  # routers panels (now in 3-routers-haproxy.json and 1-overview.json)
.editorconfig            # guarantees the final newline HAProxy needs

The routers

x-router: &router
  image: container-registry.oracle.com/mysql/community-router:8.4
  restart: unless-stopped
  depends_on:
    cluster-init:
      condition: service_completed_successfully
  environment:
    MYSQL_HOST: mysql1
    MYSQL_PORT: 3306
    MYSQL_USER: ${CLUSTER_ADMIN}
    MYSQL_PASSWORD: ${CLUSTER_ADMIN_PASSWORD}
    MYSQL_INNODB_CLUSTER_MEMBERS: 3
  networks: [cluster]

  router1:
    <<: *router
    container_name: mysql-router1
    hostname: mysql-router1
  router2:
    <<: *router
    container_name: mysql-router2
    hostname: mysql-router2
KeyMeaning
imageOracle's official MySQL Router 8.4 image (Docker Hub has none)
restart: unless-stoppedComes back after a crash or a Docker restart
condition: service_completed_successfullyStarts only after cluster-init exited 0, so the cluster metadata exists
MYSQL_HOST / MYSQL_PORTMember used to bootstrap: the router reads the cluster metadata from it and writes its own config. Only used at startup; afterwards the router follows the whole topology.
MYSQL_USER / MYSQL_PASSWORDAccount used for the bootstrap (needs metadata access, so the cluster admin)
MYSQL_INNODB_CLUSTER_MEMBERS: 3The entrypoint waits until this many members are ONLINE before bootstrapping
hostnameRequired. Each router registers in the metadata under its hostname. Without it, every new container registers under a random ID and stale entries pile up.
no ports:Routers are reachable only inside the network; only HAProxy is published

Each router listens on:

PortPurpose
6446Classic protocol, read/write → primary. While there is no primary (during a failover) the router stops listening on this port and resumes once a new primary exists.
6447Classic protocol, read-only → secondaries (round-robin)
6448 / 6449X Protocol, read/write / read-only
6450Read/write splitting: SELECTs to secondaries, writes to the primary on one connection (not exposed through HAProxy)

Registered routers

echo "$CLUSTER_ADMIN_PASSWORD" | docker exec -i mysql1 mysqlsh --js --passwords-from-stdin "$CLUSTER_ADMIN"@mysql1:3306 \
  -e "print(dba.getCluster().listRouters())"      # after: source scripts/helpers.sh

A healthy router's lastCheckIn is seconds old. Remove a stale entry (e.g. the old single router, named after its container ID):

echo "$CLUSTER_ADMIN_PASSWORD" | docker exec -i mysql1 mysqlsh --js --passwords-from-stdin "$CLUSTER_ADMIN"@mysql1:3306 \
  -e "dba.getCluster().removeRouterMetadata('<name exactly as listed, e.g. 86753a7c61ad::>')"

The load balancer service

  router-lb:
    image: haproxy:3.0
    container_name: mysql-router
    hostname: mysql-router
    restart: unless-stopped
    depends_on: [router1, router2]
    volumes:
      - ./haproxy/haproxy.cfg:/usr/local/etc/haproxy/haproxy.cfg:ro
    ports:
      - "6446:6446"
      - "6447:6447"
      - "8404:8404"
    networks: [cluster]
KeyMeaning
image: haproxy:3.0HAProxy 3.0 LTS. Runs as a non-root user, so the config must be world-readable (chmod -R a+rX haproxy).
container_name / hostname: mysql-routerTakes over the old router's name
depends_onStart order only. HAProxy copes with routers that aren't up yet (see init-addr below).
config mountThe image reads /usr/local/etc/haproxy/haproxy.cfg
ports6446/6447 for clients, 8404 for the stats page and metrics

haproxy/haproxy.cfg

global and defaults

global
    log stdout format raw local0

defaults
    mode tcp
    log global
    option tcplog
    timeout connect 5s
    timeout client  1h
    timeout server  1h
SettingMeaning
log stdout format raw local0Logs to stdout, so docker logs mysql-router shows them
mode tcpLayer-4 pass-through. HAProxy doesn't understand MySQL; it forwards bytes. Routing decisions stay with MySQL Router.
log global / option tcplogUse the global log target; one line per connection with timings and bytes
timeout connect 5sMax time to open a connection to a router
timeout client 1h / server 1hMax idle time on an open connection. Long, so idle pooled connections aren't cut; lower it if your pool validates connections.

resolvers

resolvers docker
    nameserver dns 127.0.0.11:53
    hold valid 5s
SettingMeaning
nameserver dns 127.0.0.11:53Docker's embedded DNS, which resolves container names
hold valid 5sRe-resolve names every 5s, so a recreated router container (new IP) is found again

Frontends: what clients connect to

frontend mysql_rw
    bind :6446
    default_backend routers_rw

frontend mysql_ro
    bind :6447
    default_backend routers_ro
FrontendPortSends to
mysql_rw6446routers_rw, port 6446 on each router → primary
mysql_ro6447routers_ro, port 6447 on each router → secondaries

Lesson 6 adds a third MySQL frontend, mysql_rwsplit on 6450. See HAProxy ports after Lesson 6 for the full table.

Backends: the routers

backend routers_rw
    balance leastconn
    default-server check inter 2s fall 2 rise 2 resolvers docker init-addr last,libc,none on-marked-down shutdown-sessions
    server router1 mysql-router1:6446
    server router2 mysql-router2:6446
SettingMeaning
balance leastconnNew connection goes to the router with the fewest open connections (good for long-lived DB connections)
default-serverOptions applied to every server line below
checkHealth-check each router (TCP connect)
inter 2sCheck every 2 seconds
fall 2 / rise 22 failed checks → DOWN, 2 successful → UP (~4s either way)
resolvers dockerResolve router names through Docker DNS at runtime
init-addr last,libc,noneAt startup try the last known address, then normal DNS, then start anyway with the server disabled. Without none, HAProxy refuses to start when a router doesn't exist yet.
on-marked-down shutdown-sessionsWhen a router goes DOWN, close its connections at once, so clients reconnect through the healthy router instead of hanging
server router1 mysql-router1:6446Name shown on the stats page, and the router address

routers_ro is identical with port 6447.

Lesson 7 measures what these checks add to a failover and changes them to inter 1s fall 2 rise 1 (7.3c).

Health checks are plain TCP. They detect a dead router quickly. Whether a primary exists is the router's job, not HAProxy's.

Stats and metrics

frontend stats
    mode http
    bind :8404
    stats enable
    stats uri /stats
    stats refresh 5s
    http-request use-service prometheus-exporter if { path /metrics }
SettingMeaning
mode httpThis frontend speaks HTTP (overrides defaults)
stats enable / stats uri /statsWeb page at http://localhost:8404/stats: green = UP, red = DOWN, Cur = open sessions
stats refresh 5sPage auto-refresh
use-service prometheus-exporter if { path /metrics }Built-in Prometheus exporter on /metrics

The final newline

haproxy.cfg must end with a newline. Without it HAProxy assumes the file was truncated, logs Missing LF on last line, and refuses to start. .editorconfig (insert_final_newline = true) prevents this in most editors.

Validate the config

Run inside the network, so router names resolve:

docker run --rm --network "$NETWORK_CLUSTER_NAME" \
  -v ./haproxy/haproxy.cfg:/tmp/h.cfg:ro haproxy:3.0 haproxy -c -f /tmp/h.cfg; echo "exit: $?"
ResultMeaning
no output, exit: 0Valid, both routers resolvable
only [NOTICE] could not resolve address, exit: 0Valid; a router isn't running (or you ran it without --network)
[ALERT], exit: 1Real error; HAProxy won't start

After editing: docker compose restart router-lb.


Router tests

sequenceDiagram
    participant C as Client
    participant H as HAProxy
    participant R1 as mysql-router1
    participant R2 as mysql-router2
    Note over R1: docker kill mysql-router1
    H-xR1: health check fails (1)
    H-xR1: health check fails (2): DOWN
    H--xC: shutdown-sessions closes its connections
    C->>H: reconnect
    H->>R2: route to the healthy router
    R2-->>C: OK, writes continue
    Note over R1: docker start: good checks, UP again
Losing a router: two failed health checks mark it DOWN, its sessions are closed, and clients reconnect through the other router.

Uses the helpers from Lesson 1 (source scripts/helpers.sh). Keep http://localhost:8404/stats or the Grafana dashboard open.

1. Connections are spread across both routers

for i in $(seq 1 20); do w "SELECT SLEEP(5)" >/dev/null & done; sleep 2

On the stats page, Cur sessions are split roughly evenly between router1 and router2.

2. Losing one router doesn't stop the writes

# terminal 1
txloop

# terminal 2
docker kill mysql-router1
sleep 15
docker start mysql-router1

At most one or two FAIL lines (connections open on router1 at that moment); grep -c ' FAIL ' tx.log counts them. router1 turns red within ~4s and green after the restart; writes continue through router2.

3. Router failure and database failover together

docker kill mysql-router2
P=$(q 6446); docker kill "$P"
# writes pause ~10–15s (database failover), then continue through router1 to the new primary
docker start mysql-router2 "$P"

4. Both routers down (the limit)

docker kill mysql-router1 mysql-router2     # every write fails: no path to the database
docker start mysql-router1 mysql-router2    # back after the health checks pass (~4s)
⚠️
HAProxy is now the single point of failure. It's simpler and stateless, but in production run two HAProxy instances with a shared virtual IP (keepalived), use a cloud load balancer, or use the sidecar pattern.

Router panels

Code: branch 04-router-monitoring

monitoring/prometheus.yml scrapes HAProxy:

  - job_name: haproxy
    static_configs:
      - targets: ["mysql-router:8404"]

The dashboard's MySQL Routers (via HAProxy) section:

PanelMetric
Healthy routers R/W, R/Ohaproxy_backend_active_servers{proxy="routers_rw"} (and routers_ro)
Client sessions via load balancerhaproxy_frontend_current_sessions{proxy=~"mysql_rw|mysql_ro"}
Health-check failures (15m)increase(haproxy_server_check_failures_total[15m])
Router up/downhaproxy_server_status{proxy="routers_rw", state="UP"}
Open sessions per routerhaproxy_server_current_sessions
New connections/sec per routerrate(haproxy_server_sessions_total[...])
Connection errors & check failureshaproxy_server_connection_errors_total, haproxy_server_check_failures_total

During test 2 you see Healthy routers drop to 1 (orange), router1's up/down line drop to 0, and all sessions move to router2.


The MySQL HA Lab series

  1. Series introduction
  2. Lesson 1: Environment and SQL helpers
  3. Lesson 2: A self-bootstrapping InnoDB Cluster
  4. Lesson 3: Failover, quorum and recovery
  5. Lesson 4: Monitoring with Prometheus and Grafana
  6. Lesson 5: Highly available routing with HAProxy (you are here)
  7. Lesson 6: Stale reads and read/write splitting
  8. Lesson 7: Failover tuning, switchover and partitions
  9. Lesson 8: A Spring Boot app that survives failover
  10. Lesson 9: The same app in Go
  11. Bonus: Operations and troubleshooting

All code is on GitHub: mytechbytes/mysql-ha-lab. Questions or a different result in your lab? Leave a comment below.

← Previous MySQL HA Lab, Lesson 4: Monitoring InnoDB Cluster with Prometheus and Grafana Next → MySQL HA Lab, Lesson 6: Stale Reads, Read/Write Splitting and group_replication_consistency
Part 6 of 11 · Collection MySQL HA Lab Back to collection →
Membership

Go deeper.

Members get the full technical guides, production checklists and the source repos behind every article.

You may also like