Automatically Backup Mysql Database on Linux

If you are lucky enough to use a managed Database server like RDS, you have backups taken care of. If you are using your own server for running MySQL (or any other database for that matter), you have no way to protect yourself when shit hits the fan (and believe me, it will).

In this article we will be creating a bash script that will automatically backup your MySQL installation after a specified period of time.

1. Backup Database

Dump your database, compress it to save space and delete older backups.

1
2
3
4
5
6
7
8
9
10
11
12

#!/bin/sh

USER=youruser
PASSWORD=yourpassword
DIRECTORY=yourdirectory
DATABASE=your_db_name
DUMP="$(which mysqldump)"
FILE="$DIRECTORY/$DATABASE-$(date +'%m_%d_%Y').sql"

$DUMP -u $USER -h localhost -p$PASSWORD $DATABASE > $FILE && gzip $FILE

find . -mtime 7 -exec rm {} \;

In the example above, I have deleted files older than 7 days by using -mtime 7. You can change it to delete files older than any other day.

2. Schedule Script to Run At a Given Time

You can use cron to schedule tasks in linux. To edit a cron file:

sudo crontab -e

This will open up your default editor. This is how cron files are formatted:

0 1 * * 0 /path/to/your/script.sh

There are 5 places for you to enter the timings. In each of the five places, you can either enter a numeral or add a wildcard (asterisk). This is what each of the space mean starting from left to right:

1. minute (from 0 to 59)
2. hour (from 0 to 23)
3. day of month (from 1 to 31)
4. month (from 1 to 12)
5. day of week (from 0 to 6) (0=Sunday)

Using asterisk (*) instead of a number means every. So, in the example above, the script will be run on 1 AM every Sunday.

You can also add some mathematics in your scheduling as well. For example, to run a script every 10 minutes, you can do the following:

*/10 * * * * /path/to/your/script.sh

You can schedule your script to run as you like and give absolute path to your backup script and you are all set!