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 executable. chmod +x
Database Backup
The following script will backup the database using MySQLdump, then it will bzip2 the Database with the following filename database_DATE.sql.bz2
BACKUP="/home/backup/database/database_`date +%d-%m-%Y`.sql"
/usr/bin/mysqldump -uUSERNAME -pPASSWORD --opt DATABASE > $BACKUP
/usr/bin/bzip2 $BACKUP
Database Email
The mail script will email the database as an attachment using mutt or mail, to a given email address. You can pick whichever suit your needs.
Mutt
/bin/echo "Backup Database for `date +%d-%m-%Y`" | /usr/bin/mutt -s "Backup Database for `date +%d-%m-%Y`" [email protected] -a /home/backup/database/database_`date +%d-%m-%Y`.sql.bz2
/bin/echo "Backup Database for `date +%d-%m-%Y`" | /bin/mail -v -s "Backup Database for `date +%d-%m-%Y`" -a /home/nish/backup/db/database_`date +%d-%m-%Y`.sql.bz2 [email protected]
Automation
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.
25 20 * * * /home/backup/script/dbmail.sh
Be very Cautious on emailing Larger/Sensitive Database via email.