Plesk MySQL backup script

This may not be worthy of a blog post, but people have asked for my MySQL backup script as used on the Plesk servers I manage, so here it is. It’s short and sweet and is installed by running from crontab. It shows my propensity to use awk to write scripts that are then piped through sh to be executed.

#!/bin/bash
# Jared Earle, 2008-03-28, BD-NTWK

# Set the datestamp, login credentials and backup directory
export date=$(date +\%Y\%m\%d)
export creds="-uadmin -p`sudo cat /etc/psa/.psa.shadow`"
export backupdir="/home/jearle/backup"

# delete week old files
find ${backupdir}/ -regex '.*.dump.gz' -mtime +4 -exec rm {} \;

# dump databases to the backupdir
echo "show databases;" | mysql ${creds} | egrep -v ^Database$ | \
	awk '{print "mysqldump ${creds} "$1" | \
	gzip > ${backupdir}/db-"$1"-${date}.dump.gz"}' | \
	sh

Oh, don’t forget to make sure that $backupdir isn’t readable by everyone. Yeah, obvious, I know.