= mysqldump for backups =

Making it awesome!

 * {{{
mysqldump --quick --complete-insert --quote-names mysql | gzip > dump.gz
}}}
 * logrotate fragment {{{
/var/lib/backup/mysql/*.gz {
	daily
	rotate 2
	nocreate
	missingok
}
}}}
 * the script {{{#!shell
#!/bin/bash
#
# Backup the entire MySQL
#

MY_BACKUP=/var/lib/backup/mysql
LOGROTATE_BINARY=/usr/sbin/logrotate
LOGROTATE_FRAGMENT=/tmp/mysqldump_logrotate.tmp.conf
GZIP_BINARY=gzip
# Once upon a time we tested using pigz, but it doesn't support --rsyncable
# This is important, because lots of our servers use rdiff-backup
# Maybe pigz will support it in future - <barney@anchor.net.au> 2010-06-17
#if which pigz >/dev/null ; then GZIP_BINARY=pigz ; fi

if [ ! -d $MY_BACKUP ]
then
	mkdir -v -p -m 0700 $MY_BACKUP
fi

if [ ! -d $MY_BACKUP ]
then
	echo "ERROR: database backup directory '$MY_BACKUP' does not exist." 1>&2
	exit 1
fi


# Compress data in an rsync-efficient manner, if possible
# This extra test is for pigz, as it dies if it even smells the GZIP env-var
if [ x$GZIP_BINARY == x'gzip' ] ; then
	if gzip --help | grep -q -- --rsyncable
	then
		# gzip reads this environment variable automatically
		export GZIP="--rsyncable"
	fi
fi


# rotate the old one out, clears the way for mysqldump.gz
cat > $LOGROTATE_FRAGMENT <<EOF
/var/lib/backup/mysql/*.gz {
	daily
	rotate 2
	nocreate
	missingok
}
EOF
$LOGROTATE_BINARY -f $LOGROTATE_FRAGMENT
rm -f $LOGROTATE_FRAGMENT


# do the dump
/usr/bin/mysqldump --quick --complete-insert --all-databases --quote-names | $GZIP_BINARY > $MY_BACKUP/mysqldump.gz


# Purge the binary log
# This could/should be templated for puppetised systems
if /usr/bin/mysqladmin variables | grep -q log_bin.*ON
then
        /usr/bin/mysql -e 'RESET MASTER;'
fi
}}}