db backup script
I thought I'd post the little script that I use to run daily backups of my WebGUI databases. It's quite short, so I'll just paste it in-line here.
#
# script used to backup WebGui databases
#
# this assumes that the databases all end in a
# specific tag -- I started using '_webgui'
# and then shortened it to just '_wg'.
# If you don't use some way of grepping your database
# names from the list of databases, then you'll
# probably need to include a list directly here,
# but that's not very reliable. Perhaps some other
# method would work...
sites1=`mysql -B -e "show databases" | grep _webgui`
sites2=`mysql -B -e "show databases" | grep _wg`
sites="$sites1 $sites2"
# change this to the path where your backups go
d="/data/WebGUI/baks/`date +%y%m%d.%H%M%S`_baks"
# Create a new directory that is named something like
# /data/WebGUI/baks/030124.023106_baks
mkdir -p $d
echo "backing up WebGUI databases: $(date)"
for i in $sites
do
echo "$i:"
echo " Backing up...$d/${i}_bkup.sql"
mysqldump -uwebgui -pPASSWORD --add-drop-table $i >$d/${i}_bkup.sql
echo "mysql $i <${i}_bkup.sql" >>$d/restore
done
echo "Done."
echo "----------------------------------------"
I put an entry into my crontab that looks like this:
39 5 * * * /data/WebGUI/baks/bkup_wg_dbs >>/data/WebGUI/baks/bkup_log
This runs the backup script (bkup_wg_dbs) at 05:39 every morning and appends the results to the file 'bkup_log'.
You could change the directory naming scheme (using different formatting options on the date command) to accomodate different kinds of backup strategies. I happen to prefer this "date.time" format.
One of the useful things I included is that the script also creates a 'restore' script that can be run from the backup directory to restore the entire database, making it very easy to go back to a prior database instance.
-David
