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:


      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,
      Title[order]: CRM order queue
      PageTop[order]: <h3>Number of outstanding orders</h3>
      YLegend[order]: orders
      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.