Edit file

File name : check_mysql_all.sh

Content :#!/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

Save