a tool to monitor mysql

[Doug E]

I have frequent mysql crashes and need a tool to monitor for a pattern. Something that checks to see if mysql is running every 10 minutes or so. And im cheap so it would have to be a free one.

[cd34]

#!/usr/bin/perl

use DBI;
use DBI::DBD;

my $dbh = DBI->connect("DBI:mysql:database", "username", "password");

if (!$dbh) {
  my $SENDMAIL = '/usr/sbin/sendmail -t';
  my $EMAILADDRESS = 'your@email.com';
  open (SENDMAIL,"| $SENDMAIL");
  print SENDMAIL "To: $EMAILADDRESS\n";
  print SENDMAIL "From: $EMAILADDRESS\n";
  print SENDMAIL "Subject: mysql failed to connect\n\n";
  close SENDMAIL;
}

You could put that in cron to run every 10 minutes.

The question really is, what is the failure? can you connect, but queries cannot be executed? can you connect at all? is the machine overloaded and queries are slow? is it really down or just extremely slow? Are you running out of connections?

If mysql is segfaulting and terminating, this script will alert. If it is just slow, it'll never register a failure because it is able to connect. You can add commands in there to do other things like actually run a query against a table to see if it is getting a response.

Best place to check if mysql is really stopping is the error log. check /etc/my.cnf or /etc/mysql/my.cnf to see where it is logging the errors or have your web hosting company take a look at it. Mysql usually doesn't just die without some indication of the problem, and we've got mysql servers that have run for months on end under a heavy beating and I can't remember the last time we had mysql terminate except in the case of a disk failure.