# check Mysql / mariadb server

## Introduction

**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 (cli tool)
* The icinga user needs to connect to the database server (see Installation).

## Syntax

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

```txt
______________________________________________________________________

CHECK_MYSQLSERVER
v1.0

(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.

```txt
./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. 

```txt
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

```txt
./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

```txt
./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, ...

```txt
./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

```txt
./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 

```txt
./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
 
```txt
./check_mysqlserver -m qcache-hits
INFO: Query cache (have_query_cache) is active but query_cache_type is OFF.
```


## Uninstall

As root run

```txt
./check_mysqlserver -u
```

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