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.

# This is where the output of this script will go, we don't want tons of emails so disable it by default
# This is where the backup gets emailed too
/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.

This entry was posted in HOWTO and tagged , , . Bookmark the permalink.

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. Mike Boone says:

    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.