mysql wait metadata lock check.

mysql_conn_metadata_lock.bash

#!/bin/bash -x

#-------------------
# Define
#-------------------

LANG=C

DATETIME="`date +'%Y%m%d-%H%M%S'`"

DIR_BASE="/usr/local"
DIR_CONF="${DIR_BASE}/conf"
DIR_TMP="${DIR_BASE}/tmp"
FL_LOG="${DIR_TMP}/`basename $0 .bash`_${DATETIME}_$$.log"
LIST_METALOCK="${DIR_TMP}/`basename $0 .bash`_${DATETIME}_$$"

MYSQL_USER="root"
MYSQL_CRYPT_FILE="${DIR_CONF}/mysql_backup_crypt.dat"
MYSQL_DECRYPT_DAT="${DIR_CONF}/mysql_backup_rand"
MYSQL_SOCK="/var/lib/mysql/mysql.sock"

NUM_ALERT="10"

CMD_CAT="/bin/cat"
CMD_RM="/bin/rm"


#--------------------
# Function
#--------------------

CMD_SENDMAIL="/usr/sbin/sendmail"
MAIL_FROM="<from-addr>"
MAIL_TO="<to-addr>"
MAIL_SUBJECT="### `hostname` : MySQL MetadataLock Error ###"

func_mailsend(){
    MAIL_BODY="$1"
echo "From: ${MAIL_FROM}
To: ${MAIL_TO}
Subject: ${MAIL_SUBJECT}
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-2022-jp
Content-Transfer-Encording: 7bit

${MAIL_BODY}
===== log start =====
`${CMD_CAT} ${FL_LOG}`
`${CMD_CAT} ${LIST_METALOCK}`
===== log end =====
" | ${CMD_SENDMAIL} -ti
}

#--------------------
# Main
#--------------------

logger -p user.info "### MySQL Connection metadata lock is Start. ###"

# log write
exec > ${FL_LOG} 2>&1
echo "### MySQL Connection metadata lock is Log Write Start. ###"

if [ ! -s "${MYSQL_CRYPT_FILE}" ]; then
    echo "### MySQL Connection metadata lock is File Not Exist. : Crypt File is ${MYSQL_CRYPT_FILE} ###"
    func_mailsend "### MySQL Connection metadata lock is File Not Exist. : Crypt File is ${MYSQL_CRYPT_FILE} ###"
    exit 1
fi

if [ ! -s "${MYSQL_DECRYPT_DAT}" ]; then
    echo "### MySQL Connection metadata lock is File Not Exist. : Decrypt Data File is ${MYSQL_DECRYPT_DAT} ###"
    func_mailsend "### MySQL Connection metadata lock is File Not Exist. : Decrypt Data File is ${MYSQL_DECRYPT_DAT} ###"
    exit 1
fi

# mysql crypt
MYSQL_PASS="`openssl enc -d -aes256 -in ${MYSQL_CRYPT_FILE} -pass file:${MYSQL_DECRYPT_DAT}`"
ret=$?
if [ ${ret} -ne 0 ]; then
    echo "### MySQL Connection metadata lock is Decrypt Failed. ###"
    func_mailsend "### MySQL Connection metadata lock is Decrypt Failed. ###"
    exit 2
fi

# metadata lock count check
mysqladmin -u ${MYSQL_USER} -p${MYSQL_PASS} -S ${MYSQL_SOCK} processlist | grep 'Waiting for table metadata lock' > ${LIST_METALOCK}
NUM_LOCK="`${CMD_CAT} ${LIST_METALOCK} | wc -l`"
if [ ${NUM_LOCK} -gt ${NUM_ALERT} ]; then
    logger -p user.info "### MySQL Connection metadata lock is ${NUM_LOCK}. ###"

    # non metadata lock and NULL and Sleep and NULL is kill
    LIST_KILL_ID=`mysqladmin -u ${MYSQL_USER} -p${MYSQL_PASS} -S ${MYSQL_SOCK} processlist | awk -F\| '{print $2","$3","$6","$9}' | \
    sed "s/ //g" | grep ^[0-9] | awk -F, '$3=="Sleep" && $4=="NULL" {print $1}'`

    for i in $(echo "${LIST_KILL_ID}"); do
        echo "mysqladmin -u ${MYSQL_USER} -p${MYSQL_PASS} -S ${MYSQL_SOCK} kill ${i}"
    done

    func_mailsend "### MySQL Connection metadata lock is ${NUM_LOCK}. Error. ###"
    logger -p user.info "### MySQL Connection metadata lock is Abnormal End. ###"
    exit 3
fi

${CMD_RM} -f ${LIST_METALOCK:-/nodefined} ${FL_LOG:-/nodefined}
logger -p user.info "### MySQL Connection metadata lock is ${NUM_LOCK}. Normal End. ###"

exit 0