Daily Rotaing MySQL Backups in CPanel

The following script will maintain a 7-day (weekly) rotating backup of all MySQL databases on a server. This can be useful in addition to the standard CPanel backups performed for accounts. In our environment we run this on a nightly cron from the root or other priviledged user.

#!/bin/bash
# Script will output dumps for all databases using seperate files
# Derived from this post: http://www.cyberciti.biz/faq/ubuntu-linux-mysql-nas-ftp-backup-script/

USER="root"
PASSWORD=""
HOST="localhost"
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
OUTPUT_DIR="/backup/daily-incremental/mysql-rotating"

# Parse options
while getopts ":u:p:h:o:" opt; do
	case $opt in
	  u)
      USER=$OPTARG
      ;;
	  p)
      PASSWORD=$OPTARG
      ;;
	  h)
      HOST=$OPTARG
      ;;
	  o)
      OUTPUT_DIR=$OPTARG
      ;;
	  \?)
      echo "Invalid option: -$OPTARG" >&2
      exit 1
      ;;
	  :)
      echo "Option -$OPTARG requires an argument." >&2
      exit 1
      ;;
	esac
done

VALIDATION_ERROR=false

if [ -z "$USER" ]; then
  echo "User has not been specified" >&2
  VALIDATION_ERROR=true
fi

if [ -z "$OUTPUT_DIR" ]; then
  echo "Output dir has not been specified" >&2
  VALIDATION_ERROR=true
fi

if $VALIDATION_ERROR ; then
  exit 1
fi

Weekday=`date +%A`
YearMonthDay=`date +%Y-%m-%d`

if ! [ -d "$OUTPUT_DIR/$Weekday" ]; then
  mkdir -p "$OUTPUT_DIR/$Weekday"
fi

# Delete all backups older than 7 days
find "$OUTPUT_DIR" -name "*.sql.gz" -mtime +7 -exec rm -f {} \;

# Pull List of Databases
if [ "$PASSWORD" != "" ]; then
  DBS="$($MYSQL -u $USER -h $HOST -p$PASSWORD -Bse 'show databases')"
else
  DBS="$($MYSQL -u $USER -h $HOST -Bse 'show databases')"
fi

# Create Backups
for DatabaseName in $DBS
do
  if [ $DatabaseName != "information_schema" ]; then
    FILE=$OUTPUT_DIR/$Weekday/$DatabaseName.$YearMonthDay.sql.gz
    if ! [ -e $FILE ]; then
      echo "$FILE"
  		if [ "$PASSWORD" != "" ]; then
  	    $MYSQLDUMP -u $USER -h $HOST -p$PASSWORD --skip-lock-tables $DatabaseName | gzip > $FILE
      else
  	    $MYSQLDUMP -u $USER -h $HOST --skip-lock-tables $DatabaseName | gzip > $FILE
  		fi
    fi
  fi
done