Best approach to see if a MySQL Server is up and running

I have a Master - Slave setup for a web application written in PHP. I have a pool of slaves I use for reading, and a Master that is used for writes (and reads if a write has been sent this request). I would like to incorporate an automated system for removed crashed servers from the read pool. Currently I am using:

foreach($readers as $reader)
    {

        $fp = @fsockopen($reader['host'],3306,$errno,$errstr,1);
        if(!$fp)
        {
            //Remove from pool
        }
        unset($fp);
    }

My primary question is there a more reliable method. I have had quite a few false positives, and vice versa because it is not actually checking for a MySQL server, but rather just a connection on port 3306. Is there a way to check for a MySQL server without raising an exception, which is the behaviour开发者_Python百科 of the PDO and MySQLi extensions in PHP.

---

You could just use mysql_connect() and check the result for false, and close the connection right away on success. You can make a dummy account with no privileges for that if you like.

That's really the only reliable way, especially if you want to distinguish a running MySQL server from any other random process listening on port 3306.

---

You could use mysql_ping() to check if a current DB Connection you have open is still alive

Here is the example posted at http://www.php.net/manual/en/function.mysql-ping.php

<?php
set_time_limit(0);

$conn = mysql_connect('localhost', 'mysqluser', 'mypass');
$db   = mysql_select_db('mydb');

/* Assuming this query will take a long time */
$result = mysql_query($sql);
if (!$result) {
    echo 'Query #1 failed, exiting.';
    exit;
}

/* Make sure the connection is still alive, if not, try to reconnect */
if (!mysql_ping($conn)) {
    echo 'Lost connection, exiting after query #1';
    exit;
}
mysql_free_result($result);

/* So the connection is still alive, let's run another query */
$result2 = mysql_query($sql2);
?> 

---

The best way to check if any service is alive is to actually use it. So for MySQL try to connect and execute some fast query, for web server try to fetch some file, for PHP try to fetch some simple script...

For MySQL master/slave setup, one of the solutions is to actually check the state of replication. You can check how many transactions is the slave behind master and decide to stop using that slave when/while it has old data. (I don't do the replication myself, but I think you need to compare the variables Read_Master_Log_Pos and Relay_Log_Pos)