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

Comments

useful guide.. Automating MySQL backups with rsync and Telegram notifications is a great idea. Thanks. hhow does this script handle very large databases? Would any adjustments be needed for performance or storage limits?

---

Just like from part one, the export is done and saved on your vps or remote machine, then this script the part two, transfers those saved sql exports to your offsite storage, The script transfers each file from start ti finish and sents a completed message, You can modify it to use any message platform of your choice.
Your local internet speed will be a subject of your transfer rate, fast internet equal faster transfter also consider the bandwidth of your server.