- 2 minutes read #developer

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.

This page and its contents are copyright © 2020, Ian Rogers. Theme derived from Prav