Stuff

#!/bin/bash
#
# mysql_backup.sh
#   -f : Force le backup si la taille de la base de données dépasse $MAX_SQL_SIZE
#   -v : Envoit un mail de log en fin de rapport a $SUPERVISION

### variables ###
BACKUP_DIR=/var/backup/mysqlbackup
FORCE=0
LOG_FILE="/var/log/mysql_backup.log"
MAIL_REPORT=1
MAX_SQL_SIZE=2147483648
NB_BACKUP=2
NB_BACKUP_WEEK=2
SITE="calliope"
SQL_DIR="/var/lib/mysql/"
SUPERVISION="xilaw@hebeo.fr"

### Variables a ne pas changer !!!! ###
BCKP_FILE="${BACKUP_DIR}/daily/mysql_backup-$(date +'%Y%m%d-%H%M%S').sql.bz2"
BDAY=0
DOW=`date +%w`
LOG_TEMP="/tmp/mysql_backup.log"
NB_BACKUP_W=`echo "${NB_BACKUP_WEEK} * 7" | bc`

#######################################
#######################################
#######################################
###                                 ###
###                                 ###
###   Ne pas toucher ci-dessous !   ###
###   Don't touch below this !!!    ###
###                                 ###
###                                 ###
#######################################
#######################################
#######################################

# Récupération des options
while getopts ":fFhHvV:" opts
do
  case $opts in
    f | F ) FORCE=1 ;;
    h | H ) echo "Usage: $0 [-f] [-v]";;
    v | V ) MAIL_REPORT=1 ;;
    * ) echo "Usage: $0 [-f] [-v]"
        return 1;;
  esac
done

# Fonctions de récupération du jour
which_day() {
  case $1 in
    0 ) echo -n "Sunday" ;;
    1 ) echo -n "Monday" ;;
    2 ) echo -n "Tuesday" ;;
    3 ) echo -n "Wednesday" ;;
    4 ) echo -n "Thursday" ;;
    5 ) echo -n "Friday" ;;
    6 ) echo -n "Saturday" ;;
    7 ) echo -n "Sunday" ;;
    * ) echo -n "ERROR" ;;
  esac
}

# Fonctions de log
log() {
  if [ ! -f ${LOG_FILE} ]
  then
    touch ${LOG_FILE}
  fi
  if [ ${MAIL_REPORT} -eq 1 ]
  then
    echo "`date \"+%Y %b %d %H:%M:%S\"` [BACKUP] " $1 | tee -a ${LOG_FILE} | tee -a ${LOG_TEMP}
  else
    echo "`date \"+%Y %b %d %H:%M:%S\"` [BACKUP] " $1 >>${LOG_FILE}
  fi
}
sendlog() {
  if [ $1 -eq 0 ]
  then
    if [ -f ${LOG_TEMP} ]
    then
      cat ${LOG_TEMP} |mail -s "[FAIL] Log backup of ${SITE} " ${SUPERVISION}
      rm -f ${LOG_TEMP}
    elif [ ! -f ${LOG_TEMP} ]
    then
      log "Mail not sent"
    fi
  else
    if [ -f ${LOG_TEMP} ] && [ ${MAIL_REPORT} -eq 1 ]
    then
      cat ${LOG_TEMP} |mail -s "[SUCCESS] Log backup of ${SITE}" ${SUPERVISION}
      rm -f ${LOG_TEMP}
    elif [ ! -f ${LOG_TEMP} ] && [ ${MAIL_REPORT} -eq 1 ]
    then
      log "Mail not sent"
    fi
  fi
}

log "------------------------------------------------------------------------------"
log "Start of operations !"
if [ ! -f ${LOG_TEMP} ] && [ ${MAIL_REPORT} -eq 1 ]
then
  touch ${LOG_TEMP}
elif [ -f ${LOG_TEMP} ] && [ ${MAIL_REPORT} -eq 1 ]
then
  rm ${LOG_TEMP}
  touch ${LOG_TEMP}
fi
log "Mysql backup started on ${SITE} at `date \"+%Y%m%d %H%M%S\"`"

# On cree les dossiers pour les backup si il n'existe pas
if [ ! -d ${BACKUP_DIR}/daily/ ]
then
  mkdir -p ${BACKUP_DIR}/daily/
  log "Create ${BACKUP_DIR}/daily/"
fi
if [ ! -d ${BACKUP_DIR}/weekly/ ]
then
  mkdir -p ${BACKUP_DIR}/weekly/
  log "Create ${BACKUP_DIR}/weekly/"
fi

# On supprime les anciens logs
log "Deleting old logs"
find ${BACKUP_DIR}/daily/ -mtime +${NB_BACKUP} -exec rm -rf {} \; && log "Old daily backup deleted"
find ${BACKUP_DIR}/weekly/ -mtime +${NB_BACKUP_W} -exec rm -rf {} \;  && log "Old weekly backup deleted"

BDIRS=`/bin/ls -l ${SQL_DIR} | grep -v ^total | sed -e 's/.*[[:blank:]]\(.*$\)/\1/g'`
cd ${SQL_DIR}
BSIZE=`du -bcs ${BDIRS} | awk '/total$/ { print $1 }'`
# Nettoyage de la base SQL
for BASE in `echo "show databases;" | mysql --silent`; do
  log "Analyze of database : ${BASE}"
  for TABLE in `echo "show tables;" | mysql --silent ${BASE}`; do
    log "Cleaning of table : ${BASE}.${TABLE}"
    log "`echo "OPTIMIZE TABLE ${TABLE}" | mysql --silent ${BASE}`"
  done
done
ADIRS=`/bin/ls -l ${SQL_DIR} | grep -v ^total | sed -e 's/.*[[:blank:]]\(.*$\)/\1/g'`
cd ${SQL_DIR}
ASIZE=`du -bcs ${ADIRS} | awk '/total$/ { print $1 }'`

# On log l'efficacité du nettoyage
EFF=`echo "(${BSIZE}*100)/${ASIZE}" | bc`
log "Clean success ! Before = ${BSIZE} ! After = ${ASIZE} ! Efficacity = ${EFF} %"

# On check la taille des tables a backup
A=`pwd`
SQL_DIRS=`/bin/ls -l ${SQL_DIR} | grep -v ^total | sed -e 's/.*[[:blank:]]\(.*$\)/\1/g'`
cd ${SQL_DIR}
SQL_SIZE=`du -bcs ${SQL_DIRS} | awk '/total$/ { print $1 }'`
cd $A
if [ ${SQL_SIZE} -gt ${MAX_SQL_SIZE} ] && [ ${FORCE} -ne 1 ]
then
  log "Backup not done !! The mysql folder ${SQL_DIR} is too big ! ${SQL_SIZE} is greater than ${MAX_SQL_SIZE}"
  sendlog 0
  exit 1
fi

# On vérifie qu'il y a assez de place sur /var pour le backup
SQL_SIZE2=`echo "${SQL_SIZE}/1024" | bc`
FREE=`df ${BACKUP_DIR} | tail -n -1 | awk '{print $4}'`
if [ ${SQL_SIZE2} -gt ${FREE} ]
then
  log "Backup not done !! No enough space left on ${BACKUP_DIR} => ${SQL_SIZE2} / ${FREE}"
  sendlog 0
  exit 1
else
  log "Backup can be done :) There is enough space left on ${BACKUP_DIR} => ${SQL_SIZE2} / ${FREE}"
fi

# On backup le daily en le compressant a la volee
log "Starting mysql backup and on the fly compression"
mysqldump -A --comments=0 -a -C -Q -K -F  --add-drop-table --add-locks -q --lock-tables -K | bzip2 -z -5 >${BCKP_FILE}
[ $? -eq 0 ] && log "Correctly ending mysql backup and on the fly compression" || $(log "Error on mysql backup and on the fly compression"; sendlog 0; exit 1)

# si on doit faire le backup de la semaine on cp le daily
if [ ${DOW} -eq ${BDAY} ]
then
  cp ${BCKP_FILE} ${BACKUP_DIR}/weekly/
  log "Copy daily script to weekly directory for weekly backup because we are `which_day ${BDAY}`"
else
  log "We aren't `which_day ${BDAY}` so no weekly backup is done !"
fi
log "End of operations !"
sendlog 1