Bash Script – Auto-Repair an Array of MySQL Database Servers

I have a few servers at work that regularly get crashed tables so I created this script as a workaround until I have time to find the root cause of the problem. Run it as a cron job at your own interval.

#!/bin/bash
# Run mysqlcheck auto-repair on an array of servers
# Author: Nathan Thomas
# Updated 06/25/2015 - Added username/password arrays in the loop, better error checking, code cleanup

LOGFILE=/var/log/mysql/mysql_repair_db.log
MYSQL_USER=('root' 'root2' 'bob')
MYSQL_PASS=('password' 'password2' 'password3')
MYSQL_HOST=('server1.fqdn.com' 'server2.fqdn.com' 'server3.fqdn.com')

i=0
for HOST in "${MYSQL_HOST[@]}"
do
   RETVAR=""
   RETVAL=""
   RETVAR=$( mysqlcheck -A --auto-repair -s -u "${MYSQL_USER[$i]}" -p"${MYSQL_PASS[$i]}" -h "${MYSQL_HOST[$i]}" 2>&1 )
   RETVAL=$?
   if [ "${RETVAL}" -eq 0 ] ; then
        echo "`date "+%Y %a %b%e %T"` - DB repair not needed on ${MYSQL_HOST[$i]}" >> ${LOGFILE}
      else
        echo "`date "+%Y %a %b%e %T"` - DB repair on ${MYSQL_HOST[$i]} finished with exit status \"${RETVAR}\"" >> ${LOGFILE}
   fi
((i++))
done