Automating Weekly MySQL Backups via Email

Manually backing up databases can be a frustrating and time-consuming task. It often involves logging into servers, managing credentials, transferring files, and keeping track of schedules to avoid missed backups.

In this post, I’ll walk you through the steps I took to create an automated solution for weekly MySQL backups. It involves setting up a cron job to run the backup at regular intervals, then emailing it to myself for easy access.

In this guide I will be using Ubuntu Server 22.04 LTS, however it shouldn’t be too hard to adapt these instructions for other operating systems.

Configuring mysqldump for Password-Free Access

The first step is to programatically generate a backup of the MySQL database. We can use mysqldump for this purpose.

To give it a try, log into your server and run:

mysqldump -u <db-user> -p <db-name> > test_export.sql

This will prompt you to enter your password, then will create a test_export.sql file containing the specified database, in the same directory you ran the command.

Obviously we are not going to enter the password every time, meaning we need to save it somewhere our script will be able to access it.

We could hardcode the password into the script we are about to create, but a more flexible solution is to use a .my.cnf file in our home directory. This file is a user-specific configuration file that allows MySQL tools to securely access credentials without requiring them to be entered manually or exposed in scripts.

You can check if this file exists on your system like so:

test -f ~/.my.cnf && echo "File exists" || echo "File does not exist"

Next, open the file in your editor. If the file doesn’t exist, this will create it:

cd
nano .my.cnf

The cd command navigates to the user’s home directory. For the rest of this guide, I will assume we are working from there.

Now enter:

[client]
user=your_username
password=your_password

Then press Ctrl+O to save the file, followed by Enter, and Ctrl+X to exit the editor.

Once created, ensure the file is secure by setting its permissions to allow only the owner to read it:

chmod 600 .my.cnf

MySQL tools will now automatically use these credentials when executed.

You can try it out to make sure it works. Enter:

mysql

This should land you in the MySQL command-line client without prompting for a username or password. If it doesn’t work, double-check the [client] section in your .my.cnf file and ensure the file permissions are set correctly.

Creating a MySQL Backup Script

Next we need to write a shell script to run the mysqldump command.

nano db-backup.sh

Then, adjusting the first two variables to suit your needs, enter:

#!/bin/bash

# Variables
DB_NAME="your_database_name"
BACKUP_DIR="/path/to/backup"
BACKUP_FILE="$BACKUP_DIR/backup_$(date +%Y%m%d%H%M%S).sql"

# Create backup directory if it doesn't exist
mkdir -p "$BACKUP_DIR"

# Run mysqldump
mysqldump --defaults-file=~/.my.cnf "$DB_NAME" > "$BACKUP_FILE"

# Check if backup was successful
if [ $? -eq 0 ]; then
  echo "Backup successful: $BACKUP_FILE"
else
  echo "Backup failed"
  exit 1
fi

I have added comments to the script to show what each part does. Worthy of note however, is the check to determine whether the mysqldump command was successful by evaluating its exit status ($?). This ensures that we’ll be notified if something goes wrong.

The backup is saved in the directory specified by BACKUP_DIR, which is created automatically if it doesn’t exist. The script generates a timestamped filename for each backup, keeping them organized and easy to manage.

Save the script as db-backup.sh and make it executable:

chmod +x db-backup.sh

Then run it to make sure that it is working as expected:

./db-backup.sh

If everything has gone according to plan, navigate to where you are storing the files and run the ls command. You should see something like:

jim@server:~/db$ ll
backup_20241221075046.sql

Sending the Backup via Email

Once the backup is created, you can email it to yourself using mailx. With a little luck, this may already be installed and configured on your server.

You can test this by sending a simple email (swap out the email address with your own):

echo "This is a test email" | mailx -s "Test Email" your_email@example.com

If you receive the email, mailx is ready to use. If not, you’ll need to install and configure it first.

sudo apt update && sudo apt install mailutils

sudo nano /etc/postfix/main.cf

relayhost = [smtp.gmail.com]:587
smtp_use_tls = yes
smtp_sasl_auth_enable = yes
smtp_sasl_password_maps = hash:/etc/postfix/sasl_passwd
smtp_sasl_security_options = noanonymous

sudo nano /etc/postfix/sasl_passwd

[smtp.gmail.com]:587 your_email@gmail.com:your_app_password

sudo chmod 600 /etc/postfix/sasl_passwd
sudo postmap /etc/postfix/sasl_passwd

sudo systemctl restart postfix

echo "This is a test email" | mailx -s "Test Email" your_email@gmail.com

sudo tail -f /var/log/mail.log

With mailx now working, we can update the backup script to include email functionality.

First, let’s install sharutils, which includes the uuencode utility used for encoding files for email attachments.

sudo apt update && sudo apt install sharutils

Here is the updated script:

#!/bin/bash

# Variables
DB_NAME="your_database_name"
BACKUP_DIR="/path/to/backup"
BACKUP_FILE="$BACKUP_DIR/backup_$(date +%Y%m%d%H%M%S).sql"
COMPRESSED_FILE="$BACKUP_FILE.gz"

EMAIL="your_email@example.com"
SUBJECT="Database Backup: $(date +%Y-%m-%d)"
BODY="Attached is the database backup."

# Create backup directory if it doesn't exist
mkdir -p "$BACKUP_DIR"

# Run mysqldump
mysqldump --defaults-file=~/.my.cnf "$DB_NAME" > "$BACKUP_FILE"

# Check if backup was successful
if [ $? -eq 0 ]; then
  echo "Backup successful: $BACKUP_FILE"

  # Compress the backup
  gzip "$BACKUP_FILE"
  echo "Backup compressed: $COMPRESSED_FILE"

  # Send email with compressed backup attached
  echo "$BODY" | uuencode "$COMPRESSED_FILE" "$(basename "$COMPRESSED_FILE")" | mailx -s "$SUBJECT" "$EMAIL"
  echo "Email sent to $EMAIL"
else
  echo "Backup failed"
  exit 1
fi

As you can see we have made a couple of changes:

• The backup file is now compressed using gzip before being emailed. This reduces the file size, making it faster to send and easier to store.
• The script uses uuencode to encode the compressed backup and attach it to the email.
• Timestamped filenames are used for both the uncompressed and compressed backups.

If you run the script at this point, you should receive an email with a compressed backup attached.

Automating with Cron

Finally, we want to automate the script to run once a week.

Open the crontab editor by running:

crontab -e

If it your first time running this, you might be asked to select an editor. Choose nano.

Then add the following line to schedule the script to run every Monday at midnight:

0 0 * * 1 /path/to/db-backup.sh

Be sure to replace /path/to/db-backup.sh with the actual path, before saving the file and exiting the editor.

You can confirm the job is scheduled correctly by listing the crontab:

crontab -l

If you want to run the conjob at a different time, or different frequency, you can consult this cron expression generator.

Conclusion

And that’s a wrap!

In this guide, I’ve demonstrated how to automate MySQL backups by running a backup at a regular interval, and emailing the resultant backup files to an aribtary address.

By implementing these steps and scheduling the process with cron, you can maintain regular backups effortlessly and safeguard against data loss.

If you have any questions, or are stuck on a particular part of the tutorial, hit me up in the comments below and I’ll do my best to help.