# Backup & Recovery Runbook

Covers `borg-backup.sh` (daily backup), `dump_db.sh` (MariaDB logical
dumps, invoked by the backup script), and `restore.sh` (recovery).

## 1. One-Time Setup

Run once, by hand, on the server:

1. Initialize the encrypted Borg repo:
   ```bash
   mkdir -p /home/srv/files/backups
   borg init --encryption=repokey-blake2 /home/srv/files/backups/borg-2025
   ```
2. Create the passphrase file used by both backup and restore:
   ```bash
   echo 'your-strong-passphrase' > /root/.borg-passphrase
   chmod 600 /root/.borg-passphrase
   ```
3. Create the MariaDB `backup` user used by `dump_db.sh` (read-only, no
   stop/lock of the server required thanks to `--single-transaction`):
   ```sql
   CREATE USER 'backup'@'%' IDENTIFIED BY 'a-strong-password';
   GRANT SELECT, LOCK TABLES, SHOW VIEW, TRIGGER, PROCESS, RELOAD ON *.* TO 'backup'@'%';
   ```
   ```bash
   echo 'a-strong-password' > /root/.mariadb-backup.pw
   chmod 600 /root/.mariadb-backup.pw
   ```
4. Create the root password file used only by `restore.sh` (restore needs
   CREATE/DROP privileges the `backup` user does not have):
   ```bash
   echo 'the-mariadb-root-password' > /root/.mariadb-root.pw
   chmod 600 /root/.mariadb-root.pw
   ```
5. Exclude MariaDB's raw data directory from the archive. Find the
   directory bind-mounted into the container as its datadir and drop a
   marker file in it:
   ```bash
   touch /home/srv/files/content/mariadb/data/.nobackup
   ```
   This is what allows backups to run with the container up: only the
   logical dump under `mariadb/dump/` is ever archived or restored from.
6. Configure the `scaleway` rclone remote:
   ```bash
   rclone config
   # create a remote named "scaleway", type S3, matching your Scaleway
   # Object Storage credentials and region
   ```

## 2. Deploying the Scripts

Copy `borg-backup.sh`, `dump_db.sh`, and `restore.sh` to `/opt/backup-agent/`
(this exact path is what `borg-backup.sh` invokes for `dump_db.sh`). Make
all three executable:

```bash
chmod +x /opt/backup-agent/borg-backup.sh /opt/backup-agent/restore.sh
chmod +x /opt/backup-agent/dump_db.sh
```

## 3. Scheduling

Add a cron entry to run the backup daily, off-peak:

```
# /etc/cron.d/borg-backup
30 2 * * * root /opt/backup-agent/borg-backup.sh >> /var/log/borg/cron.log 2>&1
```

Check the last run:

```bash
ls -lt /var/log/borg/backup-*.log | head -1   # latest log file
tail -50 /var/log/borg/backup-*.log           # inspect it
```

## 4. Day-2 Operations

List archives:

```bash
./restore.sh --list-archives
```

Check repo size and health:

```bash
BORG_PASSCOMMAND="cat /root/.borg-passphrase" borg info /home/srv/files/backups/borg-2025
```

Rotate the passphrase (creates a new key, re-encrypts nothing — old
archives still need the old passphrase to read, so keep both until fully
migrated):

```bash
BORG_PASSCOMMAND="cat /root/.borg-passphrase" borg key change-passphrase /home/srv/files/backups/borg-2025
```

Stale lockfile (backup or restore aborted mid-run and left the repo
locked):

```bash
BORG_PASSCOMMAND="cat /root/.borg-passphrase" borg break-lock /home/srv/files/backups/borg-2025
```

## 5. Recovery Procedures

All `restore.sh` commands accept `--dry-run` to preview exactly what would
happen without touching anything, and `--archive NAME` to target a
specific archive instead of the latest (see archive names via
`--list-archives`). Root DB credentials come from `MYSQL_ROOT_PASSWORD` in
the environment if set, otherwise from `/root/.mariadb-root.pw` — set
whichever is more convenient for how you're invoking it. Restore logs go
to `/var/log/borg/restore-*.log` (the `RESTORE_LOGDIR` environment
variable overrides the directory, mainly useful for testing). `full` and
`db` share `borg-backup.sh`'s lockfile, so a restore refuses to start
while the nightly backup is mid-run (and vice versa) rather than racing
it.

### 5.1 Full disaster recovery (new or wiped server)

Use when the whole server/container is gone and you're rebuilding from
scratch.

```bash
# 1. Reinstall borg, docker, and the mariadb container image/compose file
#    (not covered by restore.sh - this is infra provisioning).
# 2. Restore the passphrase file (from your password manager / secondary
#    backup - it is NOT stored in the repo it protects) to
#    /root/.borg-passphrase, and the root DB password to
#    /root/.mariadb-root.pw.
# 3. Preview:
./restore.sh full --dry-run
# 4. Run for real. --force is only required if /home/srv/files/content
#    already has data in it (e.g. a stale mount); omit it on a genuinely
#    empty/fresh server:
./restore.sh full --force
```

This extracts the full content tree from the archive, starts the
`mariadb` container and waits for it to report healthy, then restores
every database dump (users/grants first) — the container must be running
before any of the dump restores, which is why it starts first.

**Verify afterward:**
- `docker ps` shows `mariadb` running and healthy.
- The application responds normally.
- Spot-check row counts on a couple of tables against what you'd expect.

### 5.2 Single database restore

Use when one database got corrupted or someone ran a bad migration/query
against it — this **drops and recreates** that database.

```bash
./restore.sh db shopdb --dry-run     # preview
./restore.sh db shopdb                # prompts: type "shopdb" to confirm
```

Non-interactive (e.g. scripted from a monitoring alert): add `--yes` to
skip the typed confirmation.

**Verify afterward:** connect to the database and check the tables/row
counts you expect.

### 5.3 Single file/directory restore

Use for accidental deletion of a file, or to inspect an old version — this
never touches the running database or container.

```bash
./restore.sh file path/relative/to/content/some-file.txt --dest /tmp/recovered
```

The final location of the recovered item is printed at the end (it lands
under `/tmp/recovered/home/srv/files/content/...` — Borg preserves the
absolute path it was archived with).

## 6. Restore Drill Cadence

Quarterly, run a real `full` restore into a scratch directory (not
`/home/srv/files/content`) to confirm backups are actually usable:

`borg extract` always extracts into the current directory (there's no
`--destination` flag — this is why `restore.sh` itself `cd`s into the
destination before extracting), and the archive name must come from
`borg list --short` (plain `borg list` prints a formatted line, not a bare
name), so:

```bash
mkdir -p /tmp/restore-drill && cd /tmp/restore-drill
export BORG_REPO=/home/srv/files/backups/borg-2025
export BORG_PASSCOMMAND="cat /root/.borg-passphrase"
LATEST=$(borg list --short | tail -1)
borg extract --lock-wait 600 "::$LATEST"
```

Confirm the dump files under `mariadb/dump/` are present, non-empty, and
importable (`mysql -u root -p < mariadb/dump/somedb.sql` against a
throwaway MariaDB container). Log the drill date and outcome somewhere
durable (e.g. a team wiki page).