Mysqldump All Databases Separately

The below shell script can be put together as a command and made into an executable (chmod+x) and kept in the /usr/bin to be readily available. Make sure to edit the paths and credentials before using.


#!/bin/bash
# Backup each mysql databases into a different file, rather than one big file
# Optionally files can be gzipped (dbname.gz)
#
# Usage: scriptname [ -u username -o output_dir -z ]
#        
#    -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
#

PROG_NAME=$(basename $0)
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
        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 [ "$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 --user=$USER --password=$PASSWORD -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema)"`

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

##EOF

And this one below is a simple one to put in a backup cron:

#!/bin/bash
# backup each mysql db into a different file, rather than one big file
# as with --all-databases - this will make restores easier

USER="root"
PASSWORD="secret"
OUTPUTDIR="/var/lib/bacula"
MYSQLDUMP="/usr/bin/mysqldump"
MYSQL="/usr/bin/mysql"

# clean up any old backups - save space
rm "$OUTPUTDIR/*bak" > /dev/null 2>&1

# get a list of databases
databases=`$MYSQL --user=$USER --password=$PASSWORD \
 -e "SHOW DATABASES;" | tr -d "| " | grep -v Database`

# dump each database in turn
for db in $databases; do
    echo $db
    $MYSQLDUMP --force --opt --user=$USER --password=$PASSWORD \
    --databases $db > "$OUTPUTDIR/$db.bak"
done