Emailed backup of a database via cron – Attaching files via command line


Sometimes people want an easy offsite backup option for important databases or files, and having a cron to automate that can be helpful. One of our customers had such a case so i wrote a shell script which emailed him a copy of his database every day.

Since i thought it was a handy thing to have, as well as the idea that you can mail files from command line attache, i have posted it here in case anyone wants to take it, and use the code for similar projects.

#!/bin/bash
# This is where the output of this script will go, we don't want tons of emails so disable it by default
MAILTO=""
# This is where the backup gets emailed too
BACKUPMAIL=youremail@gmail.com
DATE=`date`
DATABASEUSER=someuser
DATABASEPASS=somepass
DATABASENAME=databasenametodump

/usr/bin/mysqldump --opt -u${DATABASEUSER} -p${DATABASEPASS} ${DATABASENAME} > /tmp/${DATABASENAME}.sql
/bin/gzip -c /tmp/${DATABASENAME}.sql | /usr/bin/uuencode ${DATABASENAME}.sql.gz  | /usr/bin/mail -s "MySQL DB ${DATABASENAME} for $DATE" ${BACKUPMAIL}

I placed this in a file in /etc/cron.d/mysql-backup and that now runs on a daily basis.
Things to Note:
* Check the path of all binaries , this was written for debian based distro, the path may differ on your system
* Check you have uuencode installed, by default its not installed on debian packages. Look for the package called sharutils.


3 responses to “Emailed backup of a database via cron – Attaching files via command line”

  1. Nice, I always wondered how to send files via email on CLI. However, the argument to uuencode is the name to give the file when it is decoded and should not have a directory name so it can be done in one line with no tmp files:

    mysqldump --opt -u${DATABASEUSER} -p${DATABASEPASS} ${DATABASENAME} \
    | gzip \
    | uuencode ${DATABASENAME}.sql.gz \
    | mail -s "MySQL DB ${DATABASENAME} for $DATE" ${BACKUPMAIL}
    

    Note there is a typo in the original post in the first use of the DATABASENAME variable.

  2. Cool idea. Don’t forget to encrypt your data before emailing it if it’s sensitive. You could pipe it to gpg after the gzip.