VPS Backup Pipeline: Automating Database and File Backups with Shell Scripts

Data loss on a VPS is not a matter of if but when. Disk failure, accidental rm -rf, security breaches, or provider outages can wipe out critical data in seconds. A robust automated backup pipeline is your safety net. This guide walks through building a complete backup system using shell scripts — supporting MySQL databases, application files, encrypted archives, and off-site storage — all running on your VPS without proprietary backup agents.

Why Automate Backups on Your VPS?

Manual backups are unreliable. Automation removes human error. A well-designed pipeline gives you:

• Recovery Point Objective (RPO) control — Define exactly how much data you are willing to lose (15 minutes, 1 hour, 24 hours).
• Recovery Time Objective (RTO) — Restore from backup in minutes with pre-tested procedures.
• Off-site redundancy — Encrypted backups stored on separate infrastructure.
• Point-in-time recovery — Restore any previous snapshot from your retention window.

When evaluating VPS hardware for backup storage, compare VPS providers on our comparison table to find plans with high-capacity NVMe storage and generous bandwidth allowances.

Backup Architecture Overview

Our pipeline follows the 3-2-1 rule: three copies of data, on two different media types, with one copy off-site. The pipeline stages are:

• Stage 1 — MySQL database dump with mysqldump and --single-transaction
• Stage 2 — Filesystem archive with tar and pigz (parallel gzip)
• Stage 3 — GPG encryption before leaving the server
• Stage 4 — Off-site transfer via rclone to S3-compatible storage

Stage 1: Database Backup Script

#!/bin/bash
# /usr/local/bin/backup-db.sh — Database backup for MySQL/MariaDB
set -euo pipefail

DB_HOST="localhost"
DB_USER="backup_user"
DB_PASS="$(cat /etc/backup/mysql_password)"
BACKUP_DIR="/var/backups/db"
RETENTION_DAYS=30
TIMESTAMP=$(date +%Y%m%d_%H%M%S)

mkdir -p "$BACKUP_DIR"

# List user databases, excluding system schemas
DATABASES=$(mysql -h "$DB_HOST" -u "$DB_USER" -p"$DB_PASS" \
  -e "SHOW DATABASES;" | grep -Ev "^(Database|information_schema|performance_schema|mysql|sys)$")

for DB in $DATABASES; do
  echo "Backing up: $DB"
  mysqldump -h "$DB_HOST" -u "$DB_USER" -p"$DB_PASS" \
    --single-transaction --quick --lock-tables=false \
    --routines --triggers --events "$DB" | gzip > "$BACKUP_DIR/${DB}_${TIMESTAMP}.sql.gz"

  # Verify backup integrity
  gzip -t "$BACKUP_DIR/${DB}_${TIMESTAMP}.sql.gz" || {
    echo "Backup corrupted: ${DB}_${TIMESTAMP}.sql.gz" | logger -t backup
    rm -f "$BACKUP_DIR/${DB}_${TIMESTAMP}.sql.gz"
  }
done

# Clean old backups
find "$BACKUP_DIR" -name "*.sql.gz" -mtime +$RETENTION_DAYS -delete
echo "Database backup completed at $(date)" | logger -t backup

Stage 2: Filesystem Archive

#!/bin/bash
# /usr/local/bin/backup-files.sh — Filesystem backup with parallel compression
set -euo pipefail

BACKUP_DIR="/var/backups/files"
SRC_DIRS="/var/www /etc /home"
EXCLUDE="--exclude=cache --exclude=tmp --exclude=.git"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
RETENTION_DAYS=14

mkdir -p "$BACKUP_DIR"

# Use pigz for parallel gzip compression (multi-core)
tar -I pigz -cf "$BACKUP_DIR/files_${TIMESTAMP}.tar.gz" \
  $EXCLUDE $SRC_DIRS 2>/dev/null

# Generate checksum for integrity verification
sha256sum "$BACKUP_DIR/files_${TIMESTAMP}.tar.gz" \
  > "$BACKUP_DIR/files_${TIMESTAMP}.tar.gz.sha256"

find "$BACKUP_DIR" -name "*.tar.gz" -mtime +$RETENTION_DAYS -delete
find "$BACKUP_DIR" -name "*.sha256" -mtime +$RETENTION_DAYS -delete
echo "Filesystem backup completed at $(date)" | logger -t backup

Stage 3: Encryption and Off-Site Transfer

#!/bin/bash
# /usr/local/bin/backup-encrypt-send.sh — Encrypt and transfer off-site
set -euo pipefail

GPG_RECIPIENT="[email protected]"
REMOTE_PATH="my-backup-bucket:vps-backups/$(hostname)/"

# Encrypt database backups
for f in /var/backups/db/*.sql.gz; do
  if [ -f "$f" ] && [ ! -f "${f}.gpg" ]; then
    gpg --encrypt --recipient "$GPG_RECIPIENT" --output "${f}.gpg" "$f"
    rm -f "$f"
  fi
done

# Encrypt filesystem archives
for f in /var/backups/files/*.tar.gz; do
  if [ -f "$f" ] && [ ! -f "${f}.gpg" ]; then
    gpg --encrypt --recipient "$GPG_RECIPIENT" --output "${f}.gpg" "$f"
    rm -f "$f"
  fi
done

# Sync encrypted backups to S3-compatible storage via rclone
rclone sync /var/backups/ "$REMOTE_PATH" --progress --checksum
echo "Off-site transfer completed at $(date)" | logger -t backup

Step 4: Automate with Cron

Schedule the pipeline in /etc/crontab:

# Database backup every 4 hours
0 */4 * * * root /usr/local/bin/backup-db.sh

# Filesystem backup daily at 2 AM
0 2 * * * root /usr/local/bin/backup-files.sh

# Encrypt and transfer to off-site daily at 4 AM
0 4 * * * root /usr/local/bin/backup-encrypt-send.sh

Conclusion

This shell-based backup pipeline runs entirely on your VPS with no third-party dependencies, encrypts data before transmission, and stores copies off-site for disaster recovery. Test your restore procedure monthly by spinning up a temporary VPS and restoring from the encrypted backup to verify integrity. Before provisioning backup storage infrastructure, see performance benchmarks on our comparison page to select providers with sufficient transfer speeds and storage IOPS for your backup workload.

Pro tip: For the database backup user, grant only SELECT, LOCK TABLES, SHOW VIEW, TRIGGER privileges — never use root credentials in backup scripts. Store the GPG private key on an offline machine for recovery scenarios.