SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Addict raydenx's Avatar
    Join Date
    Jun 2003
    Location
    Singapore
    Posts
    208
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    PHP Script To Check MySQL Status

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

    PHP Code:
    $socket_connection = @fsockopen("whatever.com"3306$error_number$error35);

    fputs($socket_connection'');

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

    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. #2
    Worship the Krome kromey's Avatar
    Join Date
    Sep 2006
    Location
    Fairbanks, AK
    Posts
    1,621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is there a particular reason why you can't use mysql_stat to get your MySQL server's status?
    PHP questions? RTFM
    MySQL questions? RTFM

  3. #3
    SitePoint Addict raydenx's Avatar
    Join Date
    Jun 2003
    Location
    Singapore
    Posts
    208
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am checking an external site. This is like an uptime checking script.

  4. #4
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,162
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    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. #5
    SitePoint Addict raydenx's Avatar
    Join Date
    Jun 2003
    Location
    Singapore
    Posts
    208
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #6
    . shoooo... silver trophy logic_earth's Avatar
    Join Date
    Oct 2005
    Location
    CA
    Posts
    9,013
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)
    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.ph...=5.0&doc=3&m=o
    Logic without the fatal effects.
    All code snippets are licensed under WTFPL.


  7. #7
    SitePoint Addict raydenx's Avatar
    Join Date
    Jun 2003
    Location
    Singapore
    Posts
    208
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by logic_earth View Post
    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.ph...=5.0&doc=3&m=o
    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. #8
    Worship the Krome kromey's Avatar
    Join Date
    Sep 2006
    Location
    Fairbanks, AK
    Posts
    1,621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So then why not simply connect with PHP's built-in functions? mysql_connect, if successful, tells you that your server is up; if the connection fails, mysql_error will tell you why. mysql_stat 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 Code:
    <?php
        mysql_connect
    ("localhost""user""pass")
            or die(
    "Failed to connect: ".mysql_error());
        echo 
    mysql_stat();
    ?>
    PHP questions? RTFM
    MySQL questions? RTFM

  9. #9
    SitePoint Addict raydenx's Avatar
    Join Date
    Jun 2003
    Location
    Singapore
    Posts
    208
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #10
    Worship the Krome kromey's Avatar
    Join Date
    Sep 2006
    Location
    Fairbanks, AK
    Posts
    1,621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:
    Code PHP:
    <?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.
    PHP questions? RTFM
    MySQL questions? RTFM

  11. #11
    SitePoint Member
    Join Date
    Jun 2007
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    kromey++

    that's a nice take.

  12. #12
    SitePoint Addict raydenx's Avatar
    Join Date
    Jun 2003
    Location
    Singapore
    Posts
    208
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •