PITR — Point-in-Time Recovery

This is the restore side of PITR. It pairs with:

An S3 lifecycle rule on the bucket, which expires shipped binlogs at the end of the retention window
The full backup CronJob (taken with --source-data=2 --single-transaction)
The binlog-shipping CronJob (ships every 15 min)

The chart’s pitr-restore Job runs scripts/pitr-restore.sh to combine the two and restore a database to any point in time within the 30-day retention window.

Safety contract: the Job will refuse to render if targetHost equals this release’s primary service. PITR is always restored into a separate staging MySQL release, never on top of production.

Two branches. With backup.layout: legacy, the Job runs scripts/pitr-restore.sh against the legacy S3 paths described below. With backup.layout: platform (the default), it runs the backup tools image’s restore.sh against this release’s per-region prefix (<backup.s3.prefix>/<clusterRegion>/dumps|binlogs), into a separate single-node target, for the databases in pitr.restore.targetDatabases. It is named <release>-restore-<pitr.restore.id>. Set those values for one restore, wait for the Job, then upgrade again without them. The rest of this page describes the legacy branch.

Architecture

┌──────────────────────────────────────────────────────────────────────────┐
│  Production primary MySQL                                                │
│      │                                                                   │
│      ├─ daily mysqldump (--source-data=2)  ──►  S3: db/db-DD-Mon-YYYY-HH-MM.sql.tar.gz
│      │                                                                   │
│      └─ binlog-shipping (every 15 min)     ──►  S3: binlogs/release/mysql-bin.NNNNNN.gz
│                                                                          │
├──────────────────────────────────────────────────────────────────────────┤
│                                                                          │
│  Restore Job (one-shot, runs in the chart's namespace)                  │
│                                                                          │
│  1. List backups under S3_BACKUP_PREFIX, pick latest ≤ TARGET_DATETIME  │
│  2. Download + decompress full backup                                    │
│  3. Parse SOURCE_LOG_FILE / SOURCE_LOG_POS from dump header              │
│  4. List binlogs under S3_BINLOG_PREFIX from that file onwards          │
│     • detect ordinal gaps → fail fast                                    │
│     • download + gunzip                                                  │
│  5. mysql < dump.sql                              ──►  staging MySQL    │
│  6. mysqlbinlog --start-pos --stop-datetime |     ──►  staging MySQL    │
│       mysql                                                              │
│  7. Verify table count, print summary                                    │
│                                                                          │
└──────────────────────────────────────────────────────────────────────────┘

Files

Path	Role
`scripts/pitr-restore.sh`	Single source of truth for the restore logic. Bash, runs inside the `cicd` image (mysql 8.4 + mysqlbinlog + s3cmd + GNU date).
`templates/pitr-restore-configmap.yaml`	Always-rendered ConfigMap that wraps the script via `.Files.Get` so the Job can mount it.
`templates/pitr-restore-job.yaml`	One-shot Kubernetes Job, gated by `pitr.restore.enabled`. Refuses to render if `targetHost` matches the chart’s primary service. `backoffLimit: 0`, `ttlSecondsAfterFinished: 3600`.
`values.yaml` `pitr.restore.*`	User-facing config knobs.

Quick start — restoring to a staging release

You need a separate MySQL release in the same namespace to restore into. The simplest layout:

NS=mysql-pitr-test
# 1. Production release (already exists, the one being restored FROM)
PROD=prod-db

# 2. Spin up an empty staging release in the same namespace
helm install staging-db helm-charts/charts/mysql \
  -n $NS --create-namespace \
  -f - <<EOF
mode: standard
mysql:
  database: myapp
  rootPassword: stagingpw   # used only for this throwaway instance
networkPolicy:
  enabled: true
EOF

# 3. Wait for staging-db-mysql-0 to be Ready
kubectl rollout status sts/staging-db-mysql -n $NS

# 4. Trigger the restore on the PRODUCTION release.
#    The Job is templated by the prod release because that's where the
#    backup S3 creds live; it RESTORES INTO the staging release.
helm upgrade $PROD helm-charts/charts/mysql -n $NS \
  --reuse-values \
  --set pitr.restore.enabled=true \
  --set 'pitr.restore.targetDatetime=2026-05-15 12:00:00' \
  --set pitr.restore.targetHost=staging-db-mysql \
  --set pitr.restore.targetDatabase=myapp

# 5. Watch
kubectl logs -n $NS job/$PROD-mysql-pitr-restore -f

# 6. When the Job finishes, clean up
helm upgrade $PROD helm-charts/charts/mysql -n $NS \
  --reuse-values \
  --set pitr.restore.enabled=false

The Job auto-deletes 1 hour after completion (ttlSecondsAfterFinished) so forgetting step 6 is mostly cosmetic.

Values reference

Key	Required	Default	Notes
`pitr.restore.enabled`	yes (to trigger)	`false`	Setting to `true` renders the Job on next `helm upgrade`.
`pitr.restore.targetDatetime`	yes	`""`	Format: `"YYYY-MM-DD HH:MM:SS"` UTC. Render-time `fail` if empty.
`pitr.restore.targetHost`	yes	`""`	DNS name of the staging MySQL primary service. Refused at render time if equal to the chart’s primary service name — chart never overwrites its own primary.
`pitr.restore.targetPort`	no	`3306`
`pitr.restore.targetDatabase`	yes if `mysql.database` is empty	`""`	Defaults to `mysql.database` if unset.
`pitr.restore.targetRootPasswordSecret`	no	`""`	Defaults to `<targetHost>` (assumes staging is another release of this chart in this namespace).
`pitr.restore.targetRootPasswordKey`	no	`"root-password"`
`pitr.restore.s3BackupPrefix`	no	`"<targetDatabase>/"`	Where the full backup tarball lives.
`pitr.restore.s3BinlogPrefix`	no	`"<binlogShipping.s3.prefix>/<release-fullname>/"`	Where the binlogs live. Aligns with the binlog-shipping CronJob’s `S3_PREFIX` (instance-id prefix, no `/{db}/` segment).
`pitr.restore.image`	no	Set in each release	Must have mysql-client 8.4, mysqlbinlog, s3cmd, GNU date.
`pitr.restore.keepWorkDir`	no	`false`	Set to `true` to skip cleanup of `/tmp/pitr-restore` — useful for post-mortems on failed restores.
`pitr.restore.extraEnv`	no	`[]`	Extra env vars (e.g. HTTP proxy) appended to the Job container.
`pitr.restore.resources`	no	`{}`	Standard Kubernetes resource limits.

How the script picks the right backup

Backup filenames produced by the db-backup image follow:

s3://<bucket>/<db>/<db>-DD-Mon-YYYY-HH-MM.sql.tar.gz

Example: s3://my-backups/myapp/myapp-23-Apr-2026-14-30.sql.tar.gz.

The script:

Lists every object under S3_BACKUP_PREFIX (which defaults to <db>/).
Filters to filenames matching <db>-DD-Mon-YYYY-HH-MM.sql.tar.gz.
Parses the date from each filename, converts to UTC epoch.
Picks the largest epoch that is ≤ TARGET_DATETIME.

If no such backup exists, the script fails with a clear message — your TARGET_DATETIME is older than any retained backup.

How the script picks the right binlogs

After parsing SOURCE_LOG_FILE (e.g. mysql-bin.000123) and SOURCE_LOG_POS from the dump header, the script:

Lists every object under S3_BINLOG_PREFIX.
Filters to objects matching <prefix>NNNNNN.gz.
Walks the list in numeric order, starting at the ordinal of SOURCE_LOG_FILE.
If there’s a gap in the sequence, the script fails fast — a missing binlog means PITR is impossible without manual intervention.
Downloads + gunzips each one into /tmp/pitr-restore/binlogs/.

The replay command:

mysqlbinlog \
    --start-position=<SOURCE_LOG_POS> \
    --stop-datetime="<TARGET_DATETIME>" \
    --database=<RESTORE_DB> \
    mysql-bin.000123 mysql-bin.000124 ... \
  | mysql --ssl-mode=PREFERRED -h <RESTORE_HOST> -P 3306 -u root -p<...>

–database caveat: the --database flag filters by the default database at the time of the SQL statement. Cross-database transactions (INSERT INTO other_db.t ...) on the restore target database are skipped. Where each release holds a single application’s single primary database, this is usually safe, but if you have cross-database writes, restore the whole server instead — drop --database and restore to a totally empty instance.

Failure modes

Failure	Surface	Recovery
`targetHost` equals primary	Render-time `fail` in `pitr-restore-job.yaml`	Point `targetHost` at a separate staging release.
`targetDatetime` older than oldest retained backup	Script fails at step 1	Move TARGET forward, or accept that pre-retention recovery is impossible.
Dump header doesn’t have `SOURCE_LOG_FILE`/`MASTER_LOG_FILE`	Script fails at step 3, prints first 50 lines of dump	Confirm `db-backup` is invoking `mysqldump --source-data=2`.
Binlog gap (missing sequence number)	Script fails at step 4 with the expected vs found ordinals	Check the binlog-shipping CronJob for failed runs. Manually upload the missing binlog if it’s still on the primary’s PVC.
Network drop mid-restore	mysql client exits non-zero; Job status `Failed`, `backoffLimit: 0` so no retry	Investigate, fix, re-run by toggling `enabled: false → true` in values (or `kubectl delete job ...` then re-`helm upgrade`).
Restore partially applied before failure	Staging release is in an inconsistent state	Wipe the staging release (`helm uninstall staging-db && kubectl delete pvc -l app.kubernetes.io/instance=staging-db -n NS`), recreate, retry.

Local invocation (no Helm)

You can run the script directly against any reachable MySQL — useful for testing or for ad-hoc restores when you don’t want to roll a Helm release:

docker run --rm -it --network host \
  -v $PWD/scripts/pitr-restore.sh:/scripts/pitr-restore.sh:ro \
  -e TARGET_DATETIME="2026-05-15 12:00:00" \
  -e RESTORE_HOST=staging-db.example.local \
  -e RESTORE_PORT=3306 \
  -e RESTORE_DB=myapp \
  -e RESTORE_ROOT_PASSWORD="$STAGING_ROOT_PW" \
  -e PRIMARY_SERVICE_NAME=prod-db \
  -e S3_ACCESS_KEY=... \
  -e S3_SECRET_KEY=... \
  -e S3_HOST=... \
  -e S3_BUCKET=my-backups \
  -e S3_REGION=... \
  -e S3_BACKUP_PREFIX=myapp/ \
  -e S3_BINLOG_PREFIX=binlogs/prod-db-mysql/ \
  <pitr.restore.image> \
  bash /scripts/pitr-restore.sh

Use the image the chart’s pitr.restore.image value names.

PRIMARY_SERVICE_NAME is required even outside the Job — it’s the script’s last line of defence against accidentally pointing RESTORE_HOST at prod.

Verification

The full PITR end-to-end test:

# 1. Pick a database with low write rate
# 2. Note the current time T1
# 3. Insert 1000 rows
# 4. Wait 2 minutes
# 5. Insert 1000 more rows (different range)
# 6. Wait for next binlog shipping cycle (15 min)
# 7. Restore to time = T1 + 1 minute (between batches)
# 8. Verify staging has the first 1000 rows but NOT the second 1000

# Pre-flight
TARGET="$(date -u -d 'X minutes ago' +'%Y-%m-%d %H:%M:%S')"
helm upgrade prod helm-charts/charts/mysql -n $NS --reuse-values \
  --set pitr.restore.enabled=true \
  --set "pitr.restore.targetDatetime=${TARGET}" \
  --set pitr.restore.targetHost=staging-mysql \
  --set pitr.restore.targetDatabase=mydb

# Watch
kubectl logs -n $NS job/prod-mysql-pitr-restore -f

# Verify on staging
kubectl exec -n $NS staging-mysql-0 -- mysql -uroot -p$STAGING_PW \
  -e "SELECT COUNT(*) FROM mydb.t1 WHERE id BETWEEN 1 AND 1000;     -- expect 1000
      SELECT COUNT(*) FROM mydb.t1 WHERE id BETWEEN 1001 AND 2000;  -- expect 0"