Masterzen's Blog

Journey in a software world…

Mysql-snmp - Monitor 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: InnoDB transactions InnoDB Buffer Pool

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.