The following two scripts on crontab will automatically back up and email the database on a timely manner. This script will work on daily backup of Blogs such as WordPress / Drupal etc

In this example, I will be using the directory /home/backup/database

It is recommended to create two different shell script named dbback.sh and dbmail.sh accordingly and set them as executables. chmod +x

The first script will backup the database using MySQLdump, then it will bzip2 the Database with the following filename database_DATE.sql.bz2

#!/bin/bash
BACKUP="/home/backup/database/database_`date +%d-%m-%Y`.sql"
/usr/bin/mysqldump -uUSERNAME -pPASSWORD --opt DATABASE > $BACKUP
/usr/bin/bzip2 $BACKUP

The second script will email the database as an attachment using mutt, to a given email address.

#!/bin/bash
/bin/echo "Backup Database for `date +%d-%m-%Y`" | /usr/bin/mutt -s "Backup Database for `date +%d-%m-%Y`" email@address.here -a /home/backup/database/database_`date +%d-%m-%Y`.sql.bz2

To make it automated, all you have to do is to add both scripts to the crontab. It is advisable to add them 10 minutes apart depending on the size of the MySQL Database. As per example below.

15 20 * * * /home/backup/script/dbbackup.sh
25 20 * * * /home/backup/script/dbmail.sh

Be very Cautious on emailing Larger/Sensitive Database via email.

{ 0 comments }