# DB Backup & Restore

**Owner**: Ops
**Last verified**: 2026-04-20 (initial install)
**Phase**: LAUNCH_PLAN.md Phase 6 #28

---

## What exists

| Thing | Path |
|---|---|
| Backup script | `/usr/local/bin/sm-backup.sh` |
| Backup storage | `/var/backups/searchmercials_v2/` (mode 700 root) |
| Retention | 30 days (pruned by the script at the end of each run) |
| MySQL credentials (app user, read-only-ish) | `/root/.my-searchmercials.cnf` (mode 600) |
| Backup log | `/var/log/sm-backup.log` |
| Cron | `/etc/cron.d/searchmercials_v2-backup` — daily at 03:00 UTC |

Backups are `gzip -9` compressed `mysqldump --single-transaction` snapshots. One snapshot ≈ 7 MB at the current data size (84 MB uncompressed).

**Off-host copy**: scaffolded via `rclone` to Backblaze B2; upload is active the moment an rclone remote named `sm-backup-b2` exists on this host. Until configured, each run logs `OFFHOST-SKIP` and continues. See "Enabling off-host backup" below.

---

## Restoring a backup

**Scenario A — scratch verification restore** (the one we run regularly to prove backups work):

```bash
# Pick a backup
BACKUP_FILE=$(ls -t /var/backups/searchmercials_v2/*.sql.gz | head -1)

# Create a scratch database (requires root via local socket)
mysql -uroot -e "DROP DATABASE IF EXISTS searchmercials_v2_restore_test;
                 CREATE DATABASE searchmercials_v2_restore_test DEFAULT CHARACTER SET utf8mb4;"

# Stream restore
gunzip -c "$BACKUP_FILE" | mysql -uroot searchmercials_v2_restore_test

# Spot-check
for t in sm_listings sm_vendors sm_account_subscriptions sm_listing_category_enrollments; do
    src=$(mysql -uroot -N -e "SELECT COUNT(*) FROM searchmercials_v2.$t")
    res=$(mysql -uroot -N -e "SELECT COUNT(*) FROM searchmercials_v2_restore_test.$t")
    printf '%-40s src=%s  restored=%s\n' "$t" "$src" "$res"
done

# Drop scratch when done
mysql -uroot -e "DROP DATABASE searchmercials_v2_restore_test;"
```

**Scenario B — full emergency restore** (actual data loss):

1. Stop anything that writes to the DB:
   ```bash
   systemctl stop apache2          # or nginx
   systemctl stop php8.1-fpm
   # Pause the Laravel scheduler cron by commenting /etc/cron.d/searchmercials_v2-scheduler
   ```
2. Confirm the backup you want:
   ```bash
   ls -la /var/backups/searchmercials_v2/
   # Pick the latest good one
   BACKUP_FILE=/var/backups/searchmercials_v2/searchmercials_v2-YYYYMMDDThhmmssZ.sql.gz
   ```
3. Replace the live DB:
   ```bash
   mysql -uroot -e "DROP DATABASE searchmercials_v2;
                    CREATE DATABASE searchmercials_v2 DEFAULT CHARACTER SET utf8mb4;"
   gunzip -c "$BACKUP_FILE" | mysql -uroot searchmercials_v2
   ```
   (The app user `sm_v2` retains its grants — no GRANT reload needed.)
4. Spot-check as in Scenario A.
5. Bring services back up:
   ```bash
   systemctl start php8.1-fpm
   systemctl start apache2
   # Re-enable the scheduler cron
   ```
6. In the app:
   ```bash
   sudo -u www-data php artisan config:cache
   sudo -u www-data php artisan route:cache
   sudo -u www-data php artisan view:cache
   ```

Restore timing (verified 2026-04-20): **~5 seconds** on this box for the current data size.

---

## Running a backup on-demand

```bash
sudo /usr/local/bin/sm-backup.sh
tail -3 /var/log/sm-backup.log
```

Each run writes one log line: `YYYY-MM-DDTHH:MM:SSZ OK wrote <path> size=<bytes> elapsed=<seconds>`. Failures log `FAIL …` and exit non-zero, which will mail `root`'s local mbox via cron's `MAILTO=root`.

---

## Verification schedule

- **Daily automated**: cron runs `sm-backup.sh` at 03:00 UTC.
- **Monthly manual**: run Scenario A above and confirm row counts match. Record result in this doc's verification line at the top.
- **Before first live-mode launch**: run Scenario A as part of the launch checklist (Phase 7).

---

## Verified on 2026-04-20

First backup + first restore test:

```
Backup   : /var/backups/searchmercials_v2/searchmercials_v2-20260420T225139Z.sql.gz
Size     : 7,312,681 bytes (6.97 MB)
Dump time: 2 seconds
Restore  : 5 seconds to scratch DB

Row counts (all MATCH):
  sm_listings                      1957
  sm_vendors                       1933
  sm_categories                    78
  sm_account_subscriptions         4
  sm_listing_category_enrollments  5
  sm_listing_marketplaces          3
  sm_invoices                      5
  sm_payments                      5
```

---

## Enabling off-host backup (Backblaze B2 via rclone)

Required before first LIVE Stripe transaction. Until configured, backups
live only on the primary disk — a disk failure takes both.

**One-time setup** (on the server, as root):

1. Create a **private B2 bucket** in the Backblaze console. Recommended name: `searchmercials-backups`. Enable bucket versioning + a lifecycle rule that keeps versions for 30-60 days (server-side retention is independent of the 30-day local retention).
2. Create a Backblaze **application key** with:
   - Name: `searchmercials-server`
   - Type: Restricted to the bucket above
   - Capabilities: `listBuckets`, `listFiles`, `readFiles`, `writeFiles`
3. On this server, run `rclone config`. Interactive:
   ```
   n)ew remote → name: sm-backup-b2
   Storage    : b2 (Backblaze B2)
   account    : <the key ID from step 2>
   key        : <the application key from step 2>
   hard_delete: false
   ```
4. Verify:
   ```bash
   rclone ls sm-backup-b2:searchmercials-backups/
   # (empty on first run — that's expected)
   ```
5. The next hourly / daily backup run will auto-upload. Confirm with:
   ```bash
   /usr/local/bin/sm-backup.sh
   grep OFFHOST /var/log/sm-backup.log | tail -5
   # Expect: OFFHOST-OK  uploaded to sm-backup-b2:searchmercials-backups/…sql.gz
   ```

**Credentials location**: rclone stores them at `/root/.config/rclone/rclone.conf`, mode 600. Do not check this file into git.

**When it breaks** (logs show `OFFHOST-FAIL`):
- The local backup is still valid and retained. Restore procedures (A and B above) still work from local.
- Next run retries automatically.
- Multi-run failures surface via Phase 6 #30 error alerting once Sentry is wired.

## Remaining risks

1. **Off-host copy not yet configured.** Scaffold is in place (see "Enabling off-host backup" above). Must complete before first LIVE Stripe transaction.
2. **`/root/.my-searchmercials.cnf` holds the DB password.** If root is compromised, backups are reachable too. Off-host copy helps here because the attacker can't reach the off-host store without separate credentials.
3. **Restore requires `mysql -uroot`** (for `CREATE DATABASE`). On this box that's socket-auth-only, which is safe. If MySQL is ever moved to a remote host, the restore procedure needs an admin user + a second `my.cnf`.
4. **No point-in-time recovery.** Binlog-based PITR would cap data loss at seconds rather than ~24h. Deferred until traffic justifies the complexity.