You are here

Home » Blogs » Shinguz's blog

Monitoring your MariaDB database with SNMP

Mon, 2021-08-16 14:24 — Shinguz

Table of Contents


What is SNMP?

A customer recently had the question if an how his MariaDB database can be easily monitored with SNMP?

SNMP means Simple Network Management Protocol. It is a widely used and standardized protocol for monitoring the health of network and other devices (including services). In principle you can monitor nearly everything with SNMP.

On Linux a common implementation of SNMP is Net-SNMP, a suite of applications used to implement SNMP v1, SNMP v2c and SNMP v3 using both IPv4 and IPv6.

SNMP is a typical client-server architecture: The client which is collecting and sending the monitoring data is called agent and the server collecting all the monitoring data is called manager.


Source: Wikipedia: SNMP

An agent can be polled by the manager to collect the monitoring data (Request/Responses) or it can send monitoring data on its own (Trap). The latter one is called a SNMP Trap.


Source: Cisco: Understanding Simple Network Management Protocol (SNMP) Traps

Each measuring event type get its own Object Identifier (OID) which looks for example as follows: 1.3.6.1.4.1.57800.1.1.1. This is a representation of a tree hierarchy called MIB (Management Information Base):


Source: DPS Telecom SNMP OID: Introduction for Industry Professionals

An OID can also be represented in a human readable textual form which looks for example as follows: FromDual-fpmmm-MIB::fpmmmStart

SNMP Agent (snmptrap)

In this project we concentrate on the SNMP trap agent (snmptrap). Which sends an asynchronous notification to the manager (snmptrapd). To install it on Debian Linux you first have to activate the Debian non-free repository:

# echo 'deb http://ftp.us.debian.org/debian/ buster main non-free' >> /etc/apt/sources.list.d/non-free.list
# apt-get update
# apt-get install snmp snmp-mibs-downloader

These 2 packages contain:

snmpSNMP (Simple Network Management Protocol) applications
snmp-mibs-downloaderinstall and manage Management Information Base (MIB) files

To accept and load the MIBs the configuration has to be adapted. It is made so complicated because of some legal reasons:

# sed -i 's/mibs :/# mibs :/g' /etc/snmp/snmp.conf

SNMP Manager (snmptrapd)

There are 2 different types of SNMP managers. The SNMP daemon (snmpd) and the SNMP trap daemon (snmptrapd). We concentrate on the later one in this project. To install it on Debian Linux you first have to activate the Debian non-free repository:

# echo 'deb http://ftp.us.debian.org/debian/ buster main non-free' >> /etc/apt/sources.list.d/non-free.list
# apt-get update
# apt-get install snmptrapd snmp-mibs-downloader

These 2 packages contain:

snmptrapdNet-SNMP notification receiver
snmp-mibs-downloaderinstall and manage Management Information Base (MIB) files

To accept and load the MIBs the configuration has to be adapted. It is so complicated because of some legal problems:

# sed -i 's/mibs :/# mibs :/g' /etc/snmp/snmp.conf
# sed -i 's/export MIBS=/# export MIBS=/g' /etc/default/snmpd

For our tests we use the following configuration file:

# cat << _EOF >/etc/snmp/snmptrapd.conf
disableAuthorization yes
authCommunity   log,execute,net public

createUser myuser MD5 mypassword DES myotherpassword

[snmp] logOption s 2
[snmp] logOption f /var/log/snmptrapd-direct.log

format2 %V\n% Agent Address: %A \n Agent Hostname: %B \n Date: %H - %J - %K - %L - %M - %Y \n Enterprise OID: %N \n Trap Type: %W \n Trap Sub-Type: %q \n Community/Infosec Context: %P \n Uptime: %T \n Description: %W \n PDU Attribute/Value Pair Array:\n%v \n -------------- \n
_EOF

Then the SNMP trap daemon has to be (re-)started:

# systemctl start snmptrapd.service

The log messages then can be found in /var/log/snmptrapd-direct.log or otherwise like this: grep snmptrap /var/log/syslog.

If you write your own MIBs they can be located here: /usr/share/snmp/mibs.

Test the SNMP Agent

An SNMP trap is send as follows:

# COMMUNITY='public'
# MANAGER='192.168.56.102'
# PORT='162'
# TRAP_OID='1.3.6.1.4.1.57800.1.1.2'
# OID='1.3.6.1.4.1.57800.1.1.1'
# TYPE='c'
# VALUE=$(date "+%s")

# snmptrap -v 2c -c ${COMMUNITY} ${MANAGER}:${PORT} '' ${TRAP_OID} ${OID} ${TYPE} "${VALUE}"

And then you will see in the SNMP trap daemon error log:

 Agent Address: 0.0.0.0
 Agent Hostname: chef.rebenweg
 Date: 22 - 7 - 20 - 30 - 1 - 4461326
 Enterprise OID: .
 Trap Type: Cold Start
 Trap Sub-Type: 0
 Community/Infosec Context: TRAP2, SNMP v2c, community public
 Uptime: 0
 Description: Cold Start
 PDU Attribute/Value Pair Array:
DISMAN-EVENT-MIB::sysUpTimeInstance = Timeticks: (72136167) 8 days, 8:22:41.67
SNMPv2-MIB::snmpTrapOID.0 = OID: 1.3.6.1.4.1.57800.1.1.2
1.3.6.1.4.1.57800.1.1.1 = Counter32: 1628864995

Or more MariaDB specific:

# VALUE=$(mariadb --user=root --execute="SELECT variable_value FROM information_schema.global_status WHERE variable_name LIKE 'threads_running'\G" | grep variable_value | cut -d' ' -f2)

# snmptrap -v 2c -c ${COMMUNITY} ${MANAGER}:${PORT} '' ${TRAP_OID} ${OID} ${TYPE} "${VALUE}"

Creating your own MIB

How you write your own MIBs you can find here: Writing your own MIBs.

MIBs can/should be located under $HOME/.snmp/mibs or /usr/local/share/snmp/mibs. The MIB search path can be found with this command:

# snmptranslate -Dinit_mib .1.3 2>&1 | grep MIBDIRS

# ll /root/.snmp/mibs /usr/share/snmp/mibs /usr/share/snmp/mibs/iana /usr/share/snmp/mibs/ietf /usr/share/mibs/site /usr/share/snmp/mibs /usr/share/mibs/iana /usr/share/mibs/ietf /usr/share/mibs/netsnmp

A tool for checking your MIB is smilint:

# apt-get install smitools

# smilint snmp/FromDual-fpmmm-MIB.mib --level=6
snmp/FromDual-fpmmm-MIB.mib:90: warning: node `fpmmmLastrun' must be contained in at least one conformance group

If you want to extend the MIB search path you can modify the MIBDIRS environment variable:

# export MIBDIRS=/home/oli/fromdual_devel/fpmmm/snmp:/home/oli/.snmp/mibs:/usr/share/snmp/mibs:/usr/share/snmp/mibs/iana:/usr/share/snmp/mibs/ietf:/usr/share/mibs/site:/usr/share/snmp/mibs:/usr/share/mibs/iana:/usr/share/mibs/ietf:/usr/share/mibs/netsnmp

To check if your MIB is correctly translated into an OID and vice versa you can use the tool snmptranslate:

# snmptranslate -DFromDual-fpmmm-MIB.mib -m +FromDual-fpmmm-MIB 1.3.6.1.4.1.57800.1.1.1
registered debug token FromDual-fpmmm-MIB.mib, 1
FromDual-fpmmm-MIB::fpmmmLastrun

# snmptranslate -On FromDual-fpmmm-MIB::fpmmmLastrun
.1.3.6.1.4.1.57800.1.1.1

And if the translation works you can send an SNMP trap with the MIB instead of the OID: 

# COMMUNITY='public'
# MANAGER='192.168.56.102'
# PORT='162'
# TRAP_OID="FromDual-fpmmm-MIB::fpmmmStart"
# OID="FromDual-fpmmm-MIB::fpmmmLastrun"
# TYPE='c'
# VALUE=$(date "+%s")

# snmptrap -v 2c -c ${COMMUNITY} ${MANAGER}:${PORT} '' ${TRAP_OID} ${OID} ${TYPE} "${VALUE}"

and this should also be translated correctly in the snmptrapd error log:

 Agent Address: 0.0.0.0
 Agent Hostname: chef.rebenweg
 Date: 22 - 7 - 20 - 30 - 1 - 4461326
 Enterprise OID: .
 Trap Type: Cold Start
 Trap Sub-Type: 0
 Community/Infosec Context: TRAP2, SNMP v2c, community public
 Uptime: 0
 Description: Cold Start
 PDU Attribute/Value Pair Array:
DISMAN-EVENT-MIB::sysUpTimeInstance = Timeticks: (72176488) 8 days, 8:29:24.88
SNMPv2-MIB::snmpTrapOID.0 = OID: FromDual-fpmmm-MIB::fpmmmStart
FromDual-fpmmm-MIB::fpmmmLastrun = Counter32: 1628864995

Sending MariaDB SNMP traps from PHP

It looks like the PHP native SNMP functions do not provide anything for sending SNMP traps. But luckily there is the FreeSDx/SNMP PHP library by ChadSikorra on GitHub which can do the job.

After installing PHP composer installing of the FreeDSx/SNMP library was no problem: 

# php composer.phar require freedsx/snmp
Using version ^0.4.0 for freedsx/snmp
./composer.json has been updated
Running composer update freedsx/snmp
Loading composer repositories with package information
Updating dependencies
Lock file operations: 3 installs, 0 updates, 0 removals
  - Locking freedsx/asn1 (0.4.4)
  - Locking freedsx/snmp (0.4.0)
  - Locking freedsx/socket (0.3.1)
Writing lock file
Installing dependencies from lock file (including require-dev)
Package operations: 3 installs, 0 updates, 0 removals
  - Downloading freedsx/socket (0.3.1)
  - Downloading freedsx/asn1 (0.4.4)
  - Downloading freedsx/snmp (0.4.0)
  - Installing freedsx/socket (0.3.1): Extracting archive
  - Installing freedsx/asn1 (0.4.4): Extracting archive
  - Installing freedsx/snmp (0.4.0): Extracting archive
2 package suggestions were added by new dependencies, use `composer suggest` to see details.
Generating autoload files

This we need for adding SNMP support to our FromDual Performance Monitor for MariaDB and MySQL (fpmmm). A simple PHP SNMP trap example you can find as follows:

$aAutoload = require_once('vendor/autoload.php');

use FreeDSx\Snmp\SnmpClient;
use FreeDSxph\Snmp\Exception\SnmpRequestException;
use FreeDSx\Snmp\Oid;

$snmp = new SnmpClient([
  'host'      => '192.168.56.102'
, 'community' => 'public'
, 'version'   => 2
, 'port'      => 162
,
]);

try {

  $date = time();
  $trapOid = '1.3.6.1.4.1.57800.1.1.2';   // FromDual-fpmmm-MIB::fpmmmStart
  $Oid = '1.3.6.1.4.1.57800.1.1.1';       // FromDual-fpmmm-MIB::fpmmmLastrun

  # The parameters are:
  #   1. The system uptime (in seconds)
  #   2. The trap OID
  #   3. The OIDs and their values
  $snmp->sendTrap(60, $trapOid, Oid::fromCounter($Oid, $date));
} catch ( SnmpRequestException $e ) {
  printf('Unable to send trap: %s', $e->getMessage());
}

Literature


Taxonomy upgrade extras: 
SNMP
monitoring