Automatically backup MySQL database with a cronjob

Introduction

Database backups are crucial because they offer a mechanism to restore data in the event that the original data is corrupted or destroyed. Databases hold crucial data, including inventory management systems, customer information, and financial records, and their loss could have a negative impact on an organisation.

The impact of data loss can be minimised by frequently maintaining database backups that make it feasible to restore the data to a previous state in case something goes wrong. Additionally, having a database backup can be helpful for testing and development because it enables you to work with a replica of the live system's data without compromising production data.

Automatic MySQL backup bash script

Bash script below will back up a MySQL database with a readable file name like database-2023-01-05_13-50-04.sql to a folder and remove backups older than 30 days.

Create a file: touch mysql-backup.sh

Create a backup directory: mkdir -p /home/user/backups

#!/bin/bash

# Script variables
BACKUP_DIR="/home/user/backups"
MYSQL_USER="db_user"
MYSQL_PASSWORD="db_password"
MYSQL_DATABASE="db_name"
MYSQL_HOST="localhost"

# Create a filename for the backup
BACKUP_NAME="$MYSQL_DATABASE-$(date +%Y-%m-%d_%H-%M-%S).sql"

# Create the backup
echo "Starting backup: ${BACKUP_NAME}"
mysqldump --column-statistics=0 -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD $MYSQL_DATABASE > "$BACKUP_DIR/$BACKUP_NAME"

if [ $? -ne 0 ]; then
  echo "mysqldump failed"
  exit 1
fi

# Delete backups older than 30 days
echo "Backup done, deleting backups older than 30 days"
find $BACKUP_DIR -type f -mtime +30 -delete

MySQL backup script setup

When you're finished configuring your script, we need to add execute permissions to the script.

chmod +x ./mysql-backup.sh

Do a test run to confirm everything is working as intended and that your MySQL credentials are correct: ./mysql-backup.sh

If you see new files in BACKUP_DIR - that means that the backup script is working, now we can add it as a cron job, which is a task that is scheduled to run automatically at a specified time or interval. Run: crontab -e on your system.

Before you add in your script, decide how often you want it to run. Here are a few examples:

Installing the cronjob

Run once a day at 3am:

0 3 * * * /home/user/mysql-backup.sh >> /home/user/mysql-backup.log

Run once a week on Sundays at 3am:

0 3 * * 0 /home/user/mysql-backup.sh >> /home/user/mysql-backup.log

Run every 12 hours:

0 */12 * * * /home/user/mysql-backup.sh >> /home/user/mysql-backup.log

Run once a month at 3am on the first day of the month:

0 3 1 * * /home/user/mysql-backup.sh >> /home/user/mysql-backup.log

Crontab.guru - The cron schedule expression editor

An easy to use editor for crontab schedules.

The cron schedule expression editor

Final crontab file should like something like this:

When you're done, simply click ctrl+x, followed by "Y". That's it, your cronjob will now automatically run.

Compress the backups with tar

Backups can be compressed with tar to save disk space, since text files tend to include a lot of repetitions, you can usually expect to save a lot of space by compressing them.

A MySQL database backup file (.sql) is a plain text file and should compress nicely with tar, as an example.

For a 100 MB .sql file, you might expect to save around 50-70% of the original size, depending on the content of the file. This would result in a compressed file size of around 30-50 MB.

For a 1 GB .sql file, you might expect to save around 60-80% of the original size, resulting in a compressed file size of around 200-400 MB.

Here's a final version of the script, with compression functionality:

#!/bin/bash

# Script variables
BACKUP_DIR="/home/user/backups"
MYSQL_USER="db_user"
MYSQL_PASSWORD="db_password"
MYSQL_DATABASE="db_name"
MYSQL_HOST="localhost"

# Create a filename for the backup
BACKUP_NAME="$MYSQL_DATABASE-$(date +%Y-%m-%d_%H-%M-%S).sql"

# Create the backup
echo "Starting backup: ${BACKUP_NAME}"
mysqldump --column-statistics=0 -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD $MYSQL_DATABASE > "$BACKUP_DIR/$BACKUP_NAME"

if [ $? -ne 0 ]; then
  echo "mysqldump failed"
  exit 1
fi

# Compress the backup using tar
echo "Compressing backup"
tar -czf "$BACKUP_DIR/$BACKUP_NAME.tar.gz" "$BACKUP_DIR/$BACKUP_NAME"

if [ $? -ne 0 ]; then
  echo "tar failed"
  exit 1
fi

# Delete the uncompressed backup and keep the compressed one
echo "Deleting uncompressed backup"
rm "$BACKUP_DIR/$BACKUP_NAME"

# Delete backups older than 30 days
echo "Backup done, deleting backups older than 30 days"
find $BACKUP_DIR -type f -mtime +30 -delete

Store backups on a remote server

The backup script can be extended even further to copy the database backups to a remote server for increased redundancy.

Log in to the machine that will have the backup script on it, and copy the SSH key to the destination server, so that SSH will not ask for your password.

ssh-copy-id user@example.com

Once done, confirm that you no longer need a password to log in to your server:

ssh user@example.com

If you logged in to your server without being prompted for a password, that means it's working, if not, you will need to troubleshoot the issue.

Add another variable to the backup script:

REMOTE_LOCATION="user@remote:/path/to/backups"

Copy the file to remote server:

# Transfer the backup to the remote location
scp "$BACKUP_DIR/$BACKUP_NAME.tar.gz" $REMOTE_LOCATION

# Check exit code of the scp command
if [ $? -ne 0 ]; then
  echo "scp failed"
  exit 1
fi

# Delete backups from the remote location that are older than 30 days
ssh $REMOTE_LOCATION "find /path/to/backups -type f -mtime +30 -delete"

Final backup script, with compression and backup copy to an external server:

#!/bin/bash

# Script variables
BACKUP_DIR="/home/user/backups"
MYSQL_USER="db_user"
MYSQL_PASSWORD="db_password"
MYSQL_DATABASE="db_name"
MYSQL_HOST="localhost"
REMOTE_LOCATION="user@remote:/path/to/backups"

# Create a filename for the backup
BACKUP_NAME="$MYSQL_DATABASE-$(date +%Y-%m-%d_%H-%M-%S).sql"

# Create the backup
echo "Starting backup: ${BACKUP_NAME}"
mysqldump --column-statistics=0 -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD $MYSQL_DATABASE > "$BACKUP_DIR/$BACKUP_NAME"

if [ $? -ne 0 ]; then
  echo "mysqldump failed"
  exit 1
fi

# Compress the backup using tar
echo "Compressing backup"
tar -czf "$BACKUP_DIR/$BACKUP_NAME.tar.gz" "$BACKUP_DIR/$BACKUP_NAME"

if [ $? -ne 0 ]; then
  echo "tar failed"
  exit 1
fi

# Delete the uncompressed backup and keep the compressed one
echo "Deleting uncompressed backup"
rm "$BACKUP_DIR/$BACKUP_NAME"

# Transfer the backup to the remote location
scp "$BACKUP_DIR/$BACKUP_NAME.tar.gz" $REMOTE_LOCATION

# Check exit code of the scp command
if [ $? -ne 0 ]; then
  echo "scp failed"
  exit 1
fi

# Delete backups older than 30 days
echo "Backup done, deleting backups older than 30 days"
find $BACKUP_DIR -type f -mtime +30 -delete

# Delete backups from the remote location that are older than 30 days
ssh $REMOTE_LOCATION "find /path/to/backups -type f -mtime +30 -delete"

Final notes on security

In this post, you learned how to set up a cron job to perform automatic backups of your MySQL database. However, there are a few important security considerations that should be made. To protect the backup script's contents, which may include your database hostname and password, you need set appropriate file permissions to prevent unauthorised access to the script by other users that you might have on your system.