dump-all-databases.sh

#!/bin/bash
# Daniel Verner
# CarrotPlant LLC
# 2011
# Backup each mysql databases into a different file, rather than one big file
# Optionally files can be gzipped (dbname.gz)
#
# Usage: dump_all_databases [ -u username -o output_dir -z ]
#
#	-h hostname of mysql server
#	-p port number
#	-u username to connect mysql server
#	-o [output_dir] optional the output directory where to put the files
#	-z gzip enabled
#
# Note: The script will prompt for a password, you cannot specify it as command line argument for security reasons
#
# based on the solution from: sonia 16-nov-05 (http://soniahamilton.wordpress.com/2005/11/16/backup-multiple-databases-into-separate-files/)


PROG_NAME=$(basename $0)
HOST="localhost"
PORT="3306"
USER=""
PASSWORD=""
OUTPUTDIR=${PWD}
GZIP_ENABLED=0
GZIP=""

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

while getopts u:o:z OPTION
do
    case ${OPTION} in
        h) HOST=${OPTARG};;
	p) PORT=${OPTARG};;
        u) USER=${OPTARG};;
        o) OUTPUTDIR=${OPTARG};;
        z) GZIP_ENABLED=1;;
        ?) echo "Usage: ${PROG_NAME} [ -u username -o output_dir -z ]"
           exit 2;;
    esac
done

if [ "$HOST" != '' ]; then
    HOST="$HOST"
fi

if [ "$PORT" != '' ]; then
    PORT="$PORT"
fi

if [ "$USER" != '' ]; then
    echo "Enter password for" $USER":"
    oldmodes=`stty -g`
    stty -echo
    read PASSWORD
    stty $oldmodes
fi

if [ ! -d "$OUTPUTDIR" ]; then
    mkdir -p $OUTPUTDIR
fi

# get a list of databases
databases=`$MYSQL --host=$HOST --port=$PORT --user=$USER --password=$PASSWORD -N -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema)"`

# dump each database in turn
for db in $databases; do
    echo "$db"
	if [ $GZIP_ENABLED == 1 ]; then
		$MYSQLDUMP --force --opt --no-tablespaces --host=$HOST --port=$PORT --user=$USER --password=$PASSWORD --databases "$db" | gzip > "$OUTPUTDIR/$db.gz"
	else
	    $MYSQLDUMP --force --opt --no-tablespaces --host=$HOST --port=$PORT --user=$USER --password=$PASSWORD --databases "$db" > "$OUTPUTDIR/$db.sql"
   	fi
done

Dump and gzip all databases readable by the given user.

mysql --host=[host] --user=[user] -p -N -e 'show databases' | \
    while read dbname; do \
        mysqldump -u [admin_user] -p -h [host] -P [port] --opt --complete-insert --routines --triggers --single-transaction \
            --set-gtid-purged=OFF --no-tablespaces --column-statistics=0 "$dbname" > "$dbname".sql \
        ; \
        [[ $? -eq 0 ]] && gzip "$dbname".sql; \
    done`