db_backup.sh

#!/bin/sh
## Make a backup of each mysql db into a separate file.
## To backup a single database simply add the db name as a parameter (or multiple db's e.g. "sh db_backup.sh dbaname1 dbaname2").

TIMESTAMP=$(date +"%F")
USER="root"
PASSWORD="root"
OUTPUTDIR="/var/www/sites/#db_backups/$TIMESTAMP"
MYSQLDUMP="/usr/bin/mysqldump"
MYSQL="/usr/bin/mysql"

# Create the directory and its parents if it doesn't exist.
mkdir -p "$OUTPUTDIR"
 
if [ -z "$1" ]; then
	databases=`$MYSQL --user=$USER --password=$PASSWORD --batch --skip-column-names -e "SHOW DATABASES;" | grep -v 'mysql\|information_schema'`
	for database in $databases; do
		$MYSQLDUMP \
		--user=$USER --password=$PASSWORD \
		--force \
		--quote-names --dump-date \
		--opt --single-transaction \
		--events --routines --triggers \
		--databases $database \
    | gzip > "$OUTPUTDIR/$database.gz"
	done
else
	for database in ${@}; do
		$MYSQLDUMP \
		--user=$USER --password=$PASSWORD \
		--force \
		--quote-names --dump-date \
		--opt --single-transaction \
		--events --routines --triggers \
		--databases $database \
    | gzip > "$OUTPUTDIR/$database.gz"
	done
fi