MySQL backup script with email support and lzma compression (for cron)

Let me share a shell script to perform a backup of a MySQL database. It is intended to run regularly (e.g. as cronjob). I created it carefully. Finally, it may distinguish itself from similar scripts by the following points:

  • It stores the backup file locally. Additionally, it sends an email with the backup file via any SMTP server you like (localhost:25 by default). STARTTLS/SSL is supported. Therefore, I use (an SMTP-based email sender I’ve written in Python). If mysqldump does not exit with return code 0, a special email is sent. mysqldump‘s stderr output is always fetched and sent via mail.
  • It does not use your database password directly in the mysqldump command (in which case other users would be able to see the your password in the process list while the command is running). This mistake seems to be done often.
  • It uses lzma compression, which produces higher compression ratios than gzip/bz2 (at the cost of computing power).
  • It automatically deletes local backup files older than a user-given number of days.
The script:
  1. #!/bin/sh
  3. # database backup settings. edit these accordingly.
  4. EMAIL=""
  6. BACKUP_DIR="/path/to/backup/dir"
  7. DB_NAME="dbname"
  8. DB_HOST=""
  9. DB_USER="username"
  10. DB_PASSWORDFILE="/path_to/.db_password_file"
  13. if [[ ! -d ${BACKUP_DIR} ]]; then
  14.     mkdir ${BACKUP_DIR}
  15. fi
  16. BACKUP_PATH=${BACKUP_DIR}/$(date +%Y%m%d-%H%M%S)_${BACKUP_NAME}.sql.lzma
  18. # dump database content to stdout. read db password from file (never put it
  19. # on the commandline directly, as other users may read it in the process
  20. # list). forward both stdout and stderr to lzma, which is more efficient
  21. # than gzip/bz2. store the data in a file with a meaningful name.
  22. mysqldump \
  23.     --user=${DB_USER} \
  24.     --password=$(cat ${DB_PASSWORDFILE}) \
  25.     --host=${DB_HOST} \
  26.     --opt --quote-names \
  27.     ${DB_NAME} \
  28.     |& lzma -c > \
  29.     ${BACKUP_PATH}
  31. # send the backup file to your email address using the SMTP server listening
  32. # on localhost:25. if you wish to use another SMTP server (localhost:465 or 
  33. # or whatever), see --help.
  34. # note: PIPESTATUS may not work within other shells than bash.
  35. if [[ ${PIPESTATUS[0]} -eq 0 ]]; then
  36.     python -s ${BACKUP_NAME} -f ${BACKUP_PATH} ${EMAIL}
  37. else
  38.     python -s ${BACKUP_NAME}_ERROR -f ${BACKUP_PATH} \
  39.     -m "mysqldump threw an error. It's stderr is within the attached archive." \
  40.     ${EMAIL}
  41. fi
  43. # delete backup files older than X days (as set above)
  44. find \
  45.     ${BACKUP_DIR} \
  46.     -name \*.sql.lzma \
  47.     -mtime +${DEL_OLDER_THAN_X_DAYS} \
  48.     -exec rm -f '{}' \;
How to use it:
  • Put the lines above in a file on your server and make it executable.
  • Create the password file containing your database password in clear text (apply chmod 600 to the file!).
  • Adjust the settings within the header of the script.
  • If you like the email feature, store (download link below) in the same directory as the backup script (or adjust the paths). If you don’t like the email feature, outcomment the corresponding lines 35 to 41.
  • If your server does not know the lzma command, replace it with gzip (line 28).
  • Configure your new backup script as cronjob.

I will publish a blog post introducing soon. This is what I can provide until then:
View the help: here
View the code: here

Download it:

Leave a Reply

Your email address will not be published. Required fields are marked *

Human? Please fill this out: * Time limit is exhausted. Please reload CAPTCHA.