Scale Out MariaDB from One Docker Node: Replication, ProxySQL, MaxScale, Galera & Keepalived HA

Published September 17, 2024

You already run MariaDB as a single Docker container. Scale-out means adding database capacity and availability with more nodes and traffic routers — not hoping one bigger my.cnf will save you forever.

This guide is written as an operations playbook: starting from one container, then covering the tools people actually combine in production.

Map of the toolbox (what each thing is for):
Async replication — copy data to more MariaDB nodes (mostly for reads / standby).
Galera — multi-primary almost-sync cluster (same dataset on every member).
ProxySQL — MySQL-protocol proxy: pooling, read/write split, query rules.
MaxScale — MariaDB’s proxy/router with monitors and failover helpers.
Keepalived — floating VIP (VRRP) so apps keep one IP when a proxy host dies.

How to read this guide (start here)

Every code block has a colored label. Do only what the label says.

Read Diagram, formula, or explanation — do not paste into a terminal.
Copy file Create this file on disk (path is in the label or first comment). Edit values for your environment.
Run Paste into a shell on the machine this section describes.
SQL Paste into a database client (mariadb / mysql / ProxySQL admin), not bash.

Follow parts in order unless a part says you can skip it. Change example IPs, passwords, and paths to match your lab.

Suggested path: Part 1–2 (choose topology) → Part 4 replication lab → proxy (6–7) → Galera/Keepalived as needed.

Part 1 — Mental model: capacity vs availability

What this part is for: understand: Mental model: capacity vs availability.
What you should do: read this section unless a labeled block says otherwise.

Before Compose files and VIP scripts, name the bottleneck you are solving. Different tools solve different walls:

Problem you measured	Wrong reflex	Right class of tool
CPU/IO saturated by SELECTs; writes still fine	“Add Galera”	Async replicas + ProxySQL/MaxScale
Primary host death causes long outage	“Add more app pods”	Replicas + Keepalived VIP + proxy failover / Galera
Single primary write QPS / dataset size is the ceiling	“One more powerful replica”	App-level split / multiple databases — not “one more slave”

Hard truth: async replicas do not multiply write throughput. Galera multiplies synchronized writers but each commit is heavier and needs a fast LAN. Pick the tool that matches the bottleneck you measured.

This article is ordered as a production ladder: start with one Docker MariaDB, add async replicas, put a proxy in front, float a VIP with Keepalived on the proxy tier, and adopt Galera only when multi-primary sync on a fast LAN is truly required.

Part 2 — Choose a topology before you touch Compose

What this part is for: cover: Choose a topology before you touch Compose.
What you should do: read the explanation, then do only blocks labeled COPY / RUN / SQL.

Starting need	Recommended first build
One Docker MariaDB, need more reads	Primary + 1–N async replicas → ProxySQL or MaxScale
Need a stable app endpoint when a host dies	Two proxy hosts + Keepalived VIP in front of DB topology
Need multi-writer / automatic member sync in one DC	3-node Galera + MaxScale/ProxySQL + Keepalived
Single primary write limit / huge multi-tenant growth	Deliberate sharding / external write-scale tooling — only after ops maturity

Practical progression most mid-level teams survive:
1) Async replica on second host
2) ProxySQL or MaxScale
3) Keepalived VIP on the proxy tier
4) Galera only if requirements demand it
5) Sharding only when write/shard economics force it (outside this guide’s scope)

Part 3 — Network budgets (ms), ports, MTU, firewall

What this part is for: cover: Network budgets (ms), ports, MTU, firewall.
What you should do: read the explanation, then do only blocks labeled COPY / RUN / SQL.

Latency budgets that actually work

Path	Ideal RTT	Usable	Avoid for live traffic
Async replica (same DC)	< 5 ms	5–40 ms	> 80–100 ms for user-facing reads
Galera members	< 1–2 ms	< 5 ms same DC	> 10–15 ms (flow control / commit pain)
App → ProxySQL/MaxScale	< 1 ms (same host/AZ)	< 5 ms	Proxy in another region “for convenience”
Keepalived VRRP peers	same L2/L3 segment	same VLAN/VPC	VRRP across broken multicast/unicast path

RUN — paste into a shell on the host this section describes.

# Measure before you promise SLA
ping -c 30 10.10.0.5
mtr -rwzc 30 10.10.0.5    # if installed
iperf3 -c 10.10.0.5       # bandwidth during planning
nc -vz 10.10.0.5 3306

Async lag intuition:
lag ≈ network_transfer_delay + SQL_apply_time + queueing

If binlog generation > link throughput, lag grows even at 2 ms RTT.
Galera commit latency rises roughly with group RTT and conflict rate.

Ports

Port	Service	Notes
3306/tcp	MariaDB + async replication	Lock to private subnets
4567/tcp + 4567/udp	Galera group comm	All members ↔ all members
4568/tcp	Galera IST	Donor ↔ joiner
4444/tcp	Galera SST	Can saturate NIC during join
6033/tcp	ProxySQL MySQL	Apps connect here
6032/tcp	ProxySQL admin	Never public
4006/tcp (typical)	MaxScale listener	Configurable
8989/tcp (example)	MaxScale REST/admin	Never public

Field network traps

Docker bridge IPs are not multi-host addresses. Remote replicas must use host private IP, VIP, or DNS — not 172.18.0.x from another machine.
bind-address=127.0.0.1 on primary silently blocks remote replicas.
MTU on WireGuard/OpenVPN: set ~1280–1420 or large SST/binlog bursts stall with “random” timeouts.
Connection tracking / asymmetric routing: stateful firewalls between replicas and primary drop long-lived replication threads.
NTP/chrony: clock skew confuses failover tooling and human timelines in logs.
TLS on untrusted links: replication uses the MySQL client protocol — enable require_secure_transport / certificates when leaving a trusted VPC.

Firewall by OS (replication + proxy ports)

Open only what the topology needs. Example: primary 10.10.0.5, replicas 10.10.0.6–7, proxy 10.10.0.10, app subnet 10.10.0.0/24.

RUN — use ONLY the section for your OS; skip the rest.

# MariaDB async: 3306/tcp from replicas + proxy monitor hosts to primary
# ProxySQL: 6033/tcp from app subnet; 6032/tcp localhost only
# MaxScale: 4006/tcp from app subnet; admin 8989 localhost only

# --- RHEL / Rocky / Alma: firewalld (on primary 10.10.0.5) ---
firewall-cmd --permanent --add-rich-rule='rule family=ipv4 source address=10.10.0.0/24 port port=3306 protocol=tcp accept'
firewall-cmd --reload

# --- Debian / Ubuntu WITH UFW (on primary) ---
sudo ufw allow from 10.10.0.0/24 to any port 3306 proto tcp comment 'mariadb-repl-apps'
sudo ufw reload

# --- nftables (generic) ---
sudo nft add rule inet filter input ip saddr 10.10.0.0/24 tcp dport 3306 accept

Verify after rules: from replica host run nc -vz 10.10.0.5 3306. “Connection refused” is often bind-address, not firewall.

Part 4 — Async primary → replica from one Docker node

What this part is for: cover: Async primary → replica from one Docker node.
What you should do: read the explanation, then do only blocks labeled COPY / RUN / SQL.

Goal: keep your existing container as primary, attach replicas that continuously apply its binary log.

Primary config and Compose

COPY FILE — save as mariadb/primary.cnf (edit passwords/IPs first).

# mariadb/primary.cnf
[mysqld]
server-id = 1
log_bin = mysql-bin
binlog_format = ROW
binlog_expire_logs_seconds = 604800
bind-address = 0.0.0.0

# MariaDB GTID helpers (recommended for rebuilds/failover)
log_slave_updates = ON
gtid_strict_mode = ON

# Safer defaults for ops
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1

COPY FILE — save as docker-compose.yml (edit passwords/IPs first).

# docker-compose.yml — primary host 10.10.0.5
services:
  mariadb-primary:
    image: mariadb:11.4.3-noble
    container_name: mariadb-primary
    hostname: mariadb-primary
    restart: unless-stopped
    environment:
      MARIADB_ROOT_PASSWORD: ${MARIADB_ROOT_PASSWORD}
      MARIADB_DATABASE: app
      MARIADB_USER: app
      MARIADB_PASSWORD: ${MARIADB_PASSWORD}
      MARIADB_REPLICATION_USER: repl
      MARIADB_REPLICATION_PASSWORD: ${MARIADB_REPLICATION_PASSWORD}
    ports:
      - "10.10.0.5:3306:3306"
    volumes:
      - mariadb_primary_data:/var/lib/mysql
      - ./mariadb/primary.cnf:/etc/mysql/conf.d/primary.cnf:ro
    healthcheck:
      test: ["CMD", "healthcheck.sh", "--connect", "--innodb_initialized"]
      interval: 10s
      timeout: 5s
      retries: 10
    networks: [dbnet]

networks:
  dbnet:
volumes:
  mariadb_primary_data:

MARIADB_REPLICATION_USER is applied on first datadir init. Existing volumes need a manual CREATE USER.

SQL — paste into MariaDB / MySQL / ProxySQL admin client (not bash).

CREATE USER 'repl'@'10.10.0.%' IDENTIFIED BY 'strong-repl-password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.10.0.%';
FLUSH PRIVILEGES;
SHOW MASTER STATUS;
SHOW VARIABLES LIKE 'gtid_binlog_pos';

Replica config and bootstrap

COPY FILE — save as mariadb/replica.cnf (edit passwords/IPs first).

# mariadb/replica.cnf
[mysqld]
server-id = 2
read_only = ON
super_read_only = ON
relay_log = relay-bin
log_bin = mysql-bin
binlog_format = ROW
log_slave_updates = ON
gtid_strict_mode = ON
bind-address = 0.0.0.0
report_host = mariadb-replica-1

COPY FILE — append to docker-compose.yml (same project as primary; lab on one host).

  mariadb-replica-1:
    image: mariadb:11.4.3-noble
    container_name: mariadb-replica-1
    hostname: mariadb-replica-1
    restart: unless-stopped
    environment:
      MARIADB_ROOT_PASSWORD: ${MARIADB_ROOT_PASSWORD}
    volumes:
      - mariadb_replica1_data:/var/lib/mysql
      - ./mariadb/replica.cnf:/etc/mysql/conf.d/replica.cnf:ro
    depends_on:
      mariadb-primary:
        condition: service_healthy
    networks: [dbnet]
    # Lab: no host port publish needed — use Docker DNS mariadb-primary

volumes:
  mariadb_replica1_data:

Unique server-id everywhere in the topology: primary=1, replica1=2, replica2=3, … Reusing an id causes silent weirdness under promotion.

Bootstrap (seed) once

RUN — paste into a shell on the host this section describes.

# Consistent logical seed (small/medium DBs)
docker exec mariadb-primary mariadb-dump -uroot -p"$ROOT" \
  --all-databases --single-transaction --master-data=2 --gtid \
  --routines --triggers --events > primary-seed.sql

docker compose up -d mariadb-replica-1
docker exec -i mariadb-replica-1 mariadb -uroot -p"$ROOT" < primary-seed.sql

SQL — paste into MariaDB / MySQL / ProxySQL admin client (not bash).

-- On replica (GTID-oriented, MariaDB):
CHANGE MASTER TO
  MASTER_HOST='mariadb-primary',
  MASTER_PORT=3306,
  MASTER_USER='repl',
  MASTER_PASSWORD='strong-repl-password',
  MASTER_USE_GTID=slave_pos,
  MASTER_CONNECT_RETRY=10,
  MASTER_HEARTBEAT_PERIOD=10;

START SLAVE;
SHOW SLAVE STATUS\G
-- Aliases on newer servers: START REPLICA / SHOW REPLICA STATUS

Verification and large-data seed

Healthy: Slave_IO_Running=Yes, Slave_SQL_Running=Yes, empty errors, low Seconds_Behind_Master.

RUN — quick replication health on replica.

docker exec mariadb-replica-1 mariadb -uroot -p"$ROOT" -e "
SHOW SLAVE STATUS\G" | egrep 'Slave_IO_Running|Slave_SQL_Running|Seconds_Behind_Master|Last_IO_Error|Last_SQL_Error'

docker exec mariadb-primary mariadb -uroot -p"$ROOT" -e "
CREATE TABLE IF NOT EXISTS app.repl_check (id INT AUTO_INCREMENT PRIMARY KEY, note VARCHAR(64));
INSERT INTO app.repl_check (note) VALUES ('primary-write');"
docker exec mariadb-replica-1 mariadb -uroot -p"$ROOT" -e "
SELECT * FROM app.repl_check ORDER BY id DESC LIMIT 1;"

Hidden tip: after import, if GTID positions disagree, do not randomly RESET MASTER on primary.
Fix replica state with a fresh seed or carefully align gtid_slave_pos from a known-good backup. Wrong GTID surgery creates duplicate key storms later.

For large datasets use mariabackup --backup on primary, prepare, restore into replica datadir, then CHANGE MASTER TO using the recorded GTID/binlog coordinates from the backup metadata.

Part 5 — Multi-host replicas (other machines)

What this part is for: cover: Multi-host replicas (other machines).
What you should do: read the explanation, then do only blocks labeled COPY / RUN / SQL.

Same replication math; different networking. On host B (10.10.0.6):

SQL — paste into MariaDB / MySQL / ProxySQL admin client (not bash).

CHANGE MASTER TO
  MASTER_HOST='10.10.0.5',   -- NOT docker service DNS across hosts
  MASTER_PORT=3306,
  MASTER_USER='repl',
  MASTER_PASSWORD='strong-repl-password',
  MASTER_USE_GTID=slave_pos,
  MASTER_CONNECT_RETRY=10;

START SLAVE;

Symptom	Likely cause	Fix
IO thread timeout	Firewall / wrong publish IP / bind-address	`nc -vz primary 3306`; fix ACL
Access denied for repl	User host pattern	`repl@'10.10.0.%'`
Works locally, fails remote	MASTER_HOST=container name	Use private IP/DNS/VIP
Lag only on remote node	Bandwidth/loss/RTT	iperf3 + mtr; move closer

Read-your-own-write: after an UPDATE, a SELECT on a lagging replica can return old data.
Route those sessions to primary, or use causal reads / “write then read primary for N ms” in the app, or proxy rules that pin after writes.

Part 6 — ProxySQL (complete practical setup)

What this part is for: cover: ProxySQL (complete practical setup).
What you should do: read the explanation, then do only blocks labeled COPY / RUN / SQL.

ProxySQL sits between apps and MariaDB. It pools connections, can split reads/writes, and shields backends from connection stampedes.

Install Compose + config

COPY FILE — create the config file named in the section (edit values first).

# docker-compose snippet
  proxysql:
    image: proxysql/proxysql:2.5.5
    container_name: proxysql
    restart: unless-stopped
    ports:
      - "10.10.0.10:6033:6033"
      - "127.0.0.1:6032:6032"
    volumes:
      - ./proxysql/proxysql.cnf:/etc/proxysql.cnf:ro
      - proxysql_data:/var/lib/proxysql
    networks: [dbnet]

COPY FILE — save as proxysql/proxysql.cnf (edit passwords/IPs first).

# proxysql/proxysql.cnf (minimal starting point)
datadir="/var/lib/proxysql"

admin_variables=
{
  admin_credentials="admin:admin_admin_change_me"
  mysql_ifaces="0.0.0.0:6032"
}

mysql_variables=
{
  threads=4
  max_connections=2048
  default_query_delay=0
  default_query_timeout=36000000
  interfaces="0.0.0.0:6033"
  default_schema="information_schema"
  server_version="11.4.0"
  monitor_username="monitor"
  monitor_password="monitor_pass"
  monitor_history=600000
  monitor_connect_interval=2000
  monitor_ping_interval=2000
}

Then configure runtime via admin port (persist to disk):

Runtime: servers, users, rules

SQL — paste into MariaDB / MySQL / ProxySQL admin client (not bash).

mysql -h127.0.0.1 -P6032 -uadmin -p

-- Backends: hostgroup 10 = writers, 20 = readers
INSERT INTO mysql_servers(hostgroup_id,hostname,port,weight,max_connections,comment) VALUES
(10,'10.10.0.5',3306,100,500,'primary'),
(20,'10.10.0.6',3306,100,500,'replica1'),
(20,'10.10.0.7',3306,100,500,'replica2');

INSERT INTO mysql_users(username,password,default_hostgroup,transaction_persistent) VALUES
('app','app_password',10,1);

-- Classic read/write split rule
INSERT INTO mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply) VALUES
(1,1,'^SELECT.*FOR UPDATE$',10,1),
(2,1,'^SELECT',20,1);

LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK;
LOAD MYSQL USERS TO RUNTIME; SAVE MYSQL USERS TO DISK;
LOAD MYSQL QUERY RULES TO RUNTIME; SAVE MYSQL QUERY RULES TO DISK;

Create monitor user on MariaDB nodes:

SQL — paste into MariaDB / MySQL / ProxySQL admin client (not bash).

CREATE USER 'monitor'@'%' IDENTIFIED BY 'monitor_pass';
GRANT USAGE, REPLICATION CLIENT ON *.* TO 'monitor'@'%';

Apps connect to VIP_OR_PROXY:6033, not directly to 3306.

App cutover and verification

Point the app DSN to ProxySQL (10.10.0.10:6033 or Keepalived VIP). Verify writes hit primary and reads can hit replicas:

RUN — smoke test through ProxySQL.

mariadb -h10.10.0.10 -P6033 -uapp -papp_password -e "
SELECT @@hostname, @@read_only;
CREATE TABLE IF NOT EXISTS app.cutover_test (ts DATETIME, msg VARCHAR(32));
INSERT INTO app.cutover_test VALUES (NOW(), 'write');
SELECT * FROM app.cutover_test ORDER BY 1 DESC LIMIT 1;"

Production ops, monitoring, anti-patterns

Underrated ProxySQL tips:
• transaction_persistent=1 keeps a session on writer after BEGIN — prevents half-transaction routing bugs.
• Put replicas that are too lagged into OFFLINE_SOFT via scheduler/scripts checking Seconds_Behind_Master.
• Never expose 6032 publicly; treat it like root SSH.
• mysql_replication_hostgroups can automate writer/reader roles if you also manage failover carefully.

Part 7 — MaxScale (complete practical setup)

What this part is for: cover: MaxScale (complete practical setup).
What you should do: read the explanation, then do only blocks labeled COPY / RUN / SQL.

MaxScale is MariaDB’s official proxy: monitors backends, readwritesplit, and can assist failover (mariadbmon).

Install Compose + maxscale.cnf

COPY FILE — docker-compose snippet for MaxScale (pin image; edit mount path).

  maxscale:
    image: mariadb/maxscale:24.02.3
    container_name: maxscale
    restart: unless-stopped
    ports:
      - "10.10.0.10:4006:4006"
      - "127.0.0.1:8989:8989"
    volumes:
      - ./maxscale/maxscale.cnf:/etc/maxscale.cnf:ro
    networks: [dbnet]

COPY FILE — save as maxscale.cnf (edit passwords/IPs first).

# maxscale.cnf
[maxscale]
threads=auto
admin_host=127.0.0.1
admin_port=8989

[primary]
type=server
address=10.10.0.5
port=3306

[replica1]
type=server
address=10.10.0.6
port=3306

[replica2]
type=server
address=10.10.0.7
port=3306

[MariaDB-Monitor]
type=monitor
module=mariadbmon
servers=primary,replica1,replica2
user=maxmon
password=maxmon_pass
monitor_interval=2000ms
auto_failover=false
auto_rejoin=false

[Read-Write-Service]
type=service
router=readwritesplit
servers=primary,replica1,replica2
user=maxuser
password=maxuser_pass
master_reconnection=true
master_failure_mode=fail_on_write
slave_selection_criteria=LEAST_CURRENT_OPERATIONS

[Read-Write-Listener]
type=listener
service=Read-Write-Service
protocol=mariadbprotocol
address=0.0.0.0
port=4006
# Legacy aliases MariaDBClient / mysqlclient still work on 24.02

SQL — paste into MariaDB / MySQL / ProxySQL admin client (not bash).

-- Grants on all MariaDB nodes (simplify for lab; tighten hosts in prod)
CREATE USER 'maxmon'@'%' IDENTIFIED BY 'maxmon_pass';
GRANT REPLICATION CLIENT, REPLICATION SLAVE, SUPER, RELOAD, PROCESS ON *.* TO 'maxmon'@'%';
-- On modern MariaDB, prefer least-privilege docs for your exact MaxScale version.

CREATE USER 'maxuser'@'%' IDENTIFIED BY 'maxuser_pass';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER ON app.* TO 'maxuser'@'%';

Cutover and verification

RUN — smoke test through MaxScale listener.

mariadb -h10.10.0.10 -P4006 -umaxuser -pmaxuser_pass -e "
SELECT @@hostname, @@read_only;
SELECT * FROM mysql.user LIMIT 1;"

Failover helpers and best practices

About auto_failover=true: only enable after you understand split-brain risks, STONITH/fencing, and that two MaxScale nodes need coordination (or a single orchestrator).
Many teams keep MaxScale for routing and do controlled failover with runbooks / Keepalived / Orchestrator — then turn automation on later.

MaxScale vs ProxySQL (practical):
MaxScale — deeper MariaDB monitor/failover story, readwritesplit, MariaDB ecosystem fit.
ProxySQL — very strong pooling/query rules, widely used in mixed MySQL/MariaDB shops, lightweight ops model.
Both can sit behind Keepalived.

Part 8 — Galera 3-node (Compose templates)

What this part is for: get a ready template for: Galera 3-node (Compose templates).
What you should do: COPY files; edit for your host; RUN apply/restart as labeled.

Galera provides multi-primary replication via wsrep. Every node has the full dataset. Writes are certified by the group. This is a different animal from async replicas.

When Galera is the right tool

Good fit	Poor fit
Need multi-writer or almost-sync members in one LAN	Read scale only (async replicas are simpler)
Same AZ / fast LAN	Stretch one Galera across continents
You accept certification conflicts / retry design	Hot-row write storms without app retries

Image note: not every generic mariadb: tag is the easiest Galera path in Docker. Many teams use images built for Galera (Bitnami MariaDB Galera, or MariaDB with libgalera_smm.so + SST helpers present). Below uses Bitnami-style env for a lab that matches production bootstrap logic; adapt paths if you use another image.

Compose vs docker run: docker run is fine for a one-shot smoke test. For production, put each Galera member in its own host’s Compose file (Git-tracked .env + compose.yml) so restarts, volumes, and env stay reproducible. Multi-host Galera is not one Compose project spanning three machines over a bridge network.

Prerequisites and firewall

Multi-host Docker reality: a Compose/bridge network named galera-net does not span physical hosts. On separate machines, publish Galera ports on each host’s private IP and set MARIADB_GALERA_NODE_ADDRESS to that host IP so nodes advertise a reachable address (not 172.x). If SST/IST flaps under published ports, prefer network_mode: host on each Galera service (still set NODE_ADDRESS to the host IP).
Same-host lab demos can share one Docker network; production multi-host clusters must use host IPs (or an overlay/k8s CNI you actually operate).

RUN — use ONLY the section for your OS; skip the rest.

# Hosts
# 10.10.0.21  galera1  (bootstrap first)
# 10.10.0.22  galera2
# 10.10.0.23  galera3

# Open between members: 3306/tcp, 4444/tcp, 4567/tcp+udp, 4568/tcp
# Run the matching block on ALL three hosts. Detect stack first (Part 3).

# --- RHEL / Rocky / Alma: firewalld ---
firewall-cmd --permanent --add-rich-rule='rule family=ipv4 source address=10.10.0.0/24 port port=3306 protocol=tcp accept'
firewall-cmd --permanent --add-rich-rule='rule family=ipv4 source address=10.10.0.0/24 port port=4444 protocol=tcp accept'
firewall-cmd --permanent --add-rich-rule='rule family=ipv4 source address=10.10.0.0/24 port port=4567 protocol=tcp accept'
firewall-cmd --permanent --add-rich-rule='rule family=ipv4 source address=10.10.0.0/24 port port=4567 protocol=udp accept'
firewall-cmd --permanent --add-rich-rule='rule family=ipv4 source address=10.10.0.0/24 port port=4568 protocol=tcp accept'
firewall-cmd --reload

# --- Debian / Ubuntu WITH UFW active ---
sudo ufw allow from 10.10.0.0/24 to any port 3306 proto tcp comment 'galera-mysql'
sudo ufw allow from 10.10.0.0/24 to any port 4444 proto tcp comment 'galera-sst'
sudo ufw allow from 10.10.0.0/24 to any port 4567 proto tcp comment 'galera-gcomm-tcp'
sudo ufw allow from 10.10.0.0/24 to any port 4567 proto udp comment 'galera-gcomm-udp'
sudo ufw allow from 10.10.0.0/24 to any port 4568 proto tcp comment 'galera-ist'
sudo ufw reload

# --- Debian / Ubuntu WITHOUT UFW (nftables) ---
sudo nft add rule inet filter input ip saddr 10.10.0.0/24 tcp dport { 3306, 4444, 4567, 4568 } accept
sudo nft add rule inet filter input ip saddr 10.10.0.0/24 udp dport 4567 accept

# --- Debian / Ubuntu WITHOUT UFW (legacy iptables) ---
for p in 3306 4444 4567 4568; do
  sudo iptables -I INPUT -s 10.10.0.0/24 -p tcp --dport $p -j ACCEPT
done
sudo iptables -I INPUT -s 10.10.0.0/24 -p udp --dport 4567 -j ACCEPT

Production Compose templates (one file per host)

Create the same layout on each machine. Only .env values differ (IP, hostname, bootstrap flag).

COPY FILE — save as /opt/galera/.env (edit passwords/IPs first).

# /opt/galera/.env  — example for galera1 (bootstrap host, first start ONLY)
MARIADB_ROOT_PASSWORD=root_change_me
MARIADB_GALERA_CLUSTER_NAME=prod_galera
MARIADB_GALERA_NODE_ADDRESS=10.10.0.21
MARIADB_GALERA_MARIABACKUP_USER=backup
MARIADB_GALERA_MARIABACKUP_PASSWORD=backup_change_me
# First boot of empty cluster:
MARIADB_GALERA_CLUSTER_ADDRESS=gcomm://
MARIADB_GALERA_CLUSTER_BOOTSTRAP=yes
HOST_BIND=10.10.0.21
CONTAINER_NAME=galera1
HOSTNAME_NODE=galera1

COPY FILE — save as /opt/galera/.env (edit passwords/IPs first).

# /opt/galera/.env  — galera2 / galera3 (and galera1 AFTER bootstrap is done)
# MARIADB_GALERA_CLUSTER_BOOTSTRAP must be absent or empty — never "yes" on joiners.
MARIADB_ROOT_PASSWORD=root_change_me
MARIADB_GALERA_CLUSTER_NAME=prod_galera
MARIADB_GALERA_NODE_ADDRESS=10.10.0.22
MARIADB_GALERA_MARIABACKUP_USER=backup
MARIADB_GALERA_MARIABACKUP_PASSWORD=backup_change_me
MARIADB_GALERA_CLUSTER_ADDRESS=gcomm://10.10.0.21,10.10.0.22,10.10.0.23
HOST_BIND=10.10.0.22
CONTAINER_NAME=galera2
HOSTNAME_NODE=galera2

COPY FILE — save as /opt/galera/compose.yml (edit passwords/IPs first).

# /opt/galera/compose.yml  — identical on all three hosts (copy as template)
name: galera-node

services:
  galera:
    image: bitnami/mariadb-galera:11.4.3
    container_name: ${CONTAINER_NAME}
    hostname: ${HOSTNAME_NODE}
    restart: unless-stopped
    ports:
      - "${HOST_BIND}:3306:3306"
      - "${HOST_BIND}:4444:4444"
      - "${HOST_BIND}:4567:4567"
      - "${HOST_BIND}:4567:4567/udp"
      - "${HOST_BIND}:4568:4568"
    environment:
      MARIADB_ROOT_PASSWORD: ${MARIADB_ROOT_PASSWORD}
      MARIADB_GALERA_CLUSTER_NAME: ${MARIADB_GALERA_CLUSTER_NAME}
      MARIADB_GALERA_NODE_ADDRESS: ${MARIADB_GALERA_NODE_ADDRESS}
      MARIADB_GALERA_MARIABACKUP_USER: ${MARIADB_GALERA_MARIABACKUP_USER}
      MARIADB_GALERA_MARIABACKUP_PASSWORD: ${MARIADB_GALERA_MARIABACKUP_PASSWORD}
      MARIADB_GALERA_CLUSTER_ADDRESS: ${MARIADB_GALERA_CLUSTER_ADDRESS}
      # Only set on first bootstrap of empty cluster; remove for normal ops:
      MARIADB_GALERA_CLUSTER_BOOTSTRAP: ${MARIADB_GALERA_CLUSTER_BOOTSTRAP:-}
    volumes:
      - galera_data:/bitnami/mariadb

volumes:
  galera_data:

Optional host networking (often more reliable for Galera than published ports): replace the ports: block with network_mode: host and keep MARIADB_GALERA_NODE_ADDRESS as the host’s private IP.

RUN — optional smoke test only; use Compose templates above for production.

# Equivalent one-liner smoke test (optional — prefer Compose above for production)
# docker run -d --name galera1 --hostname galera1 --restart=unless-stopped \
#   -p 10.10.0.21:3306:3306 -p 10.10.0.21:4444:4444 \
#   -p 10.10.0.21:4567:4567 -p 10.10.0.21:4567:4567/udp -p 10.10.0.21:4568:4568 \
#   -e MARIADB_ROOT_PASSWORD='...' -e MARIADB_GALERA_CLUSTER_NAME=prod_galera \
#   -e MARIADB_GALERA_NODE_ADDRESS=10.10.0.21 \
#   -e MARIADB_GALERA_MARIABACKUP_USER=backup -e MARIADB_GALERA_MARIABACKUP_PASSWORD='...' \
#   -e MARIADB_GALERA_CLUSTER_ADDRESS='gcomm://' -e MARIADB_GALERA_CLUSTER_BOOTSTRAP=yes \
#   -v galera1_data:/bitnami/mariadb bitnami/mariadb-galera:11.4.3

Bootstrap → join → drop bootstrap

RUN — bootstrap and join sequence (shell on each Galera host).

# --- Host 10.10.0.21 (empty cluster, FIRST START ONLY) ---
cd /opt/galera
# .env has CLUSTER_ADDRESS=gcomm:// and CLUSTER_BOOTSTRAP=yes
docker compose up -d
docker compose logs -f galera
docker compose exec galera mariadb -uroot -p"$MARIADB_ROOT_PASSWORD" \
  -e "SHOW STATUS LIKE 'wsrep_cluster_size'; SHOW STATUS LIKE 'wsrep_local_state_comment';"
# Expect: Synced, size=1

# --- Host 10.10.0.22 then 10.10.0.23 (JOIN — no bootstrap) ---
cd /opt/galera
# .env has gcomm://10.10.0.21,10.10.0.22,10.10.0.23 and NO BOOTSTRAP=yes
docker compose up -d
docker compose logs -f galera

# --- Critical: remove bootstrap from galera1 while peers are Synced ---
# Edit .env on galera1:
#   MARIADB_GALERA_CLUSTER_ADDRESS=gcomm://10.10.0.21,10.10.0.22,10.10.0.23
#   delete MARIADB_GALERA_CLUSTER_BOOTSTRAP (or leave empty)
docker compose up -d --force-recreate
# Volume galera_data is kept — you are only clearing bootstrap mode for future restarts.

Expect after join: wsrep_local_state_comment=Synced and growing wsrep_cluster_size.

After the cluster is up, remove bootstrap mode from galera1 so a reboot of node1 does not accidentally create a second cluster. Subsequent starts should use the same gcomm://n1,n2,n3 address list for all members.

Verify quorum, SST/IST, proxy front

RUN — paste into a shell on the host this section describes.

docker compose exec galera mariadb -uroot -p"$MARIADB_ROOT_PASSWORD" -e "
SHOW STATUS LIKE 'wsrep_cluster_size';
SHOW STATUS LIKE 'wsrep_cluster_status';
SHOW STATUS LIKE 'wsrep_local_state_comment';
SHOW STATUS LIKE 'wsrep_ready';
SHOW STATUS LIKE 'wsrep_connected';
"

-- On any node:
CREATE DATABASE IF NOT EXISTS app;
CREATE TABLE app.g1(id INT PRIMARY KEY, v VARCHAR(32));
INSERT INTO app.g1 VALUES (1,'galera');
-- SELECT on other nodes must see the row

Healthy: wsrep_cluster_size=3, wsrep_cluster_status=Primary, wsrep_local_state_comment=Synced, wsrep_ready=ON.

SST vs IST: a node that was briefly down usually catches up via IST (4568). Long downtime or missing seqnos triggers SST (4444) which is heavy — schedule joins carefully.
Put MaxScale or ProxySQL in front; float VIP with Keepalived. Apps should see one endpoint.

Galera tips rarely pasted into blogs:
• Hot rows → certification failures; design keys and retry logic.
• pc.recovery / grastate.dat matter after full outage — practice recovery; never guess safe_to_bootstrap.
• One Galera member can still feed an async replica for analytics/DR.
• Flow control (wsrep_flow_control_paused) means a slow node is braking the group.

Part 9 — Keepalived VIP (concepts → production)

What this part is for: cover: Keepalived VIP (concepts → production).
What you should do: read the explanation, then do only blocks labeled COPY / RUN / SQL.

If Part 6–8 felt concrete and this section felt foggy before, that is normal: Keepalived is not another database tool. It is a small host daemon that makes one IP address move between machines. Read this part in order — each subsection answers a specific question.

1) The problem Keepalived solves

Your apps need a stable database endpoint: one host + one port in the connection string.

You already have (or will have) two ProxySQL or MaxScale hosts for redundancy.
If the app points at 10.10.0.11 and that host dies, every client breaks until someone edits configs/DNS.
DNS TTL and client caches are slow and messy for this.

Solution: invent a third IP that does not belong permanently to either host — a VIP (Virtual IP), e.g. 10.10.0.100. Apps always connect to the VIP. Only one of the two proxy hosts “owns” that IP at a time. When the owner dies (or its proxy process dies), the other host takes the VIP.

READ — diagram or folder layout. Do not paste into a terminal.

Apps always use:  10.10.0.100:6033

Normal day:
  Host A 10.10.0.11  → has VIP 10.10.0.100  → ProxySQL answering
  Host B 10.10.0.12  → no VIP               → ProxySQL standby (same config)

After A dies (or ProxySQL on A fails health check):
  Host A  → dead / unhealthy
  Host B  → now has VIP 10.10.0.100 → apps still use the same DSN

Keepalived is the program that adds and removes that VIP on the NIC and talks to its peer so they agree who owns it.

Keepalived does NOT replicate MariaDB. Keepalived does NOT replace ProxySQL/MaxScale. Keepalived only moves an IP (and optionally runs a health script that decides “I am unfit to hold the VIP”).

2) How VIP + VRRP actually work

VIP: a normal IPv4 address configured on a network interface, but managed by Keepalived instead of /etc/netplan / NetworkManager. When Keepalived is MASTER, you see it in ip addr. When it is BACKUP, the address is gone from that host.

VRRP (Virtual Router Redundancy Protocol): a small election protocol between Keepalived peers.

Both hosts share the same virtual_router_id (a number 1–255 that identifies this VIP pair on the LAN).
Each host has a priority (higher wins). Example: A=120, B=100.
The higher-priority healthy node becomes MASTER and announces “I own the VIP” (VRRP advertisements, IP protocol 112).
The other node stays BACKUP and listens. If advertisements stop (peer dead) or the MASTER’s priority drops (health script), BACKUP becomes MASTER and takes the VIP.

Unicast vs multicast: classic VRRP uses multicast. Many clouds block that. This guide uses unicast: each peer lists the other peer’s real IP in unicast_peer. Prefer unicast unless you know your LAN allows multicast VRRP.

Health script: Keepalived can run a script every N seconds. Exit code 0 = healthy. Non-zero = unhealthy → lower effective priority (via weight) → VIP should move even though the machine is still up. That is how “ProxySQL crashed but the VM is fine” still fails over.

READ — diagram or folder layout. Do not paste into a terminal.

Timeline of a healthy failover (ProxySQL dies on A):

t=0   A holds VIP; script on A succeeds
t=2   ProxySQL on A stops; script starts failing
t=2+  after "fall" consecutive failures → A enters FAULT / priority drops
t≈    B stops hearing a healthy master → B takes VIP
apps  TCP connections to old owner drop; new connects to B succeed

What Keepalived does not do: it does not drain connections gracefully. Moving the VIP cuts existing TCP sessions. Applications must reconnect (pool retry / idempotent writes). That is expected.

3) Where to put Keepalived (proxy tier, not the DB)

Pattern	Verdict
VIP in front of two ProxySQL/MaxScale hosts	Recommended — apps see one endpoint; DB topology stays behind the proxy
VIP directly on MariaDB primary + replica	Dangerous without fencing — two nodes can briefly both think they are writable (split-brain)
Cloud load balancer instead of Keepalived	Often better in public cloud if VRRP is blocked; same idea (one frontend IP)

READ — diagram or folder layout. Do not paste into a terminal.

Correct stack for this article:

  Apps ──► VIP :6033 (Keepalived on proxy hosts)
              ├─ ProxySQL/MaxScale A  (MASTER holds VIP)
              └─ ProxySQL/MaxScale B  (BACKUP)
                        │
                        ▼
                MariaDB primary / replicas / Galera

ProxySQL/MaxScale on A and B must be configured identically (same backends, users, rules) via Git. Keepalived only moves the front door; the room behind both doors must look the same.

4) Lab topology and IP plan (copy these numbers)

Role	IP	Notes
Proxy host A	`10.10.0.11`	Real NIC address; higher Keepalived priority
Proxy host B	`10.10.0.12`	Real NIC address; lower priority
VIP (apps use this)	`10.10.0.100`	Must be free on the subnet; not assigned in DHCP
Proxy port	`6033`	ProxySQL (use `4006` for MaxScale)

Prerequisites before Keepalived:

ProxySQL (or MaxScale) already listening on 127.0.0.1:6033 / host IP on both A and B (Parts 6–7).
Both proxies can reach MariaDB backends.
A and B are on the same L2 segment (or a network that allows unicast VRRP between them).
10.10.0.100 is not used by any other host.

5) Packages, sysctl, firewall

RUN — paste into a shell on the host this section describes.

# On BOTH proxy hosts — packages
# Debian/Ubuntu:
sudo apt-get update
sudo apt-get install -y keepalived

# RHEL/Rocky/Alma:
# sudo dnf install -y keepalived

# Allow binding an IP that is not yet local (needed on many kernels when VIP moves)
echo "net.ipv4.ip_nonlocal_bind = 1" | sudo tee /etc/sysctl.d/99-keepalived.conf
# ip_forward is NOT required for a simple floating VIP on the same LAN.
sudo sysctl --system

# Confirm NIC name (use this in keepalived.conf → interface)
ip -br link
# Examples: eth0, ens192, enp0s3 — do not copy "eth0" blindly

RUN — use ONLY the section for your OS; skip the rest.

# Firewall: allow VRRP (IP protocol 112) between peers + app traffic to VIP port
# Detect stack first (Part 3). Peers: 10.10.0.11 ↔ 10.10.0.12

# --- RHEL / Rocky / Alma: firewalld ---
firewall-cmd --permanent --add-rich-rule='rule family=ipv4 source address=10.10.0.11 protocol value=vrrp accept'
firewall-cmd --permanent --add-rich-rule='rule family=ipv4 source address=10.10.0.12 protocol value=vrrp accept'
firewall-cmd --permanent --add-rich-rule='rule family=ipv4 source address=10.10.0.0/24 port port=6033 protocol=tcp accept'
# firewall-cmd --permanent --add-rich-rule='rule family=ipv4 source address=10.10.0.0/24 port port=4006 protocol=tcp accept'
firewall-cmd --reload

# --- Debian / Ubuntu WITH UFW active ---
# UFW has no first-class "allow vrrp" — edit /etc/ufw/before.rules
# BEFORE the IPv4 *filter COMMIT line, add:
#
#   -A ufw-before-input -p vrrp -s 10.10.0.11 -j ACCEPT
#   -A ufw-before-input -p vrrp -s 10.10.0.12 -j ACCEPT
#
sudo ufw allow from 10.10.0.0/24 to any port 6033 proto tcp comment 'proxysql-vip'
sudo ufw reload
# Verify: sudo iptables -S ufw-before-input | grep -i vrrp

# --- Debian / Ubuntu WITHOUT UFW (nftables) ---
sudo nft add rule inet filter input ip saddr 10.10.0.11 meta l4proto 112 accept
sudo nft add rule inet filter input ip saddr 10.10.0.12 meta l4proto 112 accept
sudo nft add rule inet filter input ip saddr 10.10.0.0/24 tcp dport 6033 accept

# --- Debian / Ubuntu WITHOUT UFW (legacy iptables) ---
sudo iptables -I INPUT -s 10.10.0.11 -p vrrp -j ACCEPT
sudo iptables -I INPUT -s 10.10.0.12 -p vrrp -j ACCEPT
sudo iptables -I INPUT -s 10.10.0.0/24 -p tcp --dport 6033 -j ACCEPT

6) Full configs — health script + node A + node B

Create the same health script on both hosts. It must check the local proxy (not the VIP), otherwise the BACKUP never looks healthy enough to take over cleanly in some designs.

COPY FILE — save as /etc/keepalived/check_proxy.sh (edit passwords/IPs first).

# /etc/keepalived/check_proxy.sh  — identical on A and B (ProxySQL example)
#!/bin/bash
# Exit 0 = this host may hold the VIP; non-zero = give VIP away
exec 2>/dev/null
# Check local ProxySQL — NOT 10.10.0.100
mysqladmin ping -h127.0.0.1 -P6033 -uapp -papp_password --connect-timeout=2
exit $?
# MaxScale alternative: mysqladmin ping -h127.0.0.1 -P4006 ...
# Or: nc -z 127.0.0.1 6033

RUN — paste into a shell on the host this section describes.

sudo chmod 755 /etc/keepalived/check_proxy.sh
# Quick manual test (must print "mysqld is alive" / exit 0 while proxy is up):
sudo /etc/keepalived/check_proxy.sh; echo exit:$?

COPY FILE — save as /etc/keepalived/keepalived.conf (edit passwords/IPs first).

# /etc/keepalived/keepalived.conf  — NODE A (preferred MASTER)
global_defs {
  router_id proxy_a
  script_user root
  enable_script_security
}

vrrp_script chk_proxy {
  script "/etc/keepalived/check_proxy.sh"
  interval 2          # run every 2s
  weight -30          # on failure, subtract 30 from priority (120-30=90 < B's 100)
  fall 3              # 3 failures → unhealthy
  rise 2              # 2 successes → healthy again
}

vrrp_instance VI_PROXY {
  state MASTER        # initial hint only; priority + health decide reality
  interface eth0      # CHANGE to your NIC from: ip -br link
  virtual_router_id 51
  priority 120
  advert_int 1
  # nopreempt         # uncomment if after failover you do NOT want A to steal VIP back automatically
  authentication {
    auth_type PASS
    auth_pass s3cretVI   # max 8 characters for PASS
  }
  unicast_src_ip 10.10.0.11
  unicast_peer {
    10.10.0.12
  }
  virtual_ipaddress {
    10.10.0.100/24       # match your subnet mask
  }
  track_script {
    chk_proxy
  }
}

COPY FILE — save as /etc/keepalived/keepalived.conf (edit passwords/IPs first).

# /etc/keepalived/keepalived.conf  — NODE B (BACKUP)
global_defs {
  router_id proxy_b
  script_user root
  enable_script_security
}

vrrp_script chk_proxy {
  script "/etc/keepalived/check_proxy.sh"
  interval 2
  weight -30
  fall 3
  rise 2
}

vrrp_instance VI_PROXY {
  state BACKUP
  interface eth0           # CHANGE to your NIC
  virtual_router_id 51     # MUST match A (same VIP pair)
  priority 100             # lower than A's 120
  advert_int 1
  authentication {
    auth_type PASS
    auth_pass s3cretVI     # MUST match A
  }
  unicast_src_ip 10.10.0.12
  unicast_peer {
    10.10.0.11
  }
  virtual_ipaddress {
    10.10.0.100/24
  }
  track_script {
    chk_proxy
  }
}

RUN — paste into a shell on the host this section describes.

# Start on A, then on B
sudo systemctl enable --now keepalived
sudo systemctl status keepalived --no-pager
journalctl -u keepalived -e --no-pager

# Who holds the VIP?
ip -br addr | grep 10.10.0.100
# Expect: only on A (or only on B if A is unhealthy) — NEVER on both

Config keys that must match across A and B: virtual_router_id, auth_pass, VIP address/mask, advert_int.
Keys that must differ: router_id, state/priority, unicast_src_ip, unicast_peer, and usually interface name if NICs differ.

7) Drills that answer every “what if”

Point a canary at the VIP before production cutover:

RUN — from an app/jump host (DSN uses VIP, not a single proxy IP).

# From an app/jump host — DSN uses VIP, not 10.10.0.11
mysql -h10.10.0.100 -P6033 -uapp -papp_password -e "SELECT @@hostname, NOW();"

# Watchdog during drills (second terminal):
while true; do
  mysql -h10.10.0.100 -P6033 -uapp -papp_password -N -e "SELECT NOW();" 2>/dev/null \
    || echo "$(date -Is) FAIL"
  sleep 1
done

Baseline: on A, ip addr shows 10.10.0.100; on B it does not. Canary connects.
Stop Keepalived on A (sudo systemctl stop keepalived): VIP appears on B within a few seconds. Canary may show one FAIL then recover. This proves VRRP peer takeover.
Start Keepalived on A again: with priorities above, A usually preempts and takes VIP back. If you want “stay on B until B fails”, enable nopreempt on both and set initial state BACKUP on both (common production choice).
Proxy death without killing Keepalived: on A, stop ProxySQL/MaxScale only. After fall failures, VIP moves to B while keepalived still runs on A. This proves the health script path.
Split-brain check: run ip -br addr | grep 10.10.0.100 on both hosts at the same time — must be exactly one owner. If both have it: wrong virtual_router_id collision, auth mismatch, or firewall dropping VRRP so each thinks it is alone.
Restore: start proxy on A; with preempt, VIP may return to A when script is healthy again.

RUN — paste into a shell on the host this section describes.

# Observation toolkit
journalctl -u keepalived -f
ip -br addr
# On Linux, VRRP packets (needs root):
sudo tcpdump -ni eth0 vrrp
# or: sudo tcpdump -ni eth0 proto 112

Pitfalls: two clusters sharing the same virtual_router_id on one LAN; auth_pass longer than 8 chars (silently truncated); cloud SG blocking protocol 112; wrong interface; health script that always succeeds (VIP never leaves) or always fails (VIP flaps); checking the VIP from the health script instead of 127.0.0.1.

Cutover: change application DSNs from 10.10.0.11:6033 to 10.10.0.100:6033 (ProxySQL) or 10.10.0.100:4006 (MaxScale). Keep direct host IPs only for break-glass admin.

8) FAQ — questions readers still have

Question	Answer
Is the VIP a DNS name?	No. It is a real IP on the LAN. You may put DNS on top (`db.internal → 10.10.0.100`), but Keepalived moves the IP, not the DNS record.
Do I install Keepalived inside the ProxySQL container?	No for this guide. Run Keepalived on the host (systemd). The VIP lands on the host NIC; Docker publishes/binds proxy ports on that host.
Must ProxySQL run on both hosts even when B has no VIP?	Yes. B must be ready to serve the moment it gets the VIP. Idle ProxySQL on B is normal and desired.
What if both proxies are fine but MariaDB primary dies?	Keepalived does nothing useful for that — VIP stays put. You need replica promotion / MaxScale failover / runbook (Part 10). Keepalived only covers “front door host/process” failure.
Why unicast_src_ip / unicast_peer?	So VRRP advertisements go host-to-host without multicast. Required in many cloud VPCs.
Can I use Keepalived with one proxy only?	Pointless. VIP HA needs at least two hosts that can own the IP.
Does failover lose in-flight queries?	Yes, TCP resets. Design pools with reconnect. That is the tradeoff of a floating IP.
Cloud says VRRP is blocked — now what?	Use the cloud’s TCP load balancer / NLB with health checks against ProxySQL/MaxScale on both instances. Same user-facing idea as a VIP.

Part 10 — Failover playbooks (async, proxy, Galera)

What this part is for: cover: Failover playbooks (async, proxy, Galera).
What you should do: read the explanation, then do only blocks labeled COPY / RUN / SQL.

A) Async primary death (controlled promotion)

Stop writers (app maintenance or proxy reject writes)
Compare replicas: highest GTID / lowest lag, no SQL errors
On winner: run promotion SQL below
Repoint ProxySQL writer hostgroup / MaxScale master to winner
Keep VIP on proxy tier (Keepalived unchanged if proxies healthy)
Rebuild old primary as new replica of winner

SQL — on chosen replica (winner) after writers stopped.

STOP SLAVE;
SHOW SLAVE STATUS\G
-- Confirm lowest Seconds_Behind_Master / no Last_SQL_Error

RESET SLAVE ALL;
SET GLOBAL read_only = 0;
SET GLOBAL super_read_only = 0;
SELECT @@gtid_current_pos, @@read_only;

SQL — ProxySQL: point writer hostgroup to new primary IP.

UPDATE mysql_servers SET hostname='10.10.0.6' WHERE hostgroup_id=10;
LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK;

Never promote two replicas. Never bring the old primary back online as writable without rebuild. That is instant split-brain.

B) Proxy host death

Keepalived moves VIP after health checks fail
Apps reconnect to same VIP on the other proxy
Verify backends still ONLINE; no MariaDB promotion required

C) Galera node death

Cluster remains Primary if quorum survives (e.g. 2 of 3)
Proxy monitor removes unhealthy node
Repair/rejoin node (IST if possible, SST if needed)
If all nodes lost power: follow documented bootstrap of the most advanced node only

D) What to automate vs not (mid-level honesty)

Automate: VIP move on proxy health, removing lagged replicas from read pools
Automate carefully: MaxScale/Orchestrator primary failover with fencing
Do not automate blindly: Galera new-cluster bootstrap after total outage

Part 11 — Field notes you rarely see written down

What this part is for: understand: Field notes you rarely see written down.
What you should do: read this section unless a labeled block says otherwise.

Replica on the same disk as primary doubles IO and lies about “scale.” Put replicas on other machines for real capacity.
read_only=ON without super_read_only still allows SUPER users to write — use both on replicas.
Proxy in another AZ “to save money” can add 1–3 ms per query × thousands of QPS = self-inflicted latency tax.
Semisync replication (if you enable it) reduces data-loss window but adds primary latency; it is not Galera and not free HA.
Delayed replicas (MASTER_DELAY) are excellent for ransomware/bad migration recovery — underrated insurance.
Backup the primary AND prove restore on a schedule. Replicas are not backups.
Connection pools in every microservice + no proxy can open thousands of threads to primary; ProxySQL often fixes “mysterious” primary meltdown.
Galera + async replica of one member is a valid hybrid: cluster for local HA, async replica for analytics/DR region.
Keepalived without app reconnect logic still drops in-flight connections; clients must retry idempotently.

RUN — lag/capacity signals (MariaDB host shell).

docker exec mariadb-primary mariadb -uroot -p"$ROOT" -e "
SHOW GLOBAL STATUS LIKE 'Threads_running';
SHOW GLOBAL STATUS LIKE 'Questions';"

SQL — replication lag, ProxySQL pool, Galera flow control.

SHOW SLAVE STATUS\G
-- ProxySQL admin:
SELECT * FROM stats_mysql_connection_pool;
-- Galera:
SHOW STATUS LIKE 'wsrep_flow_control_paused';

Pre-flight checklist (Senior DevOps)

What this part is for: gate production cutover for any topology in this guide.
What you should do: confirm every item on staging before changing production DSN.

Unique server-id on every MariaDB node; binlog + GTID enabled on primary
Replication: IO/SQL threads Yes; test row replicates primary → replica
Firewall: nc -vz from replica/proxy to primary :3306 succeeds
Proxy: monitor user works; read/write split tested with real app queries
Keepalived: VIP moves on proxy health drill; only one host owns VIP at a time
Failover runbook practiced: promotion SQL + ProxySQL hostgroup update documented
Backups + restore test on schedule (replica ≠ backup)
Galera only if RTT budget met; bootstrap flag removed after cluster up

Part 12 — Decision matrix and final checklist

What this part is for: cover: Decision matrix and final checklist.
What you should do: read the explanation, then do only blocks labeled COPY / RUN / SQL.

Need	Use
More SELECTs	Async replicas + ProxySQL or MaxScale
Stable endpoint when proxy host dies	Keepalived VIP on proxy pair
Multi-writer / synced members one DC	Galera (3+) + proxy
Controlled primary promotion	Runbook ± MaxScale/Orchestrator
Write scale beyond one primary	Sharding / multi-primary redesign (Galera or app-level split)

Measure bottleneck: read CPU, write CPU, disk, lag, connection count
Enable binlog + unique server-id on the single Docker node
Add remote replica(s); verify network ms/bandwidth/ports
Place ProxySQL or MaxScale in front; split reads safely
Deploy Keepalived VIP across two proxy hosts with real health checks
Document async promotion and proxy failover drills; practice them
Adopt Galera only with LAN RTT budget and quorum plan
Consider deliberate sharding or multi-primary only when single-primary economics fail — measure first
Backups + restore tests remain mandatory in every topology

Scaling MariaDB from one Docker container is a stack: replication or Galera for data distribution, ProxySQL/MaxScale for traffic policy, and Keepalived for a stable VIP on the proxy tier. Build in that order unless your requirements clearly skip a step.