#!/bin/bash
#
# Backs up all MySQL databases individually.
#
# Move to usr/local/sbin, make executable and run as root:
# sudo mv ~/backup-databases.sh /usr/local/sbin/backup-databases
# sudo chmod +x /usr/local/sbin/backup-databases
# sudo backup-databases
# Make sure target directory is properly referenced.
#
# Backup All Databases Individually
# ------------------------------------------------------------------------------
USER="XXXXXXX"
PASSWORD="XXXXXX"
HOST="localhost"
DB_BACKUP_PATH="/media/backupdrive/ubuntu.14.04/databases"

# Get list of databases, but not 'Database' or 'information_schema'
# ------------------------------------------------------------------------------
DATABASES=$(mysql --user=$USER --password=$PASSWORD -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema)")

DUMPFAIL=false

# Remove previous dumped databases
# ------------------------------------------------------------------------------
rm $DB_BACKUP_PATH/*

# Set up log
# ------------------------------------------------------------------------------
echo "Database backup report. ${TIMESTAMP}" > $DB_BACKUP_PATH/DB_LOG
echo "=======================================" >> $DB_BACKUP_PATH/DB_LOG

# Create dumps for each database
# ------------------------------------------------------------------------------
for DB in $DATABASES
do

  mysqldump -v --user=$USER --password=$PASSWORD --single-transaction --log-error=$DB_BACKUP_PATH/$DB.log --host=$HOST $DB > $DB_BACKUP_PATH/$DB.sql

  # Reportage - log result of mysqldump
  # ----------------------------------------------------------------------------
  if [[ $? -eq 0 ]]

  then

    echo -e "Mysqldump created ${DB}.sql\n" >> $DB_BACKUP_PATH/DB_LOG

  else

    echo "Mysqldump encountered a problem backing up ${DB}. Look in ${DB_BACKUP_PATH}/${DB}.log for information.\n" >> $DB_BACKUP_PATH/DB_LOG
    $DUMPFAIL=true

  fi

done