MySQL has become an important part of most Web servers, especially over the last few years. With all those databases, the risk of corruption, intrustion, data loss, hardware failure, and lots of other factors necessitate automated backups.

Luckily, backing up MySQL isn’t nearly as hard as it might sound. Actually, it’s only one command. But, not many people have only one MySQL DB, and dumping all of them to a single file can be hard to parse should you only need to import one. This is easily remedied by making a script run on cron to dump them all individually.

I set this script to run once a week, monday morning at 5am. It generates a logfile as well, info.txt.sl, which contains the date, a list of files, and their MD5Sum. Finally, it uses bzip2 to compress the files efficiently. MD5Sums are critical in determining the integrity of the archive and to ensure the database has not been corrupted or altered since the snapshot was taken.

Without further ado;

#!/bin/bash

#grab only the month, day, and year.
THEDATE=$(date +%m%d%y)

#full path for backup directory. Make sure it is writable by the user you have cron run the script as.
BACKUPDIR=’/mysqlbackup’

#full path for logfile. Probably don’t change this.
LOGFILE=”$BACKUPDIR/info.txt.sql”

#username to connect to the DB with
USR=’root’

#password for above user. Change this. Those are single quotes, not backticks.
PSWD=’MYSQL USER PASSWORD GOES HERE’

#toss the date into the logfile. This is the system provided date.
date > $LOGFILE

#the actual dumps. Make sure you change all the passwords.
#to add another database, follow this format: (replace [DBNAME] with the database name)
#mysqldump -u $USR -p$PSWD [DBNAME] > $BACKUPDIR/[DBNAME]_$THEDATE.sql

mysqldump -u $USR -p$PSWD information_schema > $BACKUPDIR/information_schema_$THEDATE.sql
mysqldump -u $USR -p$PSWD mysql > $BACKUPDIR/mysql_$THEDATE.sql

#MD5Sum the dumps and write that to the log.
md5sum $BACKUPDIR/information_schema_$THEDATE.sql >> $LOGFILE
md5sum $BACKUPDIR/mysql_$THEDATE.sql >> $LOGFILE

#bzip2 all the files, including the log. Name the archive with the date.
tar -cvvjf $BACKUPDIR/$THEDATE.tar.bz2 $BACKUPDIR/*.sql

#clean up the dumped files as they are already tar’d
rm -rf $BACKUPDIR/*.sql

Add that to a crontab to run as a user with write permission on the backup directory and however often you want. Note that in its current form, it will overwrite the archive if you run it twice in one day. You can simply modify the “THEDATE=$(date +%m%d%y)” string to put more info in there, such as “THEDATE=$(date +%m%d%y%H%M%S)” to make the files suffixed with something like “081308170651″ (08th month, 13th day, 08th 2dig-year, 17th hour, 6th minute, 51st second). This may be more reliable depending on how often you want to run the backup.