Monitoring Entries In A MySQL Table With MRTG

At work last week I set up some MRTG reports to monitor our MySQL servers. I used the handy mrtg-mysql-load utility to get queries/slow queries information but I also needed to monitor the number of records in a particular table and graph it using MRTG. For those unfamiliar with MRTG, graphing “custom” data is very simple. All you need to do is write a script that outputs 4 lines:

Line 1 -current state of the first variable, normally ‘incoming bytes count’
Line 2 -current state of the second variable, normally ‘outgoing bytes count’
Line 3 -string (in any human readable format), telling the uptime of the target.
Line 4 -string, telling the name of the target.

So I created a simple script to get the record count from a particular table:

#!/bin/sh
echo 0
mysql -h $1 -uwww -e "SELECT COUNT(*) from injector" nextstat_injector | tail -1
echo 0
echo "Queued Records"

Which I would call passing the MySQL database host as the first argument. Once I had the script I added a new MRTG target like this:

Target[injector]: `/etc/mrtg/injector-stat 192.168.1.121` + `/etc/mrtg/injector-stat 192.168.1.122` + `/etc/mrtg/injector-stat 192.168.1.123`
MaxBytes[injector]: 500000
Options[injector]: nopercent,growright,nobanner,nolegend,noinfo,gauge,integer,transparent,noi
Title[injector]: Injector Records Queued
PageTop[injector]:<h3>Injector Records</h3>
YLegend[injector]: records
ShortLegend[injector]:
LegendI[injector]:
LegendO[injector]: records

You’ll notice that I’m adding the totals from 3 different servers to graph a single sum value in MRTG. Any mathematical expression can be used to generate your graph values. From the manual:

You can also combine several target definitions in a mathematical expression. Any syntactically correct expression that the Perl interpreter can evaluate to will work. An expression could be used, for example, to aggregate both B channels in an ISDN connection or to calculate the percentage hard disk utilization of a server from the absolute used space and total capacity.

For a graph like this you’ll want to use similar options to the ones I used above. nopercent disables percentage display, growright tells the graph to read from left to right instead of the default right to left, gauge tell MRTG that the data points are “current status” measurements rather than ever-increasing counters, noi tells MRTG there is no “input” data to compare against “output” data and a few cosmetic display options. The resulting graph looks something like this:

injector-day.png

So there you go…a quick and easy way to monitor entries in a MySQL table using MRTG.

Don't be stingy...share with your friends!

2 thoughts on “Monitoring Entries In A MySQL Table With MRTG

  1. I see a lot of interesting content on your page. You have to spend a lot of time writing, i know how to save you a lot of work, there is a
    tool that creates unique, google friendly posts in couple of seconds, just search in google – k2 unlimited content

  2. I read a lot of interesting articles here. Probably you spend a lot of time writing, i know how to save you a
    lot of work, there is an online tool that creates unique,
    SEO friendly posts in seconds, just search in google – laranitas free content source

Leave a Reply

Your email address will not be published. Required fields are marked *