Backups are essential for keeping your data safe, and automating the backup process can save you both time and effort. If your database is ever lost or corrupted, having a recent backup can be a lifesaver.
Regular backups are crucial for any website or application using a MySQL database. Automating this process ensures that backups occur regularly, without the need to remember to perform them manually.
In this guide, we’ll show you how to create a simple Bash script to automate MySQL database backups. Then, we’ll set up a Cron job to run this backup script automatically at scheduled times, ensuring that your databases are backed up without fail.
Step 1: Creating MySQL Backup Script
First, let’s create a simple Bash script that will handle the backup process.
nano backup_mysql.sh
Copy and paste the following script into the backup_mysql.sh
file:
#!/bin/bash # MySQL Credentials MYSQL_USER="your_mysql_username" MYSQL_PASS="your_mysql_password" MYSQL_HOST="localhost" # Backup Directory (ensure this directory exists) BACKUP_DIR="/path/to/your/backup/directory" # Email settings EMAIL_TO="[email protected]" EMAIL_SUBJECT="MySQL Backup Report - $(date +"%Y-%m-%d %H:%M:%S")" EMAIL_BODY="" # Get current date to append to backup filename DATE=$(date +"%Y-%m-%d_%H-%M-%S") # Databases to back up (list the names of the databases you want to back up) DATABASES=("db1" "db2" "db3") # Loop through each database and back it up for DB in "${DATABASES[@]}"; do BACKUP_FILE="$BACKUP_DIR/$DB_$DATE.sql" echo "Backing up database $DB to $BACKUP_FILE..." # Perform the backup using mysqldump mysqldump -u $MYSQL_USER -p$MYSQL_PASS -h $MYSQL_HOST $DB > $BACKUP_FILE # Check if the backup was successful if [ $? -eq 0 ]; then echo "Backup of $DB completed successfully!" else echo "Backup of $DB failed!" fi done # Clean up backups older than 30 days (optional) find $BACKUP_DIR -type f -name "*.sql" -mtime +30 -exec rm -f {} \; # Send email alert echo -e "$EMAIL_BODY" | mail -s "$EMAIL_SUBJECT" "$EMAIL_TO"
What Does This Script Do?
- MySQL Credentials: You need to provide your MySQL username, password, and host (usually localhost).
- Backup Directory: This is where your backups will be stored. Make sure this directory exists on your system.
- Timestamped Backup: The script creates backups with a timestamp (e.g., 2025-04-28_12-30-00.sql) to avoid overwriting old backups.
- Databases to Back Up: In the
DATABASES
array, list the names of the databases you want to back up. You can add or remove databases as needed. - Backup Command: The script uses mysqldump to create backups of your databases.
- Old Backup Cleanup: The script also deletes backups older than 30 days (you can adjust this time as needed).
- Email Alerts: After running the backup, the script sends an email with the results – whether each database backup succeeded or failed.
Sync Backup to Remote Server (Optional but Recommended)
After your local backup is created, it’s a smart move to sync it to a remote server for extra safety, which ensures your backups survive even if your main server crashes.
Here’s the rsync command you can add to the end of your backup script:
# Sync backup to remote server (optional but recommended) SSH_KEY="/path/to/your/private/key.pem" # Path to your SSH private key REMOTE_USER="your_remote_user" # Remote server username REMOTE_HOST="your.remote.server.com" # Remote server address REMOTE_DIR="/path/on/remote/server" # Target directory on remote server rsync -avz \ -e "ssh -i $SSH_KEY -o StrictHostKeyChecking=no" \ --delete-after \ "$BACKUP_DIR/" \ "$REMOTE_USER@$REMOTE_HOST:$REMOTE_DIR/"
Make sure the remote server is reachable, and the destination directory exists with proper write permissions.
Once you’ve saved your script, make it executable by running chmod
command:
chmod +x backup_mysql.sh
Step 2: Testing MySQL Backup Script
Before setting up the Cron job, it’s a good idea to test the script manually to make sure everything is working as expected.
./backup_mysql.sh
Check your backup directory to ensure the backups are created successfully. If everything looks good, proceed to the next step.
Step 3: Automating MySQL Backups with Cron Jobs
Now that we have the backup script, the next step is to automate it by using Cron, a tool that runs commands at scheduled times.
crontab -e
Add a Cron job to run the backup script automatically. For example, to run the script every day at 2 AM, add the following line:
0 2 * * * /bin/bash /path/to/your/backup_mysql.sh
Here’s how the Cron schedule works:
0
: The minute (0th minute).2
: The hour (2 AM).*
: Every day of the month.*
: Every month.*
: Every day of the week.
To verify that your Cron job is running, you can check the system logs or temporarily set the script to run at a closer time to see if it works.
grep CRON /var/log/syslog
Additional Considerations
- Security: Storing your MySQL password in the script is convenient but not secure. For better security, you can store your credentials in a
.my.cnf
file in your home directory and configure the script to read from there. - Backup Location: Make sure that the backup directory has enough space for your backups. If you’re running multiple backups, it’s a good idea to set up a separate storage location (like an external hard drive or cloud storage).
- Backup Frequency: Depending on how often your data changes, you might want to adjust the Cron job schedule. For example, you could run the backup every hour, every week, or only on certain days.
Conclusion
By automating MySQL backups with a simple Bash script and Cron job, you ensure that your databases are safely backed up regularly without needing to remember to do it manually. With just a few lines of code, you can set up an automatic backup system that runs at your preferred intervals.
You should create a mirror as well with something like rsync:
@Nole,
Thanks for the suggestion!
I’ve updated the article to include the
rsync
mirror option you mentioned, which is a great way to keep remote backups in sync.Backups are good, and this script looks solid (though I haven’t tested it myself yet). But backups are only part of the solution. Backups can fail for various reasons. It’s the restore process that must be tested repeatedly to ensure the most recent backup is valid and that you can actually recover the data. Otherwise, you’re just burning compute cycles and wasting your time.
Also, if you’re running it through cron, make sure the results are emailed to you (or your team). That way, you’ll know it’s working or be alerted to any failures so you can investigate and fix them.
@TJ,
Absolutely agree – backups without verified restores are just false confidence. Testing the restore process regularly is critical to ensure the backup is actually usable when it matters most. And yes, setting up email alerts from cron jobs is a simple but powerful way to catch silent failures early.
Thanks for the solid reminder — I’ll update the article to reflect these important points.
I recently switched to automating my MySQL backups using a Bash script with Cron jobs, and it’s been a game-changer! It’s an easy-to-follow, reliable method to ensure my databases are backed up regularly without any manual effort. Highly recommend this approach for anyone looking for a simple, automated solution.
Thanks for the guide! I still prefer using automated Bash scripts with Cron jobs for MySQL backups on my Linux server.
I don’t use Linux, but for Windows, I rely on dbForge MySQL Backup tool. It’s simple and effective for automating backups with a user-friendly interface!
This is a fantastic solution for automating MySQL backups, really clear and easy to follow.
Thanks!
I tested the MySQL backup automation using a Bash script and Cron jobs on 2 sites. One was using Joomla, and the other was running WordPress. I needed to move these sites and wanted to use the cron-job scheduled backups via the Bash script (just for testing purposes).
It worked like a charm. I would recommend this approach to anyone running a PHP-based website who wants to ensure automatic, hassle-free backups and avoid the stress of data loss.