How to: Backup MySQL database & email results using BASH

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.

# 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.

./ my_blog_db

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: