1. Cluster Architecture Overview

What is Galera / PXC?

Synchronous multi-master replication for MySQL/MariaDB. Every node is a primary — reads and writes on any node. Percona XtraDB Cluster (PXC) bundles Galera with Percona Server.

Key Concepts

Concept	Description
wsrep	Write-Set Replication — the Galera replication API
SST	State Snapshot Transfer — full data copy to a joining node
IST	Incremental State Transfer — only missing writesets from GCache
GTID	Global Transaction ID — unique sequence number for each writeset
GCache	Galera Cache — ring buffer storing recent writesets for IST

Node States

SyncedDonorJoinerJoinedDesyncedInitialized

Typical Topology

Client → HAProxy/ProxySQL (R/W split)
            ├── Node 1 (Primary)   ←──wsrep──→  Node 2 (Primary)
            │                                        ↕ wsrep
            └──────────────────────────────────→  Node 3 (Primary)

All nodes are equal (multi-master), but best practice:
- Write to ONE node (avoid certification conflicts)
- Read from ALL nodes

Ports

Port	Purpose
3306	MySQL client connections
4567	Galera replication (group communication)
4568	IST (Incremental State Transfer)
4444	SST (State Snapshot Transfer)

2. Essential Status Variables — The Dashboard

Cluster State (run on every node)

SHOW STATUS LIKE 'wsrep_cluster_size';          -- Expected: 3 (or your node count)
SHOW STATUS LIKE 'wsrep_cluster_status';        -- Expected: Primary
SHOW STATUS LIKE 'wsrep_local_state_comment';   -- Expected: Synced
SHOW STATUS LIKE 'wsrep_ready';                 -- Expected: ON
SHOW STATUS LIKE 'wsrep_connected';             -- Expected: ON
SHOW STATUS LIKE 'wsrep_incoming_addresses';    -- All node IPs

Replication Health

SHOW STATUS LIKE 'wsrep_local_recv_queue_avg';  -- Should be < 0.5
SHOW STATUS LIKE 'wsrep_local_send_queue_avg';  -- Should be < 0.5
SHOW STATUS LIKE 'wsrep_flow_control_paused';   -- Should be < 0.1 (10%)
SHOW STATUS LIKE 'wsrep_flow_control_sent';     -- Should be 0 ideally
SHOW STATUS LIKE 'wsrep_cert_deps_distance';    -- Higher = more parallel apply

Performance

SHOW STATUS LIKE 'wsrep_local_cert_failures';   -- Certification conflicts
SHOW STATUS LIKE 'wsrep_local_bf_aborts';       -- Brute-force aborts (conflicts)
SHOW STATUS LIKE 'wsrep_replicated_bytes';      -- Total bytes replicated
SHOW STATUS LIKE 'wsrep_last_committed';        -- Last committed GTID seqno

One-liner Health Check

mysql -e "SHOW STATUS WHERE Variable_name IN (
  'wsrep_cluster_size','wsrep_cluster_status','wsrep_local_state_comment',
  'wsrep_ready','wsrep_connected','wsrep_flow_control_paused',
  'wsrep_local_recv_queue_avg','wsrep_local_send_queue_avg',
  'wsrep_local_cert_failures','wsrep_local_bf_aborts'
);"

Quick Health Check Table

Variable	✓ Healthy	⚠ Warning	✗ Critical
wsrep_cluster_size	= expected nodes	N-1	< quorum
wsrep_cluster_status	Primary	—	Non-Primary
wsrep_local_state_comment	Synced	Joined / Donor	Initialized / Desynced
wsrep_ready	ON	—	OFF
wsrep_flow_control_paused	< 0.01	0.01 – 0.1	> 0.1
wsrep_local_recv_queue_avg	< 0.5	0.5 – 1.0	> 1.0
wsrep_local_cert_failures	stable / low	increasing	rapid increase

3. Monitoring Intervals — When to Check What

Check	Frequency	Command / Method	Alert Threshold
Cluster size	Every 30s	wsrep_cluster_size	!= expected count
Cluster status	Every 30s	wsrep_cluster_status	!= 'Primary'
Node state	Every 30s	wsrep_local_state_comment	!= 'Synced' for > 5min
Flow control paused	Every 1min	wsrep_flow_control_paused	> 0.1
Receive queue	Every 1min	wsrep_local_recv_queue_avg	> 1.0
Send queue	Every 1min	wsrep_local_send_queue_avg	> 0.5
Certification conflicts	Every 5min	wsrep_local_cert_failures delta	> 10/min
GCache usage	Every 5min	wsrep_gcache_pool_size vs configured	> 80%
Replication lag (seqno)	Every 1min	Compare wsrep_last_committed	delta > 100
Disk space	Every 5min	df -h /var/lib/mysql	> 80%
Slow queries	Every 5min	Slow query log / PMM	> baseline
SST trigger events	On occurrence	state_comment = Joiner	Any occurrence
Backup validation	Weekly	Restore test on standby	Failure
MySQL error log	Every 1min	tail /var/log/mysqld.log	WSREP errors
ProxySQL/HAProxy health	Every 30s	Backend server status	Any node DOWN
Table fragmentation	Monthly	OPTIMIZE TABLE	> 30%
User/grants audit	Monthly	pt-show-grants	Drift from baseline
PXC version check	Monthly	SELECT @@version	Behind 2+ minor
Config drift	Monthly	diff my.cnf across nodes	Any diff
TLS expiry	Weekly	OpenSSL check	< 30 days
Purge binary logs	Daily (auto)	expire_logs_days	Disk > 80%

Prometheus Scrape Config

# prometheus.yml
scrape_configs:
  - job_name: 'mysql-pxc'
    scrape_interval: 15s       # Node-level metrics
    static_configs:
      - targets: ['node1:9104', 'node2:9104', 'node3:9104']

  - job_name: 'pxc-custom'
    scrape_interval: 30s       # wsrep status variables
    metrics_path: /probe

Recommended Grafana Alerts

🚨 Critical (page immediately)

wsrep_cluster_status != Primary
wsrep_cluster_size < quorum (< 2 for 3-node)
wsrep_ready = OFF
wsrep_connected = OFF
Disk usage > 90%

⚠️ Warning (Slack/email)

wsrep_flow_control_paused > 0.05 for 5 min
wsrep_local_recv_queue_avg > 1.0 for 5 min
wsrep_local_cert_failures increasing > 10/min
wsrep_local_state_comment != Synced for > 2 min
Disk usage > 80%
Node count = expected − 1

i️ Info (daily digest)

SST events occurred
Flow control events > 0
Large transactions replicated (> 1GB)

4. Node Lifecycle Operations

Starting the Cluster (Bootstrap)

# FIRST NODE ONLY — bootstrap new cluster
# PXC 8.0:
systemctl start mysql@bootstrap
# Or:
mysqld --wsrep-new-cluster

# Verify bootstrap:
mysql -e "SHOW STATUS LIKE 'wsrep_cluster_size';"  -- Should be 1
mysql -e "SHOW STATUS LIKE 'wsrep_cluster_status';" -- Should be Primary

# SUBSEQUENT NODES — join existing cluster
systemctl start mysql
# Node will automatically find cluster via wsrep_cluster_address
# Watch for IST vs SST in logs

Safe Rolling Restart (one node at a time)

# 1. Check cluster health first
mysql -e "SHOW STATUS LIKE 'wsrep_cluster_size';"

# 2. Desync node (optional, prevents donor selection)
mysql -e "SET GLOBAL wsrep_desync=ON;"

# 3. Stop gracefully
systemctl stop mysql

# 4. Perform maintenance (config change, upgrade, etc.)

# 5. Start node
systemctl start mysql

# 6. Wait for sync
watch -n1 "mysql -e \"SHOW STATUS LIKE 'wsrep_local_state_comment';\""

# 7. Re-enable sync
mysql -e "SET GLOBAL wsrep_desync=OFF;"

# 8. Verify
mysql -e "SHOW STATUS LIKE 'wsrep_cluster_size';"

# 9. Repeat for next node (NEVER restart 2 nodes simultaneously!)

⚠️ Graceful Shutdown of Entire Cluster

Shut down nodes one by one. The LAST node stopped = highest committed seqno.

# On each node (one at a time):
systemctl stop mysql

# Find the most advanced node for next bootstrap:
cat /var/lib/mysql/grastate.dat
# Look for: seqno: <highest number>
# Node with highest seqno + safe_to_bootstrap: 1 → bootstrap this one first

5. SST vs IST — State Transfer

IST (Incremental State Transfer)

Uses GCache (donor sends only missing writesets)
Fast, non-blocking (donor stays operational)
Happens when: node was briefly offline, GCache has needed transactions
Monitor: Joined → Synced

SST (State Snapshot Transfer)

Full data copy from donor to joiner
Methods: xtrabackup (recommended), rsync, mysqldump
SLOW, donor may be impacted (desync)
Happens when: new node, GCache insufficient, large gap
Monitor: Joiner (for long time)

GCache Sizing (determines IST window)

# my.cnf — size GCache to cover your maintenance window
[mysqld]
wsrep_provider_options="gcache.size=2G; gcache.recover=yes"

# Rule of thumb:
# GCache should hold enough writesets for your longest expected downtime
# 1GB GCache ≈ covers ~30min downtime (depends on write volume)
# Check: SHOW STATUS LIKE 'wsrep_replicated_bytes'; (bytes/hour)
# GCache size > (bytes_per_hour × max_expected_downtime_hours)

Forcing IST over SST

# Check if IST is possible before restarting a node:
# On donor:
mysql -e "SHOW STATUS LIKE 'wsrep_last_committed';"     # e.g., 150000
# On joiner (from grastate.dat): seqno: 149500
# Diff: 500 writesets needed
# If GCache has 500+ writesets available → IST will happen

# If GCache is too small → SST will trigger
# To force IST: increase gcache.size and restart donor first

6. Backup & Recovery

Backup Strategy (xtrabackup)

# xtrabackup (recommended for PXC) — run on ONE node, desync first
mysql -e "SET GLOBAL wsrep_desync=ON;"
xtrabackup --backup --target-dir=/backup/$(date +%F) \
  --user=backup --password=xxx \
  --galera-info  # saves GTID position

mysql -e "SET GLOBAL wsrep_desync=OFF;"

# Verify backup
xtrabackup --prepare --target-dir=/backup/$(date +%F)

# Backup schedule:
# Full backup:   Weekly (Sunday 02:00)
# Incremental:   Daily (02:00)
# Binlog backup: Continuous (every 5 min)
# Retention:     30 days full, 7 days incremental

Recovery Scenarios

Scenario 1: Single node crashed

systemctl start mysql
# IST will resync if GCache sufficient, else SST

Scenario 2: Two nodes down

# Start the node with HIGHEST seqno first
cat /var/lib/mysql/grastate.dat  # check seqno on each

Scenario 3: ALL nodes down (full cluster crash)

# Find safest node:
cat /var/lib/mysql/grastate.dat   # on each node
# Look for safe_to_bootstrap: 1 OR highest seqno
# If all show seqno: -1 → find via:
mysqld --wsrep-recover 2>&1 | grep "Recovered position"

# Bootstrap from safest node:
# Edit grastate.dat → safe_to_bootstrap: 1
systemctl start mysql@bootstrap
# Then start other nodes normally

⚠️ Scenario 4: Split brain (DANGER!)

# wsrep_cluster_status = Non-Primary on some nodes
# DON'T just restart — identify which partition has correct data
mysql -e "SET GLOBAL wsrep_provider_options='pc.bootstrap=YES';"
# Only on the partition you want to keep!

7. ProxySQL / HAProxy Configuration

ProxySQL (recommended for PXC)

-- Add PXC nodes to ProxySQL
INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight) VALUES
  (10, 'node1', 3306, 1000),  -- writer hostgroup
  (10, 'node2', 3306, 1000),
  (10, 'node3', 3306, 1000),
  (20, 'node1', 3306, 1000),  -- reader hostgroup
  (20, 'node2', 3306, 1000),
  (20, 'node3', 3306, 1000);

-- Galera health check
INSERT INTO mysql_galera_hostgroups
  (writer_hostgroup, backup_writer_hostgroup, reader_hostgroup,
   offline_hostgroup, active, max_writers, writer_is_also_reader)
VALUES (10, 30, 20, 40, 1, 1, 1);

-- Query rules for R/W split
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup)
VALUES (1, 1, '^SELECT.*FOR UPDATE', 10),    -- writes
       (2, 1, '^SELECT', 20);                 -- reads

LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

HAProxy (simpler alternative)

# /etc/haproxy/haproxy.cfg
frontend mysql_frontend
    bind *:3306
    default_backend mysql_pxc

backend mysql_pxc
    mode tcp
    balance leastconn
    option mysql-check user haproxy_check

    # Single-writer setup (recommended)
    server node1 10.0.0.1:3306 check weight 1000
    server node2 10.0.0.2:3306 check weight 1000 backup
    server node3 10.0.0.3:3306 check weight 1000 backup

    # Or: clustercheck script (port 9200)
    option httpchk GET /
    server node1 10.0.0.1:3306 check port 9200 inter 5s
    server node2 10.0.0.2:3306 check port 9200 inter 5s
    server node3 10.0.0.3:3306 check port 9200 inter 5s

Clustercheck Script for HAProxy

#!/bin/bash
# /usr/local/bin/clustercheck
MYSQL_USER="clustercheck"
MYSQL_PASS="xxx"

READY=$(mysql -u$MYSQL_USER -p$MYSQL_PASS -Nse \
  "SHOW STATUS LIKE 'wsrep_ready'" 2>/dev/null | awk '{print $2}')
STATE=$(mysql -u$MYSQL_USER -p$MYSQL_PASS -Nse \
  "SHOW STATUS LIKE 'wsrep_local_state_comment'" 2>/dev/null | awk '{print $2}')

if [ "$READY" = "ON" ] && [ "$STATE" = "Synced" ]; then
    echo -e "HTTP/1.1 200 OK\r\n\r\nNode synced."
else
    echo -e "HTTP/1.1 503 Service Unavailable\r\n\r\nNode not ready."
fi

8. Performance Tuning

PXC-specific my.cnf

[mysqld]
# Galera settings
wsrep_slave_threads = 4                    # Parallel apply threads (= CPU cores / 2)
wsrep_cert_deps_distance = 50             # Target: higher = more parallelism
wsrep_provider_options = "
  gcache.size=2G;                          # IST window (see Section 5)
  gcache.recover=yes;                      # Recover GCache after crash
  gcs.fc_limit=256;                        # Flow control trigger (default 16)
  gcs.fc_factor=0.8;                       # Resume replication at 80% of fc_limit
  evs.send_window=256;                     # Send window (increase for WAN)
  evs.user_send_window=128;                # User send window
  gcs.max_packet_size=131072;              # Max packet 128K
  cert.log_conflicts=yes;                  # Log certification conflicts
"

# InnoDB tuning for Galera
innodb_buffer_pool_size = 70% of RAM       # Standard MySQL tuning
innodb_log_file_size = 1G                  # Larger = fewer checkpoints
innodb_flush_log_at_trx_commit = 0         # Safe with Galera (wsrep handles durability)
innodb_autoinc_lock_mode = 2               # REQUIRED for Galera (interleaved)
innodb_doublewrite = 1                     # Keep ON

# Binary log (needed for SST with xtrabackup)
log_bin = mysql-bin
binlog_format = ROW                        # REQUIRED for Galera
binlog_row_image = FULL

# Timeouts
wsrep_sync_wait = 0                        # 0 for performance, per-session for reads-after-writes
pxc_strict_mode = ENFORCING                # Catch unsupported operations

Flow Control Tuning

-- Diagnose flow control
SHOW STATUS LIKE 'wsrep_flow_control_paused';     -- % time paused
SHOW STATUS LIKE 'wsrep_flow_control_sent';        -- FC messages sent BY THIS NODE
SHOW STATUS LIKE 'wsrep_local_recv_queue_avg';     -- Avg receive queue

-- If flow_control_paused > 0.1:
-- 1. Increase wsrep_slave_threads (more parallel apply)
-- 2. Increase gcs.fc_limit (higher threshold before FC kicks in)
-- 3. Check slow node (disk I/O, CPU)
-- 4. Reduce write load on cluster
-- 5. Check for large transactions (avoid bulk INSERT/UPDATE/DELETE)

9. Common Issues & Troubleshooting

Problem	Symptoms	Cause	Fix
Split brain	cluster_status = Non-Primary	Network partition	Bootstrap with pc.bootstrap=YES
Node won't join	SST fails, timeout in logs	Firewall blocking 4444/4567/4568	Open ports, check iptables
High flow control	flow_control_paused > 0.1	Slow node, large txns	Increase wsrep_slave_threads
Cert conflicts	cert_failures increasing	Multi-master same-row writes	Write to single node
SST instead of IST	Full transfer on rejoin	GCache too small	Increase gcache.size
Stuck in "Joining"	Never reaches "Synced"	SST donor overloaded	Desync donor, check bandwidth
Deadlock errors	Error 1213	Galera cert vs local lock	App retry logic, smaller txns
Auto-increment gaps	IDs have gaps	auto_increment_increment	Expected behavior, not a bug
WSREP not prepared	Queries fail after start	Node not yet synced	Wait for wsrep_ready = ON
Donor OOM during SST	Joiner fails	OOM during xtrabackup	Increase memory, pick robust donor

Log Locations

# MySQL error log
/var/log/mysqld.log
/var/log/mysql/error.log

# Galera-specific messages (grep for):
grep -i "wsrep\|galera\|sst\|ist\|flow.control\|cert" /var/log/mysqld.log

# SST log
/var/lib/mysql/innobackup.backup.log      # xtrabackup SST
/var/lib/mysql/rsync_sst.log               # rsync SST

10. Maintenance Calendar Template

📅 Daily

☐ Check cluster size and node states (automated monitoring)
☐ Review MySQL error logs for WSREP warnings
☐ Verify backup completion
☐ Check disk space on all nodes

📅 Weekly

☐ Validate backup by test restore
☐ Review slow query log trends
☐ Check replication lag trends (flow control paused %)
☐ Review ProxySQL/HAProxy stats
☐ Check TLS certificate expiry

📅 Monthly

☐ PXC version check (security patches)
☐ Config drift check across nodes (diff my.cnf)
☐ Review and optimize GCache size based on actual write volume
☐ Table maintenance: ANALYZE TABLE, check fragmentation
☐ User/grants audit
☐ Capacity planning: disk growth trend, connection count trend
☐ Test failover procedure (controlled single-node restart)

📅 Quarterly

☐ Full disaster recovery drill (restore from backup, bootstrap fresh cluster)
☐ Security audit (users, grants, network, encryption)
☐ Performance baseline update (compare to previous quarter)
☐ PXC major version upgrade planning
☐ Review and update runbooks

📅 Annually

☐ Major version upgrade execution (during maintenance window)
☐ Hardware refresh assessment
☐ Capacity planning for next year
☐ Compliance audit (if applicable)