Backup all MySQL databases into separate files

dump.sh

#!/bin/sh
## backup each mysql db into a different file, rather than one big file
## as with --all-databases. This will make restores easier.
## To backup a single database simply add the db name as a parameter (or multiple dbs)
 
## Putting the script in /var/backups/mysql seems sensible... on a debian machine that is
## Create the user and directories
# mkdir -p /var/backups/mysql/databases
# useradd --home-dir /var/backups/mysql --gid backup --no-create-home mysql-backup
## Remember to make the script executable, and unreadable by others
# chown -R mysql-backup:backup /var/backups/mysql
# chmod u=rwx,g=rx,o= /var/backups/mysql/dump.sh

## crontab entry - backup every night at 02:00
# sudo -u mysql-backup crontab -e
# 0 2 * * * /var/backups/mysql/dump.sh
 
## Create 'backup' mysql user
# CREATE USER 'backup'@'localhost' IDENTIFIED BY 's3cr3t'; 
# GRANT EVENT, LOCK TABLES, PROCESS, REFERENCES, SELECT, SHOW DATABASES, SHOW VIEW, TRIGGER ON *.* TO 'backup'@'localhost' ; 

# Usage
#1: To dump specific database
# ./dump.sh database_name
#2 To Dump all databases
# ./dump.sh
 
USER="backup"
PASSWORD="s3cr3t"
OUTPUTDIR=$(dirname $0)"/databases"
MYSQLDUMP="/usr/bin/mysqldump"
MYSQL="/usr/bin/mysql"
SKIP_TABLES='^mysql$\|^information_schema$\|^performance_schema$\|^sys$'

#Create directory in case it does not exist
mkdir -p $OUTPUTDIR

#Save user grants
$MYSQL --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" \
| $MYSQL --skip-column-names -A | sed 's/$/;/g' > "$OUTPUTDIR/mysql_user_grants.sql"

if [ -z "$1" ]; then
	databases=`$MYSQL --batch --skip-column-names -e "SHOW DATABASES;" | grep -v $SKIP_TABLES`
else
	databases=${@}
fi

for database in $databases; do
		echo "Exporting $OUTPUTDIR/$database.sql"
		$MYSQLDUMP --force --quote-names --dump-date --opt --single-transaction --events --routines --triggers \
		--databases $database --result-file="$OUTPUTDIR/$database.sql"
done
exit;