Forum Discussion

andrew_waldvoge's avatar
andrew_waldvoge
Icon for Nimbostratus rankNimbostratus
Jan 23, 2007

mysql monitoring

I'm looking for a way to configure monitoring for a mysql database.

 

Currently we have the following rule in place which monitors the database indirectly through the URL, but whenever apache or tomcat are off-line the monitor fails and the pool member is down.

 

 

monitor ADMIN_RCOM {

 

defaults from http

 

recv "HTTP/1.1 200 OK"

 

send "GET /rcom-us/do/keepAlive HTTP/1.1

 

Host: 216.35.67.138

 

Connection: close

 

 

."

 

 

Is there a way to monitor mysql directly, perhaps sending a simple sql select query?

 

 

Any help/advice would be greatly appreciated, thanks.

5 Replies

  • Richard__Harlan's avatar
    Richard__Harlan
    Historic F5 Account
    I guess I am not getting what you want to do. Is the MySQL server in a pool behind the LTM? If so you could use a perl script on another computer to probe the MySQL database then up iControl to mark the server up or down.

     

     

    I guess my big question is why? If you are using Apache and TomCat to get to the database server then if they are down then the access point to the database is down so for the most part the database is down if you cannot access it.

     

     

    Now like I said before if you have the MySQL box behind the BigIP and other things are hitting it besides the web application. Then you need to monitor the MySql box and not through the site. To do this on the pool you can use tcp monitor this will make sure the server is listening on the MySQL port. Then you can user the perl/iControl script to more in depth monitoring of the database, using SQL querys. Note what ever you monitor using the perl script must not change or you risk the iContol script taking down the pool when it should not. As to how you write the iContol script there are a lot of posts that talk about how to mark down a member with a iControl script. Hope this help you.
  • Hunter32 writes: "I guess I am not getting what you want to do. Is the MySQL server in a pool behind the LTM? If so you could use a perl script on another computer to probe the MySQL database then up iControl to mark the server up or down."

     

     

    Hunter it is obvious why someone would want to do this as I am looking for the same solution. The reason is that I have tomcat running with DB backend all behind the LB and currently since I am monitoring tomcat only with the LB if the DB goes down the the LB thinks everything is still fine and does not redirect to our maintenance page but just times-out with an error message.

     

     

    The correct check should not only test tomcat but test al the way through to the DB.

     

     

    I have attempted to try the following http monitor:

     

    SEND STRING: GET saturn/gateway/webin/https?msisdn=4089049051&shortCode=73163&message=lee32&operatorId=31002&ret_override=Simplewire&username=lee&password=esposo

     

    RECEIVE STRING: 200

     

     

    However this fails. What I cannot figure out is what the recieve string should be as there is no text/html response it only gives an http 200 OK response which this http monitor does not seem to recognize which is strange seeing as it is an HTTP monitor.

     

    I've tried it with no response string, 200 response, 200 OK, OK, port alias, no port alias and nothing works. I've triple verified the get string is correct and works fine with jmeter and a web browser.

     

     

    PLEASE HELP

     

     

    Thanks,

     

    --Neil Ridlinghafer

     

    iLoop Mobile, Inc.

     

    www.iloopmobile.com
  • the issue hunter32 was getting at is that if you are using tomcat or something then you're clearly *not* monitoring a database, you're monitoring an http server... his monitor, as yours is, is clearly http based. if your send string just so happens to call something on the server which triggers a database connection on the back end of tomcat, and that's great, but that's not monitoring the database as far as the F5 box is concerned. the recieve string should contain the entire tcp payload, so you should have the full response to play with. if you don't know what it looks like, try using curl to see the entire payload yourself. ideally you would craft a page which returns a string on the successful completion, then it's much clearer all round.

     

     

     

    [btw when are people going to realise that putting "i" infront of anything doesn't make it look good, just a copy...?]
  • If you're looking for a binary state (ie, up or down), then your best bet would be an external monitor. Write a shell or perl script to log into the database, do whatever checking you need to do, and if all is good, return a 1, otherwise, return a 0. In fact, there are plenty of monitor script out there that you could customize to meet your needs, the url to one is below:

     

     

    http://blog.arabx.com.au/?p=695 Click here

     

     

    Make sure to note your script execution time so that it doesn't exceed your polling interval.
  • Where's the edit button on my posts?

     

     

    I forgot to add that there is a Wiki entry for external monitors:

     

     

    http://devcentral.f5.com/wiki/default.aspx/AdvDesignConfig/ExternalMonitor.html Click here