22

✏️ Editing: check_a2_long_mysql_queries

#!/bin/bash

# SYSENG-22017 enabling checks for long queries.
# Time threshold for long queries (in seconds)
THRESHOLD=10800  # 3hours

# Log file path
LOG_FILE="/var/log/cmk_mysql_long_queries.log"

if ! mysqladmin ping >/dev/null 2>&1; then
  echo "MySQL_Long_Queries - Couldn't connect to MySQL"
  echo -e "$(date '+%Y-%m-%d %H:%M:%S') - Error: Unable to connect to MySQL." >> "${LOG_FILE}"
  exit 2
fi

# Check if the log file exists, and if not, create it
if [ ! -f "${LOG_FILE}" ]; then
  touch "${LOG_FILE}"
fi

# Connect to MySQL and get the long-running queries
LONG_QUERIES=$(nice -n19 mysql --defaults-extra-file=/root/.my.cnf -b -N --skip-comments -e "SELECT DB, USER, TIME, INFO FROM INFORMATION_SCHEMA.PROCESSLIST WHERE time > ${THRESHOLD} AND Command NOT LIKE 'Sleep';" 2>&1)

# Output the result for CheckMK
NUM_LONG_QUERIES=$(echo "${LONG_QUERIES}" | grep -v '^$' | wc -l)
if [ "${NUM_LONG_QUERIES}" -gt 0 ]; then
  echo "MySQL_Long_Queries - There are ${NUM_LONG_QUERIES} long-running queries. Log file ${LOG_FILE}"
  echo -e "$(date '+%Y-%m-%d %H:%M:%S') - Long-running queries details:" >> "${LOG_FILE}"
  echo "${LONG_QUERIES}" >> "${LOG_FILE}"
  exit 2
else
  echo "MySQL_Long_Queries - No long-running queries found."
  exit 0
fi

MR SKB