How to set up automated database backups on DigitalOcean

Log into your DigitalOcean account and navigate to the Databases section in the left sidebar. If you're using a managed database, select your database cluster from the list. For self-managed databases on droplets, go to Droplets and select your database server.

For managed databases, click on your database cluster and go to the Settings tab. Under Backup Configuration, enable Automated Backups by toggling the switch. Set your preferred backup window during low-traffic hours and configure the retention period (7-35 days available).

Select Enable Point-in-Time Recovery for additional protection, which allows restoration to any specific moment within the retention window.

In the Settings tab, scroll to Alerting and click Add Alert. Configure email notifications for backup failures by selecting Backup Status as the metric and entering your email address. Set the alert threshold to trigger on backup failures or warnings.

For droplet-based databases, SSH into your server and create a backup script:

#!/bin/bash
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_DIR="/var/backups/mysql"
mkdir -p $BACKUP_DIR

# For MySQL
mysqldump -u root -p[PASSWORD] --all-databases > $BACKUP_DIR/backup_$DATE.sql

# For PostgreSQL
pg_dumpall -U postgres > $BACKUP_DIR/backup_$DATE.sql

# Compress backup
gzip $BACKUP_DIR/backup_$DATE.sql

# Remove backups older than 7 days
find $BACKUP_DIR -name "*.gz" -mtime +7 -delete

Save this script as /usr/local/bin/db_backup.sh and make it executable with chmod +x /usr/local/bin/db_backup.sh.

Open the crontab editor with crontab -e and add a line to schedule daily backups:

0 2 * * * /usr/local/bin/db_backup.sh >> /var/log/db_backup.log 2>&1

This runs the backup script every day at 2 AM and logs output to /var/log/db_backup.log. Verify the cron job is scheduled by running crontab -l.

Create a DigitalOcean Space for storing backups by going to Spaces in your control panel and clicking Create a Space. Choose a datacenter region and name your space. Install the AWS CLI tool on your droplet with apt install awscli and configure it with your Spaces access keys:

aws configure --profile digitalocean

Modify your backup script to upload compressed backups to Spaces using: aws s3 cp $BACKUP_DIR/backup_$DATE.sql.gz s3://your-space-name/ --profile digitalocean

For managed databases, trigger a manual backup from the Backups tab to verify the process works. For self-managed databases, run your backup script manually with /usr/local/bin/db_backup.sh and check the output in /var/log/db_backup.log.

Verify backups are being created and stored properly by checking your backup directory or Spaces bucket. Test restoration from a backup to ensure data integrity.

Set up monitoring by checking the Metrics tab in your database dashboard for managed databases. For self-managed setups, create a monitoring script that checks backup log files and sends alerts on failures. Regularly review backup sizes and adjust retention policies as needed.

Consider setting up additional monitoring with DigitalOcean's monitoring agent or third-party tools to track backup job success rates and storage usage.