#!/usr/local/bin/bash
# This is a simple MySQL backup script, databases will be extracted for direct injection
# into mysql for a restore. All dumps will be bzip2 compressed to save space.
# License:	LGPL (http://www.fsf.org/licenses/lgpl.html)
# Purpose:	backup database(s) to file(s)
# Author:	Alexander Kuehn (nagilum@nagilum.org)
# Hompage:	http://www.nagilum.org/#p
# 
# Version history
# 1.0		initial release
# 1.1		made script more verbose and do some basic
#			error checking, created functions
# 1.2		added period setting
#
#  settings: 
# Where to work in?
cd /backup/databases

# database definition: host:database:user:password:period
# use "*" (without the quotes) as database name for all databases
# period is either "M" (month) or "W" (week)
# it determines whether to use 01..31 (default) or 1..7 as extension
# for the dumps. This way you can have different amounts of 
# dumps for different databases.
# 
databases="localhost:*:root:mypassword:M \
	dbserver:mydatabase:myuser:mypassword:W" 

mysqldump=/usr/local/bin/mysqldump
mysql=/usr/local/bin/mysql
dump_opts="-e -O net_buffer_length=1048576 --add-drop-table --quote-names"
comp_flt="/usr/bin/bzip2 -9"
extension=".bz2"
umask 077

# dump_ok(dbase)
function dump_ok() {
	echo "Database dump $1 succeeded."
}

# dump_error(dbfile)
function dump_error() {
	rm "$1"
	echo "Some error occured during database dump, broken $1 dump removed!"
}

# not_enough_space(dbfile needed avail) {
function not_enough_space() {
	echo "Dump $1 was not created, estimated $2 blocks needed only $3 available!"
}

# dump_it(db backupdir backupfile period)
function dump_it() {
	db=$1
	backupdir=$2
	backupfile=$3
	period=$4
	mkdir -p "${backupdir}"
	chmod 700 "${backupdir}"
	if [ "${period}" = "W" ];then
		lastbackup=`ls -1t "${backupdir}/${db}"_?.bz2 2>/dev/null|head -1`
	else
		lastbackup=`ls -1t "${backupdir}/${db}"_??.bz2 2>/dev/null|head -1`
	fi
	if [ -z "${lastbackup}" ]; then
		minfree=1;
	else
		minfree=`ls -s "${lastbackup}"|sed -e s/^\ \*//g|cut -d' ' -f1`
	fi
	avail=`df -P "${backupdir}"|tail -1|sed -e s/\ \ \*/\ /g|cut -d' ' -f4`
	if [ -r "${backupfile}" ];then
		freed=`ls -s "${backupfile}"|sed -e s/^\ \*//g|cut -d' ' -f1`
		avail=`expr $avail + $freed`
	fi
	if [ $minfree -gt $avail ]; then
		not_enough_space "${backupfile}" $minfree $avail
	else
		${mysqldump} -h ${host} -u ${user} ${dump_opts} -p${pass} ${db}| ${comp_flt} >"${backupfile}"
		if [ $? -gt 0 ]; then
			# some error occurred, our dump is probably broken
			dump_error "${backupfile}"
		else
			chmod 600 "${backupfile}"
			dump_ok "${backupfile}"
		fi
	fi
}

for database in ${databases}; do
	host=`echo ${database}|cut -d: -f1`
	dbase=`echo ${database}|cut -d: -f2`
	user=`echo ${database}|cut -d: -f3`
	pass=`echo ${database}|cut -d: -f4`
	period=`echo ${database}|cut -d: -f5`
	if [ "${dbase}" = '*' ] 
	then	dbs=`echo "show databases;"|${mysql} -h ${host} -u ${user} -p${pass}|sed -e 1d`
		mkdir -p "${host}"
		chmod 700 "${host}"
		for db in ${dbs}
		do	backupdir=${host}/${db}"_db"
			if [ "${period}" = "W" ];then
				backupfile=${backupdir}/${db}"_"`date +"%u"`.bz2
			else
				backupfile=${backupdir}/${db}"_"`date +"%d"`.bz2
			fi
			dump_it "${db}" "${backupdir}" "${backupfile}" "${period}"
		done
	else
		backupdir=${dbase}"_db"
		if [ "${period}" = "W" ];then
			backupfile=${backupdir}/${dbase}"_"`date +"%u"`.bz2
		else
			backupfile=${backupdir}/${dbase}"_"`date +"%d"`.bz2
		fi
		dump_it "${dbase}" "${backupdir}" "${backupfile}" "${period}"
	fi
done