Simple automated MySQL Backup databases script

If your site relies on a MySQL database and stores information you can't afford to lose, you definitely need to implement a backup procedure in case, which I don't, in case of any type of disaster. To save backup, we need to use the "mysqldump" command. mysqldump is a small shell script that make backup (dump) of MySQL Databases.

First let's create the backup script:

vi /path/to/script/mysqlbackup.sh

and add the following code:

#!/bin/bash

#
# Config
#

now=$(date +%Y/%m/%d)
fileName=$(date +%H-%M)

dbUser=root
dbPass=
dbHost=`hostname`

if [ "$1" == "" ]; then
    dbDatabase="--all-databases"
else
    dbDatabase="--databases $1"
fi

backupFolder="/backup/db/$now/"

#
# End Config
#

if [ ! -e "$backupFolder" ]; then
    mkdir -p "$backupFolder"
fi

if [ "$dbPass" != "" ]; then
    dbPass="-p=$dbPass"
fi

#
# Functions
#
/usr/bin/mysqldump -u$dbUser $dbPass -h$dbHost $dbDatabase | bzip2 > $backupFolder$fileName.bz2

Save the file and exit

Now let's make that script executable:

chmod +x /path/to/script/mysqlbackup.sh

Setup your cronjobs:

crontab -e

Add the following information:

#Daily backup at 00:05
5 0 * * *  root    /path/to/script/mysqlbackup.sh 'database1' >/dev/null 2>&1

#Weekly backup on sunday at 00:05
5 0 * * 0  root    /path/to/script/mysqlbackup.sh 'database2 database3' >/dev/null 2>&1

#Monthly backup on the 1st of the month at 00:05
5 0 1 * *  root    /path/to/script/mysqlbackup.sh >/dev/null 2>&1

Restore

To restore a backup, simply unzip it:

bunzip2 <filename>

and run the following command:

mysql -uusername -ppassword -hdbserver database < "/path/to/filename.sql"