≡ Menu

Daily MySQL Database Backup & Email

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

#!/bin/bash
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/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

Mail

#!/bin/bash
/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@address.here

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.

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

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

Comments on this entry are closed.

  • Ante

    Very handy. Thanks!

  • Nelson

    Very nice post. Appreciate it. will this work on MySQL in windows? If not do you have the windows equivalent

    • Nelson, the concept is very straight forward… I would guess you need
      to make a batch script or a vb script to get the job done. Unfortunatly I don’t have a Windows version.

Next post:

Previous post:

Copyright © Nish Vamadevan 2002-2017. All Rights Reserved. Terms and Policies.