DEV Community

Yash
Yash

Posted on

How to Set Up Automated Database Backups on Linux (PostgreSQL and MySQL)

#postgres #mysql #devops #linux

How to Set Up Automated Database Backups on Linux (PostgreSQL and MySQL)

If your production database isn't being backed up automatically, you're one bad deploy away from losing everything.

This is how to set up automated backups that actually work.

PostgreSQL Automated Backup

The Backup Script 

sudo nano /usr/local/bin/pg-backup.sh
Enter fullscreen mode Exit fullscreen mode 
#!/bin/bash
set -e

BACKUP_DIR="/var/backups/postgresql"
DATE=$(date +%Y%m%d_%H%M%S)
KEEP_DAYS=7
DB_NAME="${1:-all}"  # Pass db name or backs up all

mkdir -p "$BACKUP_DIR"

if [ "$DB_NAME" = "all" ]; then
    # Backup all databases
    pg_dumpall -U postgres | gzip > "$BACKUP_DIR/all_${DATE}.sql.gz"
else
    # Backup specific database
    pg_dump -U postgres -Fc "$DB_NAME" > "$BACKUP_DIR/${DB_NAME}_${DATE}.dump"
fi

# Remove backups older than KEEP_DAYS
find "$BACKUP_DIR" -type f -mtime +$KEEP_DAYS -delete

# Verify backup file exists and is not empty
LATEST=$(ls -t "$BACKUP_DIR" | head -1)
if [ ! -s "$BACKUP_DIR/$LATEST" ]; then
    echo "ERROR: Backup file is empty!" | mail -s "DB Backup FAILED" you@email.com
    exit 1
fi

echo "Backup successful: $BACKUP_DIR/$LATEST ($(du -h "$BACKUP_DIR/$LATEST" | cut -f1))"
Enter fullscreen mode Exit fullscreen mode 
chmod +x /usr/local/bin/pg-backup.sh
Enter fullscreen mode Exit fullscreen mode

Set Up pg_hba.conf for Passwordless Local Backup 

sudo nano /etc/postgresql/*/main/pg_hba.conf
Enter fullscreen mode Exit fullscreen mode

Add (for local connections):

local   all             postgres                                trust
Enter fullscreen mode Exit fullscreen mode

Or use a .pgpass file:

# ~/.pgpass
# hostname:port:database:username:password
localhost:5432:*:postgres:your_password
chmod 600 ~/.pgpass
Enter fullscreen mode Exit fullscreen mode

Schedule with Cron 

sudo crontab -e
Enter fullscreen mode Exit fullscreen mode 
# Daily backup at 2 AM
0 2 * * * /usr/local/bin/pg-backup.sh mydb >> /var/log/pg-backup.log 2>&1

# Weekly full backup on Sunday
0 1 * * 0 /usr/local/bin/pg-backup.sh all >> /var/log/pg-backup.log 2>&1
Enter fullscreen mode Exit fullscreen mode

MySQL Automated Backup 

sudo nano /usr/local/bin/mysql-backup.sh
Enter fullscreen mode Exit fullscreen mode 
#!/bin/bash
set -e

BACKUP_DIR="/var/backups/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
KEEP_DAYS=7
MYSQL_USER="backup_user"
MYSQL_PASS="${MYSQL_BACKUP_PASSWORD}"  # Set as env var in cron

mkdir -p "$BACKUP_DIR"

# Get all databases
DATABASES=$(mysql -u "$MYSQL_USER" -p"$MYSQL_PASS" -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema|sys)")

for DB in $DATABASES; do
    mysqldump -u "$MYSQL_USER" -p"$MYSQL_PASS"         --single-transaction         --routines         --triggers         "$DB" | gzip > "$BACKUP_DIR/${DB}_${DATE}.sql.gz"
    echo "Backed up: $DB"
done

find "$BACKUP_DIR" -type f -mtime +$KEEP_DAYS -delete
echo "Backup complete. Files in $BACKUP_DIR"
Enter fullscreen mode Exit fullscreen mode

Create a dedicated backup user:

CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'strong_password';
GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES ON *.* TO 'backup_user'@'localhost';
FLUSH PRIVILEGES;
Enter fullscreen mode Exit fullscreen mode

Off-Site Backup (Critical)

Local backups protect against mistakes. Off-site protects against server failure.

Sync to S3 

# Install AWS CLI
sudo apt install awscli -y
aws configure  # Add your AWS credentials

# Add to backup script
aws s3 cp "$BACKUP_DIR/$LATEST" s3://your-backup-bucket/postgresql/

# Or sync entire directory
aws s3 sync "$BACKUP_DIR" s3://your-backup-bucket/postgresql/
Enter fullscreen mode Exit fullscreen mode

Test Your Backups Monthly 

# PostgreSQL restore test
pg_restore -U postgres -d test_restore_db /var/backups/postgresql/latest.dump

# MySQL restore test
gunzip -c /var/backups/mysql/mydb_latest.sql.gz | mysql -u root -p test_restore_db
Enter fullscreen mode Exit fullscreen mode

A backup you've never tested is not a backup.

I built ARIA to solve exactly this.
Try it free at step2dev.com — no credit card needed.

Top comments (0)