Offsite Database Backup Procedure

Purpose

This procedure configures an encrypted offsite backup for a single database using Restic and Backblaze B2.

Overview

The procedure has two phases:

Phase 1 creates an encrypted database dump on a nightly schedule.
Phase 2 uploads that encrypted dump to Backblaze B2 cloud storage.

After both phases are complete, the backup is verified with a test restore.

Prerequisites

This guide assumes the following is already in place:

A Linux server running the database to be backed up
A Backblaze B2 account (a cloud storage service, similar to Amazon S3)
Restic installed (a backup tool that handles encryption and deduplication, explained below)
GPG installed (a tool for encrypting files)

For this example, database dumps are created from a Docker container running MariaDB. The same approach applies to any database that supports command-line exports.

Key Terms

Term	Definition
Database dump	An export of all data in a database to a single file. Used to create a portable copy that can be restored later.
Backblaze B2	A cloud storage service. Files are stored on servers in a remote data center.
Restic	A backup tool that only uploads the parts of files that changed since the last run. This is called deduplication.
GPG	GNU Privacy Guard. A tool that encrypts files so they cannot be read without a password.
AES-256	A strong encryption standard. Files encrypted with AES-256 cannot be read without the correct passphrase.
3-2-1 Rule	A backup best practice: keep 3 copies of data, on 2 different storage types, with 1 copy offsite.
Cron	A built-in Linux tool that runs scripts on a set schedule (for example, every night at 2:00 AM).
Principle of least privilege	A security practice where each user account only has the permissions it needs and nothing more.

Phase 1: Create an Encrypted Database Dump

Before You Begin

Warning

Each phase uses a separate user account with limited permissions. Do not run backup scripts as root or as a shared user. This follows the principle of least privilege.

Create the Backup User

Create a dedicated user for database dumps:
```
useradd -m -s /bin/bash backupuser
```
Grant this user permission to run docker exec on the database container. Do not grant any other access.

Create a directory for storing dumps:

mkdir -p /home/backupuser/dumps
mkdir -p /home/backupuser/scripts

Create a passphrase file for GPG encryption. Set permissions so that only the backup user can read it:
```
touch /home/backupuser/.backup-passphrase
chmod 600 /home/backupuser/.backup-passphrase
```
Open the file and add a strong passphrase. Save this passphrase in a separate, secure location. Without it, encrypted backups cannot be restored.

Write the Backup Script

Create the script file:

touch /home/backupuser/scripts/backup-database.sh
chmod 700 /home/backupuser/scripts/backup-database.sh

Add the following contents:

#!/bin/bash
# Runs as 'backupuser' via cron

set -euo pipefail

BACKUP_DIR="/home/backupuser/dumps/database"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
RETENTION_DAYS=7

# Create the backup folder if it does not exist
mkdir -p "$BACKUP_DIR"

# Export the database from the Docker container and compress the output
docker exec mariadb-container mysqldump \
    --user=backupuser \
    --password="${DB_PASS}" \
    --single-transaction \
    --routines \
    --triggers \
    database_name | gzip > "$BACKUP_DIR/database_${TIMESTAMP}.sql.gz"

# Encrypt the dump with AES-256
gpg --batch --yes --symmetric \
    --cipher-algo AES256 \
    --passphrase-file /home/backupuser/.backup-passphrase \
    "$BACKUP_DIR/database_${TIMESTAMP}.sql.gz"

# Remove the unencrypted version
rm "$BACKUP_DIR/database_${TIMESTAMP}.sql.gz"

# Delete encrypted dumps older than the retention window
find "$BACKUP_DIR" -name "*.sql.gz.gpg" \
    -mtime +$RETENTION_DAYS -delete

echo "[$(date)] Backup complete: database_${TIMESTAMP}.sql.gz.gpg"

Replace mariadb-container, database_name, backupuser, and DB_PASS with the actual values for the target database.

Test the script manually before scheduling it:

sudo -u backupuser /home/backupuser/scripts/backup-database.sh

Verify that an encrypted .sql.gz.gpg file appears in /home/backupuser/dumps/database/:
```
$ ls /home/backupuser/dumps/database/
database_20260312_020000.sql.gz.gpg
```

Schedule the Script

Open the crontab for the backup user:
```
sudo crontab -u backupuser -e
```

Add an entry to run the script nightly at 2:00 AM:

0 2 * * * /home/backupuser/scripts/backup-database.sh

Phase 2: Upload to Backblaze with Restic

Before You Begin

Warning

Do not skip encryption in Phase 1. Restic adds its own encryption layer, but the database dumps should already be encrypted before they leave the local machine. This provides two layers of protection.

Create the Restic User

Create a separate user for the offsite upload:
```
useradd -m -s /bin/bash resticuser
```
Grant this user read-only access to the encrypted dumps in /home/backupuser/dumps/. Do not grant write access.

Create a password file for Restic:

touch /home/resticuser/.restic-password
chmod 600 /home/resticuser/.restic-password

Add a strong password to the file. Save this password separately. It is required to restore from Backblaze.

Initialize the Restic Repository

Set the environment variables for Backblaze:

export RESTIC_REPOSITORY="b2:your-bucket-name"
export RESTIC_PASSWORD_FILE="/home/resticuser/.restic-password"
export B2_ACCOUNT_ID="your-account-id"
export B2_ACCOUNT_KEY="your-account-key"

Initialize the repository:
```
restic init
```
Confirm the output shows the repository was created. Expected output:
```
created restic repository at b2:your-bucket-name
```

Write the Sync Script

Create the script file:

touch /home/resticuser/scripts/sync-to-backblaze.sh
chmod 700 /home/resticuser/scripts/sync-to-backblaze.sh

Add the following contents:

#!/bin/bash
# Runs as 'resticuser' with limited permissions

set -euo pipefail

export RESTIC_REPOSITORY="b2:your-bucket-name"
export RESTIC_PASSWORD_FILE="/home/resticuser/.restic-password"
export B2_ACCOUNT_ID="${B2_ACCOUNT_ID}"
export B2_ACCOUNT_KEY="${B2_ACCOUNT_KEY}"

# Upload encrypted dumps to Backblaze
restic backup \
    --verbose \
    --exclude-caches \
    --tag "nightly" \
    --tag "$(hostname)" \
    /home/backupuser/dumps/database

# Clean up old snapshots: keep 7 daily, 4 weekly, 6 monthly
restic forget \
    --keep-daily 7 \
    --keep-weekly 4 \
    --keep-monthly 6 \
    --prune

echo "[$(date)] Backblaze sync complete"

Test the script manually:

sudo -u resticuser /home/resticuser/scripts/sync-to-backblaze.sh

Verify the upload by listing snapshots:

restic snapshots

Expected output:

ID        Time                 Host        Tags
-------------------------------------------------------
a1b2c3d4  2026-03-12 03:00:05  server01    nightly,server01

Schedule the Sync

Open the crontab for the restic user:
```
sudo crontab -u resticuser -e
```
Add an entry to run the sync nightly at 3:00 AM (one hour after Phase 1):
```
0 3 * * * /home/resticuser/scripts/sync-to-backblaze.sh
```

Verify the Backup

Warning

A backup that has never been restored is not a backup. Complete this section before considering the procedure finished.

Download the latest snapshot from Backblaze:

restic restore latest --target /tmp/restore-test

Locate the encrypted dump file in the restored output:
```
ls /tmp/restore-test/home/backupuser/dumps/database/
```

Decrypt the dump using GPG:

gpg --batch --decrypt \
    --passphrase-file /home/backupuser/.backup-passphrase \
    /tmp/restore-test/home/backupuser/dumps/database/database_TIMESTAMP.sql.gz.gpg \
    > /tmp/restore-test/database.sql.gz

Decompress the file:

gunzip /tmp/restore-test/database.sql.gz

Import the dump into a test database:

docker exec -i mariadb-test mysql \
    --user=backupuser \
    --password="${DB_PASS}" \
    test_database < /tmp/restore-test/database.sql

Confirm the restored data matches the original by checking record counts and recent entries.
Clean up the test files:
```
rm -rf /tmp/restore-test
```

Troubleshooting

Common issues and their solutions:

Problem	Cause	Solution
Script runs but no `.gpg` file appears	GPG passphrase file is empty or has wrong permissions	Verify the file is not empty and permissions are set to `600`
`docker exec` returns "container not found"	Container name does not match or container is not running	Run `docker ps` to confirm the container name and status
Restic returns "repository does not exist"	Environment variables are not set or incorrect	Verify `RESTIC_REPOSITORY`, `B2_ACCOUNT_ID`, and `B2_ACCOUNT_KEY` are correct
Cron job does not run	Script is not executable or cron syntax is wrong	Verify permissions with `ls -l` and validate syntax at crontab.guru
GPG decryption fails during restore	Wrong passphrase or corrupted file	Confirm the passphrase matches the one stored during setup

For a production deployment, monitoring and alerting (for example, notifications on failed runs or missed schedules) would be covered in a separate procedure.

Cost

A single database backup of this type typically produces a small storage footprint. As a reference point, backing up approximately 15 GB of data costs $0.03 per month at Backblaze B2 pricing.

Result: 3-2-1 Coverage

After completing this procedure, the database is protected by the 3-2-1 backup rule:

Copy	Location	Storage Type
1	Live database	Local drive
2	Local backup	On-site backup server or separate disk
3	Backblaze B2	Cloud object storage

Three copies. Two storage types. One offsite.

Quick Reference

Files and Directories

Path	Purpose
`/home/backupuser/scripts/backup-database.sh`	Database dump script
`/home/backupuser/dumps/database/`	Encrypted dump storage
`/home/backupuser/.backup-passphrase`	GPG encryption passphrase
`/home/resticuser/scripts/sync-to-backblaze.sh`	Offsite sync script
`/home/resticuser/.restic-password`	Restic repository password

Environment Variables

Variable	Used By	Description
`DB_PASS`	Backup script	Password for the database user
`B2_ACCOUNT_ID`	Sync script	Backblaze B2 account ID
`B2_ACCOUNT_KEY`	Sync script	Backblaze B2 application key
`RESTIC_REPOSITORY`	Sync script	Restic repository location (e.g., `b2:your-bucket-name`)
`RESTIC_PASSWORD_FILE`	Sync script	Path to the Restic password file