Monitoring MySQL table sizes with MRTG

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.