#!/bin/bash
#
# Script to backup all mysql tables as separate files
#
# $BACKUP_PATH/<date>/<db_name>/<host>_<table_name>.gz

# MySQL settings
MYSQL_USER="root"
MYSQL_PASS=""
MYSQL_HOST="localhost"

# Ignore these databases (space separated)
IGNORE="test"

# Backup path
BACKUP_PATH="/usr/backup/databases"

#
# End Configuration
#

# Paths to programs
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
CHOWN="$(which chown)"
CHMOD="$(which chmod)"
GZIP="$(which gzip)"

# Misc vars
HOST="$(hostname)"
NOW="$(date +"%d%m%Y")"

BACKUP_PATH+="/$NOW"
MYSQL_PERMS="-u $MYSQL_USER -h $MYSQL_HOST"

IGNORE+=" information_schema mysql performance_schema"

echo $BACKUP_PATH
if [ "$MYSQL_PASS" ] ; then
	MYSQL_PERMS+=" -p$MYSQL_PASS"
fi

# Check backup path and create it if necessary

DBS="$($MYSQL $MYSQL_PERMS -Bse 'show databases')"

for DB in $DBS ; do
	if [[ "$IGNORE" =~ "$DB" ]] ; then
		echo "$(date +"%d-%m-%Y %H:%M:%S") Skipping $DB"
		continue
	fi
	# create path if it doesn't exist
	SUBPATH="$BACKUP_PATH/$DB"
	[ ! -d $SUBPATH ] && mkdir -p $SUBPATH || :

	# Change permissions so only root can access backups
	$CHOWN 0.0 -R $SUBPATH
	$CHMOD 0600 $SUBPATH

	echo "$(date +"%d-%m-%Y %H:%M:%S") Backing up $DB to $SUBPATH"
	TABLES="$($MYSQL $MYSQL_PERMS -Bse 'show tables from '$DB)"
	for TABLE in $TABLES ; do
		FILE="$SUBPATH/${HOST}_${TABLE}.sql.gz"
		$MYSQLDUMP $MYSQL_PERMS $DB $TABLE | $GZIP -9 > $FILE
	done
	echo "$(date +"%d-%m-%Y %H:%M:%S") ...done!"
done

echo "$(date +"%d-%m-%Y %H:%M:%S") All done"

exit 0