backup_sql.sh

#!/bin/bash
# @author: Johnny Martin
# Created:   24/02/2017

MYSQL_USER="XXXXXX"
MYSQL_PASS="XXXXXX"

for ACCOUNT_PATH in /home/*/ ; do
  ACCOUNT=$(basename "$ACCOUNT_PATH")

  # Get databases with an account name prefix
  DATABASES=$(mysql -u$MYSQL_USER -p$MYSQL_PASS -B -N -e "SHOW DATABASES LIKE '${ACCOUNT}\_%'")

  # Check if databases exist
  if [ ! -z "$DATABASES" ]; then
    for database in $DATABASES; do
      # Dump individual databases into account sql backup folder
      mysqldump -u"$MYSQL_USER" -p"$MYSQL_PASS" --force --opt -B "$database" | gzip > "/home/$ACCOUNT/_sqlbackup/$database.sql.gz"
      echo "Backing up $database"
    done

    # Set permissions back to normal
    chown -R $ACCOUNT.$ACCOUNT "/home/$ACCOUNT/_sqlbackup/"
    chmod -R 700 "/home/$ACCOUNT/_sqlbackup/"
  fi