How to Set Up Automated Database Backups on Your VPS: MySQL and PostgreSQL

Database backups are your safety net. Whether you run MySQL or PostgreSQL on your VPS, an automated offsite backup strategy ensures you can recover from corruption, accidental deletion, or server failure in minutes instead of days. This guide walks through setting up automated backups using cron, mysqldump/pg_dump, and offsite storage — with scripts you can deploy in under 15 minutes.

Prerequisites

• A Linux VPS (Ubuntu 22.04/24.04 recommended) with root or sudo access
• MySQL 8.0+ or PostgreSQL 15+ installed and running
• An offsite storage destination (we’ll cover S3-compatible, rsync, and cloud storage options)
• Basic familiarity with the command line

Step 1: Creating the Backup Script for MySQL

Create a dedicated backup user with minimal privileges — never use root for automated backups:

CREATE USER 'backup'@'localhost' IDENTIFIED BY 'strong-password-here';
GRANT SELECT, SHOW VIEW, RELOAD, REPLICATION CLIENT, EVENT, TRIGGER ON *.* TO 'backup'@'localhost';
FLUSH PRIVILEGES;

Now create the backup script at /usr/local/bin/backup-mysql.sh:

#!/bin/bash
set -e

DB_USER="backup"
DB_PASS="strong-password-here"
BACKUP_DIR="/var/backups/mysql"
DATE=$(date +%Y-%m-%d_%H-%M-%S)
RETENTION_DAYS=7

mkdir -p "$BACKUP_DIR"

# Dump each database separately
databases=$(mysql -u"$DB_USER" -p"$DB_PASS" -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema|sys)")

for db in $databases; do
    mysqldump --single-transaction --routines --triggers         -u"$DB_USER" -p"$DB_PASS" "$db"         | gzip > "$BACKUP_DIR/${db}_${DATE}.sql.gz"
    echo "Backed up: $db"
done

# Remove backups older than retention period
find "$BACKUP_DIR" -name "*.sql.gz" -mtime +$RETENTION_DAYS -delete

echo "MySQL backup completed: $DATE"

Make it executable: chmod +x /usr/local/bin/backup-mysql.sh

Step 2: Creating the Backup Script for PostgreSQL

PostgreSQL uses pg_dump per database or pg_dumpall for a cluster-level backup. Create /usr/local/bin/backup-pgsql.sh:

#!/bin/bash
set -e

BACKUP_DIR="/var/backups/postgresql"
DATE=$(date +%Y-%m-%d_%H-%M-%S)
RETENTION_DAYS=7

mkdir -p "$BACKUP_DIR"

# Get list of databases (exclude template databases)
databases=$(sudo -u postgres psql -t -c "SELECT datname FROM pg_database WHERE datistemplate = false;")

for db in $databases; do
    sudo -u postgres pg_dump --no-owner --compress=9 "$db"         > "$BACKUP_DIR/${db}_${DATE}.sql.gz"
    echo "Backed up: $db"
done

# Remove old backups
find "$BACKUP_DIR" -name "*.sql.gz" -mtime +$RETENTION_DAYS -delete

echo "PostgreSQL backup completed: $DATE"

Make it executable: chmod +x /usr/local/bin/backup-pgsql.sh

Step 3: Automating with Cron

Add a cron entry to run daily at 3 AM:

# Edit crontab
sudo crontab -e

# Add one of these lines depending on your database:
# MySQL
0 3 * * * /usr/local/bin/backup-mysql.sh >> /var/log/db-backup.log 2>&1

# PostgreSQL
0 3 * * * /usr/local/bin/backup-pgsql.sh >> /var/log/db-backup.log 2>&1

For databases that can’t tolerate losing up to 24 hours of data, add an hourly differential backup:

# Hourly incremental (MySQL) — requires binary logging
0 * * * * mysql -u"$DB_USER" -p"$DB_PASS" -e "FLUSH BINARY LOGS;" &&   cp /var/lib/mysql/binlog.* /var/backups/mysql/binlogs/

Step 4: Offsite Storage Options

A backup on the same server is not a real backup — if the disk fails, you lose both the database and its backup. Here are offsite options sorted by ease of setup:

Method	Setup Time	Monthly Cost	Best For
rsync to another VPS	10 minutes	$5–10 additional VPS	Users with a second server
S3-compatible object storage	15 minutes	$2–5 per 100 GB	Production workloads
rsync.net / BorgBase	20 minutes	$12–15 per 100 GB	Offsite dedicated backup service
Google Drive / Dropbox (rclone)	10 minutes	Free (up to 15 GB)	Personal/low-volume backups

Using rclone for S3-compatible storage:

# Install rclone
sudo apt install rclone -y
rclone config  # Follow prompts for your S3 provider

# Add to the backup script
rclone sync "$BACKUP_DIR" "remote:bucket-name/db-backups/"

Step 5: Testing Your Backups

Untested backups are no backups at all. Add a monthly restore test using a separate staging VPS:

# Test restore for MySQL
gunzip < /var/backups/mysql/mydb_2026-01-01_03-00-00.sql.gz | mysql -u root -p

# Test restore for PostgreSQL
gunzip < /var/backups/postgresql/mydb_2026-01-01_03-00-00.sql.gz | sudo -u postgres psql

Monitoring Backup Health

Add basic health checks to detect backup failures before you need them:

• Check backup file size — a zero-byte file means the backup failed silently
• Monitor backup logs (/var/log/db-backup.log) for error strings
• Use Uptime Kuma or Healthchecks.io to ping a URL after each successful backup
• Set up email or Slack alerts when backup size changes by more than 20%

Putting It All Together

Automated database backups on a VPS are straightforward: create a backup script, schedule it with cron, and sync the results offsite. The entire setup takes under 30 minutes and will save you days of downtime if something goes wrong. Compare VPS plans on our comparison table to find a provider with sufficient storage for your backup retention needs.