mysql-snmp – monitoring MySQL with SNMP

What’s that?

mysql-snmp is a Net-SNMP Perl subagent that connects to a MySQL server and reports its statistics through SNMP. Additionally this project contains material to graph MySQL variables/counters in OpenNMS.

The graphs are based on the excellent MySQL Cacti Templates by Baron Schwartz.

What’s this stuff is doing?

This is a small perl daemon that:

• connects locally to Net-SNMP snmpd to report statistics
• connects remotely or locally to a MySQL server to fetch statistics

The daemon ships with a MIB file I crafted, and OpenNMS configuration snippets to start monitor your MySQL servers without hassles.

Here are two OpenNMS graphs:

You sold me, where do I get it?

This software is only available in my MySQL-SNMP github repository.

To install the software on a server:

1. clone the repository:

$ git clone git://github.com/masterzen/mysql-snmp.git

2. install

If you run a debian server, build the debian package (the easiest way):

$ cd mysql-snmp
$ dpkg-buildpackage -rfakeroot -uc -b

If you don’t, modify the Makefile and install manually

$ cd mysql-snmp
$ sudo make install

You’ll have to craft your own startup init.d script, or grab the one I did for the debian package (have a look into the debian/ sub-directory).

3. Configure

First configure the local net-snmp by allowing AgentX:

add to /etc/snmp/snmpd.conf

master agentx

and restart snmpd.

Configuration of the mysql-agent.pl daemon is done with command line options. You’ll have to tell it how to connect to your MySQL Server with the -u, -p and -h options.

Then start the daemon, monitor your /var/log/syslog file to see if it works

Then try it:

$ snmpwalk -m MYSQL-SERVER-MIB -v 2c -c public localhost enterprises.20267
MYSQL-SERVER-MIB::myKeyReadRequests.0 = Counter32: 514350
MYSQL-SERVER-MIB::myKeyReads.0 = Counter32: 1379
MYSQL-SERVER-MIB::myKeyWriteRequests.0 = Counter32: 75717
MYSQL-SERVER-MIB::myKeyWrites.0 = Counter32: 300
MYSQL-SERVER-MIB::myHistoryList.0 = Gauge32: 147
MYSQL-SERVER-MIB::myInnodbTransactions.0 = Counter64: 4188320597
MYSQL-SERVER-MIB::myReadViews.0 = Gauge32: 2
MYSQL-SERVER-MIB::myCurrentTransactions.0 = Gauge32: 9
MYSQL-SERVER-MIB::myLockedTransactions.0 = Gauge32: 0
MYSQL-SERVER-MIB::myActiveTransactions.0 = Gauge32: 2

If you see an ouput as the one above, then it worked fine.

4. OpenNMS

To start monitoring your MySQL servers with OpenNMS, you have to:

• augment datacollection-config.xml with the mysql-server mib information
• declare the various graphs

Have a look to the opennms/ directory in the source archive, then paste the <group> content of mysql.datacollection-config.xml to the datacollection-config.xml file.

Then add the mysql group to the hostdef part you’re using.

Then copy the various reports from mysql.snmp-graph.properties to your OpenNMS snmp-graph.properties and then add the report list to the global report list of the same file.

Then restart your computer.