Entry

How can I setup a monitoring service to check that MySQL is always running?

Feb 8th, 2000 06:36
Nathan Wallace, Tim Bunce

mysqld runs on port 3306 by default ( you can change it), and also
listens on a Unix socket /tmp/mysqld.sock ( you can change that too).

Monitoring mysqld is very easy. You can write a program in C or Perl
that runs from a cron or as a daemon, periodically connects to MySQL and
runs a simple query. If one of the steps fails, it e-mails you or your
pager -- about 10 lines in Perl ( Tim Bunce will do it in one :-) ), and
about 25 in C.

From Tim Bunce - I'd probably head in this direction (untested):

 perl -MDBI -e "DBI->connect(..., ..., ...,{RaiseError=>1})" \
        || ( echo "mysql down" | mail sms )

Just set up a cron job and use 'mysqladmin ping' to check the server
every few minutes or so.  I have this setup now for every 5 minutes and
it emails my pager if it goes down.  It's worked perfectly fine for over
a year now.  If you want to go a little deeper than just a ping try
doing something like mysql dbname -e"show tables".  That will tell you
if mysqld is alive and your db appears to be accessible.

So far I've been beeped once in over a year.  That was about 5 minutes
after I manually shutdown mysqld and forgot to restart it!!

My paging service allows me to send emails to my pager.  I simply send
it to xxxxxx@mobilecomm.com and a minute later my hip makes a noise.  It
works well as long as you keep the length of the email short!

If your pager doesn't support email service and your machine has a modem
you can set it up to dial your pager and send a brief numeric message. 
I've only done that on Windows so I won't offer a how-to suggestion for
other platforms.

You can take your checking as deep as you want.  When I originally set
up my monitoring system I had a php script that did all kinds of
extensive querying and consistency checking on the db.  It took care of
handling the email to my pager if needed.  My requirements are a bit
simpler now and I've found that mysqladmin ping is good enough for me.