How to Build a High‑Availability Percona XtraDB Cluster with Docker and HAProxy

1. Cluster Options

1. Replication

Fast but only weak consistency; suitable for low‑value data such as logs, posts, news.

Master‑slave structure: writes go to master and sync to slaves; reads can be from slaves, but writes to slaves are not synced back.

Asynchronous replication: master returns success before slaves finish syncing, which may cause read‑after‑write inconsistencies.

For details see the linked article.

2. Percona XtraDB Cluster (PXC)

Slower but provides strong consistency; suitable for high‑value data such as orders, customers, payments.

Bidirectional synchronization: any node’s write is replicated to all other nodes; reads and writes are possible on any node.

Synchronous replication: a write returns success only after all nodes have committed; transactions are atomic across the cluster.

Installation of PXC Cluster

1. Pull image

docker pull percona/percona-xtradb-cluster:5.7.33

2. Tag image (shorten name)

docker tag percona/percona-xtradb-cluster:5.7.33 pxc
# Remove original image
docker rmi percona/percona-xtradb-cluster:5.7.33

3. Create internal Docker network (net1)

# Create subnet
docker network create --subnet=172.18.0.0/24 net1
# Inspect network
# docker network inspect net1
# Remove network
# docker network rm net1

4. Create five Docker volumes (PXC cannot directly use host data)

docker volume create v1
docker volume create v2
docker volume create v3
docker volume create v4
docker volume create v5
# Inspect volume location
# docker inspect v1
# Remove volume
# docker volume rm v1

5. Deploy five PXC nodes (wait ~1 minute after the first node before creating the next)

# Node 1
docker run -d --name=mysql-node1 -p 3310:3306 --privileged=true -e MYSQL_ROOT_PASSWORD=123456 -e CLUSTER_NAME=PXC -e XTRABACKUP_PASSWORD=abc123456 -v v1:/var/lib/mysql --net=net1 --ip 172.18.0.2 pxc

# Node 2
docker run -d --name=mysql-node2 -p 3311:3306 --privileged=true -e MYSQL_ROOT_PASSWORD=123456 -e CLUSTER_NAME=PXC -e XTRABACKUP_PASSWORD=abc123456 -e CLUSTER_JOIN=mysql-node1 -v v2:/var/lib/mysql --net=net1 --ip 172.18.0.3 pxc

# Node 3
docker run -d --name=mysql-node3 -p 3312:3306 --privileged=true -e MYSQL_ROOT_PASSWORD=123456 -e CLUSTER_NAME=PXC -e XTRABACKUP_PASSWORD=abc123456 -e CLUSTER_JOIN=mysql-node1 -v v3:/var/lib/mysql --net=net1 --ip 172.18.0.4 pxc

# Node 4
docker run -d --name=mysql-node4 -p 3313:3306 --privileged=true -e MYSQL_ROOT_PASSWORD=123456 -e CLUSTER_NAME=PXC -e XTRABACKUP_PASSWORD=abc123456 -e CLUSTER_JOIN=mysql-node1 -v v4:/var/lib/mysql --net=net1 --ip 172.18.0.5 pxc

# Node 5
docker run -d --name=mysql-node5 -p 3314:3306 --privileged=true -e MYSQL_ROOT_PASSWORD=123456 -e CLUSTER_NAME=PXC -e XTRABACKUP_PASSWORD=abc123456 -e CLUSTER_JOIN=mysql-node1 -v v5:/var/lib/mysql --net=net1 --ip 172.18.0.6 pxc

6. Test the cluster by connecting with Navicat to any node and performing CRUD operations; changes should replicate to all other nodes.

HAProxy Load Balancing

Without load balancing, a single node handles all requests, leading to high load and poor performance.

Using HAProxy distributes requests evenly across nodes, reducing per‑node load and improving performance.

1. Pull HAProxy image docker pull haproxy:2.3.13 2. Create configuration directory mkdir -p /home/apps/haproxy 3. Create HAProxy configuration file

vim /home/apps/haproxy/haproxy.cfg
# Global settings
global
    chroot /usr/local/etc/haproxy
    log 127.0.0.1 local5 info
    daemon

defaults
    log global
    mode http
    option httplog
    option dontlognull
    timeout connect 5000
    timeout client 50000
    timeout server 50000

# Admin stats UI
listen admin_stats
    bind 0.0.0.0:8888
    mode http
    stats uri /dbs
    stats realm Global\ statistics
    stats auth admin:123456

# MySQL load balancing
listen proxy-mysql
    bind 0.0.0.0:3306
    mode tcp
    balance roundrobin
    option tcplog
    option mysql-check user haproxy
    server mysql-node1 172.18.0.2:3306 check weight 1 maxconn 2000
    server mysql-node2 172.18.0.3:3306 check weight 1 maxconn 2000
    server mysql-node3 172.18.0.4:3306 check weight 1 maxconn 2000
    server mysql-node4 172.18.0.5:3306 check weight 1 maxconn 2000
    server mysql-node5 172.18.0.6:3306 check weight 1 maxconn 2000
    option tcpka

4. Create a password‑less, no‑privilege HAProxy user inside the MySQL cluster for health checks

# Enter container
docker exec -it mysql-node1 /bin/bash
# Login MySQL
mysql -uroot -p123456
# Create user
create user 'haproxy'@'%' identified by '';

5. Deploy HAProxy container

docker run -d --name haproxy-node1 -p 4001:8888 -p 4002:3306 --restart always --privileged=true -v /home/apps/haproxy:/usr/local/etc/haproxy --net=net1 --ip 172.18.0.7 haproxy:2.3.13

6. Start HAProxy

# Enter container
docker exec -it haproxy-node1 /bin/bash
# Launch HAProxy
haproxy -f /usr/local/etc/haproxy/haproxy.cfg

Access Testing

1. Web UI: http:// ip :4001/dbs (admin / 123456)

2. Database access: use Navicat to connect to port 4002 (HAProxy forwards to the cluster).

3. Simulate node failure by stopping containers; remaining nodes and the HAProxy proxy continue to serve requests.

Node Crash or Restart

1. If a slave node crashes, simply restart it; data will auto‑synchronize as long as the primary is still alive.

2. If the primary node crashes and it was the last node in the cluster, restart it with safe_to_bootstrap: 1 set in grastate.dat:

# Edit grastate.dat
vim /var/lib/docker/volumes/v1/_data/grastate.dat
# Change safe_to_bootstrap to 1
safe_to_bootstrap: 1
# Start primary node
docker start mysql-node1

If other nodes are still running, the primary’s data may be outdated; remove the primary container and re‑join it as a slave using -e CLUSTER_JOIN=mysql-node2:

docker run -d --name=mysql-node1 -p 3310:3306 --privileged=true -e MYSQL_ROOT_PASSWORD=123456 -e CLUSTER_NAME=PXC -e XTRABACKUP_PASSWORD=abc123456 -e CLUSTER_JOIN=mysql-node2 -v v1:/var/lib/mysql --net=net1 --ip 172.18.0.2 pxc

3. Alternative recovery: delete all cluster containers and the grastate.dat files in the volumes, then recreate the cluster from scratch (risk of data loss if the former primary was not up‑to‑date).

# Remove containers
docker rm mysql-node1 mysql-node2 mysql-node3 mysql-node4 mysql-node5
# Remove grastate.dat files
rm -rf /var/lib/docker/volumes/v1/_data/grastate.dat
rm -rf /var/lib/docker/volumes/v2/_data/grastate.dat
rm -rf /var/lib/docker/volumes/v3/_data/grastate.dat
rm -rf /var/lib/docker/volumes/v4/_data/grastate.dat
rm -rf /var/lib/docker/volumes/v5/_data/grastate.dat
# Recreate containers as described above

Source: https://www.cnblogs.com/lvlinguang/p/15210200.html