I made a php script with some help from dwfreed that will try to connect to mysql and report back. You can configure a monitor to check this over http. Change <database user here> to your database user and <database password here> to your database password. I would suggest you use a test database and not a production one and make sure this file is secure as the password is in plaintext. If someone wants to edit/update this, please feel free.

<?php
$dbhost = 'localhost';
$dbuser = '<database user here>';
$dbpass = '<database password here>';

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

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

_________________
If you haven't heard this:

echo '[q]sa[ln0=aln256%Pln256/snlbx]sb3135071790101768542287578439snlbxq'|dc

You aren't using your computer enough.

<?php
$dbhost = 'localhost';
$dbuser = '<database user here>';
$dbpass = '<database password here>';

$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

_________________
Disclaimer: I am no longer employed by Linode; opinions are my own alone.

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

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

_________________
Paid support
How to ask for help
1. Give details of your problem
2. Post any errors
3. Post relevant logs.
4. Don't hide details i.e. your domain, it just makes things harder
5. Be polite or you'll be eaten by a grue

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

_________________
Disclaimer: I am no longer employed by Linode; opinions are my own alone.

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

_________________
'If debugging is the process of removing bugs, then programming must be the process of putting them in.' //Edsger Dijkstra
'Nothing is withheld from us which we have conceived to do.' | 'Do things that have never been done.' //Russell Kirsch

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;

<?php
$host="localhost";
$user="monitoruser";
$pass="monitorpassword";
$dbs=array(db1,db2,db3,db4);
try {
  foreach ($dbs as $db) {
        $dbconn = new PDO('mysql:dbname='.$db.';host='.$host, $user, $pass);
  }
  echo 'OK';
} catch (PDOException $ex) {
  //echo 'Connection failed: ' . $ex->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

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