Script to automate remote backup of local mysql database

A script that runs locally on the MySQL server, dumps the MySQL database(s), and then automatically sends the backup offsite (remote server) for safe storage.

Key Features of this Script:

Dumps local MySQL database.
Compresses the dump.
Sends it securely to a remote backup server via scp.
Cleans up old local and remote backups.
Logging included.

Script: `mysql_remote_backup.sh`

#!/bin/bash

# -------------- CONFIGURATION ---------------- #

# MySQL Credentials (local server)
MYSQL_USER="root"
MYSQL_PASS="your_mysql_password"
MYSQL_DB="your_database_name"   # Or --all-databases

# Local Temporary Backup Directory
LOCAL_BACKUP_DIR="/tmp/mysql-backup"
LOG_FILE="/var/log/mysql-remote-backup.log"

# Remote Backup Server Details
REMOTE_USER="backupuser"
REMOTE_HOST="your.remote-server.com"
REMOTE_PORT="22"
REMOTE_DIR="/backups/mysql"

# Retention
LOCAL_RETENTION_DAYS=3     # Delete local backups older than 3 days
REMOTE_RETENTION_DAYS=7    # Delete remote backups older than 7 days

# -------------- SCRIPT START ----------------- #

DATE=$(date +%F-%H%M%S)
BACKUP_NAME="${MYSQL_DB}-backup-${DATE}.sql"
ARCHIVE_NAME="${BACKUP_NAME}.tar.gz"

mkdir -p $LOCAL_BACKUP_DIR

echo "[$(date)] Starting MySQL Backup..." | tee -a $LOG_FILE

# Dump MySQL Database
mysqldump -u$MYSQL_USER -p$MYSQL_PASS $MYSQL_DB > $LOCAL_BACKUP_DIR/$BACKUP_NAME

if [ $? -ne 0 ]; then
    echo "[$(date)] MySQL Dump Failed!" | tee -a $LOG_FILE
    exit 1
fi

# Compress Backup
tar -czf $LOCAL_BACKUP_DIR/$ARCHIVE_NAME -C $LOCAL_BACKUP_DIR $BACKUP_NAME
rm -f $LOCAL_BACKUP_DIR/$BACKUP_NAME

# Transfer Backup Offsite via SCP
scp -P $REMOTE_PORT $LOCAL_BACKUP_DIR/$ARCHIVE_NAME ${REMOTE_USER}@${REMOTE_HOST}:${REMOTE_DIR}/

if [ $? -eq 0 ]; then
    echo "[$(date)] Backup Successfully Transferred Offsite." | tee -a $LOG_FILE
else
    echo "[$(date)] SCP Transfer Failed!" | tee -a $LOG_FILE
    exit 1
fi

# Cleanup Local Old Backups
find $LOCAL_BACKUP_DIR -type f -mtime +$LOCAL_RETENTION_DAYS -delete

# Cleanup Remote Old Backups
ssh -p $REMOTE_PORT ${REMOTE_USER}@${REMOTE_HOST} "find ${REMOTE_DIR} -type f -mtime +${REMOTE_RETENTION_DAYS} -delete"

echo "[$(date)] MySQL Backup Completed Successfully." | tee -a $LOG_FILE

exit 0

Steps to Use:

1. Save the script:

1	vi /root/mysql_remote_backup.sh

2. Make it executable:

1	chmod +x /root/mysql_remote_backup.sh

3. Run it manually:

1	/root/mysql_remote_backup.sh

Automate via Cron:

Backup daily at 1AM:

1	crontab -e

Add:

1	0 1 * * * /root/mysql_remote_backup.sh

SSH Key Authentication (Recommended):

1 2	ssh-keygen ssh-copy-id -p 22 [email protected]

Logs Location:

1	/var/log/mysql-remote-backup.log

Backup Locations:

Location	Path	Retention
Local	/tmp/mysql-backup/	3 days
Remote	/backups/mysql/ (on remote)	7 days

Other:

Cheap VPS Recommendations Page:

VPS Deals

Apr 06, 2025