How to Automate MySQL Database Backups with Shell Script and Cron

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.