How to: Backup MySQL database & email results using BASH

Posted on Friday, Feb 17, 2006 at 10:11 AM in PHP, Linux

The script below does the same and only depends on mutt (or another command line email client) being installed. A better solution would be not to email the file but to setup ssh key authentication on another server and use scp (secure copy) to send the file over. That way, your database file is transferred over an encrypted connection, which is more secure, and you don't have to wory about your message being blocked due to a large file attachement.

#!/bin/bash

# Backup your MySQL database and have it mailed to you
# requires mutt or another command line email client

if [ -z "$1" ]
then
    echo "Database name expected as the first parameter"
    exit
fi

if [ -z "$2" ]
then
    echo "Recipient expected as second parameter"
    exit;
fi

# config
tmp="/tmp"
db_user="backup_user"
db_pw="secret"
db_name=$1
db_host="localhost"
recp=$2
today=`date +%Y-%m-%d`
mysqldump_opts="--add-drop-table -acQq"
# email settings
subject="DB Backup for $1"
sql_file=$tmp/$db_name.$today.sql
gzip_file=$tmp/$db_name.$today.sql.gz

# create the backup
mysqldump $mysqldump_opts -u $db_user -h $db_host -p$db_pw $db_name > $sql_file

#create the gzip'd attachment
gzip $sql_file 

#send the mail
#we could also scp it offsite instead (better)
echo "Automated database backup" | mutt -a $gzip_file -s "$subject" $recp

#delete temp file
rm $gzip_file

I've added one improvement - you have to specify the database name and the recipient email at the command line.

./email_db.sh my_blog_db me@example.com

This makes the script more useful, since you can now loop over a list of your database and their owner's email addresses and send them the backup automatically. Download the shell script: email_db.sh