Automated backups of MySQL databases

Unless you have intelligent backup software that can do something smart to backup your databases, restoring a backup of a running MySQL server is like restarting your database after a hard system crash, it's a crap shoot. Since I don't have any fancy backup software that can help I decided to use mysqldump to create a snapshot of my database server and write it out to a compressed SQL file. Then my (dumb) backup software can continue to be used and I will be able to recover easily if my server dies.

Here's the quick and dirty script:

# This script automates a call to mysqldump
# and sends the output to a file in a backup
# directory. The script is set up to keep
# seven days of history.
# Before you can run this script you must
# set up a MySQL user that can perform the
# backup. This user must have permission to
# SELECT and LOCK TABLES. The user should not
# be permitted to access MySQL in any way other
# than through the local socket. Here's how the
# user should be created:
# GRANT SELECT,LOCK TABLES ON *.* TO 'SomeUser'@'localhost' IDENTIFIED BY 'SomePassword'
# This script should be owned by root and only
# root should be able to read, write, and
# execute it. (i.e., chmod 700)

DAYOFWEEK=`/bin/date +%u`

# dump out all the databases
${MYSQLDUMP} -u ${BACKUPUSR} --password=${BACKUPPWD} --opt \
    --all-databases > ${BACKUPDIR}/mysql_databases.${DAYOFWEEK}.sql

# compress the output file
${GZIP} -f ${BACKUPDIR}/mysql_databases.${DAYOFWEEK}.sql