Intended for use with cron. This script will backup all (or one specific) database, the specified user has access to on the given DB server, and remove backups older than the specified duration to keep them for. If no params are passed, defaults will be used. and backups created for _every_ database.

db_backup.sh

#!/bin/bash

USAGE="$0 [-u <user> -p <password> -h <host> -P <PORT> -d <database> -D <destination/directory/without/trailing/slash>]"
DESTINATION=`pwd`
USER=root
PASS=root
HOST=localhost
PORT=3306

# SPECIFY HOW LONG TO RETAIN BACKUPS
# Note, the format is OS dependent.
#
# On Linux, specify value like this:
#   6 hours
#   1 week
#   1 month
#   3 months
#
# On mac, specify value like this (Same examples as above):
#   6h
#   1w
#   1m
#   3m
KEEP_FOR="3 days"

while getopts ':u:p:P:h:d:D:' opt
do
  case $opt in
    u) USER=$OPTARG;;
    p) PASS=$OPTARG;;
    h) HOST=$OPTARG;;
    P) PORT=$OPTARG;;
    d) DB=$OPTARG;;
    D) DESTINATION=$OPTARG;;
   \?) echo "ERROR: Invalid option: $USAGE"
       exit 1;;
  esac
done

if [ -z "$DB" ]; then
  for i in `mysql -u $USER -p$PASS -h $HOST -P $PORT -e "show databases;" | grep -Ev "^(Database|mysql|performance_schema|information_schema)$"`; do
    if [ ! -d "$DESTINATION/${i}" ]; then
      mkdir $DESTINATION/${i}
    fi
    mysqldump -c -u $USER -p$PASS -h $HOST -P $PORT ${i} | gzip > $DESTINATION/${i}/$(date "+%Y%m%d%H%M").sql.gz
  done
else
  if [ ! -d "$DESTINATION/$DB" ]; then
    mkdir $DESTINATION/$DB
  fi
  mysqldump -c -u $USER -p$PASS -h $HOST -P $PORT $DB | gzip > $DESTINATION/${DB}/$(date "+%Y%m%d%H%M").sql.gz
fi


###
# Delete old backups
###
if [ "$(uname)" = Darwin ]
  then
    THRESHOLD=$(date -v-$KEEP_FOR "+%Y%m%d%H%M")
  else
    THRESHOLD=$(date -d "${KEEP_FOR} ago" "+%Y%m%d%H%M")
fi

find ${DESTINATION} -maxdepth 2 -type f -print0 | while IFS= read -d '' -r file
do
  ## Test filename pattern
  if [[ "$(basename "$file")" =~ ^[0-9]{12}.sql.gz$ ]]
  then
    ## Delete if older than threshold
    [ "$(basename "$file" .sql.gz)" -le "$THRESHOLD" ] && rm -v -- "$file"
  fi
done