Execute Failure/Recovery Drills and Defend the Final MySQL Production Design

Drill ID / timestamp: _____________________________Failure injected: _________________________________Expected detector: ________________________________Detected at: ______________________________________Operator/application action: ______________________Recovery declared at: _____________________________Measured detection time: __________________________Measured recovery time: ___________________________RPO / lost-or-ambiguous operations: _______________Business invariants checked: ______________________MySQL/Router/log evidence: ________________________SLO met? yes/no/unknown: __________________________What made recovery harder: ________________________Follow-up owner and change: _______________________

# Expected failure because the account requires TLS:mysql -h 127.0.0.1 -P 3306 -u sh_cap_app -p --ssl-mode=DISABLED# Positive path; for production use VERIFY_CA / VERIFY_IDENTITY with trusted CA:mysql -h 127.0.0.1 -P 3306 -u sh_cap_app -p --ssl-mode=REQUIRED

SELECT USER(),CURRENT_USER(),CURRENT_ROLE(),CONNECTION_ID();SHOW SESSION STATUS LIKE 'Ssl_cipher';SHOW GRANTS FOR CURRENT_USER;SELECT PROCESSLIST_ID,PROCESSLIST_USER,PROCESSLIST_HOST,PROCESSLIST_DBFROM performance_schema.threadsWHERE PROCESSLIST_ID=CONNECTION_ID();

USE servicehub_capstone;START TRANSACTION;UPDATE work_orders SET priority=5 WHERE work_order_id=1;-- Wait here, then attempt after Session B locks row 2:UPDATE work_orders SET priority=5 WHERE work_order_id=2;

USE servicehub_capstone;START TRANSACTION;UPDATE work_orders SET priority=4 WHERE work_order_id=2;-- Now attempt row 1; one transaction should become the deadlock victim.UPDATE work_orders SET priority=4 WHERE work_order_id=1;COMMIT;

SHOW ENGINE INNODB STATUS;SELECT THREAD_ID,EVENT_ID,STATE,TRX_ID,ACCESS_MODE,ISOLATION_LEVELFROM performance_schema.events_transactions_current;SELECT *FROM performance_schema.data_lock_waits;

1. Record baseline at concurrency 8.2. Run the same workload at concurrency 32 (or the highest safe laptop value).3. Capture p95/p99, error rate, Threads_running, CPU, storage latency, statement digests.4. Do NOT raise max_connections as the first reaction.5. Restore normal concurrency and confirm latency/error rate recover.6. Record whether the bottleneck was CPU, I/O, locks, bad SQL, pool pressure, or unknown.

SHOW GLOBAL STATUS WHERE Variable_name IN('Threads_connected','Threads_running','Connection_errors_max_connections', 'Aborted_clients','Aborted_connects');SELECT EVENT_NAME,COUNT_STAR,       ROUND(SUM_TIMER_WAIT/1000000000000,3) AS total_secondsFROM performance_schema.events_waits_summary_global_by_event_nameORDER BY SUM_TIMER_WAIT DESCLIMIT 15;

USE servicehub_capstone;SELECT COUNT(*) AS open_before FROM work_orders WHERE status='OPEN';SELECT NOW(6) AS incident_marker;-- Intentional lab mistake: committed logical damage.UPDATE work_orders SET status='CANCELLED' WHERE status='OPEN';COMMIT;SELECT COUNT(*) AS open_after FROM work_orders WHERE status='OPEN';SELECT COUNT(*) AS cancelled_after FROM work_orders WHERE status='CANCELLED';

var cluster = dba.getCluster('serviceHubCapstone')cluster.status({extended: 1})// Identify the current primary from status output.// Stop ONLY one disposable sandbox instance using the supported sandbox command,// or stop its mysqld process through your local lab method.// Then re-run:cluster.status({extended: 1})

USE servicehub_capstone;-- Every work order references a valid asset at the same site.SELECT COUNT(*) AS cross_site_mismatchesFROM work_orders wJOIN assets a ON a.asset_id=w.asset_idWHERE a.site_id<>w.site_id;-- Every event has a work order (also enforced by FK, checked explicitly here).SELECT COUNT(*) AS orphan_eventsFROM work_order_events eLEFT JOIN work_orders w ON w.work_order_id=e.work_order_idWHERE w.work_order_id IS NULL;-- No duplicate integration idempotency keys are possible because PK enforces it.SELECT COUNT(*) AS duplicate_keysFROM (  SELECT idempotency_key FROM integration_requests  GROUP BY idempotency_key HAVING COUNT(*)>1) d;SELECT status,COUNT(*)FROM work_orders GROUP BY status ORDER BY status;

1. ADR + SLO/workload/capacity assumptions2. schema DDL + migration ledger/checksums3. index portfolio + EXPLAIN/EXPLAIN ANALYZE evidence4. security roles/accounts/TLS/session contract5. repeatable load-test code + >=3 run artifacts + OS/MySQL evidence6. backup manifest/checksum + latest successful restore test7. PITR preflight + retention/runway evidence8. topology diagram + cluster/replication/Router evidence9. dashboard/alerts + escalation owners10. incident runbooks + completed failure-drill records11. upgrade/rollback plan + point-of-no-return authority12. unmet SLOs, cost/complexity, known risks, revisit triggers, next improvements

DROP DATABASE IF EXISTS servicehub_capstone_restore;DROP DATABASE IF EXISTS servicehub_capstone;DROP USER IF EXISTS 'sh_cap_app'@'127.0.0.1';DROP ROLE IF EXISTS 'r_sh_app_rw','r_sh_app_ro','r_sh_operator';