Deploy Percona XtraDB Cluster (PXC) on Docker Swarm with Load Balancing

1. Explanation

PXC version: 5.7.43

Three machines: 192.168.50.9, 192.168.50.18, 192.168.50.245

Docker installed on all hosts

Increase file descriptor limit:

ulimit -HSn 65535

2. Basic Preparation

2.1 Change Hostnames

Set a unique hostname on each machine for easier management.

# View hostname
hostname
# Set hostname
hostnamectl set-hostname xxx

2.2 Import Images

If the environment is offline, load the pre‑packed tar files; otherwise pull from Docker Hub.

cd /home/HA/pxc && docker load -i swarm.tar && docker load -i percona-xtradb-cluster.tar
# Or pull directly
docker pull percona/percona-xtradb-cluster:5.7.43
docker pull swarm

2.3 Create Swarm Cluster

Initialize Swarm on the first node and join the other two nodes.

docker swarm init

If you encounter errors, run docker swarm leave on the problematic node before re‑joining.

Verify nodes with:

docker node ls

2.4 Create Overlay Network

docker network create -d overlay --attachable mysql_network

3. PXC Installation

3.1 Configuration Files

Copy the MySQL configuration to each host:

mkdir -p /home/mysql/config && cp /home/HA/pxc/my.cnf /home/mysql/config/

Key my.cnf settings (excerpt):

[mysqld]
skip_ssl
federated
skip-name-resolve
default-time-zone=+08:00
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
log_slave_updates
wsrep_provider_options='repl.commit_order =3;gcache.size=4096M'
wsrep_log_conflicts
server-id=1   # change per node
...

Important fixes for binlog sync issues: enable log_slave_updates, set wsrep_provider_options, and add wsrep_log_conflicts. Ensure each node has a unique server-id.

3.2 Deploy Nodes

On the init node (192.168.50.9):

docker volume create mysql_data
docker run -d -p 3306:3306 \
  -e MYSQL_ROOT_PASSWORD=3er4#ER$ \
  -e CLUSTER_NAME=PXC1 -e XTRABACKUP_PASSWORD=3er4#ER$ \
  -v mysql_data:/var/lib/mysql \
  -v /etc/localtime:/etc/localtime \
  -v /home/mysql/config:/etc/percona-xtradb-cluster.conf.d \
  --privileged --name=pn1 --net=mysql_network \
  percona/percona-xtradb-cluster:5.7.43

On the other two nodes (192.168.50.18 and .245), add CLUSTER_JOIN=pn1 and use distinct container names (pn2, pn3).

docker volume create mysql_data
docker run -d -p 3306:3306 \
  -e MYSQL_ROOT_PASSWORD=3er4#ER$ \
  -e CLUSTER_NAME=PXC1 -e XTRABACKUP_PASSWORD=3er4#ER$ \
  -e CLUSTER_JOIN=pn1 \
  -v mysql_data:/var/lib/mysql \
  -v /home/mysql/config:/etc/percona-xtradb-cluster.conf.d \
  --privileged --name=pn2 --net=mysql_network \
  percona/percona-xtradb-cluster:5.7.43
# repeat for pn3

Check cluster status from any MySQL client:

show status like '%wsrep_incoming_addresses%';

3.3 Common Failure Handling

Non‑master node crash : simply restart the container.

Master node crash : stop another node, locate the data directory, edit grastate.dat to set safe_to_bootstrap=1, remove the failed container, then re‑initialize the master and let other nodes join.

4. Load Balancing the PXC Cluster

4.1 Nginx (not recommended)

Basic stream configuration example:

stream {
    upstream mysql23306 {
        hash $remote_addr consistent;
        server 192.168.50.18:3306 max_fails=5 fail_timeout=30s;
        server 192.168.50.9:3306 max_fails=5 fail_timeout=30s;
        server 192.168.50.245:3306 max_fails=5 fail_timeout=30s;
    }
    server {
        listen 23306;
        proxy_connect_timeout 60;
        proxy_timeout 300s;
        proxy_pass mysql23306;
    }
}

4.2 HAProxy (preferred)

Create a MySQL heartbeat user and configure haproxy.cfg with TCP health checks and round‑robin balancing.

# Create heartbeat user
use mysql;
create user 'haproxy'@'%' identified by '';
flush privileges;

# Sample HAProxy config (excerpt)
global
    chroot /usr/local/etc/haproxy
    log 127.0.0.1 local5 info
    daemon
    maxconn 10000

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

listen proxy-mysql
    bind 0.0.0.0:3306
    balance roundrobin
    option tcplog
    option mysql-check user haproxy
    server MySQL_1 10.0.1.2:3306 check weight 1 maxconn 2000
    server MySQL_2 10.0.1.4:3306 check weight 1 maxconn 2000
    server MySQL_3 10.0.1.6:3306 check weight 1 maxconn 2000

Run HAProxy in a Docker container attached to the overlay network:

docker run -d -p 18888:8888 -p 13306:3306 \
  -v /home/haproxy:/usr/local/etc/haproxy \
  --name h1 --privileged --net=mysql_network haproxy:2.8

Note: IP changes after container restarts can be mitigated by assigning static IPs or using host IPs.