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.

Ravi Saive
I am an experienced GNU/Linux expert and a full-stack software developer with over a decade in the field of Linux and Open Source technologies

Each tutorial at TecMint is created by a team of experienced Linux system administrators so that it meets our high-quality standards.

Join the TecMint Weekly Newsletter (More Than 156,129 Linux Enthusiasts Have Subscribed)
Was this article helpful? Please add a comment or buy me a coffee to show your appreciation.

9 Comments

Leave a Reply
  1. You should create a mirror as well with something like rsync:

    rsync -avz \
          -e "ssh -i $SSH_KEY -o StrictHostKeyChecking=no" \
          --delete-after \
          "$BACKUP_DIR/" \
          "$REMOTE_USER@$REMOTE_HOST:$REMOTE_DIR/"
    
    Reply
    • @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.

      Reply
  2. 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.

    Reply
    • @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.

      Reply
  3. 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.

    Reply
  4. 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!

    Reply
  5. 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.

    Reply

Got Something to Say? Join the Discussion...

Thank you for taking the time to share your thoughts with us. We appreciate your decision to leave a comment and value your contribution to the discussion. It's important to note that we moderate all comments in accordance with our comment policy to ensure a respectful and constructive conversation.

Rest assured that your email address will remain private and will not be published or shared with anyone. We prioritize the privacy and security of our users.