#!/bin/bash
#
########################################################################################
# Script name: bck_mysql.sh
# Note: if this script differs from original, please rename it (and its ini file), so
# that it is not replaced inadvertently by an automatic update.
#
# Version: v1.0  (2011-11-02) Kleinigkeiten korrigiert. Replikation pruefen.
#				Hauptkonfigurationsdatei bck.ini wird gelesen. (faw)
# Version: v0.4  (2011-10-11) kleine Fehler korrigieren. Reporting. DB-Tabellen koennen
#				ohne Lock gedumpt werden. (faw)
# Version: v0.3  (2011-09-19) Fehlerbehandlung (faw)
# Version: v0.2  (2011-06-17) in_array() implementiert, so dass man Tabellen excludieren
#				kann (faw)
# Version: v0.1  (2011-04-29) Skript erzeugt (faw)
#
# Description: Dieser Skript sichert die MySQL Datenbanken und prueft, ob die
#	       Replikation in Ordnung ist.
########################################################################################

set -o pipefail

# pipefail: If pipefail is enabled, the pipeline's return status is the value of the
#	last (rightmost) command to exit with a non-zero status, or zero if all commands
#	exit successfully.

set -o nounset

# nounset: Treat unset variables as an error when performing parameter expansion.  If
#	expansion is attempted on an unset variable, the shell prints an error message,
#	and, if not interactive, exits with a non-zero status.

set -o errtrace

# errtrace: If set, any trap on ERR is inherited by shell functions, command
#	substitutions, and commands executed in a subshell environment.
#

# Catch errors
trap errortrap ERR

PATH="/sbin:/bin:/usr/sbin:/usr/bin:/usr/local/sbin:/usr/local/bin/"

DATE="$(date +"%Y%m%d")"
DATEDUMP="$(date -u +"%Y-%m-%d")"
HOSTNAME="$(hostname -f)"

BCKCFG="$(dirname $0)/bck.ini"
BCKMYSQLCFG="${0%*.sh}.ini"
IAM="$(basename $0)"

# If $REPORT is not set in the .ini file, then it defaults to this one:
REPORT="/var/db_backup/report_bck.log"


if [[ -f "$BCKCFG" ]]; then
  # parsing general options and variables
  . "$BCKCFG"
else
  echo "EE: $IAM: No ${BCKCFG} configuration file found ! Exiting."
  echo "EE: $IAM: No ${BCKCFG} configuration file found ! Exiting." >> $REPORT
  exit 1
fi

if [[ -f "$BCKMYSQLCFG" ]]; then
  # parsing options and variables for this script
  . "$BCKMYSQLCFG"
else
  echo "EE: $IAM: No ${BCKMYSQLCFG} configuration file found ! Exiting."
  echo "EE: $IAM: No ${BCKMYSQLCFG} configuration file found ! Exiting." >> $REPORT
  exit 1
fi

if [[ ! "${HOSTNAME}" ]]; then
  HOSTNAME="unknown_hostname"
  echo "WW: $IAM: Hostname is unknown !"
  echo "WW: $IAM: Hostname is unknown !" >> $REPORT
fi

if [[ ! -f /lib/lsb/init-functions ]]; then
  echo "EE: $IAM: LSB Functions have to be installed for this script to run !"
  echo "EE: $IAM: LSB Functions have to be installed for this script to run !" >> $REPORT
  exit 1
fi

. /lib/lsb/init-functions

########################################################################################

usage () {
  echo "Usage: $0 [-d BACKUP_DIR]"
  echo "-d BACKUP_DIR: path to the backup directory. (default from .ini file: ${BACKUPDIR})"
  echo
  echo "Messages are similar to Xorg: EE means Error, AA is an Alarm, WW is Warning"
  echo "II stands for Informations"
}



errortrap () {
  printf "EE: $IAM: Error trapped. Errcode is $?\n"
  printf "EE: $IAM: Error trapped. Errcode is $?\n" >> $REPORT
}



in_array () {
  el="$1"
  shift
  while [[ $# -gt 0 ]]; do
    [[ $el = "$1" ]] && return 0
    shift
  done
  return 1
}



check_freespace () {
SCALE=3

case $UNIT in
  KB)
	DIV=1024
	;;

  MB)
	DIV=1048576
	;;

  GB)
	DIV=1073741824
	;;

  TB)
	DIV=1099511627776
	;;
  *)
	DIV=1
	SCALE=0
	UNIT="bytes"
	;;
esac

let PERCFUL="$(stat -f -c "100-%f*100/%b" "$BACKUPDIR")"
let FREERAW="$(stat -f -c "%f*%S" "$BACKUPDIR")"

if [[ ! "${PERCFUL}" ]]; then
  log_action_msg "EE: check_freespace: \$PERCFUL not set ! Exiting function"
  echo "EE: check_freespace: \$PERCFUL not set ! Exiting function." >> $REPORT
  return
fi

if [[ ! "${FREERAW}" ]]; then
  log_action_msg "EE: check_freespace: \$FREERAW not set ! Exiting function"
  echo "EE: check_freespace: \$FREERAW not set ! Exiting function." >> $REPORT
  return
fi

FREE="$(echo "scale=$SCALE; $FREERAW / $DIV" | bc)"

if [[ ! "${FREE}" ]]; then
  log_action_msg "EE: check_freespace: \$FREE not set ! Exiting function"
  echo "EE: check_freespace: \$FREE not set ! Exiting function." >> $REPORT
  return
fi

WARNFUL="$(echo "$FSPWARN < $PERCFUL" | bc)"
ALARMFUL="$(echo "$FSPALARM < $PERCFUL" | bc)"

if [[ ! "${WARNFUL}" ]]; then
  log_action_msg "EE: check_freespace: \$WARNFUL not set ! Exiting function"
  echo "EE: check_freespace: \$WARNFUL not set ! Exiting function." >> $REPORT
  return
fi

if [[ ! "${ALARMFUL}" ]]; then
  log_action_msg "EE: check_freespace: \$ALARMFUL not set ! Exiting function"
  echo "EE: check_freespace: \$ALARMFUL not set ! Exiting function." >> $REPORT
  return
fi

if [[ "${ALARMFUL}" = 1 ]]; then
  log_action_msg "Partition $BACKUPDIR is ${PERCFUL}% full (${FREE} ${UNIT} free)"
  log_action_msg "AA: check_freespace: ALARM ! Partition $BACKUPDIR is ${PERCFUL}% full (${FREE} ${UNIT} free)"
  echo "Partition $BACKUPDIR is ${PERCFUL}% full (${FREE} ${UNIT} free)" >> $REPORT
  echo "AA: check_freespace: ALARM ! Partition $BACKUPDIR is ${PERCFUL}% full (${FREE} ${UNIT} free)" >> $REPORT
elif [[ "${WARNFUL}" = 1 ]]; then
  log_action_msg "Partition $BACKUPDIR is ${PERCFUL}% full (${FREE} ${UNIT} free)"
  log_action_msg "WW: check_freespace: Warning ! Partition $BACKUPDIR is ${PERCFUL}% full (${FREE} ${UNIT} free)"
  echo "Partition $BACKUPDIR is ${PERCFUL}% full (${FREE} ${UNIT} free)" >> $REPORT
  echo "WW: check_freespace: Warning ! Partition $BACKUPDIR is ${PERCFUL}% full (${FREE} ${UNIT} free)" >> $REPORT
else
  log_action_msg "Partition $BACKUPDIR is ${PERCFUL}% full (${FREE} ${UNIT} free)"
  echo "Partition $BACKUPDIR is ${PERCFUL}% full (${FREE} ${UNIT} free)" >> $REPORT
fi
}



check_mysql () {
# cd into the dumpdir, sometimes it's mounted via autofs, so it's not always mounted.
cd "${MYDUMPDIR}"

  if [[ ! -d "${MYDUMPDIR}" ]]; then
    echo "II: $IAM: Creating $MYDUMPDIR..."
    echo "II: $IAM: Creating $MYDUMPDIR..." >> $REPORT
    mkdir -p "${MYDUMPDIR}" || { log_action_msg "EE: $IAM: Cannot mkdir ${MYDUMPDIR}, exiting"; echo "EE: $IAM: Cannot mkdir ${MYDUMPDIR}, exiting." >> $REPORT; exit 1; }
    chmod 700 "${MYDUMPDIR}" || { log_action_msg "EE: $IAM: Cannot chmod ${MYDUMPDIR}, exiting"; echo "EE: $IAM: Cannot chmod ${MYDUMPDIR}, exiting." >> $REPORT; exit 1; }
    echo "II: $IAM: Created $MYDUMPDIR."
  fi

  if [[ -f "${MYCNFFILE}" ]]; then
    MYCNF="--defaults-file=${MYCNFFILE} "
  else
    MYCNF=""
  fi
}



check_slave() {
  if [[ "$(echo $ISSLAVE | awk '{print tolower($0)}')" = "yes" ]]; then
    if [[ 0 > ${WARNDELAY} ]]; then
      WARNDELAY="0"
    fi

    DELAY=$(mysql -NBqe "show slave status\G" | grep Seconds_Behind_Master)
    if [[ ! "$DELAY" ]]; then
      log_action_msg "II: check_slave: This MySQL Host is not a slave"
      log_action_msg "II: check_slave: Please check if the config is not broken or"
      log_action_msg "II: check_slave: unset \$ISSLAVE in $BCKMYSQLCFG"
      echo "II: check_slave: This MySQL Host is not a slave." >> $REPORT
      echo "II: check_slave: Please check if the config is not broken or" >> $REPORT
      echo "II: check_slave: unset \$ISSLAVE in $BCKMYSQLCFG" >> $REPORT
      return 1
    fi
    DELAY=${DELAY#*:}

    if [[ "$(echo "${DELAY} > ${WARNDELAY}" | bc)" != "0" ]]; then
      log_action_msg "WW: check_slave: MySQL Replication has delay: ${DELAY} seconds"
      echo "WW: check_slave: MySQL Replication has delay: ${DELAY} seconds." >> $REPORT
    fi
  else
    log_action_msg "II: check_slave: This host is not configured as being a MySQL slave"
    echo "II: check_slave: This host is not configured as being a MySQL slave" >> $REPORT
  fi
}



backup_mysql () {
umask 077
# cd into the backupdir, sometimes it's mounted via autofs. Without this, maybe
# check_freespace produces an error
cd "$BACKUPDIR"

### Deleting old dumps, if there are some. We want a clean directory !
cd "${MYDUMPDIR}" || { log_action_msg "EE: backup_mysql: Cannot cd into ${MYDUMPDIR}, exiting"; \
			echo "EE: backup_mysql: Cannot cd into ${MYDUMPDIR}, exiting." >> $REPORT; exit 1; }
if [[ "$(find "${MYDUMPDIR}" -type f | wc -l)" != "0" ]]; then
  find "${MYDUMPDIR}" -type f -print0 | xargs -0 rm
fi

printf "II: backup_mysql: Free space before dump:\t\t"
printf "II: backup_mysql: Free space before dump:\t\t" >> $REPORT
check_freespace

### Dumping databases
TABLECOUNT=0
STIME="$(date +%s)"
### Don't touch this variable $SECONDS !!! It's a bash special variable.
SECONDS=0
echo "II: backup_mysql: Dumping MySQL databases"
echo "II: backup_mysql: Dumping MySQL databases" >> $REPORT
if [[ "$MYCNF" ]]; then
  DBS="$(mysqlshow "${MYCNF}" | awk '/^| /{if( ($2 != "Databases") && ($2 != "") ){print $2}}')"
else
  DBS="$(mysqlshow | awk '/^| /{if( ($2 != "Databases") && ($2 != "") ){print $2}}')"
fi

for DB in ${DBS[@]}; do
  if [[ "$MYCNF" ]]; then
    TABLES="$(mysql "${MYCNF}" "${DB}" -NBqe "show tables;")";
  else
    TABLES="$(mysql "${DB}" -NBqe "show tables;")";
  fi

  if [[ "$TABLES" ]]; then
    echo "II: backup_mysql: Dumping database $DB"
    echo "II: backup_mysql: Dumping database $DB" >> $REPORT
    for TBL in ${TABLES[@]}; do
      if in_array "${DB}.${TBL}" "${IGNTBL[@]}"; then
	log_action_msg "WW: backup_mysql: table excluded: ${DB}.${TBL}"
	echo "WW: backup_mysql: table excluded: ${DB}.${TBL}" >> $REPORT
      else
	let ++TABLECOUNT
	if in_array "${DB}.${TBL}" "${TBLNOLOCK[@]}"; then
	  log_action_msg "WW: backup_mysql: table dumped without locks: ${DB}.${TBL}"
	  echo "WW: backup_mysql: table dumped without locks: ${DB}.${TBL}" >> $REPORT

	  if [[ "$MYCNF" ]]; then
	    mysqldump "${MYCNF}" ${MYOPTS} --lock-tables=0 "${DB}" "${TBL}" -r "${DB}-${TBL}-${DATE}.sql" || \
	    { log_action_msg "EE: backup_mysql: mysqldump failed on ${DB}.${TBL} ! Exiting"; \
		echo "EE: backup_mysql: mysqldump failed on ${DB}.${TBL} ! Exiting." >> $REPORT; exit 1; }
	  else
	    mysqldump ${MYOPTS} --lock-tables=0 "${DB}" "${TBL}" -r "${DB}-${TBL}-${DATE}.sql" || \
	    { log_action_msg "EE: backup_mysql: mysqldump failed on ${DB}.${TBL} ! Exiting"; \
		echo "EE: backup_mysql: mysqldump failed on ${DB}.${TBL} ! Exiting." >> $REPORT; exit 1; }
	  fi
	else
	  if [[ "$MYCNF" ]]; then
	    mysqldump "${MYCNF}" ${MYOPTS} "${DB}" "${TBL}" -r "${DB}-${TBL}-${DATE}.sql" || \
	    { log_action_msg "EE: backup_mysql: mysqldump failed on ${DB}.${TBL} ! Exiting"; \
		echo "EE: backup_mysql: mysqldump failed on ${DB}.${TBL} ! Exiting." >> $REPORT; exit 1; }
	  else
	    mysqldump ${MYOPTS} "${DB}" "${TBL}" -r "${DB}-${TBL}-${DATE}.sql" || \
	    { log_action_msg "EE: backup_mysql: mysqldump failed on ${DB}.${TBL} ! Exiting"; \
		echo "EE: backup_mysql: mysqldump failed on ${DB}.${TBL} ! Exiting." >> $REPORT; exit 1; }
	  fi
	fi
      fi
    done
  fi
done
log_action_msg "II: backup_mysql: done dumping databases"
echo "II: backup_mysql: done dumping databases" >> $REPORT
### Done dumping databases

DUMPCOUNT="$(find "$MYDUMPDIR" -type f -name '*.sql' | wc -l)"
[[ $TABLECOUNT = $DUMPCOUNT ]] || { log_action_msg "EE: backup_mysql: dumpfile count ($DUMPCOUNT) is different from table count ($TABLECOUNT). Exiting"; \
	echo "EE: backup_mysql: dumpfile count ($DUMPCOUNT) is different from table count ($TABLECOUNT). Exiting." >> $REPORT; exit 1; }

### Checking if dumps successfully completed
# checking in every dump that it is terminated successfully
# Sample:
# -- Dump completed on 2011-09-20 10:26:13
for DB in ${DBS[@]}; do
    if [[ "$MYCNF" ]]; then
      TABLES="$(mysql "${MYCNF}" "${DB}" -NBqe "show tables;")"
    else
      TABLES="$(mysql "${DB}" -NBqe "show tables;")"
    fi
    if [[ "$TABLES" ]]; then
	for TBL in ${TABLES[@]}; do
	    if ! in_array "${DB}.${TBL}" "${IGNTBL[@]}"; then
		FILE="$(find "${BACKUPDIR}" -type f -name "${DB}-${TBL}-${DATE}.sql")"
		if [[ "$FILE" ]] && [[ -f "$FILE" ]]; then
		    LINE="$(tail -n1 "${FILE}" | grep -e "Dump completed on ${DATEDUMP%-*}")"
		else
		    log_action_msg "EE: backup_mysql: Dumpfile ${BACKUPDIR}/${DB}-${TBL}-${DATE}.sql not found ! Exiting"
		    echo "EE: backup_mysql: Dumpfile ${BACKUPDIR}/${DB}-${TBL}-${DATE}.sql not found ! Exiting." >> $REPORT
		    exit 1
		fi
		if [[ "$LINE" ]]; then
		    END="${LINE#*on } UTC"
		    if [[ "$STIME" > $(date +"%s" -d "$END") ]]; then
			log_action_msg "EE: backup_mysql: Dump ${DB}-${TBL}-${DATE}.sql is erroneous (wrong times) ! Exiting"
			echo "EE: backup_mysql: Dump ${DB}-${TBL}-${DATE}.sql is erroneous (wrong times) ! Exiting." >> $REPORT
			exit 1
		    fi
		else
		    log_action_msg "EE: backup_mysql: Dump ${DB}-${TBL}-${DATE}.sql does not contain any \"Dump completed on...\" line ! Exiting"
		    echo "EE: backup_mysql: Dump ${DB}-${TBL}-${DATE}.sql does not contain any \"Dump completed on...\" line ! Exiting." >> $REPORT
		    exit 1
		fi
	    fi
	done
    else
	log_action_msg "WW: backup_mysql: there is no table to back up in this database ($DB) !"
	echo "WW: backup_mysql: there is no table to back up in this database ($DB) !" >> $REPORT
    fi
done
### Done checking if dumps successfully completed

printf "II: backup_mysql: Free space before packing into tar:\t"
printf "II: backup_mysql: Free space before packing into tar:\t" >> $REPORT
check_freespace

if [[ "$(echo $MYCOMP | awk '{print tolower($0)}')" = "yes" ]]; then
  ### Compressing dumps into one file
  echo "II: backup_mysql: Packing into a tar archive..."
  echo "II: backup_mysql: Packing into a tar archive" >> $REPORT
  tar --remove-files -zcf "${BACKUPDIR}"/mydatabases-${DATE}-${HOSTNAME}.tar.gz -C / "$MYDUMPDIR"/*
  echo "II: backup_mysql: done"
fi

TOTALTIME=$SECONDS

printf "II: backup_mysql: Free space after packing into tar:\t"
printf "II: backup_mysql: Free space after packing into tar:\t" >> $REPORT
check_freespace
}



report_freespace() {
printf "II: Free space after backup:\t\t"
printf "II: report_freespace: Free space after backup:\t\t" >> $REPORT
check_freespace
}



report_dumpstats() {
TOTALTIMEH="$(echo "scale=3;${TOTALTIME}/3600" | bc)"
log_action_msg "II: report_dumpstats: ${TABLECOUNT} tables have been dumped in ${TOTALTIME} seconds (${TOTALTIMEH} hours)"
echo "II: report_dumpstats: ${TABLECOUNT} tables have been dumped in ${TOTALTIME} seconds (${TOTALTIMEH} hours)" >> $REPORT

if [[ "${TBLNOLOCK[@]}" ]]; then
  log_action_msg "II: report_dumpstats: Following tables have been dumped without locks:"
  echo "II: report_dumpstats: Following tables have been dumped without locks:" >> $REPORT
  printf "II: report_dumpstats: %s\n" "${TBLNOLOCK[@]}"
  printf "II: report_dumpstats: %s\n" "${TBLNOLOCK[@]}" >> $REPORT
fi

if [[ "${IGNTBL[@]}" ]]; then
  log_action_msg "II: report_dumpstats: Following tables have been ignored:"
  echo "II: report_dumpstats: Following tables have been ignored:" >> $REPORT
  printf "II: report_dumpstats: %s\n" "${IGNTBL[@]}"
  printf "II: report_dumpstats: %s\n" "${IGNTBL[@]}" >> $REPORT
fi
}



report_dumpsize() {
if [[ "$(echo $MYCOMP | awk '{print tolower($0)}')" != "yes" ]]; then
  return
fi
DUMPSIZE=""
DUMPSIZEMB=""
FILE="$(find "${BACKUPDIR}" -name "mydatabases-${DATE}-${HOSTNAME}.tar.gz")"
if [[ ! -f "$FILE" ]]; then
  log_action_msg "EE: report_dumpsize: Dump file ${FILE} does not exist ! Exiting"
  echo "EE: report_dumpsize: Dump file ${FILE} does not exist ! Exiting." >> $REPORT
  exit 1
else
  DUMPSIZE="$(stat -c "%s" "${FILE}")"
  DUMPSIZEMB="$(echo "scale=3; ${DUMPSIZE}/1048576" | bc)"
  log_action_msg "II: report_dumpsize: ${FILE} is ${DUMPSIZEMB} MB"
  echo "II: report_dumpsize: ${FILE} is ${DUMPSIZEMB} MB" >> $REPORT
fi

YEST="$(date -d "yesterday" "+%Y%m%d")"
OLDSIZE=""
OLDSIZEMB=""
OLDFILE="$(find "${BACKUPDIR}" -name "mydatabases-${YEST}-${HOSTNAME}.tar.gz")"
if [[ ! -f "$OLDFILE" ]]; then
  log_action_msg "WW: report_dumpsize: There is no old dump file: ${OLDFILE}."
  echo "WW: report_dumpsize: There is no old dump file: ${OLDFILE}." >> $REPORT
else
  OLDSIZE="$(stat -c "%s" "${OLDFILE}")"
  OLDSIZEMB="$(echo "scale=3; ${OLDSIZE}/1048576" | bc)"
  log_action_msg "II: report_dumpsize: ${OLDFILE} is ${OLDSIZEMB} MB"
  echo "II: report_dumpsize: ${OLDFILE} is ${OLDSIZEMB} MB" >> $REPORT
fi

if [[ "${OLDSIZE}" ]] && [[ "${DUMPSIZE}" ]]; then
  if [[ ${OLDSIZE} > ${DUMPSIZE} ]]; then
    log_action_msg "WW: report_dumpsize: The actual dump is smaller than the last dump !"
    echo "WW: report_dumpsize: The actual dump is smaller than the last dump !" >> $REPORT
  fi
fi
}



report_start() {
echo "................................................" >> $REPORT
echo "......... $(date) ........" >> $REPORT
echo "................................................" >> $REPORT
}



#--------------------------------------------------------------------------------------#
report_close() {
echo "................................................" >> $REPORT
}



#--------------------------------------------------------------------------------------#

errparam=0

report_start

while getopts "hd:s:" param; do
  case $param in
      d)  # Backup Directory is other than the default $BACKUPDIR
          BACKUPDIR="$OPTARG"
          echo "II: $IAM: Backup dir specified from the commandline: $BACKUPDIR"
          echo "II: $IAM: Backup dir specified from the commandline: $BACKUPDIR" >> $REPORT
          ;;

      h)  # print usage
          errparam=3
          ;;

      \?) echo "WW: $IAM: Parameter $OPTARG is invalid"
	  echo "WW: $IAM: Parameter $OPTARG is invalid." >> $REPORT
          errparam=3
          ;;
  esac
done

if [[ $errparam = 3 ]]; then
  ### [[ -t 0 ]] tests if the script is run in a terminal or not
  [[ -t 0 ]] || { echo "II: $IAM: not running in a terminal, not printing usage. Exiting."; \
		echo "II: $IAM: not running in a terminal, not printing usage. Exiting." >> $REPORT; exit; }
  usage >&2;
  exit 3
fi

# Backup MySQL database
check_mysql
check_slave
backup_mysql

report_freespace
report_dumpsize
report_dumpstats
report_close

exit $errparam;

