Chapter 22 · Production Capstone: Design, Secure, Scale, Tune, and Recover MySQL

Implement Backups, PITR, Replication/HA, Monitoring, Alerts, and Incident Runbooks

Prove recoverability and operational readiness with backup/restore, PITR prerequisites, an optional local InnoDB Cluster/Router topology, dashboards, alerts, and incident runbooks.

Advanced capstone180–300 minServiceHub production capstoneMySQL Community Server 8.4.10 LTSInnoDBsingle local server mandatoryMySQL Shell 8.4.10 + Router 8.4.10 optional HA extension3-member single-primary InnoDB Cluster production targetLast reviewed: August 2026

Learning outcomes

High availability is not recoverability, monitoring is not an incident response, and a backup file is not proof that data can be restored. This lesson connects those controls. The mandatory single-node lab creates and restores a logical backup, verifies binary-log/PITR prerequisites, records checksums and business invariants, and builds alert/runbook evidence. A free optional topology extension deploys the capstone's three-member InnoDB Cluster and Router locally using MySQL Shell sandbox instances.

Create and restore a consistent logical backup into a disposable validation schema and verify known business invariants.

Prove binary-log, GTID, retention, and position prerequisites for point-in-time recovery without pretending a missing log interval is recoverable.

Build or inspect the chosen three-member single-primary InnoDB Cluster and Router topology where local resources permit.

Define a small dashboard/alert set for backup age, replication/cluster health, latency/errors, capacity, connections, and resource saturation.

Write executable runbooks for backup failure, replication/cluster lag, primary loss, and capacity/performance incidents.

Safety boundary

Restore, PITR, replication, and failover experiments target disposable schemas/instances only. Never replay binary logs into the damaged production source while investigating an incident, and never promote a candidate until the previous writer is fenced or proven unavailable.

Create a backup that has an acceptance test

For an InnoDB-only logical lab, mysqldump --single-transaction provides a consistent transactional snapshot without taking a global read lock for the duration. The consistency guarantee does not extend to nontransactional tables, and concurrent DDL can invalidate the assumptions. The capstone records the exact command and validates the restored state.

text · logical backup — mysql client tools; password is prompted

# Linux/macOS shell or Windows terminal with MySQL bin directory on PATH:mysqldump -h 127.0.0.1 -P 3306 -u root -p \  --single-transaction --routines --events --triggers \  --set-gtid-purged=OFF servicehub_capstone > servicehub_capstone.sql# Linux/macOS checksum:sha256sum servicehub_capstone.sql# Windows PowerShell checksum:Get-FileHash .\servicehub_capstone.sql -Algorithm SHA256

The hash detects artifact corruption/change; it does not prove database correctness. Record business invariants before restore.

sql · source-side recovery invariants

USE servicehub_capstone;SELECT COUNT(*) AS sites FROM sites;SELECT COUNT(*) AS assets FROM assets;SELECT COUNT(*) AS work_orders FROM work_orders;SELECT COUNT(*) AS events FROM work_order_events;SELECT SUM(status='OPEN') AS open_orders FROM work_orders;SELECT MIN(work_order_id),MAX(work_order_id) FROM work_orders;

Restore into a disposable target and compare

sql · prepare a clean validation database

DROP DATABASE IF EXISTS servicehub_capstone_restore;CREATE DATABASE servicehub_capstone_restore  CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

text · restore the dump into the validation database

mysql -h 127.0.0.1 -P 3306 -u root -p servicehub_capstone_restore < servicehub_capstone.sql

sql · restore acceptance — compare row and schema invariants

SELECT COUNT(*) AS work_ordersFROM servicehub_capstone_restore.work_orders;SELECT COUNT(*) AS eventsFROM servicehub_capstone_restore.work_order_events;SELECT COUNT(*) AS source_tablesFROM information_schema.TABLESWHERE TABLE_SCHEMA='servicehub_capstone';SELECT COUNT(*) AS restored_tablesFROM information_schema.TABLESWHERE TABLE_SCHEMA='servicehub_capstone_restore';SHOW CREATE TABLE servicehub_capstone_restore.work_orders;

A real restore test also exercises application canaries, foreign-key consistency, routines/events if used, account/role reconstruction strategy, and encrypted secret/key dependencies. The validation database is never treated as a replacement until acceptance passes.

Prove PITR prerequisites before an incident

sql · binary-log and GTID recovery preflight

SELECT @@log_bin AS log_bin,       @@binlog_format AS binlog_format,       @@gtid_mode AS gtid_mode,       @@enforce_gtid_consistency AS enforce_gtid_consistency,       @@binlog_expire_logs_seconds AS binlog_retention_seconds;SHOW BINARY LOG STATUS;SHOW BINARY LOGS;SELECT @@GLOBAL.gtid_executed AS gtid_executed,       @@GLOBAL.gtid_purged AS gtid_purged;

PITR requires a usable full-backup boundary plus every required binary-log interval after that boundary. If a required log was purged or never captured, changing a retention variable after the incident cannot recreate it. The safe response is to quantify the resulting RPO gap, not invent a recovery chain.

Restore-oriented failure: detect a broken chain

text · recovery manifest — intentionally remove one copied log only in the lab

backup: servicehub_capstone.sqlbackup_sha256: <recorded hash>backup_end_position: <record from backup procedure>required_binlogs:  - binlog.000042  - binlog.000043  - binlog.000044LAB FAILURE:- copy the recovery artifacts into a disposable directory;- remove binlog.000043 from the COPY, never from the server/data directory;- recovery preflight must stop because the interval is incomplete;- restore the missing copied artifact and verify its checksum before replay.

The correct behavior is to fail recovery preflight, not to skip to binlog.000044 and declare success. A silent gap can create a logically inconsistent database even when the replay command exits successfully.

Optional free HA extension: three-member InnoDB Cluster + Router

Oracle's MySQL Shell documentation includes local sandbox deployment specifically for testing. Three instances are used because a three-member group can lose one member and still retain a majority. Sandbox instances on one physical host demonstrate mechanics, not real failure-domain independence.

javascript · MySQL Shell JavaScript — local sandbox cluster

// Run in mysqlsh JavaScript mode. Use disposable sandbox passwords.dba.deploySandboxInstance(3310)dba.deploySandboxInstance(3320)dba.deploySandboxInstance(3330)shell.connect('root@localhost:3310')var cluster = dba.createCluster('serviceHubCapstone')cluster.addInstance('root@localhost:3320')cluster.addInstance('root@localhost:3330')cluster.status({extended: 1})

text · bootstrap Router against the local cluster

# Use the actual bootstrap account and directory appropriate for your OS.mysqlrouter --bootstrap root@localhost:3310 --directory mysql-router-capstone# Start using the generated script/config, then connect through the printed RW port.# The common sandbox classic RW endpoint is often 6446, but use Router's output.mysql -h 127.0.0.1 -P <ROUTER_RW_PORT> -u sh_cap_app -p --ssl-mode=REQUIRED

In production, Cluster members must occupy independent failure domains appropriate to the SLO, and Router is normally deployed near the application tier. AdminAPI-managed Cluster configuration should not be manually drifted with raw Group Replication settings.

Observe HA as state, not as an adjective

sql · replication / Group Replication evidence

SELECT MEMBER_ID,MEMBER_HOST,MEMBER_PORT,MEMBER_STATE,MEMBER_ROLE,MEMBER_VERSIONFROM performance_schema.replication_group_membersORDER BY MEMBER_HOST,MEMBER_PORT;SELECT CHANNEL_NAME,SERVICE_STATE,LAST_ERROR_NUMBER,LAST_ERROR_MESSAGEFROM performance_schema.replication_connection_status;SELECT CHANNEL_NAME,SERVICE_STATE,LAST_ERROR_NUMBER,LAST_ERROR_MESSAGEFROM performance_schema.replication_applier_status_by_coordinator;SELECT @@GLOBAL.gtid_executed;

For asynchronous replication, use the corresponding receiver/applier Performance Schema tables and SHOW REPLICA STATUS-style evidence. “Connected” does not mean caught up, and “caught up” does not prove application/business correctness.

Dashboard and alert contract

Signal	Alert intent	First evidence/runbook
API error/latency SLO burn	customer-visible symptom	application + Router + statement digest timeline
Cluster member/quorum state	availability risk	cluster.status + replication_group_members + logs
replication/applier lag/errors	freshness/failover risk	receiver/applier state, GTIDs, worker errors
backup age / restore-test age	recoverability risk	backup manifest + most recent successful restore drill
binlog retention/runway	PITR-chain risk	oldest required log vs retention/storage
disk capacity/runway	hard availability risk	filesystem + data/binlog/backup growth forecast
Threads_running / connection pressure	concurrency saturation symptom	pool metrics + MySQL thread/wait evidence
redo/I/O/temp-work pressure	storage/performance cause candidate	status deltas + Performance Schema + OS latency

A threshold must be actionable. Static “CPU > 80%” alerts without workload/latency context often create noise. Prefer SLO symptoms plus cause-oriented signals whose escalation path is known.

Runbooks are decision trees, not command dumps

text · primary-loss runbook — abbreviated acceptance version

Trigger: primary unreachable / Router reports no RW destination1. Freeze risky deploy/DDL; capture incident timestamp and client symptoms.2. Determine topology state: quorum, member roles, GTID state, network reachability.3. Fence the former writer if there is any possibility it can still accept traffic.4. For InnoDB Cluster, let supported election/AdminAPI/Router mechanisms operate;   do not force quorum unless the documented exceptional conditions are met.5. Verify the new primary through Router: write canary + read-back + business invariants.6. Check old primary rejoin/recovery state before returning it to service.7. Record measured availability impact, ambiguous client transactions, and follow-up.STOP CONDITIONS:- no majority/quorum and old writer not safely fenced;- unexplained GTID divergence;- application canary or business invariants fail.

Equivalent runbooks are included for backup failure, replication lag, and capacity/performance incidents. Each has a trigger, evidence to gather, safe actions, stop conditions, verification, and post-incident artifacts.

Knowledge check

Why restore a backup into a separate validation database?
What makes PITR possible?
Why are three Cluster members used?
Does a local three-sandbox Cluster prove production HA?
What should every incident runbook include?

Reveal answers

It tests recoverability without overwriting the source and allows explicit comparison before any cutover.
A valid base backup/recovery boundary plus an unbroken sequence of required binary-log events to the chosen stop point.
A three-member group can tolerate one member loss while retaining a majority.
No. It proves mechanics on one host, not independent failure domains or production capacity.
Trigger, evidence, safe actions, stop conditions, verification, ownership/escalation, and recorded outcome.

Authoritative references

Keep knowledge open

Help the academy stay free and grow.

If these tutorials save you time, a small donation supports new lessons, technical review, diagrams, examples, and long-term maintenance.

ETHEthereum / ERC-20 only

0x716c4Ab160C4B66F31a28AE2448BfF68fc3a2ef0

Send only assets compatible with the Ethereum/ERC-20 network. Do not send TRC-20/TRON assets.