DEV Community

Cover image for MariaDB Galera Cluster & ProxySQL Read/Write Splitting for Zero-Downtime SQL Scaling
Amir Mehmood
Amir Mehmood

Posted on Originally published at hostingpodium.com

MariaDB Galera Cluster & ProxySQL Read/Write Splitting for Zero-Downtime SQL Scaling

#linux #database #sre #devops

Originally published on the HostingPodium Enterprise Engineering Blog.

Free SRE & Developer Diagnostics: Test your stack live with our Global DNS Propagation Checker & SSL Health Auditor, Free Website Speed & Core Web Vitals Checker, or explore all 9 Free Webmaster & Cloud Engineering Tools.

Comprehensive enterprise systems guide on MariaDB Galera Cluster & ProxySQL Read/Write Splitting for Zero-Downtime SQL Scaling, covering high-availability architecture, security protocols, and production SLAs.

Scope and assumptions

This guide targets production environments that require continuous availability while scaling write traffic across multiple data centers. It assumes Ubuntu 22.04 LTS or RHEL-compatible distributions, MariaDB 10.11, ProxySQL 2.5, and an orchestrator capable of handling systemd services (e.g., Kubernetes, Nomad, or plain VMs). All commands are shown for a Debian-based host; replace apt with yum/dnf where appropriate.

Architecture overview

The topology consists of a three-node Galera cluster providing synchronous replication, a set of ProxySQL instances acting as TCP load balancers, and an optional health-check sidecar that reports node state to a monitoring stack.

Component Count Primary role
MariaDB Galera nodes 3-5 Synchronous write quorum
ProxySQL instances 2-n Read/write routing, failover
Monitoring agents 1 per host Metrics, health probes

Prerequisites

  • Network latency < 2 ms between Galera nodes (ideally < 1 ms).
  • Dedicated VLAN for database traffic to avoid cross-traffic jitter.
  • System limits: net.core.somaxconn=65535, fs.file-max=200000, ulimit -n 65535 on each host.
  • Time sync via chrony or ntpd with maxdelay=100ms.

Provisioning the Galera cluster

Package installation 

sudo apt-get update && sudo apt-get install -y mariadb-server galera-4 rsync
Enter fullscreen mode Exit fullscreen mode

Configuration file

All nodes share /etc/mysql/mariadb.conf.d/60-galera.cnf. Adjust the wsrep_cluster_address to list every node.

[mysqld]
bind-address=0.0.0.0
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
innodb_flush_log_at_trx_commit=2
max_connections=1000
query_cache_type=0
skip-name-resolve

# Galera settings
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_name="prod-galera"
wsrep_cluster_address="gcomm://10.0.0.11,10.0.0.12,10.0.0.13"
wsrep_node_address="{{ ansible_host }}"
wsrep_node_name="{{ inventory_hostname }}"
wsrep_sst_method=rsync
wsrep_sst_auth="sst_user:sst_password"
Enter fullscreen mode Exit fullscreen mode

Bootstrap sequence

On the first node execute:

sudo systemctl start mariadb@bootstrap.service
Enter fullscreen mode Exit fullscreen mode

Subsequent nodes start normally:

sudo systemctl start mariadb
Enter fullscreen mode Exit fullscreen mode

Validate quorum with:

mysql -e "SHOW STATUS LIKE 'wsrep_cluster_size';"
Enter fullscreen mode Exit fullscreen mode

Output must equal the number of nodes.

Deploying ProxySQL

Installation 

wget https://repo.proxysql.com/ProxySQL/proxysql-2.5.4-ubuntu22_amd64.deb
sudo dpkg -i proxysql-2.5.4-ubuntu22_amd64.deb
sudo systemctl enable proxysql && sudo systemctl start proxysql
Enter fullscreen mode Exit fullscreen mode

Initial configuration

Connect to the admin interface on port 6032 and define two backend groups: one for writes (the whole Galera set) and one for reads (the same set, but with weight tuned for replica preference).

mysql -h 127.0.0.1 -P 6032 -u admin -padmin

INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight) VALUES
 (10, '10.0.0.11', 3306, 1),
 (10, '10.0.0.12', 3306, 1),
 (10, '10.0.0.13', 3306, 1);

INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight) VALUES
 (20, '10.0.0.11', 3306, 1),
 (20, '10.0.0.12', 3306, 1),
 (20, '10.0.0.13', 3306, 1);

LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
Enter fullscreen mode Exit fullscreen mode

Read/write routing rules

ProxySQL uses query-based rules. The following rule sends any transaction-starting statement to hostgroup 10 (writes) and all others to hostgroup 20 (reads).

INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (1, 1, '^\s*START\s+TRANSACTION|INSERT|UPDATE|DELETE|REPLACE|ALTER|CREATE|DROP|TRUNCATE', 10, 1);

INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (2, 1, '.*', 20, 1);

LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;
Enter fullscreen mode Exit fullscreen mode

Zero-downtime write scaling

Adding a new Galera node does not interrupt existing traffic. Follow the steps below.

Step-by-step node addition

  • Provision a fresh VM with identical kernel parameters.
  • Install MariaDB and copy the shared 60-galera.cnf file.
  • Set wsrep_cluster_address="gcomm://10.0.0.11,10.0.0.12,10.0.0.13,{{ new_ip }}".
  • Start the service: sudo systemctl start mariadb. The node performs an SST (State Snapshot Transfer) from the designated donor.
  • Verify wsrep_cluster_size reflects the new count.
  • Update ProxySQL mysql_servers table with the new host and reload runtime.

Node removal without outage

To retire a node, first drain it from ProxySQL, then stop MariaDB, and finally remove it from the Galera configuration.

# Drain from ProxySQL
DELETE FROM mysql_servers WHERE hostname='10.0.0.12' AND hostgroup_id=10;
DELETE FROM mysql_servers WHERE hostname='10.0.0.12' AND hostgroup_id=20;
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

# Stop MariaDB gracefully
sudo systemctl stop mariadb

# Remove from wsrep_cluster_address on remaining nodes
# (edit the file and restart the service)
Enter fullscreen mode Exit fullscreen mode

Operational runbook

Daily health check

  • Run mysql -e "SHOW STATUS LIKE 'wsrep_local_state_comment';" on each node; result must be Synced.
  • Confirm ProxySQL SELECT * FROM stats_mysql_connection_pool; shows ConnUsed < 80 % of ConnUsed limit.
  • Check proxysql-admin --check for rule consistency.

Failover scenario

If a write node disappears, Galera automatically re-elects a primary. ProxySQL continues routing writes to the remaining nodes because the mysql_servers table still lists them. However, to avoid split-brain, enforce wsrep_sst_donor to a healthy node.

SET GLOBAL wsrep_sst_donor='10.0.0.13';
Enter fullscreen mode Exit fullscreen mode

After the failed node recovers, re-add it to the cluster and ProxySQL as described in the addition steps.

Performance tuning checklist

  • Set innodb_flush_log_at_trx_commit=2 for a balance between durability and latency.
  • Adjust wsrep_slave_threads to the number of CPU cores (e.g., 8).
  • Configure max_allowed_packet=64M to accommodate bulk inserts.
  • On ProxySQL, raise mysql-max_connections to 2000 and mysql-connect_timeout_server to 5000 ms.

Observability and alerting

Export metrics via Prometheus exporters:

  • MariaDB: mysqld_exporter exposing galera_cluster_status, wsrep_local_state, and wsrep_flow_control_paused.
  • ProxySQL: proxysql_exporter exposing proxysql_backend_bytes_sent, proxysql_frontend_connections, and proxysql_query_rules_hits.

Typical alerts:

# Alert if any node falls out of sync
- alert: GaleraNodeDesync
 expr: mysql_global_status_wsrep_local_state{state!="Synced"}
 for: 2m
 labels:
 severity: critical
 annotations:
 summary: "Galera node {{ $labels.instance }} not synced"

# Alert if write latency exceeds 30 ms
- alert: ProxySQLWriteLatency
 expr: rate(proxysql_backend_latency_seconds_sum{hostgroup="10"}[1m]) / rate(proxysql_backend_latency_seconds_count{hostgroup="10"}[1m]) > 0.03
 for: 5m
 labels:
 severity: warning
Enter fullscreen mode Exit fullscreen mode

Security hardening

  • Enable ssl=ON in MariaDB and generate per-node certificates. Add require_secure_transport=ON to the config.
  • Configure ProxySQL to terminate TLS on the client side and forward traffic over the internal VLAN using mysql_ssl_cert and mysql_ssl_key.
  • Restrict admin access to 127.0.0.1:6032 with firewall rules (e.g., ufw allow from 10.0.0.0/24 to any port 6032).
  • Audit SST transfers: use wsrep_sst_method=xtrabackup-v2 with encrypted streams.

Comparison with alternative patterns

Traditional master-slave replication separates reads and writes at the application layer, requiring manual failover and risking data loss during primary outage. The Galera-ProxySQL stack offers synchronous writes, automatic primary election, and programmable routing without code changes. Compared to MySQL Group Replication, Galera provides lower write latency on LAN environments but lacks built-in multi-primary conflict resolution beyond last-write-wins. For workloads that can tolerate eventual consistency, a sharded Vitess deployment may achieve higher throughput, yet it introduces operational complexity not present in a three-node Galera cluster.

Production Verification Checklist & Operational Runbook: MariaDB Galera Cluster & ProxySQL Read/Write Splitting for Zero-Downtime SQL Scaling

Before promoting any MariaDB Galera Cluster & ProxySQL Read/Write Splitting for Zero-Downtime SQL Scaling deployment into production traffic, senior platform and SRE teams validate deterministic failure handling, kernel resource ceilings, and observability telemetry across Prometheus, Grafana Enterprise, OpenTelemetry, Alertmanager. Unlike lab prototypes, production clusters must survive upstream network partitions, sudden concurrency bursts, and rolling node maintenance without dropping in-flight client transactions.

Pre-Cutover Acceptance Gates 

- **Synthetic Load & Tail-Latency Benchmarking:** Execute sustained concurrency tests against staging endpoints for MariaDB Galera Cluster & ProxySQL Read/Write Splitting for Zero-Downtime SQL Scaling, verifying that p95 and p99 response latencies remain within contractual SLO boundaries under 3x peak projected load.

- **Fault Injection & Chaos Verification:** Simulate primary worker termination, AZ link degradation, and storage IOPS saturation to confirm automated failover and circuit-breaker recovery complete within sub-second thresholds.

- **Telemetry & Alert Routing Validation:** Confirm Prometheus scrape targets, structured JSON log pipelines, and OpenTelemetry trace propagation emit actionable metrics for mariadb-galera-cluster-proxysql-read-write-splitting before live traffic cutover.

- **Immutable Rollback Readiness:** Verify that declarative [GitOps](https://hostingpodium.com/our-services/devops-ci-cd/) manifests, database migration snapshots, and previous container image digests can restore the prior production state in under 120 seconds.
Enter fullscreen mode Exit fullscreen mode 
# Production Readiness Verification Probe (mariadb-galera-cluster-proxysql-read-write-splitting)
systemctl is-active --quiet prometheus-node-exporter && echo "TELEMETRY_OK"
sysctl net.core.somaxconn net.ipv4.tcp_tw_reuse vm.swappiness
curl -fsSL -o /dev/null -w "http_code=%{http_code} ttfb=%{time_starttransfer}s
" http://127.0.0.1:8080/healthz
Enter fullscreen mode Exit fullscreen mode

By enforcing these pre-cutover verification gates alongside 15-minute guaranteed human engineer response for critical P1 incidents with 99.99% platform availability., engineering teams ensure MariaDB Galera Cluster & ProxySQL Read/Write Splitting for Zero-Downtime SQL Scaling operates with predictable throughput, cryptographic isolation, and continuous audit compliance across SOC2 audit reporting, continuous configuration drift detection, and daily immutable offsite snapshots..

Free Webmaster, SRE & Cloud Diagnostic Tools (No Signup Required)

Verify and benchmark your production infrastructure directly from your browser using the HostingPodium Free Webmaster & Developer Suite:

Free Engineering Utility Primary Diagnostic Use Case
Global DNS Propagation & SSL Checker Verify live A, AAAA, CNAME, MX, TXT, NS & CAA records across 21+ global resolvers + TLS certificate audits
Website Speed Test & Core Web Vitals Auditor Measure server TTFB, LCP, INP, CLS, and HTTP/2 compression latency in real time
Rich Snippets & Schema.org JSON-LD Generator Generate & validate structured JSON-LD markup for TechArticles, FAQs, Products & Organizations
HTTP Redirect Chain & Status Code Tracer Trace 301/302 redirect hops, inspect response headers, and verify load balancer health checks
DMARC, SPF & DKIM Email Security Validator Audit domain email authentication policies and generate compliant DMARC records
Google SERP Simulator & Meta Tag Previewer Preview desktop/mobile Google snippets, OpenGraph cards, and pixel-width truncation
Free WebP Image Converter & Optimizer Client-side PNG/JPG to WebP compression for faster Core Web Vitals LCP

Related Enterprise Architecture & Benchmarks

Top comments (0)