Backup MySQL database with CRON

It is important to backup your database. I case of an error, you can easily 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 creating a new database user with limited permissions.

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.

To use this user during crontab backup, you have to create a file in your home directory called .my.cnf and insert the user credentials. The file should look like this:

[mysqldump]
user=
password=

What does the shell script look like?

Create a new file sqlbackup.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.

You can also add the server upload algorithm in this file.

How to configure the CRON?

On an Ubuntu 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.

Please comment below, if you have any questions.

Credits:

• Post photo by Patrick Lindenberg on Unsplash