Monitor Mysql

````
';
$dbpass = '';

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die("Error connecting to mysql");

if ($conn) {
print "CONNECT OK";
}
?>
````

There, put it in a code block so it's easier to copy/paste :P

mysql_* is deprecated as of PHP 5.5 you should use PDO or MySQLi

http://uk3.php.net/manual/en/function.mysql-connect.php

@obs:

mysql_* is deprecated as of PHP 5.5 you should use PDO or MySQLi

PHP 5.5 hasn't even been released yet, so I don't think that's going to be an issue for a while :P That said, if you're concerned, you can simply change 'mysqlconnect' to 'mysqliconnect' in the example, and it'll work just fine with MySQLi.

What about if the webserver (nginx + php / apache + php) crashes, but mysql no, the monitor will trigger an false alert about mysql.

@diegocn:

What about if the webserver (nginx + php / apache + php) crashes, but mysql no, the monitor will trigger an false alert about mysql.

It's our responsibility to determine if that is indeed the cause.

-Tim

I came up with a “better” solution than running a PHP script from an unrelated web server. This solution also aides my specific implementation case: my ‘MySQL’ Linode only allows connecting to 3306 via specific IP/v6 addresses, which effectively limits even authorized access to my other Linodes on the LAN.

This solution is a simple Perl script, doing a simple check for a running MySQL process, and using inetd to make it available on the network. Ideally, Linode would publish what IP range(s) it does monitoring checks from, and I would limit access to just that, but this script really doesn’t do much, so I’m not all that worried about it.

Instructions:

1. Prepare the monitoring script

I put my script in /usr/local/bin, but you could conceivably put it anywhere. I named mine ‘mysql-heartbeat’.

#!/usr/bin/perl

select(STDOUT);

if (`pgrep mysql` eq '')
{
    print "DOWN";
}
else
{
    print "Linode Managed: heartbeat-mysql";
}

print "\n";

Once you’ve saved it, be sure to make it executable:

root> chmod +x mysql-heartbeat

2. Configure /etc/services

In order for inetd to know what port to run your service on, you’ll need to add its description to bottom of the /etc/services file:

# Local services
mysql-heartbeat 13306/tcp                       # MySQL aliveness daemon

I used port 13306, but you can use anything not already in use on your system.

3. Configure inetd

I’m using Debian Squeeze (6.0), which doesn’t seem to include inetd by default — it’s easy to install:

root> apt-get install inetutils-inetd

inetd won’t start yet, because by default, it includes no services. We’ll fix that now.

At the bottom of /etc/inetd.conf:

#:OTHER: Other services
mysql-heartbeat stream tcp nowait root /usr/local/bin/mysql-heartbeat mysql-heartbeat

Save inetd.conf, and (re)start inetd:

root> dpkg-reconfigure inetutils-inetd
Stopping internet superserver: inetd.
Starting internet superserver: inetd.

That’s it! You can test to see if the daemon is working correctly using telnet, from any machine:

abrahamvegh@zulu:/$ telnet mysql.internal 13306
Trying 192.168.[redacted]...
Connected to mysql.internal.
Escape character is '^]'.
Linode Managed: heartbeat-mysql
Connection closed by foreign host.
abrahamvegh@zulu:/$

The only thing left to do is configure a TCP monitoring check in the Managed control panel. I’ll leave that one as an exercise for the reader. ;)

Hi,

In my case, if apache go down, mysql don't will be important, so both are equally important for me.

This is my current script for monitoring multiple mysql databases over http;

getMessage();
  echo 'ERROR';
}
?>

I think the "better" solution in perl from abrahamvegh could be problematic. If you monitor only one (or two) process, this does not mean that the service works ok.

mysqld can be overloaded, and monitor can say "service ok", but it would be false "ok", but it is a good way to monitor if you can not use php ;-)

@fede:

I think the "better" solution in perl from abrahamvegh could be problematic. If you monitor only one (or two) process, this does not mean that the service works ok.

mysqld can be overloaded, and monitor can say "service ok", but it would be false "ok", but it is a good way to monitor if you can not use php ;-)

Good point. Here’s a much better version of my Perl script, which actually checks to see if the MySQL server is accessible:

#!/usr/bin/perl

my $monitor_host = 'localhost';
my $monitor_port = '3306';
my $monitor_user = 'username';
my $monitor_pass = 'sekret';

use DBI;

sub down
{
    my $reason = shift;

    print "DOWN: $reason\n";

    exit;
}

DBI->connect("dbi:mysql:mysql:$monitor_host:$monitor_port", $monitor_user, $monitor_pass) or down($DBI::errstr);

print "Linode Managed: heartbeat-mysql\n";

You may need to install DBD::mysql on Debian, which is as simple as

root> apt-get install libdbd-mysql-perl