Backup MySQL database with CRON

It is important to backup your database. In case of an error, you can restore all data. You do not have to do the backup process manually.

I wanted to automate my MySQL database backup workflow. To do this, I created a shell script, which collects all database data and stores it into a file. After creation, the script compresses the file and uploads it to another server. I use another server to decrease the risk of data loss. If my primary production server fails, I still have all backups on another physical system.

Create a new database user

It is highly recommended to create a new database user with limited permissions instead of using an admin user.

You can restrict the new user to the following permissions:

• SELECT
• RELOAD
• FILE
• LOCK TABLES
• TRIGGER
• SHOW VIEW

You can set these permissions global or for every needed database/table.

What does the shell script look like?

Create a new file backup.sh and paste the following code:

#!/bin/bash

today=`date '+%Y_%m_%d'`;
mysqldump DATABASE_NAME > /PATH_TO_BACKUP_FOLDER/dump-$today.sql
tar -cPf /PATH_TO_BACKUP_FOLDER/dump-$today.tar /PATH_TO_BACKUP_FOLDER/dump-$today.sql
rm /PATH_TO_BACKUP_FOLDER/dump-$today.sql

backupdir='/PATH_TO_BACKUP_FOLDER'
DAYS_OLD=30

for d in $backupdir ; do
    if [ -d "$d" ] ; then
        FILE="$d/"$(ls "$d" -t1  | head -n 1)
        if test $(find "$FILE" -mtime +$DAYS_OLD) ; then
            rm $FILE
        fi
    fi
done

To get this code running, you need to replace 2 placeholders: DATABASE_NAME, PATH_TO_BACKUP_FOLDER.

The code will also delete local backup files older than 30 days. If you want to increase or decrease this value, change DAYS_OLD.

How to configure the CRON?

On an linux system execute crontab -e. This will open an editor. At the end of the file insert:

0 1 * * * /PATH_TO_BACKUP_FOLDER/sqlbackup.sh

This command will then execute every day at 01:00 AM.

Credits:

• Post photo by Patrick Lindenberg on Unsplash