Chapter 22 · Production Capstone: Design, Cluster, Secure, Tune, and Recover MariaDB
Implement Backups/PITR, HA, Monitoring, Alerts, Upgrades, and Incident Runbooks
Turn the capstone into an operable service with tested physical backups and PITR, an optional local GTID replica topology, monitoring/alerts, client fencing expectations, upgrade gates, and executable incident runbooks.
Learning outcomes
A production database is not complete when queries pass. It is complete only when the team can restore data, identify the recovery point, observe health, route clients safely during failure, and upgrade without guessing. This lesson builds those operating controls around the ServiceHub capstone.
Create a least-privilege mariadb-backup identity, take a physical backup, prepare it, and prove a restore in an isolated target.
Preserve binary-log coordinates and rehearse point-in-time recovery without replaying beyond the chosen boundary.
Build an optional free two-node asynchronous GTID topology and monitor receive/apply health without calling it automatic failover.
Define user-impact alerts plus database leading indicators and write executable incident runbooks.
Add version/upgrade gates that require backup, compatibility, topology and performance evidence before change.
The lab examples target MariaDB Community Server 12.3.2 as the
current GA reference and retain 11.8 LTS as the course anchor.
mariadb-backup must be compatible with the source
server version. Verify package/tool versions before every real
restore or upgrade.
1. Backup identity and prerequisites
CREATE USER IF NOT EXISTS 'mariadb-backup'@'localhost' IDENTIFIED BY 'DISPOSABLE-backup-secret';GRANT RELOAD, PROCESS, LOCK TABLES, BINLOG MONITOR ON *.* TO 'mariadb-backup'@'localhost';SHOW GRANTS FOR 'mariadb-backup'@'localhost';SHOW VARIABLES WHERE Variable_name IN ('datadir','log_bin','log_bin_basename','binlog_format');SHOW BINARY LOGS;
Current MariaDB documentation lists RELOAD,
PROCESS, LOCK TABLES and
BINLOG MONITOR for the common backup case, with
additional privileges for features such as history records,
Galera/replica metadata or killing long queries. Grant the exact
extras only when the selected options require them.
2. Take, prepare and verify a physical backup
# Use a protected option file or secret injection in real automation.rm -rf /tmp/capstone22-fullmariadb-backup --backup --target-dir=/tmp/capstone22-full --user=mariadb-backup --password='DISPOSABLE-backup-secret'mariadb-backup --prepare --target-dir=/tmp/capstone22-fullls -lah /tmp/capstone22-fullcat /tmp/capstone22-full/xtrabackup_checkpointscat /tmp/capstone22-full/xtrabackup_binlog_info 2>/dev/null || true
A backup file copy is not yet a restore.
--prepare makes the copied InnoDB files consistent.
The acceptance test is an isolated restore that starts a MariaDB
server and passes business invariants.
Restore into an ISOLATED disposable datadir/container: 1. stop the disposable target server 2. ensure target datadir is empty 3. mariadb-backup --copy-back --target-dir=/tmp/capstone22-full 4. set correct filesystem ownership for the server account 5. start the target using a port/socket that cannot receive production traffic 6. query row counts + key business invariants 7. record restore start/end time and MariaDB error logNever overwrite the only live datadir to “test” a backup.# Use the SAME MariaDB release/tool compatibility required by the backup.sudo rm -rf /srv/mariadb22-restoresudo install -d -o mysql -g mysql /srv/mariadb22-restoresudo mariadb-backup --copy-back --target-dir=/tmp/capstone22-full --datadir=/srv/mariadb22-restoresudo chown -R mysql:mysql /srv/mariadb22-restore# Start an isolated instance on a non-production port/socket.sudo -u mysql mariadbd --datadir=/srv/mariadb22-restore --socket=/tmp/mariadb22-restore.sock --port=33306 --pid-file=/tmp/mariadb22-restore.pid --log-error=/tmp/mariadb22-restore.err --skip-networking=0 &mariadb --socket=/tmp/mariadb22-restore.sock -e "SELECT VERSION(); SELECT COUNT(*) FROM servicehub22.ticket;"# Stop/remove this isolated target after the drill using your OS/service controls.3. Add PITR: backup plus retained binary logs
Point-in-Time Recovery (PITR) starts from a known backup and replays binary-log events only through the chosen recovery boundary. The backup’s binlog metadata tells you where replay can start; preserved binary logs provide the changes after that point.
read FIRST_BINLOG START_POSITION BACKUP_GTID < /tmp/capstone22-full/xtrabackup_binlog_infoprintf 'backup starts at %s position %s GTID %s' "$FIRST_BINLOG" "$START_POSITION" "$BACKUP_GTID"BINLOG_ARCHIVE=/var/recovery-binlogs# The archive is a protected copy of retained binlogs, not the live datadir.mapfile -t ALL_LOG_NAMES < <(find "$BINLOG_ARCHIVE" -maxdepth 1 -type f -name 'mariadb-bin.*' -printf '%f' | sort)BINLOG_PATHS=()for f in "${ALL_LOG_NAMES[@]}"; do [[ "$f" < "$FIRST_BINLOG" ]] && continue BINLOG_PATHS+=("$BINLOG_ARCHIVE/$f")doneprintf 'replay input: %s' "${BINLOG_PATHS[@]}"mariadb-binlog --verbose --base64-output=DECODE-ROWS "${BINLOG_PATHS[0]}" | less# Generate replay SQL into a FILE first; use the incident's verified stop boundary.mariadb-binlog --start-position="$START_POSITION" --stop-datetime='2026-08-20 21:15:00' "${BINLOG_PATHS[@]}" > /tmp/capstone22-replay.sql# Inspect the boundary before applying to the isolated restored target.less /tmp/capstone22-replay.sqlmariadb --host=127.0.0.1 --port=33306 < /tmp/capstone22-replay.sqlTimestamps depend on time-zone interpretation and transaction boundaries. Position/GTID options also have exact semantics and version caveats. For a real incident, identify the destructive transaction and commit boundary from the actual logs; do not paste this sample timestamp.
4. Optional topology-heavy lab: two-node GTID replication
services: primary: container_name: servicehub22-primary image: mariadb:12.3.2 environment: MARIADB_ROOT_PASSWORD: disposable-root command: - --server-id=221 - --log-bin=mariadb-bin - --binlog-format=ROW - --gtid-domain-id=22 - --gtid-strict-mode=ON ports: ["33221:3306"] replica: container_name: servicehub22-replica image: mariadb:12.3.2 environment: MARIADB_ROOT_PASSWORD: disposable-root command: - --server-id=222 - --log-bin=mariadb-bin - --log-slave-updates=ON - --relay-log=relay-bin - --gtid-domain-id=22 - --gtid-strict-mode=ON - --read-only=ON ports: ["33222:3306"]networks: default: name: servicehub22_netFor the cleanest learning path, start replication on fresh nodes before running the Lesson 2 migrations/seed on the primary. That avoids pretending an already-populated replica is consistent without a seed/backup step.
-- On primaryCREATE USER 'repl22'@'%' IDENTIFIED BY 'DISPOSABLE-repl-secret';GRANT REPLICATION REPLICA ON *.* TO 'repl22'@'%';-- On replica (fresh disposable node)STOP REPLICA;RESET REPLICA ALL;SET GLOBAL gtid_slave_pos='';CHANGE MASTER TO MASTER_HOST='primary', MASTER_USER='repl22', MASTER_PASSWORD='DISPOSABLE-repl-secret', MASTER_PORT=3306, MASTER_USE_GTID=slave_pos;START REPLICA;SHOW REPLICA STATUS\GThen apply the capstone migrations/seed to the primary and verify rows on the replica. In production, an existing dataset requires a trusted seed/backup and exact GTID/position alignment. Core async replication does not provide client routing, automatic promotion or fencing.
5. Monitoring: user impact first, database evidence second
| Layer | Signal | Alert intent |
|---|---|---|
| Application | request success/error rate; p95/p99 DB time; pool acquisition time | page on user impact or sustained SLO burn |
| Connections | Threads_running/connected; aborted connections; pool saturation | detect queueing/exhaustion before total outage |
| InnoDB | lock waits/deadlocks; buffer/read/write; dirty/redo trends | explain contention or storage pressure |
| Disk | filesystem free%; latency; IOPS/throughput; inode/file limits | prevent full disk and correlate DB waits |
| Async replica | IO/SQL thread state; GTID/lag; Last_IO_Error/Last_SQL_Error | detect broken/stale recovery copy |
| Galera variant | wsrep_ready, cluster_status, local_state_comment, flow-control | detect quorum/state/backpressure |
| Backup | last successful backup + last successful restore drill | a backup job success alone is not enough |
| Binlog | oldest retained log vs RPO/replica outage requirement | avoid losing PITR/rejoin window |
SELECT NOW(6), VERSION(), @@server_id, @@read_only;SHOW GLOBAL STATUS WHERE Variable_name IN ('Threads_connected','Threads_running','Created_tmp_disk_tables', 'Innodb_buffer_pool_reads','Innodb_buffer_pool_read_requests', 'Innodb_buffer_pool_pages_dirty','Innodb_row_lock_current_waits');SHOW PROCESSLIST;SHOW ENGINE INNODB STATUS\GSHOW BINARY LOGS;-- replica only: SHOW REPLICA STATUS\G-- Galera only: SHOW GLOBAL STATUS LIKE 'wsrep_%';6. Write runbooks as decision trees, not prose slogans
| Incident | First evidence | Unsafe shortcut | Recovery direction |
|---|---|---|---|
| Backup failure | tool exit/log + destination space + privileges | delete old good backup immediately | preserve last good copy; fix cause; rerun; restore-test |
| Replica lag/breakage | IO vs SQL state, GTID, errors, host resources | promote because Seconds_Behind looks high | classify receive/apply/data error; repair/rebuild |
| Primary/node loss | reachability, old-primary fencing, replica position | allow both nodes to accept writes | fence old writer; prove candidate; route clients deliberately |
| Lock storm | processlist, InnoDB trx/locks, application deploy timeline | kill random sessions repeatedly | identify blocker/transaction shape; drain/retry safely |
| Disk pressure | filesystem + binlog/backup/temp/table growth | rm database files by hand | stop growth, move/purge only safe artifacts, restore headroom |
| Upgrade issue | error log, version/config/plugin diff, canary metrics | downgrade datadir blindly | follow tested rollback boundary or restore/rebuild |
7. Upgrade gate integrated with recovery
Before changing a production node: [ ] source/target release notes and upgrade path reviewed [ ] server + mariadb-backup + connector + Galera/plugin compatibility recorded [ ] config diff finds removed/renamed options [ ] fresh backup exists AND isolated restore passed [ ] binlog/GTID coordinates retained [ ] replica/Galera rolling order is documented for these exact versions [ ] application canary + representative benchmark baseline captured [ ] rollback boundary states what is reversible and what requires restore/rebuildAfter change: [ ] error log clean enough to accept [ ] system tables / mariadb-upgrade requirements satisfied for target [ ] grants, schema, critical plans and application transactions pass [ ] p95/p99/error rates compared against pre-change baselineCheck your reasoning
- Why is --prepare part of backup correctness?
- What does xtrabackup_binlog_info contribute to PITR?
- Why does a healthy replica not remove the need for PITR?
- What must happen before promoting an async replica?
- Why alert on restore drills, not only backup jobs?
Review the answers
-
Physical files are copied at different times; prepare applies recovery so the backup becomes consistent and restorable.
-
It records binary-log/GTID coordinates associated with the backup so replay can start from the correct recovery point.
-
A replica can reproduce destructive SQL; PITR lets you restore a prior base and stop replay before the bad transaction.
-
Prove its state/position and fence the old writer so two independent primaries cannot accept conflicting writes.
-
A successful backup process does not prove files, credentials, tooling and procedures can restore within the required RTO.
8. Wrong approach: “HA means we can restore later”
High availability optimizes continuity; disaster recovery protects recoverability. Postponing restore tests because a replica or cluster is green creates correlated risk: the same schema mistake, bad DML, credential error or version incompatibility may exist everywhere. The repair is a calendar of automated backups plus regular isolated restore/PITR drills with measured RPO/RTO.
Production judgment and bridge to Lesson 5
The capstone now has a control plane: backup, PITR, HA evidence, alerts, runbooks and an upgrade gate. Lesson 5 deliberately breaks it. The goal is not chaos for its own sake; it is to discover which guarantees exist only in documentation and which survive a timed, observable incident.