#!/bin/bash
#                                                          +----+----+----+----+
#                                                          |    |    |    |    |
# Author: Mark David Scott Cunningham                      | M  | D  | S  | C  |
#                                                          +----+----+----+----+
# Created: 2018-11-02
# Updated: 2018-11-02
#
# Purpose: Watch for crashed tables being logged and then repair them
#

# If lockfile is missing create lock file and insert pid of script run
# If lockfile exists, then quit out assuming that another check is running
lockfile='/root/mysql-crash-check.lock'

if [[ ! -f $lockfile ]]; then
  echo $$ > $lockfile
else
  exit;
fi

# Determine error log location and set current datetime
logfile=$(mysql -NBe 'select @@log_error')
datetime=$(date --date="-1 minute" +"%y%m%d %k:%M")

# Set output file for search and log the search
checkfile='/root/mysql-crash-check.chk'
grep -i "${datetime}.*is marked as crashed" $logfile > $checkfile

# Set email address and log file for emailing back out
email="root@$HOSTNAME"
outfile='/root/mysql-crash-check.log'

# If the error log shows crashed tables try to repair them
if [[ -s $checkfile ]]; then
  echo "----------" > $outfile
  awk -F[\'/] '{print $3,$4}' $checkfile | sort | uniq | while read line; do
    mysqlcheck -r $line
  done &> $outfile
  cat $checkfile $outfile | mail -s "MySQL Crashed Tables :: $HOSTNAME :: $(date +'%F %T')" $email;
fi

# Cleanup
rm -f $lockfile