Re: custom mysql monitoring

[Paul Monaghan]

Vincent Rivellino wrote:

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> I'm sorry if this has been covered before, but I haven't found anything
> regarding it in the monit docs or in the list archives.
>
> I'd like to setup a few monit checks to verify the function of our mysql
> database.  These checks will require connecting to the database and
> performing a few queries.
>
> Does monit support directing connecting to mysql and performing queries?
> If not, does it support executing an external application to perform
> actions and have the check fail based on the return code?
>  
Believe it or not, and I'm sure the monit developers aren't going to 
believe this, but YES IT DOES! Sort of. ;)

You can fake this ability by using monit to connect to a web server 
which queries the database.  For example on my website I have monit grab 
a nice big HTTP GET of a PHP script.  In case PHP crashes (FastCGI under 
Zeus) I'll get a "Bad Gateway" error from Zeus, if monit ever gets this 
HTTP error it will restart Zeus which in turn restarts PHP. There is no 
reason you couldn't build your SQL test queries in PHP and have monit 
use the HTTP test, if you can't connect to the database return a "500 
internal server error" or something.  Just change your stop and start 
program to be you init script for mysql.

check process mysql with pidfile /usr/local/zeus/web/internal/pid
       group web
       start program = "/etc/init.d/mysql start"
       stop program = "/etc/init.d/mysql stop"
       if failed URL http://www.somc.on.ca:80/mysqltest.php?query=1 
then restart
       if 5 restarts within 5 cycles then timeout
 
Where "query=1" would be a predefined query in your mysqltest.php 
script.  If you can CGI/PHP/Perl it and serve it from a web server monit 
can check it. :)

The only draw back to this would be the reliability of the web server in 
front of the database testing this but there is no reason why the above 
wouldn't work since monit's HTTP checks are far more complete than the 
MySQL checks.

Regards,

Paul