The mysql command can do quite a lot in batch mode. Here I’ll show how to graph the size of a MySQL table (the number of rows it contains) over time with MRTG. I’ll assume you have a correct MRTG and MySQL installation. To get the number of rows in a table we can use the COUNT function in a SELECT. To see the number of orders in an example Customer Relationship Management database:
SELECT COUNT(*) FROM order
Now let’s assume we have a safe MySQL user ‘bill’ with the password ‘ben’ that can read the order table from database ‘crm’ on localhost. In a Linux shell file we can write:
mysql -ubill -pben -e "SELECT COUNT(*) FROM order;" crm | tail -1
Now we can write a script to be used by mrtg. The output format is
* Line 1: 'In' count * Line 2: 'Out' count * Line 3: uptime string * Line 4: Title string
We only need the ‘Out’ value and the title string:
#!/bin/sh echo 0 mysql -ubill -pben -e "SELECT COUNT(*) FROM order;" crm | tail -1 echo 0 echo 'Table Size'
If we call this script table-size and put it in the same directory as the mrtg config files, then we can add an mrtg target like this:
Target[order]: `/etc/mrtg/table-size` Options[order]: nopercent,growright,nobanner,nolegend,noinfo,gauge, integer,noi,transparent Title[order]: CRM order queue PageTop[order]: <h3>Number of outstanding orders</h3> YLegend[order]: orders ShortLegend[order]: LegendI[order]: LegendO[order]: orders
By using the transparent option mrtg generates images that can be embedded in web pages with a background graphic. By replacing the first ‘echo 0' in table-size with another mysql statement, and removing the ‘noi’ option from the mrtg target, you can compare the sizes of two tables in one graph.