How to Set Up Database Backup Automatically

Prerequisites

Before setting up automated DB backups, make sure you have:

• SSH access to your VPS
• MySQL/MariaDB installed
• Root or sudo privileges (or database user with SELECT, LOCK TABLES)

Step 1: Create Backup Directory

Connect to your VPS:

ssh hxroot@YOUR_SERVER_IP -p 22

sudo mkdir -p /backup/mysql
sudo chmod 750 /backup/mysql

Step 2: Create Backup Script

sudo nano /usr/local/bin/auto-db-backup.sh

Add content:

#!/bin/bash
BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
DB_USER="root"
DB_PASS="your_root_password"
RETENTION_DAYS=7

# Backup all databases
mysqldump -u $DB_USER -p$DB_PASS --all-databases --single-transaction --quick --lock-tables=false > $BACKUP_DIR/all_databases_$DATE.sql

# Compress
gzip $BACKUP_DIR/all_databases_$DATE.sql

# Delete backups older than RETENTION_DAYS
find $BACKUP_DIR -type f -name "*.sql.gz" -mtime +$RETENTION_DAYS -delete

echo "Database backup completed at $(date)" >> /var/log/db-backup.log

# Optional: Sync to remote server
# rsync -avz $BACKUP_DIR/ user@remote:/backup/mysql/

Make executable:

sudo chmod +x /usr/local/bin/auto-db-backup.sh

Step 3: Test the Script

sudo /usr/local/bin/auto-db-backup.sh

ls -la /backup/mysql/

Step 4: Schedule via Cron

sudo crontab -e

0 1 * * * /usr/local/bin/auto-db-backup.sh

Runs nightly at 1:00 AM.

Backup Specific Database Only

Modify script:

mysqldump -u $DB_USER -p$DB_PASS --single-transaction your_database_name > $BACKUP_DIR/your_database_$DATE.sql

Backup Multiple Databases Separately

for DB in db1 db2 db3; do
    mysqldump -u $DB_USER -p$DB_PASS $DB > $BACKUP_DIR/${DB}_$DATE.sql
    gzip $BACKUP_DIR/${DB}_$DATE.sql
done

Use .my.cnf to Avoid Password in Script

Create MySQL config file:

sudo nano /root/.my.cnf

[client]
user=root
password=your_root_password

sudo chmod 600 /root/.my.cnf

Then in script, remove -p option:

mysqldump --all-databases > $BACKUP_DIR/all_$DATE.sql

Restore from Backup

gunzip /backup/mysql/all_databases_20260429.sql.gz
mysql < /backup/mysql/all_databases_20260429.sql

Email Alert on Backup Failure

if [ $? -eq 0 ]; then
    echo "Backup successful" >> /var/log/db-backup.log
else
    echo "Backup FAILED" | mail -s "DB Backup Failed" admin@example.com
fi