Graphing MySQL performance statistics with MRTG

How to...

• Create a user allowed to retrieve statistics from the MySQL server

  GRANT USAGE ON * TO status@localhost IDENTIFIED BY 'showstatus';
  FLUSH PRIVILEGES;
  

• Create a script /usr/local/bin/mrtg_read_2_mysql_vars which retrieves the variables from MySQL

  #! /bin/sh
  
  export PATH=/usr/bin:$PATH
  
  TMP=/tmp/read_2_mysql_vars.$$.TMP
  
  if [ -z "$3" ] ; then
          HOST=localhost
  else
          HOST=$3
  fi
  
  echo 'SHOW GLOBAL STATUS;' | mysql -u status --password=showstatus -h $HOST > $TMP
  
  VAR1=`grep ^$1 $TMP | awk '{ print $2; }'`
  VAR2=`grep ^$2 $TMP | awk '{ print $2; }'`
  UP=`grep "^Uptime" $TMP | head -n 1 | awk '{ print $2; }'`
  
  if [ $UP -gt 86400 ] ; then
  	DAYS=`echo "scale=3; $UP / 86400" | bc`
          UPTIME="$DAYS days"
  else
          UPTIME="$UP seconds"
  fi
  
  rm -f $TMP
  
  echo $VAR1
  echo $VAR2
  echo $UPTIME
  hostname
  

• Alter mrtg.cfg (Debian: /etc/mrtg.cfg) to graph the requested variables

  Target[mysqltraffic]: `/usr/local/bin/mrtg_read_2_mysql_vars Bytes_received Bytes_sent`
  MaxBytes[mysqltraffic]: 100000000
  Options[mysqltraffic]: growright
  Title[mysqltraffic]: Traffic
  YLegend[mysqltraffic]: bytes
  LegendI[mysqltraffic]:  received
  LegendO[mysqltraffic]:  sent
  PageTop[mysqltraffic]: <H1>Traffic</H1>
  ShortLegend[mysqltraffic]: bytes
  
  Target[mysqlconnections]: `/usr/local/bin/mrtg_read_2_mysql_vars Connections Connections`
  MaxBytes[mysqlconnections]: 1000
  Options[mysqlconnections]: growright
  Title[mysqlconnections]: Number of connections
  YLegend[mysqlconnections]: n
  LegendI[mysqlconnections]:  n
  LegendO[mysqlconnections]:  n
  PageTop[mysqlconnections]: <H1>Number of connections</H1>
  ShortLegend[mysqlconnections]: n
  
  Target[mysqlbufferpool]: `/usr/local/bin/mrtg_read_2_mysql_vars Innodb_buffer_pool_read_requests Innodb_buffer_pool_write_requests`
  MaxBytes[mysqlbufferpool]: 1000
  Options[mysqlbufferpool]: growright
  Title[mysqlbufferpool]: Innodb buffer pool
  YLegend[mysqlbufferpool]: requests
  LegendI[mysqlbufferpool]:  read requests
  LegendO[mysqlbufferpool]:  write requests
  PageTop[mysqlbufferpool]: <H1>Innodb buffer pool</H1>
  ShortLegend[mysqlbufferpool]: n
  
  Target[mysqldatarw]: `/usr/local/bin/mrtg_read_2_mysql_vars Innodb_data_reads Innodb_data_writes`
  MaxBytes[mysqldatarw]: 1000
  Options[mysqldatarw]: growright
  Title[mysqldatarw]: Number of data reads/writes
  YLegend[mysqldatarw]: n
  LegendI[mysqldatarw]:  reads
  LegendO[mysqldatarw]:  writes
  PageTop[mysqldatarw]: <H1>Number of data reads/writes</H1>
  ShortLegend[mysqldatarw]: n
  
  Target[mysqlkeyrw]: `/usr/local/bin/mrtg_read_2_mysql_vars Key_reads Key_writes`
  MaxBytes[mysqlkeyrw]: 1000
  Options[mysqlkeyrw]: growright
  Title[mysqlkeyrw]: Number of key reads/writes
  YLegend[mysqlkeyrw]: n
  LegendI[mysqlkeyrw]:  reads
  LegendO[mysqlkeyrw]:  writes
  PageTop[mysqlkeyrw]: <H1>Number of key reads/writes</H1>
  ShortLegend[mysqlkeyrw]: n