So many things come to mind over this. One, why not use a shell script to check the server, or ping it instead of using a socket connection. When you communicate on port 3306 you are trying to talk with the MySQL server, so if your response is not what it is expecting, you will get messages of being blocked fairly quickly as it will then it is a DNS attack and shut you out.
Second thought is, I am certain there are products you can buy that will do this for you, and if the client wants it badly enough, it would be well worth their while to have such a product purchased.
I am writing this for my own sites. I do not use shell scripts and this is how my paid uptime service checks for mysql database status too. If I know the right input to use in the fputs function and know what the “success messages”, I can get this to work.
To connect to a MySQL server from a remote location you must allow remote connections then set up host (ip address) a user can login from (or set the user as any host).
Yes, I have done that. If not, I would not have received the “=???” response. Previously, I received responses like host.domain.com is not allowed blah blah blah.
So then why not simply connect with PHP’s built-in functions? [fphp]mysql_connect[/fphp], if successful, tells you that your server is up; if the connection fails, [fphp]mysql_error[/fphp] will tell you why. [fphp]mysql_stat[/fphp] will give you additional information about your server (including uptime) if you want it.
I fail to see any situation where these functions would not tell you whether or not your server is running properly.
<?php
mysql_connect("localhost", "user", "pass")
or die("Failed to connect: ".mysql_error());
echo mysql_stat();
?>
That would work for sure but I am looking for a solution that assumes that you do not have the username and password to the mysql database. Uptime services do not ask for a username or password.
Ah, now I think I understand. You’re not checking your own servers, you’re checking the servers of people who sign up for your uptime monitoring service! Am I finally on the right page?
I’d simply use the error codes returned by the MySQL function mysql_errno after an intentionally unsuccessful mysql_connect call:
<?php
@mysql_connect("remotehost", "foo", "bar"); //try a connection
switch(mysql_errno())
{
case 0: //just in case we accidentally get a valid connection
mysql_close(); //we don't break here because we want to fall through to "server running"
case 1045: //server responded with access denied
echo "MySQL server running";
break;
case 2003: //no MySQL server responded
echo "MySQL server not running";
break;
case 2005: //oops! can't find the server
echo "Cannot find server - check host name";
break;
default: //sanity check in case I missed something above; these responses should probably be logged for later review
echo "Unknown response: ".mysql_errno().": ".mysql_error();
}
?>
May need to do some more testing, this is just what I came up with after about 5 minutes dinking around with it.
I think I will just use the database connection function to test the mysql server. I just wanna create my own uptime script to check on my sites. I don’t wanna pay my uptime service $25 bucks per month any more.