monitoring MySQL programmatically? SNMP?

Question

I've been asked to see if we can monitor the "health" of mysql server in some way. The proposal was that there might be snmp support (this is for version 5.0.x). I was not able to find definitive information on it - A link to a reference would be great.

The real issue is to get periodic information on the "health" or status of mysql. We could write our own process that sites and actively talks to it, then publish that to our GUI (the GUI does not talk to mysql right now).

Is there a simple mechanism that can be used, or other best practice when one has no interest in the content of mysql, but just want to know what its status is? (up, down, available, other, etc)

The gui app is java, the rest of our stuff is a mix of java and c++

I'm not looking for a third party solution - just want to know if mysql "publishes" snmp data for others to consume.

Answer 1

This might help: MySQL Activity Report

Didn't see that you wanted it posted to your GUI app. Though, if your app can display images, you could just load the images that MySQL AR outputs...

Answer 2

I think there is a SNMP agent available in MySQL Enterprise Monitor, but that is of course for the commercial version. There are also several other commercial snmp agents available.

There is a C API for MYSQL that can be used for many of the monitoring tasks that you may be interested in: mysql_ping, mysql_sqlstate, etc. Since you were mentioning that you were using C++ that may be an option.

Take a look at MySQL C API for complete documentation.

That said, there is also some status information available that you can fetch with SQL statements: 'SHOW PROCESSLIST', 'SHOW SLAVE STATUS', etc.

Documentation about this you can find at SHOW Syntax