Database backup script

From Tuxation

This script uses mysqlhotcopy to read the database from the server and then compress the resulting directory and finally, copy it to H: drive (which is backed-up by IST). The backup file is tagged with a date for easy reference.

WARNING: Please note that when running scripts in crontab, pay special attention to directory paths as cron uses absolute path. It helps to create an error log just to be sure the script runs fine with cron.

#!/bin/sh
#
# This is: /home/dennis/bin/dbbackup.sh

DATE=`date +%Y%m%d`

# Read wikidb database.
mysqlhotcopy --user=<username> wikidb /tmp

# Compress directory.
tar cjvf /tmp/wikidb.tar.bz2 /tmp/wikidb/

# Copy compress file to H: drive.
mv -f /tmp/wikidb.tar.bz2 /home/dennis/h/mediawiki/wikidb-$DATE.tar.bz2

# Delete local directory.
rm -rf /tmp/wikidb

# To restore, just decompress the back-up file and copy the
# contents of the wikidb directory to /var/lib/mysql/wikidb/
# as in:
#
# $ sudo cp -dR wikidb/* /var/lib/mysql/wikidb/
echo "Done."

To add the script into crontab such that it runs every 8pm everyday:

# Backup Wiki Database
0 20 * * *      root    /home/dennis/bin/dbbackup.sh >/dev/null 2>/var/log/dbbackup.err