Originally written around 2002

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.