Deploying a Percona XtraDB Cluster (PXC) with Docker and HAProxy Load Balancing

Cluster方案 (Cluster Options)

1. Replication – fast but only weak consistency, suitable for low‑value data such as logs, posts, news. Uses a master‑slave structure; writes go to master and are synced to slaves, but slaves cannot write back to master. Asynchronous replication may return success to the client before slaves are fully synced.

2. PXC (Percona XtraDB Cluster) – slower but provides strong consistency, ideal for high‑value data like orders, customers, payments. Data sync is bidirectional; any node can read/write and changes are propagated to all nodes. Synchronous replication ensures a transaction is committed on all nodes before the client receives success.

Installation of PXC Cluster

1. Pull the Docker image docker pull percona/percona-xtradb-cluster:5.7.33 2. Tag the image for brevity

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

3. Create an internal Docker network (net1) for security

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

4. Create five Docker volumes (v1‑v5) because the PXC containers cannot directly access host files

docker volume create v1<br>docker volume create v2<br>docker volume create v3<br>docker volume create v4<br>docker volume create v5<br># View volume details<br># docker inspect v1<br># Remove volume<br># docker volume rm v1

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

# Node 1<br>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<br><br># Node 2<br>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<br><br># Node 3<br>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<br><br># Node 4<br>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<br><br># Node 5<br>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 a load balancer, a single node handles all traffic, leading to high load and poor performance. HAProxy distributes requests evenly across the five nodes, reducing per‑node load and improving throughput.

1. Pull HAProxy image docker pull haproxy:2.3.13 2. Create configuration directory mkdir -p /home/apps/haproxy 3. Create haproxy.cfg with the following content

global<br>    chroot /usr/local/etc/haproxy<br>    log 127.0.0.1 local5 info<br>    daemon<br>defaults<br>    log global<br>    mode http<br>    option httplog<br>    option dontlognull<br>    timeout connect 5000<br>    timeout client 50000<br>    timeout server 50000<br># Admin stats UI<br>listen admin_stats<br>    bind 0.0.0.0:8888<br>    mode http<br>    stats uri /dbs<br>    stats realm Global\ statistics<br>    stats auth admin:123456<br># MySQL load balancing<br>listen proxy-mysql<br>    bind 0.0.0.0:3306<br>    mode tcp<br>    balance roundrobin<br>    option tcplog<br>    option mysql-check user haproxy<br>    server mysql-node1 172.18.0.2:3306 check weight 1 maxconn 2000<br>    server mysql-node2 172.18.0.3:3306 check weight 1 maxconn 2000<br>    server mysql-node3 172.18.0.4:3306 check weight 1 maxconn 2000<br>    server mysql-node4 172.18.0.5:3306 check weight 1 maxconn 2000<br>    server mysql-node5 172.18.0.6:3306 check weight 1 maxconn 2000<br>    option tcpka

4. Create a MySQL user for HAProxy health checks (no password, no privileges)

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

5. Run the HAProxy container

docker run -it -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 inside the container

# Enter container<br>docker exec -it haproxy-node1 /bin/bash<br># Launch HAProxy with the config file<br>haproxy -f /usr/local/etc/haproxy/haproxy.cfg

Access Testing

1. Admin UI: open http://<IP>:4001/dbs (user: admin, password: 123456).

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

3. Simulate node failure by stopping one or more MySQL containers; the remaining nodes and the HAProxy proxy continue to serve traffic.

Node Failure or Restart Procedures

Slave node failure : if the primary node is still alive, simply restart the stopped container; data will auto‑sync.

Primary node failure :

If the primary was the last node to leave the cluster (its data is up‑to‑date), restart it after setting safe_to_bootstrap: 1 in grastate.dat.

If other nodes are still running and the primary’s data is stale, remove the primary container, keep its volume, and re‑join it as a slave using -e CLUSTER_JOIN=other‑node.

Alternative recovery: delete all containers and the grastate.dat files in the volumes, then recreate the cluster from scratch (risk of data loss if the former primary held the latest data).

References

https://www.cnblogs.com/wanglei957/p/11819547.html

https://www.cnblogs.com/wangbiaohistory/p/14638935.html