PHP Script To Check MySQL Status

PHP
1

I am trying to create a script to check the status of a mysql server on one of my sites by going through the port 3306…

Here’s the code I have so far…


$socket_connection = @fsockopen("whatever.com", 3306, $error_number, $error, 35);

fputs($socket_connection, '');

#RETRIEVE CONNECTION RESPONSE
if(!feof($socket_connection))
{
    $connection_response = fgets($socket_connection, 4096);
}

fclose($socket_connection);

echo $connection_response;

My connections are fine after adding my source IP to the mysql’s “whitelist”.

I keep getting errors like:

s���ÿiHost ‘host.blahblahblah.com’ is blocked because of many connection errors; unblock with ‘mysqladmin flush-hosts’

=���

Do you guys know what is a successful response from a mysql connection?

2

Is there a particular reason why you can’t use [fphp]mysql_stat[/fphp] to get your MySQL server’s status?

3

I am checking an external site. This is like an uptime checking script.

4

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.

Matt

5

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.

6

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).

http://www.mysql.com/search/index.php?q=remote+connections&base=http%3A%2F%2Fdev.mysql.com&lang=en&version=5.0&doc=3&m=o

7

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.

8

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();
?>
9

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.

10

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.

11

kromey++

that’s a nice take. :slight_smile:

12

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.