Monitoring MySQL with MRTG

    By Nathan Linley

To monitoring status information from mysql in MRTG, you can use the following files and configurations. In this method, a perl script is used to read whatever status information you want out of mysql, and puts it into a text file. An exec program specified in snmpd reads the values that are taken out. Status information is flushed from mysql on each run to remove the cumulative nature of some of the variables.

Perl script to get status variables

mysqlcheck.pl


#!/usr/bin/perl
#Written by Nathan Linley

use DBI;

open (OUTFILE,">/usr/local/etc/mysqlstat");

$dbh = DBI->connect("DBI:mysql:database=mysql","usernamet","password") || die "could not connect to database";

$query[0] = qq{ show status LIKE 'Com_insert' };
$query[1] = qq{ show status LIKE 'Com_select' };
$query[2] = qq{ show status LIKE 'Com_update' };
$query[3] = qq{ show status LIKE 'Com_delete' };
$query[4] = qq{ show status LIKE 'Threads_connected' };
$query[5] = qq{ show status LIKE 'Threads_running' };
$query[6] = qq{ show status LIKE 'Qcache_free_memory' };

foreach $q (@query) {
     runquery($q);
}

$query = qq{ flush status };
$sdh = $dbh->prepare($query);
$sdh->execute;
$sdh->finish;

$dbh->disconnect;


sub runquery {
     $query1 = $_[0];
     my $sdh = $dbh->prepare($query1) || die "Could not prepare query $query1";
     $sdh->execute;
     my $result = $sdh->fetchrow_hashref();
     print ($result->{'Value'} . "\n");
     print OUTFILE ($result->{'Value'} . "\n");
}

Perl script to read the data


mysqlfeeder.pl

#!/usr/bin/perl

open (MYSQL_TOTALS, "/usr/local/etc/mysqlstat");

while () {
     chop;
     $val = $_;
     $val += 0;
     print "$_\n";
}
close(MYSQL_TOTALS);

Add this line to crontab

*/5 * * * * /root/scripts/snmpd/mysqlcheck.pl


Add this line to snmpd.conf

exec 1.3.6.1.4.1.24993.42 mysqlfeeder.pl /root/scripts/snmpd/mysqlfeeder.pl


Here is a working sample of this added to an MRTG.cfg file

Target[mysql_threads]: 1.3.6.1.4.1.24993.42.101.5&1.3.6.1.4.1.24993.42.101.6:public@localhost
Title[mysql_threads]: Threads
PageTop[mysql_threads]: <H1>Connected vs Running Mysql Threads</H1>
MaxBytes[mysql_threads]: 10000
YLegend[mysql_threads]: Threads
LegendI[mysql_threads]: Connected
LegendO[mysql_threads]: Running
Legend1[mysql_threads]: Connected
Legend2[mysql_threads]: Running
ShortLegend[mysql_threads]: Threads
Options[mysql_threads]: growright,nopercent,gauge

Target[mysql_qcache]: 1.3.6.1.4.1.24993.42.101.7&1.3.6.1.4.1.24993.42.101.7:public@localhost
Title[mysql_qcache]: QCache Free
PageTop[mysql_qcache]: <H1>MYSQL QCache Free Memory</H1>
MaxBytes[mysql_qcache]: 10000
ShortLegend[mysql_qcache]: Bytes
YLegend[mysql_qcache]: Free mem
Legend1[mysql_qcache]: Free mem
LegendI[mysql_qcache]: Free mem
Options[mysql_qcache]: growright,nopercent,gauge
Target[mysql_insert]: 1.3.6.1.4.1.24993.42.101.1&1.3.6.1.4.1.24993.42.101.1:public@localhost
Title[mysql_insert]: Insert Queries
PageTop[mysql_insert]: <H1>Mysql Insert Queries</H1>
MaxBytes[mysql_insert]: 10000
ShortLegend[mysql_insert]: Inserts
YLegend[mysql_insert]: Total inserts
Legend1[mysql_insert]: Inserts
LegendI[mysql_insert]: Inserts
Options[mysql_insert]: growright,nopercent,gauge


Target[mysql_select]: 1.3.6.1.4.1.24993.42.101.2&1.3.6.1.4.1.24993.42.101.2:public@localhost
Title[mysql_select]: Select Queries
PageTop[mysql_select]: <H1>Mysql Select Queries</H1>
MaxBytes[mysql_select]: 10000
ShortLegend[mysql_select]: Selects
YLegend[mysql_select]: Total select
Legend1[mysql_select]: Selects
LegendI[mysql_select]: Selects
Options[mysql_select]: growright,nopercent,gauge


Target[mysql_update]: 1.3.6.1.4.1.24993.42.101.3&1.3.6.1.4.1.24993.42.101.3:public@localhost
Title[mysql_update]: Update Queries
PageTop[mysql_update]: <H1>Mysql Update Queries</H1>
MaxBytes[mysql_update]: 10000
ShortLegend[mysql_update]: Updates
YLegend[mysql_update]: Total update
Legend1[mysql_update]: Updates
LegendI[mysql_update]: Updates
Options[mysql_update]: growright,nopercent,gauge


Target[mysql_delete]: 1.3.6.1.4.1.24993.42.101.4&1.3.6.1.4.1.24993.42.101.4:public@localhost
Title[mysql_delete]: Mysql Delete Queries
PageTop[mysql_delete]: <H1>Mysql Delete Queries</H1>
MaxBytes[mysql_delete]: 10000
ShortLegend[mysql_delete]: Deletes
YLegend[mysql_delete]: Total delete
Legend1[mysql_delete]: Deletes
LegendI[mysql_delete]: Deletes
Options[mysql_delete]: growright,nopercent,gauge

Hosted by www.Geocities.ws

1