mysqlbackup.sh

#!/bin/bash

USER='root'
PASS='demoserver'
HOST='localhost'

BACKUPDIR='/backup'
IGNOREDBS='information_schema performance_schema'
RETENTION=14

MYSQL=`which mysql`
MYSQLDUMP=`which mysqldump`
GZIP=`which gzip`
SED=`which sed`

log() {
 # $1 Message
 # $2 Exit
 local MESSAGE="${1}"
 local EXIT=${2}
 echo "$(date +'[%Y-%m-%d %H:%M:%S]') - ${MESSAGE}"
 [ "${EXIT}" != "" ] && exit 1
}

if [ ! -d "$BACKUPDIR" ]; then
  mkdir -p "${BACKUPDIR}"
  [ $? -ne 0 ] && log "${BACKUPDIR} is not exist" true
fi

DATABASES=$($MYSQL -h "${HOST}" -u "${USER}" -p"${PASS}" --skip-column-names -Bne "SHOW DATABASES")
for ignore in $IGNOREDBS; do
  DATABASES=$(echo $DATABASES | "${SED}" -e "s/${ignore}//g")
done

if [ -z "${DATABASES}" ]; then
  log "Couldnt find database to backup" true
fi

log "Backup directory: ${BACKUPDIR}"
for DB in $DATABASES; do
  log "Creating backup of ${DB}"
  NOW=$(date +'%Y-%m-%d-%H-%M-%S')
  "${MYSQLDUMP}" --single-transaction --disable-keys --extended-insert -h"${HOST}" -u "${USER}" -p"${PASS}" "${DB}" \
    | "${GZIP}" > "${BACKUPDIR}/${DB}-${NOW}.sql.gz"
  [ $? -ne 0 ] && log "Couldnt create backup of ${DB}" || log "Backup created for ${DB}"
done

# remove old backups
find "${BACKUPDIR}" -mtime +$RETENTION -exec rm -v  {} \;