Automated Database Backups for AcelleMail

What this is for#

The first backup is the cheapest you'll ever take. By the time you actually need to restore — a botched migration, a hardware failure, a misclicked "Delete List" — the cost of not having a backup is hours of downtime, lost subscribers, lost campaign history, and a furious customer-success conversation.

This guide walks the production-correct setup: daily mysqldump with rotation, offsite copies, file-tree snapshots, a restore drill workflow, and monitoring that tells you when it silently stops working.

The whole setup is 30 minutes. Do it within the first hour of going to production.

What needs backing up#

Two things, distinct and both required:

1. The MySQL database (acellemail schema) — contains subscribers, lists, campaigns, automations, templates, billing history, sending logs. This is the irreplaceable bit.
2. The storage directory (/var/www/acellemail/storage/) — contains user-uploaded campaign images, attached files, exports, generated PDFs. Lost storage = broken historical campaigns (images 404 in old emails). Recoverable from sources, but painful.

What you don't need to back up:

• Application code (/var/www/acellemail/ minus storage/ and .env) — restorable from the CodeCanyon install bundle + your specific patches
• vendor/, node_modules/, bootstrap/cache/ — regenerable
• .env — back up separately to a password manager / secret vault, not in the regular file backup (contains DB password, API keys, app key)

Step 1 — Daily MySQL backup#

Create the script /usr/local/bin/acellemail-db-backup.sh:

#!/bin/bash
# Daily MySQL backup for AcelleMail — single-transaction, gzipped, 30-day retention

set -euo pipefail

DEST="/var/backups/acellemail/db"
TIMESTAMP="$(date +%Y-%m-%d_%H%M%S)"
KEEP_DAYS=30

# Read DB credentials from the app's .env (single source of truth)
ENV="/var/www/acellemail/.env"
DB_NAME="$(grep '^DB_DATABASE=' "$ENV" | cut -d= -f2-)"
DB_USER="$(grep '^DB_USERNAME=' "$ENV" | cut -d= -f2-)"
DB_PASS="$(grep '^DB_PASSWORD=' "$ENV" | cut -d= -f2- | tr -d '"')"

mkdir -p "$DEST"

# --single-transaction = consistent snapshot without table locks (works on InnoDB)
# --routines = include stored procedures + functions
# --triggers = include triggers
# --quick = stream rows row-by-row (lower memory for large tables)
mysqldump \
    --user="$DB_USER" \
    --password="$DB_PASS" \
    --single-transaction \
    --routines \
    --triggers \
    --quick \
    --no-tablespaces \
    "$DB_NAME" \
  | gzip -9 \
  > "$DEST/$DB_NAME-$TIMESTAMP.sql.gz"

# Rotation: delete anything older than KEEP_DAYS
find "$DEST" -name "$DB_NAME-*.sql.gz" -mtime "+$KEEP_DAYS" -delete

# Sanity-check the backup is non-empty
if [ ! -s "$DEST/$DB_NAME-$TIMESTAMP.sql.gz" ]; then
    echo "FATAL: backup is empty!" >&2
    exit 1
fi

# Optional: log size for monitoring
SIZE=$(du -h "$DEST/$DB_NAME-$TIMESTAMP.sql.gz" | cut -f1)
echo "[$(date)] Backup ok: $DEST/$DB_NAME-$TIMESTAMP.sql.gz ($SIZE)"

Make it executable + schedule:

sudo chmod +x /usr/local/bin/acellemail-db-backup.sh
sudo chown root:root /usr/local/bin/acellemail-db-backup.sh

# Run as root to read /var/www/acellemail/.env (which is 0640 root-readable)
# Daily at 02:00 — pick a low-traffic time
echo "0 2 * * * /usr/local/bin/acellemail-db-backup.sh >> /var/log/acellemail-backup.log 2>&1" \
  | sudo tee -a /etc/crontab

--single-transaction is critical — without it, mysqldump locks tables for the duration of the dump, which can be minutes on a large DB. With InnoDB (the default) and --single-transaction, you get a consistent point-in-time snapshot without any lock.

Verify the script works manually before relying on cron:

sudo /usr/local/bin/acellemail-db-backup.sh
ls -lah /var/backups/acellemail/db/
# Should show one .sql.gz file from "just now"

Step 2 — File-tree snapshot#

The storage/ directory needs less frequent backup — campaign assets change rarely once uploaded. Weekly is plenty:

sudo tee /usr/local/bin/acellemail-files-backup.sh <<'EOF'
#!/bin/bash
set -euo pipefail

DEST="/var/backups/acellemail/files"
TIMESTAMP="$(date +%Y-%m-%d)"
KEEP_WEEKS=8

mkdir -p "$DEST"

# Tar the storage tree, exclude transient subdirs that would just bloat
tar czf "$DEST/storage-$TIMESTAMP.tar.gz" \
    --exclude='framework/cache' \
    --exclude='framework/sessions' \
    --exclude='framework/views' \
    --exclude='logs/laravel.log' \
    -C /var/www/acellemail storage

# Rotation: keep last KEEP_WEEKS only
find "$DEST" -name 'storage-*.tar.gz' -mtime "+$((KEEP_WEEKS * 7))" -delete

SIZE=$(du -h "$DEST/storage-$TIMESTAMP.tar.gz" | cut -f1)
echo "[$(date)] Files backup ok: $DEST/storage-$TIMESTAMP.tar.gz ($SIZE)"
EOF

sudo chmod +x /usr/local/bin/acellemail-files-backup.sh

# Weekly on Sunday at 03:00
echo "0 3 * * 0 /usr/local/bin/acellemail-files-backup.sh >> /var/log/acellemail-backup.log 2>&1" \
  | sudo tee -a /etc/crontab

Why exclude framework/cache,sessions,views,logs/laravel.log? They regenerate themselves and bloat the backup. Anything truly user-uploaded sits under storage/app/ and is captured.

Step 3 — Offsite copy#

A local backup that's still on the same disk as the database protects against accidental deletion. It does not protect against:

• Drive failure (RAID dies; backup goes with it)
• Server compromise (attacker rm -rf everything)
• Cloud-provider-level data loss (rare but real)

For real durability you need an offsite copy. Three good options ranked by ops simplicity:

Option A — rclone to Backblaze B2 / Wasabi (cheapest, simplest)#

sudo apt install -y rclone

# Interactive config — set up your B2/Wasabi credentials
sudo rclone config
# Choose: New remote → Name: b2 (or whatever) → Type: b2 → enter Application Key ID + Application Key

# Add to /etc/crontab — runs daily at 04:00 (after the DB backup)
0 4 * * * /usr/bin/rclone sync /var/backups/acellemail/ b2:your-bucket/acellemail/ --log-file=/var/log/rclone-backup.log

Backblaze B2 pricing as of 2026: $6/TB-month storage, free egress up to 3× monthly storage. A 10 GB AcelleMail backup costs $0.06/month + free restore. Hard to beat.

Option B — restic to S3 / B2 (incremental + encrypted)#

sudo apt install -y restic

# One-time init (creates an encrypted repo)
sudo restic -r s3:s3.amazonaws.com/your-bucket/acellemail init
# It'll prompt for a password — STORE IT SOMEWHERE SAFE; without it backups are unrecoverable

# Backup
sudo restic -r s3:s3.amazonaws.com/your-bucket/acellemail backup /var/backups/acellemail

# Prune (run weekly)
sudo restic -r s3:s3.amazonaws.com/your-bucket/acellemail forget \
  --keep-daily 7 --keep-weekly 4 --keep-monthly 12 --prune

Restic does content-addressed deduplication — only changed bytes are uploaded after the first full sync. Significantly cheaper for the bandwidth side, with the trade-off of needing the repo password to restore.

Option C — provider snapshots (operator-zero-effort)#

DigitalOcean Snapshots, AWS EBS Snapshots, Hetzner Cloud Snapshots — all let you schedule daily droplet-wide snapshots. Pros: zero scripting; restores the full droplet exactly. Cons: more expensive than B2; restore requires building a new droplet (10–30 min); harder to extract just the DB.

Recommended pattern: local rotation (Step 1+2) for fast restores + B2 offsite (Option A) for disaster recovery + monthly provider snapshot for full-droplet protection.

Step 4 — The restore drill (the part most people skip)#

A backup you've never restored is not a backup. Schedule a monthly restore drill into your calendar:

# Restore to a non-production location to confirm it works

# 1. Pick a recent backup
LATEST=$(ls -t /var/backups/acellemail/db/*.sql.gz | head -1)
echo "Restoring from: $LATEST"

# 2. Create a scratch database
sudo mysql -e "CREATE DATABASE acellemail_restore_test;"

# 3. Restore into it
gunzip -c "$LATEST" | sudo mysql acellemail_restore_test

# 4. Sanity-check the restored data
sudo mysql acellemail_restore_test -e "
  SELECT (SELECT COUNT(*) FROM customers) AS customers,
         (SELECT COUNT(*) FROM subscribers) AS subscribers,
         (SELECT COUNT(*) FROM campaigns) AS campaigns,
         (SELECT MAX(created_at) FROM email_log) AS latest_send;
"
# Numbers should look sane; latest_send should be < 1 hour old (your live cron is firing)

# 5. Drop the scratch DB
sudo mysql -e "DROP DATABASE acellemail_restore_test;"

If any step fails, your backup is broken — fix it before you ever need it for real. Common breakage:

• --single-transaction errors because some tables aren't InnoDB → identify them and convert (ALTER TABLE foo ENGINE = InnoDB)
• Restore fails on a foreign-key constraint → add SET FOREIGN_KEY_CHECKS=0; ... SET FOREIGN_KEY_CHECKS=1; wrapping
• Access denied for restore → the user running the restore needs CREATE, INSERT, ALTER on the scratch DB

Document any tweaks in your backup script so they're applied for real next time.

Step 5 — Production restore workflow (when something actually broke)#

For a real disaster:

# 1. STOP the queue workers so nothing keeps writing to the broken state
sudo supervisorctl stop acellemail-master:* acellemail-worker:*

# 2. Stop incoming web traffic — put up a maintenance page
sudo -u www-data php /var/www/acellemail/artisan down

# 3. Take a "before" snapshot of the current (broken) database for forensics
DB_NAME=$(grep '^DB_DATABASE=' /var/www/acellemail/.env | cut -d= -f2-)
sudo mysqldump --single-transaction "$DB_NAME" | gzip > /tmp/pre-restore-$(date +%F-%H%M).sql.gz

# 4. Drop + recreate the database (clean slate)
sudo mysql -e "DROP DATABASE $DB_NAME; CREATE DATABASE $DB_NAME CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
sudo mysql -e "GRANT ALL ON $DB_NAME.* TO '$DB_NAME'@'localhost';"

# 5. Restore from the chosen backup
LATEST=/var/backups/acellemail/db/${DB_NAME}-XXXX.sql.gz   # pick the right one
gunzip -c "$LATEST" | sudo mysql "$DB_NAME"

# 6. If storage/ was also lost, restore from the latest weekly tar
sudo tar xzf /var/backups/acellemail/files/storage-YYYY-MM-DD.tar.gz \
  -C /var/www/acellemail
sudo chown -R www-data:www-data /var/www/acellemail/storage

# 7. Bring the site back up
sudo -u www-data php /var/www/acellemail/artisan up
sudo supervisorctl start acellemail-master:* acellemail-worker:*

# 8. Verify in the admin: most-recent campaigns visible, subscriber count reasonable

Document any deviations in a restore playbook PDF kept somewhere outside the server (Google Drive, Notion, password manager).

Step 6 — Monitoring#

A backup that silently stops working is the worst kind. Add these monitors:

Backup age check#

# A simple cron that screams if no backup was created today
0 8 * * * find /var/backups/acellemail/db/ -name '*.sql.gz' -mtime -1 | grep -q . || \
  curl -sf "https://hc-ping.com/your-ping-uuid/fail"

Healthchecks.io free tier gives you up to 20 monitored crons + Slack/email alerts. Set up two checks:

• acellemail-db-backup-done — pings at the end of the backup script (curl -sf https://hc-ping.com/uuid)
• acellemail-offsite-sync-done — pings at the end of the rclone job

If a ping doesn't arrive on schedule, you get an alert. Significantly more reliable than "I'll check the log file each morning."

Backup size sanity check#

A sudden 90% drop in backup size means something is broken (DB user lost a permission, a partition deleted, the backup truncated). Watch the trend:

# Add to the daily backup script
SIZE=$(stat -c '%s' "$DEST/$DB_NAME-$TIMESTAMP.sql.gz")
# Post to a metrics endpoint, or log a CSV for graphing
echo "$(date +%s),$SIZE" >> /var/log/acellemail-backup-sizes.csv

Monthly restore-test cron#

# Auto-run the restore drill from Step 4 on the 1st of each month
0 4 1 * * /usr/local/bin/acellemail-restore-test.sh >> /var/log/acellemail-restore-test.log 2>&1

If the script exits non-zero, get paged. Restore drills you never run will eventually fail when you need them.

Common issues#

FAQ#

Should I use binary log + point-in-time recovery (PITR)? For most AcelleMail installs, no. PITR (mysqlbinlog | mysql replay) lets you restore to any second within the binlog retention window, but it requires log-bin = ON, more disk I/O, and operationally complex restore steps. The daily-snapshot approach loses at most 24 hours; PITR is worth the complexity only if you genuinely can't tolerate that.

MariaDB version of the same script? Identical commands. mysqldump ships with MariaDB and accepts the same flags.

Can I exclude tables to make backups smaller? The email_log table grows fastest on busy installs. Excluding it cuts backup size dramatically but means you lose historical sending data on restore. Trade-off; document the choice.

# Exclude email_log structure + data
mysqldump ... --ignore-table=$DB_NAME.email_log "$DB_NAME"
# Still get the schema so the table exists:
mysqldump ... --no-data --tables email_log "$DB_NAME"

Encrypted backups? Restic (Option B in Step 3) encrypts client-side with a password. For rclone/B2, use rclone crypt to wrap an existing remote with encryption. Either way, never lose the encryption password — without it the backup is shredded data.

RDS / managed DB equivalent? AWS RDS, DigitalOcean Managed DB, Hetzner Cloud DB all offer point-in-time automated backups with 7-day retention by default. Use them; they're cheaper than rolling your own at scale. Still take periodic snapshots of storage/ separately (managed DB doesn't cover the file tree).

Backups are encrypted at rest on the server — do I need to encrypt them again? Defense in depth. Server disk encryption protects against drive theft; encrypted offsite backups protect against the cloud provider seeing your data. Use both.

How long should I keep backups? Cost-vs-paranoia trade-off:

• Daily for 30 days (covers recent oopses)
• Weekly for 6 months (covers "we noticed last quarter that...")
• Monthly for 7 years (for legal / compliance reasons; GDPR + various data-retention regimes)

At ~10 GB compressed per backup, that's ~600 GB total at $6/TB-month = ~$3.60/month for full retention. Money well spent.

Related articles#

• Setting Up Queue Workers and Cron Jobs — pair with backups for full ops setup
• Post-Install Hardening Checklist — Control 20 covers the basic daily-DB-dump cron; this article expands on it
• Install AcelleMail on Ubuntu 24.04 LTS — prerequisite
• Scaling for 100K+ Emails Per Day — at scale, consider RDS-style managed backups
• GDPR Compliance Guide for Email Marketing — retention requirements for EU sender programs
• Docker Deployment Guide for AcelleMail — Docker-equivalent backup recipes (mysqldump via compose exec)