mysql-hourly-daily-backup

#!/bin/bash
# dit script maakt elk uur een dump van de database zonder extended inserts
# als het dan nodig is, kan je zonder eerst een dump te moeten maken meteen lekker greppen in de dump file
# iedere 24 uur worden de oude dumps weer overschreven.

# Global variables:

PATH='/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin'
SCRIPT_PATH="${0}"
ARG1="${1}"
TOTARG="${#}"
TIME_STAMP="$(date +'%Y%m%d_%Hhr%M')"

LOG="/var/log/dump.log"

# First things first, dit script mag alleen als root draaien
  user=$(whoami)
  if [ ${user} != "root" ]
  then
      echo "Bark Bark! This script only runs as root!"
      exit 1
  fi

# The Functions:

# function die
function die()
{
  echo "${SCRIPT_PATH}: ${1}"
  exit 1
}

# functie backup
function fBackup()
{
 # check for BACKUP var
  [ -n ${BACKUP} ] || die "Not enough arguments given!"

 # checken binaries
  { test -x /usr/bin/mysqldump  && test -x /usr/bin/mysql && test -x /bin/gzip ; } || die "This script depends on mysql-common!"

 # check op .my.cnf
  if [ ! -f /root/.my.cnf ]; then
    die "/root/.my.cnf Not found!"
  fi

 # checken op connectiviteit
  mysql --connect_timeout=2 -Be 'show databases' -h 127.0.0.1 |grep -Eq '(information_schema|mysql)'
  [ ${?} = 0 ] || die "Failed to connect to database!"

 # backuppen
  for DB in $( mysql --connect_timeout=2 -Be "show databases" |sed 1d |grep -Ev '(information_schema)' | xargs echo )
  do
    # set location
     if [ ${BACKUP} = 'hourly' ] ; then
       DEST="/home/dump/hourly"
       NAME="${DB}-dump-$(date +"%H:00").sql.gz"
     elif [ ${BACKUP} = 'daily' ] ; then
       DEST="/home/dump/daily"
       NAME="${DB}-dump-$(date +"%Y%m%d").sql.gz"
     fi

    # checken op dir
     [ -d ${DEST} ] || die "${DEST} dir not found!"

    # backup mysql database:
     mysqldump --skip-extended-insert ${DB} | gzip  > ${DEST}/${NAME}
     [ ${?} = 0 ] || { echo "${SCRIPT_PATH}: Failed to dump database ${DB} to ${DEST}/${NAME}" |tee -a ${LOG} ; continue; }
  done

  # mooie output:
  echo "${SCRIPT_PATH}: mysql backup of \"${TIME_STAMP}\" backup succeeded " >> ${LOG}

  return 0
}

# Shows usage.
function fUsage()
{
  echo
  echo "Usage: ${SCRIPT_PATH} daily|hourly|usage"
  echo
  echo "hourly         : makes an hourly dump of the DB"
  echo "daily          : makes a daily dump of the DB"
  echo "usage          : show usage information."
  echo
  echo "This script uses --skip-extended-insert for dumping"
  echo

  return 0
}

# The main function.
function fMain()
{
  case "${ARG1}"
  in
    usage)
      fUsage
      ;;
    daily)
      BACKUP=daily
      fBackup
      ;;
    hourly)
      BACKUP=hourly
      fBackup
      ;;
    *)
      fUsage
      exit 1
      ;;
  esac

  return 0
}


# Start the program:
fMain "${@}"

# Exit with previous return code:
exit "${?}"