Skip to content

Database Backup

Purpose

Nightly encrypted MySQL backup to Cloudflare R2, per environment - and how to run one manually before a risky migration.

Status: disabled by default

# helm/akshayabazaar/values.yaml
backup:
  enabled: false
Enable per-environment in that environment's values-<env>.yaml.

Architecture/context

mysqldump (read-only against the external DB)
   |
   v
gzip
   |
   v
AES-256 encrypt
   |
   v
upload to R2 (WRITE-ONLY - this script never deletes anything, anywhere)

Retention (daily/weekly/monthly expiry) is enforced entirely by R2's own lifecycle rules (scripts/r2-lifecycle-policy.json), not by the backup script - a bug in the script can waste storage but can never delete a backup out from under a rotation still in progress.

Files involved

helm/akshayabazaar/files/mysql-backup.sh, helm/akshayabazaar/templates/backup/mysql-backup-cronjob.yaml, helm/akshayabazaar/templates/backup/mysql-backup-scripts-configmap.yaml, helm/akshayabazaar/examples/mysql-backup-secret.example.yaml, scripts/mysql-backup.sh, scripts/r2-lifecycle-policy.json, scripts/apply-r2-lifecycle.sh.

Configuration (Secret, never a literal value in any values file)

DB_HOST: "<DB_HOST>"
DB_PORT: "3306"
DB_NAME: "ecommerce_db"
DB_USER: "<READ_ONLY_DB_USER>"
DB_PASSWORD: "<MYSQL_PASSWORD>"
BACKUP_ENCRYPTION_PASSPHRASE: "<AES_256_PASSPHRASE>"   # NOT the same secret as any DB/API credential
R2_ACCOUNT_ID: "<CLOUDFLARE_ACCOUNT_ID>"
R2_ACCESS_KEY_ID: "<R2_ACCESS_KEY_ID>"
R2_SECRET_ACCESS_KEY: "<R2_SECRET_ACCESS_KEY>"
R2_BACKUP_BUCKET: "akshayabazaar-backups-<env>"

A separate R2 bucket per environment, and a separate bucket entirely from the media bucket - blast-radius isolation, so a compromise of backup credentials never also exposes the live application's own DB/JWT/payment secrets, and vice versa. Staging's bucket is declared in terraform/environments/staging as r2_backup_bucket_name with the default akshayabazaar-backups-staging, which is distinct from production's documented akshayabazaar-backups-production name. Terraform outputs only the bucket name and S3 endpoint; access keys are still generated manually after the bucket exists and stored only in the backup Secret. ENVIRONMENT_LABEL (optional) prefixes every uploaded key with <label>/ as a second, defense-in-depth layer even if a bucket were ever pointed at by two environments by mistake.

Commands

Read-Only Inspection

# Confirm the CronJob is scheduled (read-only)
kubectl get cronjob -n akshayabazaar-staging
kubectl get cronjob -n akshayabazaar-production

# Watch job execution and read logs (read-only)
kubectl get jobs -n akshayabazaar-staging -w
kubectl logs -n akshayabazaar-staging -l job-name=mysql-backup-... --tail=100

Manual Triggering (Gated Invariant)

Staging and production backups run automatically via scheduled Kubernetes CronJobs. Operators must not run ungated kubectl create job directly against staging or production namespaces. Any manual backup invocation against staging or production must be gated via bash scripts/pre-deploy-gate.sh <namespace> or dispatched through the authoritative CI/CD pipeline.

For local development or disposable test clusters only:

# Local/Test environments only (NOT staging/production)
kubectl create job -n akshaya-local --from=cronjob/akshaya-local-mysql-backup manual-backup-$(Get-Date -Format yyyyMMdd-HHmmss)

# Local disposable test run directly outside Kubernetes (test DB only):
DB_HOST=127.0.0.1 DB_USER=root DB_PASSWORD=test \
BACKUP_ENCRYPTION_PASSPHRASE=test \
R2_ACCOUNT_ID=test R2_ACCESS_KEY_ID=test R2_SECRET_ACCESS_KEY=test R2_BACKUP_BUCKET=test \
  ./helm/akshayabazaar/files/mysql-backup.sh

Validation / expected result

Backup pipeline validated end to end (mysqldump → gzip → openssl encrypt → decrypt → restore → row/table counts matched) against a disposable database - see repository BACKUP_RESTORE.md "Restore validation" for the full transcript.

Troubleshooting

  • CronJob never runs - confirm backup.enabled: true for this environment's values file, and that the Secret referenced by backup.existingSecretName actually exists in the namespace.
  • Job fails at the mysqldump step - almost always credentials (a read-only DB user is strongly recommended, never the application's own DB_USER) or network reachability to the data VM.
  • Job fails at the upload step - R2 access key/secret or bucket name mismatch; confirm against the Secret, never by guessing a new value.

Rollback / recovery (restore)

Restoring from a backup is a deliberate, manual, human-run operation - never automated by this script (write-only, by design). See repository BACKUP_RESTORE.md for the full restore procedure. Never restore into staging/production without confirming this is the intended recovery action first - see Operations → Rollback for when a Helm/application rollback is sufficient instead of a database restore.

Security considerations

  • Read-only DB credentials recommended for the backup user - it only ever needs mysqldump --single-transaction (SELECT, SHOW VIEW, TRIGGER, LOCK TABLES, EVENT), never write access.
  • Backups are AES-256 encrypted before upload - losing the encryption passphrase makes every existing backup permanently unrecoverable (by design - store it in a password manager / secret manager with restricted access, in addition to wherever the Kubernetes Secret lives).
  • Separate R2 bucket, separate Secret, from the application's own media/DB credentials.

Shared-VPS production launch prerequisites

The shared-VPS deployment runner (scripts/deploy-production-vps.sh) uses a separate age-encrypted, pre-migration/baseline backup path. Production launch is prohibited until all of the following are independently evidenced:

  • a production-only backup bucket, distinct from public/private media and staging;
  • dedicated, bucket-scoped backup credentials that are not the media credentials;
  • a bucket/prefix lock covering production/ for the approved retention period;
  • the matching age private identity stored offline and absent from the VPS;
  • a tested retrieve/decrypt/extract/manifest/recovery procedure;
  • the production-only Data Protection key volume vayzomart_production_data_protection_keys (docker-compose.production.yml), never populated from or shared with staging's vayzomart_staging_data_protection_keys; the API refuses to start without it mounted and writable. It is not part of the database backup: losing it invalidates only Data Protection-protected payloads (no business data), so do not copy it off-host or between environments.

R2 bucket-lock inspection uses Cloudflare's management API. The deployment VPS must not receive a broad Cloudflare management token merely to automate that check. An operator verifies the lock out of band, records the non-secret evidence/change reference in PRODUCTION_BACKUP_BUCKET_LOCK_EVIDENCE_ID, and sets PRODUCTION_BACKUP_BUCKET_LOCK_VERIFIED=true. The deployment script fails closed without both values, but the boolean is an attestation, not an API proof.

Retention duration remains subject to the approved accounting/legal policy. Lifecycle configuration, age private-key rotation, offline-key recovery, and periodic restore cadence are operational follow-ups; the deployment script never deletes backup objects. SIGINT, SIGTERM, and ordinary exits clean local temporary archive/encrypted/readback files. SIGKILL, a process/host crash, or power loss can still leave permission-restricted temporary files for operator cleanup; the local SQL dump and manifest intentionally remain for rollback.