Automating MySQL Backups on a VPS with Telegram Alerts Part 2

Question

Backing up MySQL databases is essential for data security and disaster recovery. In this article, we continue from our previous post and provide a script to automate MySQL database backups from a VPS to a local machine. This script ensures your backups are safe and up-to-date.

Prerequisites

• A Debian-based VPS
• MySQL installed and running
• SSH access to your VPS
• A local machine with enough storage for backups

MySQL Backup Script

The following script automates MySQL database backups by securely transferring them from a VPS to a local folder. It also sends notifications via Telegram when a backup succeeds or fails.

Script Overview

• Uses rsync to transfer backup files from the VPS.
• Logs all actions for debugging purposes.
• Sends a Telegram notification on success or failure.
• Cleans up old backup files on the VPS after a successful transfer.

Backup Script

#!/bin/bash
# MySQL Backup Script for VPS
# Author: Gift Balogun

# Configuration
REMOTE_HOST="your_vps_ip"
REMOTE_USER="root"
REMOTE_PORT=22
LOCAL_PATH="/WebsiteBackup"
REMOTE_PATH="/root/backup/mysql"
LOG_FILE="/var/log/mysql_backup.log"

# Telegram Notification
TELEGRAM_BOT_TOKEN="your_bot_token"
TELEGRAM_CHAT_ID="your_chat_id"

# Logging function
log() {
    echo "$(date '+%Y-%m-%d %H:%M:%S') - $1" | tee -a "$LOG_FILE"
}

log "Starting MySQL backup from $REMOTE_HOST..."

# Ensure host key is added
ssh-keyscan -p $REMOTE_PORT $REMOTE_HOST >> ~/.ssh/known_hosts 2>/dev/null

# Perform backup
rsync -e "ssh -o StrictHostKeyChecking=no -p $REMOTE_PORT" -avzP "$REMOTE_USER@$REMOTE_HOST:$REMOTE_PATH" "$LOCAL_PATH" 2>> "$LOG_FILE"

if [ $? -eq 0 ]; then
    log "Backup completed successfully."
    curl -s -X POST "https://api.telegram.org/bot$TELEGRAM_BOT_TOKEN/sendMessage" \
         -d chat_id="$TELEGRAM_CHAT_ID" \
         -d text="Backup completed successfully from $REMOTE_HOST to $LOCAL_PATH." >> "$LOG_FILE"
    
    # Cleanup remote backup folder
    ssh "$REMOTE_USER@$REMOTE_HOST" -p "$REMOTE_PORT" "rm -rf $REMOTE_PATH/*" 2>> "$LOG_FILE"
    log "Remote files deleted successfully."
else
    log "Backup failed. Check logs for details."
    curl -s -X POST "https://api.telegram.org/bot$TELEGRAM_BOT_TOKEN/sendDocument" \
         -F chat_id="$TELEGRAM_CHAT_ID" \
         -F document=@"$LOG_FILE"
fi

Explanation

1. SSH and rsync Setup:

   • The script ensures that the VPS host key is added to known_hosts to prevent SSH authentication issues.
   • rsync securely transfers MySQL backups from the VPS to the local machine.

2. Logging and Notifications:

   • The script logs each action to /var/log/mysql_backup.log.
   • If the backup succeeds, a success message is sent via Telegram.
   • If it fails, the log file is sent to Telegram for troubleshooting.

3. Cleanup:

   • After a successful backup, the remote backup folder is cleared to save space.

Running the Script

• Save the script as backup.sh.
• Make it executable: chmod +x backup.sh
• Run it manually: ./backup.sh
• Schedule it with cron: crontab -e

  0 2 * * * /path/to/backup.sh
  

  This runs the script every day at 2 AM.

Conclusion

This script simplifies MySQL database backups and ensures data safety. You can find the full script on GitHub:
https://github.com/giftbalogun/localmysqlbackup

If you haven't read Part 1, check it out here:
https://coderlegion.com/1487/automate-your-vps-database-backups-like-a-pro-a-complete-mysql-backup-script