check_mysqlserver.md

Check Mysql / mariadb server

Script: check_mysqlserver

check_mysqlserver is a plugin execute different checks on a mysql server instance. The kind of check is defined by a parameter -m METHOD.

Requirements

• mysql (mysql client as cli tool)
• The icinga user needs to connect to the database server (see Installation).

Standalone installation

From this repository you need next to this script:

• inc_pluginfunctions shared function for all IML checks written in bash

Syntax

$ check_mysqlserver [-i|-u|-m METHOD]

______________________________________________________________________

CHECK_MYSQLSERVER
v1.2

(c) Institute for Medical Education - University of Bern
Licence: GNU GPL 3

https://os-docs.iml.unibe.ch/icinga-checks/Checks/check_mysqlserver.html
______________________________________________________________________

USAGE:
  check_mysqlserver [OPTIONS] -m METHOD

OPTIONS:
  -h  this help
  -i  install monitoring user (must be executed as root)
  -u  uninstall monitoring user (must be executed as root)

PARAMETERS:
  -m  method; valid methods are:
      connections     current/ max connections
      connects        connects per min and aborted connections/ clients
      commands        current running statements insert, select, ...
      qcache-blocks   query cache blocks total vs free
      qcache-queries  cached / non cached queries; low memeory prunes 
      qcache-hits     query cache efficiency: cached vs non cached and inserted

EXAMPLES:
  check_mysqlserver -i
  check_mysqlserver -m commands

Installation

To give access to the database there is the param -i(for "install"). This command needs to be run as root.

./check_mysqlserver -i

This creates a database user "icingamonitor" with a 64 byte random password. To store the credentials a file file be created: /etc/icingaclient/.my.cnf. It is the known mysql password file. The mysql check plugin sets $HOME to /etc/icingaclient and this config will be found automatically.

cat /etc/icingaclient/.my.cnf
#
# generated on Tue Jun  8 10:01:31 CEST 2021
#
[client]
user=icingamonitor
host=localhost
password=qrYEiPG33qNoedO4oWNrmtDOtEmQ9tw8pYEYcPbuYphUweemJEOC4fzZXIWJFvZ5

To test the connection run ./check_mysqlserver -m connections.

Checks

connections

current/ max connections

./check_mysqlserver -m connections
OK: Mysql connections :: current/ max connections
           max_connections:       151
      Max_used_connections:        11
                                  --> 7 % 

        Threads_connected:         3
                                  --> 1 % 

                Questions:         1
 
 |maxconnections=151;; maxusedconnections=11;; threadsconnected=3;; questions=1;;

connects

connects per min and aborted connections/ clients

./check_mysqlserver -m connects
OK: Mysql connects :: connects per min and aborted connections/ clients
               Connections:     956785 ... delta =  per min
           Aborted_clients:         19 ... delta =  per min
          Aborted_connects:          1 ... delta =  per min
 
 |connections=0;; abortedclients=0;; abortedconnects=0;;

commands

current running statements insert, select, ...

./check_mysqlserver -m commands
OK: Mysql commands :: currently executed commands
                    delete:         0
                    insert:         0
                   replace:         0
                    select:         0
                    update:         0
 
 |delete=0;; insert=0;; replace=0;; select=0;; update=0;;

qcache-blocks

query cache blocks total vs free

./check_mysqlserver -m qcache-blocks
INFO: Query cache (have_query_cache) is active but query_cache_type is OFF.

qcache-queries

cached / non cached queries; low memeory prunes

./check_mysqlserver -m qcache-queries
INFO: Query cache (have_query_cache) is active but query_cache_type is OFF.

qcache-hits

query cache efficiency: cached vs non cached and inserted

./check_mysqlserver -m qcache-hits
INFO: Query cache (have_query_cache) is active but query_cache_type is OFF.

Uninstall

As root run

./check_mysqlserver -u

This drops the database user icingaclient and removes the config file /etc/icingaclient/.my.cnf.

Graph

See templates for mysql graphs in https://git-repo.iml.unibe.ch/iml-open-source/icinga-graphite-templates.