MySNMP
One of the easiest way I have found to monitor MySQL is cacti. There is simple templates that draw most important mysql statistics. You can use teMySQLcacti or the improved mysql-cacti-templates.
But the only way to fetch data is via mysql connection. All of theses scripts are using a php script and a local connection.
So, I’ve writed a sample C program that connect to mysql and work with net-snmp in order to get all mysql stats within snmp protocol. No more need to have mysql access from cacti server. Cacti is also optimized to retrieve snmp data. If you have many host, snmp is for you. With spine (C cacti poller), you can retrieved thousand of snmp data per seconds.
C code:
Here is the source code: mysnmp-1.0rc.tar.gz
Compilation tested on freebsd, netbsd and linux. You need mysql developpement librairies.
You need to add in your snmpd.conf (only tested with net-snmp):
pass .1.3.6.1.4.1.2021.69775 /usr/local/sbin/mysnmp -u root -p xxxx <other flags>
Cacti code:
Here is some cacti templates, with the OID used by this program: cacti-templates
WARNING: I’m working on these templates and I didn’t test this export. I will change it later because there is more graph to add.
Technical explanation:
I use the OID: .1.3.6.1.4.1.2021.69775 for all mysql statistic. Data set are result of “show global status”, and some data computed from “show innodb status”. There is also a sub-branch within OID: .1.3.6.1.4.1.2021.69775.75283 in order to retrieve slave status.
The user need access to “replication client” if you want slave stat and “super” if you need innodb stat.
The list of the snmp OID tree. (All data available through snmp with this program)
All data are cached in a file, in order to avoid multiple connect to mysql for multiple retrieval (program is ran one time per snmp get). You can define how many time data are in cache. The slave tree is not cached. There is two params: -c for the path of the cache file. And -t for the time (in seconds) data will be in cache. If the cache file is locked, mysnmp will retry multiple times (default 3, change this with -r argument) and will wait 100 milliseconds between each try (change this with -w argument).
To Do:
- There is some work to do in order to manage error.
- The -f arguments is not implemented.
- Need to make a configure script
- Need to test cacti templates
- Need to manage snmp type (gauge, integer…)
[ 69775 and 75283 are 'mysql' and 'slave' on a T9 (phone) keyboard ]