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.

Jared Earle is a writer, photographer and systems administrator. You can find him on Twitter most of the time.

Posted in geek Tagged with: , ,
  • Gavin

    I think this script is simple and great – surprised noone else has stumbled on it and commented!

    It’s surprising how plesk doesn’t offer a simple way of just backing up databases, to keep important dynamic data backups lightweight (free from files).

    BTW isn’t “-mtime +4” deleting files older than 4 days, rather than older than a week?

    Is it possible to specify a few databases to be backed up, rather than all of them?

    Cheers on a fine elegant script!

    Gavin

  • Pete

    I could not get your script to work for me… in the end I tried this:
    http://www.dwalker.co.uk/phpmysqlautobackup/
    this is running great.