#!/bin/bash
# Ref - 8698jxvww - Consolidated MySQL checks
#

SQL_PID=$(sudo pgrep mysqld)
MARIADB_PID=$(sudo pgrep mariadb)
if [[ -n "${SQL_PID}" ]]; then
  PID=${SQL_PID}
  SQL=MySQL
  sql_admin_bin="/usr/bin/mysqladmin"
  if [[ -e "/opt/mysql-8.0/bin/mysql" ]]; then
    sql_client="/opt/mysql-8.0/bin/mysql"
  else
    sql_client="/usr/bin/mysql"
  fi
  VER=$(${sql_client} --version 2>/dev/null| awk '{print $3}' | cut -d ',' -f1)
elif [[ -n "${MARIADB_PID}" ]]; then
  PID=${MARIADB_PID}
  SQL=MariaDB
  if [[ -e "/usr/bin/mysqladmin" ]]; then
    sql_admin_bin="/usr/bin/mysqladmin"
  else
    sql_admin_bin="/usr/bin/mariadb-admin"
  fi
  if [[ -e "/usr/bin/mysql" ]]; then
    sql_client="/usr/bin/mysql"
    VER=$(${sql_client} --version 2>/dev/null| grep -oP '[0-9]+\.[0-9]+\.[0-9]+(?=-MariaDB)')
  else
    sql_client="/usr/bin/mariadb"
    VER=$(${sql_client} --version 2>/dev/null| grep -oP '[0-9]+\.[0-9]+\.[0-9]+(?=-MariaDB)')
  fi
else
  echo "MySQL Status: [[ CRITICAL ]] - No SQL process found"
  exit 2
fi

mysql_status_check_func() {
  mysql_status_check=$(${sql_admin_bin} status 2>/dev/null)
  if echo "${mysql_status_check}" | grep -qi uptime; then
    echo "${mysql_status_check}"
  else
    echo "${SQL} Check - [[ CRITICAL ]] - ${mysql_status_check}"
  fi
}

mariadb_provider=$(${sql_client} -V 2>/dev/null | grep MariaDB)
if [[ -n "${mariadb_provider}" ]]; then
  inf_table="information_schema.GLOBAL_STATUS"
else
  inf_table="performance_schema.global_status"
fi

mysql_status_connections_func() {
  mysql_conn_data=$(${sql_client} -Nse "
  SELECT
    CONCAT(
      'Query/Execute processes: ',
      (SELECT COUNT(*) FROM information_schema.processlist WHERE COMMAND NOT IN ('Sleep', 'Binlog Dump')),
      '; ',
      'Open_table_definitions / @@table_definition_cache: ',
      (SELECT VARIABLE_VALUE FROM ${inf_table} WHERE VARIABLE_NAME = 'Open_table_definitions'),
      '/',
      @@table_definition_cache,
      '; ',
      'queries with cache lock: ',
      (SELECT VARIABLE_VALUE FROM ${inf_table} WHERE VARIABLE_NAME = 'Table_locks_waited')
    ) AS status_summary;
  " 2>/dev/null)
  max_qe_processes=$(${sql_client} -Nse "SELECT COUNT(*) FROM information_schema.processlist WHERE COMMAND NOT IN ('Sleep', 'Binlog Dump');" 2>/dev/null)
  if [[ -n "${max_qe_processes}" ]]; then
    if [[ "${max_qe_processes}" -lt 50 ]]; then
      echo "${SQL} Connections: OK - ${mysql_conn_data}"
    else
      echo "${SQL} Connections: [[ CRITICAL ]] - ${mysql_conn_data}"
    fi
  else
    echo "${SQL} Connections: [[ CRITICAL ]] - unable to read the database for Connections"
  fi
}

mysql_status_connect_time_func() {
  mysql_select_test=$(${sql_client} -e "SELECT 100000;" 2>/dev/null)
  if [[ -n "${mysql_select_test}" ]]; then
    if echo "${mysql_select_test}" | grep -q 100000; then
      mysql_conn_time_raw=$( { time ${sql_client} -e "SELECT 1;" >/dev/null 2>&1; } 2>&1 | grep real | awk '{print $2}' | sed 's/^0m//;s/s$//')
      mysql_conn_time=$(printf "%.0f" "${mysql_conn_time_raw}")

      if [[ "${mysql_conn_time}" -gt 5 ]]; then
        echo "${SQL} Connection time: [[ CRITICAL ]] - ${mysql_conn_time_raw} second(s)"
      else
        echo "${SQL} Connection time: OK - ${mysql_conn_time_raw} seconds"
      fi
    else
      echo "${SQL} Connection time: [[ CRITICAL ]] - failed to run a SELECT query"
    fi
  else
    echo "${SQL} Connection time: [[ CRITICAL ]] - unable run SELECT query"
  fi
}

mysql_status_memory_func() {
  mysql_mem=$(sudo grep VmRSS /proc/${PID}/status | awk '{print $2}');
  mysql_mem_in_gb=$(sudo grep VmRSS /proc/${PID}/status | awk '{print int($2/1024/1024)}');
  total_mem=$(sudo grep MemTotal /proc/meminfo | awk '{print $2}')
  total_mem_in_gb=$(sudo grep MemTotal /proc/meminfo | awk '{print int($2/1024/1024)}')
  mysql_mem_usage_p=$(( (${mysql_mem} * 100) / ${total_mem} ))

  if [ "${total_mem_in_gb}" -ge 35 ]; then
    ninety_p_in_gb=$((${total_mem_in_gb}*90/100))
    if [[ "${mysql_mem_in_gb}" -ge "${ninety_p_in_gb}" ]];then
      echo "${SQL} Memory Usage: [[ CRITICAL ]] - ${usagep}% of total RAM, usage ${mysql_mem_in_gb}GB of ${total_mem_in_gb}GB"
    else
      echo "${SQL} Memory Usage: OK - ${mysql_mem_in_gb}GB/${total_mem_in_gb}GB"
    fi
  else
    echo "${SQL} Memory Usage: OK - ${mysql_mem_in_gb}GB of ${total_mem_in_gb}GB"
  fi
}

mysql_status_open_files_func() {
  mysql_conn_open_files=$(${sql_client} -Nse "SELECT variable_value FROM ${inf_table} WHERE variable_name = 'Open_files';" 2>/dev/null)
  if [[ -n "${mysql_conn_open_files}" ]]; then
    mysql_conn_open_files_max=$(${sql_client} -Nse "SELECT @@global.open_files_limit;" 2>/dev/null)
    mysql_conn_open_files_used_percent=$(awk -v open="${mysql_conn_open_files}" -v max="${mysql_conn_open_files_max}" \ 'BEGIN {printf "%.0f", (open/max)*100}')
    if [[ "${mysql_conn_open_files_used_percent}" -gt 95 ]]; then
      echo "${SQL} Open Files: [[ CRITICAL ]] - ${mysql_conn_open_files} open files out of ${mysql_conn_open_files_max} limit (${mysql_conn_open_files_used_percent}%)"
    else
      echo "${SQL} Open Files: OK - ${mysql_conn_open_files} open files out of ${mysql_conn_open_files_max} limit (${mysql_conn_open_files_used_percent}%)"
    fi
  else
   echo "${SQL} Open Files: [[ CRITICAL ]] - unable to read the database for Openfiles"
  fi
}

mysql_status_threads_func() {
  mysql_conn_threads_data=$(${sql_client} -Nse "SHOW STATUS LIKE 'Threads_%';" 2>/dev/null| column -t | sed 's/  */ /g')
  mysql_Threads_connected=$(awk '/Threads_connected/ {print $2}' <<< "${mysql_conn_threads_data}")
  if [[ -n "${mysql_Threads_connected}" ]]; then
    if [[ "${mysql_Threads_connected}" -gt 200 ]]; then
      echo "${SQL} Threads: [[ CRITICAL ]] - ${mysql_Threads_connected} Threads connected of 200 threshold - ${mysql_conn_threads_data}" | sed ':a;N;$!ba;s/\n/, /g'
    else
      echo "${SQL} Threads: OK - ${mysql_conn_threads_data}" | sed ':a;N;$!ba;s/\n/, /g'
    fi
  else
    echo "${SQL} Threads: [[ CRITICAL ]] - unable to read the database for Threads"
  fi
}

mysql_check_runner() {
  mysql_status_check_func
  mysql_status_connections_func
  mysql_status_connect_time_func
  mysql_status_memory_func
  mysql_status_open_files_func
  mysql_status_threads_func
}

mysql_check_data_raw=$(mysql_check_runner)
mysql_check_data_all=$(echo "${mysql_check_data_raw}"| sed ':a;N;$!ba;s/\n/ | /g')
mysql_check_data_alerts=$(echo "${mysql_check_data_raw}"| grep CRIT | sed ':a;N;$!ba;s/\n/ | /g')
if echo "${mysql_check_data_all}" | grep -q CRIT; then
  echo "Check ${SQL} ($VER) Status: CRIT - ${mysql_check_data_alerts}"
  exit 2
else
  echo "Check ${SQL} ($VER) Status: ${mysql_check_data_all}"
  exit 0
fi