mysqlbackup.sh

#!/bin/bash

function backupdb {
	USER=$1
	PASSWORD=$2
	REMOTEIP=$3
	OUTPUTDIR="/Volumes/Backup/MYSQL/$(date +%F)"

	MYSQLDUMP="/usr/local/bin/mysqldump"
	MYSQL="/usr/local/bin/mysql"

	# create backup directory
	mkdir "$OUTPUTDIR"

	if [ ! -z $5 ]
	then 
    	DATABASES=$5
	else
		# get a list of databases
		DATABASES=`$MYSQL -h $REMOTEIP -u $USER --password=$PASSWORD \
	 				-e "SHOW DATABASES;" | tr -d "| " | grep -v Database`
	fi

	 
	SKIPDB="information_schema, performance_schema, mysql, phpmyadmin, atmail, billing, psa \
	horde, apsc, cphulkd, eximstats, leechprotect, logaholicDB_vps6, roundcube, sitebuilder5"

	# dump each database and import
	for db in $DATABASES; do
		if [[ "$SKIPDB" != *"$db"* ]] && [[ "$db" != *"phpmyadmin"* ]]; then
			$MYSQLDUMP -h $REMOTEIP -u $USER --password=$PASSWORD $db > $OUTPUTDIR/$db.sql
			echo "DROP DATABASE $db" | $MYSQL -u $USER --password=$PASSWORD
			echo "CREATE DATABASE $db" | $MYSQL -u $USER --password=$PASSWORD
			$MYSQL -u admin --password=$PASSWORD $db < $OUTPUTDIR/$db.sql
		fi
	done
}

backupdb username password ip