Database Backup and Recovery Automation for Cloud Databases
The Critical Importance of Automated Backups
Data loss is not a question of if, but when. Hardware failures, accidental deletions, ransomware, and software bugs can all destroy your database. Without automated, tested backups, a single incident can be catastrophic. On ServerRaja Cloud VPS, you are responsible for your own backup strategy—our infrastructure provides the raw storage and compute, but protecting your data is your duty.
This guide provides production-ready backup automation scripts for both MySQL and PostgreSQL, covering full backups, incremental strategies, point-in-time recovery, and off-site storage.
Backup Strategy Overview
A robust backup strategy follows the 3-2-1 rule: - **3** copies of your data - **2** different storage media - **1** off-site copy
For cloud databases, this typically means: 1. Local backup on the database server (for fast recovery) 2. Copy to a separate ServerRaja VPS or object storage (for redundancy) 3. Copy to a different region or cloud provider (for disaster recovery)
MySQL Backup Automation
Full Daily Backup with mysqldump
#!/bin/bash
# /opt/backups/mysql-full-backup.shBACKUP_DIR="/opt/backups/mysql" DATE=$(date +%Y%m%d_%H%M%S) RETENTION_DAYS=7 MYSQL_USER="backup_user" MYSQL_PASS="BackupUserSecurePass123!"
mkdir -p "$BACKUP_DIR"
# Perform full backup with all databases mysqldump \ --user="$MYSQL_USER" \ --password="$MYSQL_PASS" \ --all-databases \ --single-transaction \ --routines \ --triggers \ --events \ --flush-logs \ --master-data=2 \ --hex-blob \ --result-file="${BACKUP_DIR}/full_${DATE}.sql"
# Compress the backup gzip "${BACKUP_DIR}/full_${DATE}.sql"
# Calculate checksum for integrity verification sha256sum "${BACKUP_DIR}/full_${DATE}.sql.gz" > "${BACKUP_DIR}/full_${DATE}.sql.gz.sha256"
# Remove backups older than retention period find "$BACKUP_DIR" -name "full_*.sql.gz" -mtime +$RETENTION_DAYS -delete find "$BACKUP_DIR" -name "full_*.sha256" -mtime +$RETENTION_DAYS -delete
# Log the backup echo "[$(date)] Full backup completed: full_${DATE}.sql.gz" >> /var/log/mysql-backup.log
# Verify backup integrity if gzip -t "${BACKUP_DIR}/full_${DATE}.sql.gz"; then echo "[$(date)] Backup integrity check passed" >> /var/log/mysql-backup.log else echo "[$(date)] ERROR: Backup integrity check FAILED" >> /var/log/mysql-backup.log # Send alert echo "MySQL backup integrity check failed on $(hostname)" | \ mail -s "BACKUP ALERT" [email protected] fi ```
Binary Log Backup for Point-in-Time Recovery
#!/bin/bash
# /opt/backups/mysql-binlog-backup.shBINLOG_DIR="/var/log/mysql" BACKUP_DIR="/opt/backups/mysql/binlogs" DATE=$(date +%Y%m%d_%H%M%S)
mkdir -p "$BACKUP_DIR"
# Flush current binary log and archive previous ones mysql -u backup_user -p'BackupUserSecurePass123!' -e "FLUSH BINARY LOGS;"
# Copy completed binary logs to backup directory cp ${BINLOG_DIR}/mysql-bin.[0-9]* "$BACKUP_DIR/"
# Compress older binlogs find "$BACKUP_DIR" -name "mysql-bin.*" ! -name "mysql-bin.$(ls -t ${BINLOG_DIR}/mysql-bin.[0-9]* | head -1 | xargs basename | sed 's/mysql-bin.//')" -exec gzip {} \;
echo "[$(date)] Binary log backup completed" >> /var/log/mysql-backup.log ```
PostgreSQL Backup Automation
Full Backup with pg_dumpall
#!/bin/bash
# /opt/backups/postgresql-full-backup.shBACKUP_DIR="/opt/backups/postgresql" DATE=$(date +%Y%m%d_%H%M%S) RETENTION_DAYS=7
mkdir -p "$BACKUP_DIR"
# Full cluster backup (includes roles and tablespaces) pg_dumpall -U postgres --clean --if-exists \ > "${BACKUP_DIR}/full_${DATE}.sql"
# Compress gzip "${BACKUP_DIR}/full_${DATE}.sql"
# SHA256 checksum sha256sum "${BACKUP_DIR}/full_${DATE}.sql.gz" \ > "${BACKUP_DIR}/full_${DATE}.sql.gz.sha256"
# Cleanup old backups find "$BACKUP_DIR" -name "full_*.sql.gz" -mtime +$RETENTION_DAYS -delete find "$BACKUP_DIR" -name "full_*.sha256" -mtime +$RETENTION_DAYS -delete
echo "[$(date)] PostgreSQL full backup completed: full_${DATE}.sql.gz" \ >> /var/log/pg-backup.log ```
Custom Format Backup for Flexible Recovery
#!/bin/bash
# /opt/backups/postgresql-custom-backup.shBACKUP_DIR="/opt/backups/postgresql" DATE=$(date +%Y%m%d_%H%M%S) databases=("appdb" "analytics" "users")
mkdir -p "$BACKUP_DIR"
for db in "${databases[@]}"; do # Custom format allows selective table recovery pg_dump -U postgres -Fc --verbose "$db" \ > "${BACKUP_DIR}/${db}_${DATE}.dump" # List contents for verification pg_restore -l "${BACKUP_DIR}/${db}_${DATE}.dump" \ > "${BACKUP_DIR}/${db}_${DATE}.toc" echo "[$(date)] Backed up database: $db" >> /var/log/pg-backup.log done ```
Point-in-Time Recovery (PITR)
PostgreSQL PITR with WAL Archiving
Configure continuous WAL archiving in `postgresql.conf`:
archive_mode = on
archive_command = 'cp %p /opt/backups/postgresql/wal/%f'
wal_level = replica
max_wal_senders = 3
Recovery procedure:
# Stop PostgreSQL
sudo systemctl stop postgresql# Restore base backup cp /opt/backups/postgresql/full_20250701_020000.sql.gz /tmp/ gunzip /tmp/full_20250701_020000.sql.gz
# Create recovery signal file touch /var/lib/postgresql/16/main/recovery.signal
# Set recovery target in postgresql.conf # recovery_target_time = '2025-07-15 14:30:00' # restore_command = 'cp /opt/backups/postgresql/wal/%f %p'
sudo systemctl start postgresql ```
Scheduling Backups with Cron
# Edit crontab
sudo crontab -e# MySQL: Full backup daily at 2 AM, binlog backup every hour 0 2 * * * /opt/backups/mysql-full-backup.sh >> /var/log/mysql-backup.log 2>&1 0 * * * * /opt/backups/mysql-binlog-backup.sh >> /var/log/mysql-backup.log 2>&1
# PostgreSQL: Full backup daily at 3 AM, custom format every 6 hours 0 3 * * * /opt/backups/postgresql-full-backup.sh >> /var/log/pg-backup.log 2>&1 0 */6 * * * /opt/backups/postgresql-custom-backup.sh >> /var/log/pg-backup.log 2>&1 ```
Off-Site Backup with rsync
#!/bin/bash
# /opt/backups/sync-offsite.shLOCAL_DIR="/opt/backups" REMOTE_USER="backup" REMOTE_HOST="backup-server.yourdomain.com" REMOTE_DIR="/backups/$(hostname)"
rsync -avz --delete \ -e "ssh -i /root/.ssh/backup_key" \ "$LOCAL_DIR/" \ "${REMOTE_USER}@${REMOTE_HOST}:${REMOTE_DIR}/"
echo "[$(date)] Off-site sync completed" >> /var/log/backup-sync.log ```
Testing Your Backups
An untested backup is no backup at all. Schedule monthly restore tests:
#!/bin/bash
# /opt/backups/test-restore-mysql.shLATEST_BACKUP=$(ls -t /opt/backups/mysql/full_*.sql.gz | head -1) TEST_DB="restore_test_$(date +%Y%m%d)"
# Restore to a test database gunzip -c "$LATEST_BACKUP" | mysql -u root -p'rootpass' "$TEST_DB"
# Verify row counts echo "Restored database size:" mysql -u root -p'rootpass" -e "SELECT table_name, table_rows FROM information_schema.tables WHERE table_schema='${TEST_DB}';"
# Cleanup mysql -u root -p'rootpass" -e "DROP DATABASE ${TEST_DB};"
echo "[$(date)] Restore test completed successfully" >> /var/log/mysql-backup.log ```
Conclusion
Automated, verified backups are your last line of defense against data loss. Implement the scripts in this guide on your ServerRaja Cloud VPS, configure off-site storage, and—most importantly—test your restores regularly. The time you invest in backup automation today will save you from a potentially business-ending disaster tomorrow.