Project E — Automated, Tested Backup/DR Pipeline

Overview

Turn Project 3's manual mysqldump --single-transaction | gzip runbook into a scheduled, automated pipeline with retention policy and — critically — automated, tested restores, not just backups that are assumed to work.

Overlaps almost entirely with dba-project-ideas.md #3 ("Automated backup + retention policy") — that doc is the more detailed version of this exact project (specific retention tiers, Airflow DAG framing, MinIO storage reusing the existing scoped-key pattern). Read that doc's version first; this entry mainly adds the "why this maps to a 2026 hiring trend" framing (disaster recovery / verified restores called out specifically in this session's research) and cross-region/object-storage strategy as explicit scope.

What It Demonstrates

Disaster recovery with verified restores (not just "we have backups") is called out specifically as a DBA-portfolio differentiator in this session's research. This is also the lowest-new-infrastructure project of the five — it finishes something already started (Project 3) rather than opening new ground.

Prerequisites / What We Need

• An Airflow DAG (matching the existing pipeline pattern) or cron job to run backups on a schedule.
• A retention policy decided up front (e.g. daily for 7 days, weekly for a month — dba-project-ideas.md #3 already proposes this exact scheme).
• MinIO storage, reusing the existing least-privilege scoped-key pattern already used by 3+ other projects/features this session.
• A way to automatically verify a restore — not "I ran a restore once and it worked" (Project 3 already proved that manually) but a scheduled job that restores into a scratch database and checks row counts/schema match, on a recurring basis.

Plan

1. Convert the manual backup command into a scheduled Airflow DAG task.
2. Implement the retention policy: automatic pruning of backups older than the retention window, per tier.
3. Store backups in MinIO via a scoped key (new dedicated key, not reused from another project, matching this portfolio's least-privilege pattern).
4. Build an automated restore-verification job: on a schedule, restore the latest backup into a scratch DB, check it matches expectations, log pass/fail.
5. Deliberately let a restore-verification fail once (e.g. corrupt a test backup on purpose) and confirm the failure is actually detected and surfaced, not silently ignored.
6. Document the full cycle: backup → retention → verified restore, with evidence (logs) that it's run successfully on a schedule, not just once.

Deliverables

• Airflow DAG for scheduled backup + retention + verification.
• MinIO bucket + scoped key for backup storage, documented.
• A log/report showing the verification job has run successfully multiple times on schedule (not just once by hand).