Managing a VPS (Virtual Private Server) with a MySQL database? Then you know how important **regular backups** are!
A single accidental command, server crash, or unexpected failure can **wipe out critical data** in seconds. That’s why automating backups is a must!
In this guide, you’ll learn how to:
- ✅ Automate MySQL backups on your VPS
- ✅ Compress backups to save space
- ✅ Automatically delete old backups
- ✅ Schedule backups using cron jobs
By the end of this guide, you’ll have a **fully automated, hands-free backup solution** running on your server!
Why You Need Automated MySQL Backups
Relying on **manual backups** is risky and inefficient. Here's why automation is essential:
- ❌ **Mistakes happen** – A single
DROP DATABASE command can erase years of data.
- ❌ **Downtime is expensive** – Losing customer data can damage your reputation and business.
- ❌ **Disk failures are unpredictable** – Outdated backups won’t help if your server crashes.
- ✅ **Automation ensures regular backups** – Set it once, and let your VPS handle the rest!
️ Prerequisites
Before we start, make sure you have:
- ✔️ A VPS running **Debian/Ubuntu**
- ✔️ MySQL/MariaDB installed
- ✔️ Sudo privileges
Step 1: Download the MySQL Backup Script
Clone the backup script from GitHub:
git clone https://github.com/giftbalogun/vpsMySQLBackup.git
cd vpsMySQLBackup
⚙️ Step 2: Configure the Backup Script
Open the script and edit your MySQL credentials and backup settings:
nano backup_mysql.sh
Modify these lines:
MYSQL_USER="your_mysql_username"
MYSQL_PASSWORD="your_mysql_password"
DAYS_TO_KEEP=5 # Set how many days to keep backups (0 to keep forever)
GZIP=1 # Set to 1 for compressed backups, 0 for raw SQL
BACKUP_PATH="/root/backup/mysql"
**Tip:** Make sure your MySQL user has the correct privileges!
Step 3: Make the Script Executable & Run It
Give the script execution permission:
chmod +x backup_mysql.sh
Run the script manually to test it:
sudo ./backup_mysql.sh
✅ If everything is set up correctly, you’ll see output like this:
--------------------------------------------------------
MySQL Backup Script - Copyright © 2024 Gift Balogun
--------------------------------------------------------
[INFO] Backing up database: my_database (compressed)
[SUCCESS] Backup completed: my_database
[INFO] Removing backups older than 5 days...
[INFO] Cleanup completed.
[INFO] MySQL Backup Process Completed Successfully!
Step 4: Automate Backups with Cron Jobs
To ensure backups run automatically, we’ll use **cron jobs**.
1️⃣ Open the cron job editor:
crontab -e
2️⃣ Add this line at the bottom:
0 2 * * * /path/to/backup_mysql.sh >> /var/log/mysql_backup.log 2>&1
This schedules the backup **every day at 2 AM** and logs the output.
3️⃣ Save and exit (CTRL + X, then Y and ENTER).
Done! Your server now automatically backs up MySQL databases daily.
️ Step 5: Restore a Backup (When Needed)
In case of data loss, restoring a backup is simple:
1️⃣ Find your backup file:
ls -lh /root/backup/mysql/
2️⃣ Restore it to MySQL:
mysql -u your_mysql_username -p your_database_name < /root/backup/mysql/your_backup.sql
If your backup is compressed (.sql.gz), use:
gunzip -c /root/backup/mysql/your_backup.sql.gz | mysql -u your_mysql_username -p your_database_name
️ Troubleshooting
- **Permission Denied?** Run the script with
sudo or check file permissions.
- **Crontab Not Running?** Ensure the script has execute permission and use the full path in
crontab -e.
- **Backup Files Missing?** Double-check the
BACKUP_PATH directory and DAYS_TO_KEEP settings.
Final Thoughts
Automating MySQL backups **saves time**, **prevents data loss**, and ensures **peace of mind**.
Want the full script? **Get it on GitHub:**
View Full Code Here
Need Help or Have Questions? Drop a comment below!