Templates for monitoring MySQL through SNMP

[spions]

Russian version of this manual is available at
http://shurshun.ru/cacti-monitoring-mysql/

This is a set of templates for monitoring MySQL servers through SNMP protocol with Cacti.

It is based on the knowledge and techniques of project designs:

1) MySQL Performance Graphs on Cacti via SNMP
https://www.pitt-pladdy.com/blog/_20120 ... _via_SNMP/
2) Percona MySQL Monitoring Template for Cacti
http://www.percona.com/software/percona ... ng-plugins
3) masterzen/mysql-snmp
https://github.com/masterzen/mysql-snmp
4) Cofyc/cacti-templates
https://github.com/Cofyc/cacti-templates

INSTALLATION
============

1) Download "cacti-mysql" project.
https://github.com/spions/cacti-mysql/a ... master.zip

2) Log into MySQL on monitoring server and execute the following (changing "secret" as appropriate)
to give the script permission to monitor:

Code: Select all

   [monitoring-server]# mysql -uroot -p
	CREATE USER 'monitor'@'localhost' IDENTIFIED BY 'secret';
	GRANT PROCESS ON *.* TO 'monitor'@'localhost';
	FLUSH PRIVILEGES;

   Mariadb can create a user with a blank password. It needs to be changed.
	SET PASSWORD FOR 'monitor'@'localhost' = PASSWORD('secret');

3) In order to read all these variables from MySQL we need credentials.
Change the login and password /etc/snmpd/mysql-stats.conf to access the MYSQL

Code: Select all

cd /etc/snmp/
wget https://raw.github.com/spions/cacti-mysql/master/mysql-stats.conf

4) Create cache dir, download mysql-stats and change snmpd.conf.

Code: Select all

cd /etc/snmp/
mkdir /etc/snmp/mysql_cache
chmod 777 /etc/snmp/mysql_cache
       
wget https://raw.github.com/spions/cacti-mysql/master/mysql-stats
chmod 755 mysql-stats

This one is simply an extension script for snmpd that grabs (and caches) the data when called.
Assuming you put this extension script in /etc/snmp and have a cache directory for my extensions
of /etc/snmp/mysql_cache/ (as described in previous articles), you can add the following to your
/etc/snmp/snmpd.conf file and then restart snmpd:

Code: Select all

extend mysqlqcache /etc/snmp/mysql-stats /etc/snmp/mysql_cache/cache _qcache_miss_ratio _qcache_hit_ratio qcache_hits qcache_inserts qcache_total_blocks qcache_free_blocks _qcache_used_blocks query_cache_size qcache_free_memory _qcache_used_memory qcache_queries_in_cache
extend mysqlbufferpool /etc/snmp/mysql-stats /etc/snmp/mysql_cache/cache _innodb_buffer_pool_miss_ratio _innodb_buffer_pool_hit_ratio innodb_buffer_pool_reads innodb_buffer_pool_read_requests innodb_buffer_pool_write_requests
extend mysqlbufferpoolstat /etc/snmp/mysql-stats /etc/snmp/mysql_cache/cache innodb_buffer_pool_pages_total innodb_buffer_pool_pages_data innodb_buffer_pool_pages_free innodb_buffer_pool_pages_dirty innodb_buffer_pool_pages_misc
extend mysqlinnodbrows /etc/snmp/mysql-stats /etc/snmp/mysql_cache/cache innodb_rows_read innodb_rows_deleted innodb_rows_updated innodb_rows_inserted
extend mysqlkeycache /etc/snmp/mysql-stats /etc/snmp/mysql_cache/cache _key_miss_ratio _key_hit_ratio key_reads key_read_requests
extend mysqlcomm /etc/snmp/mysql-stats /etc/snmp/mysql_cache/cache questions com_select com_delete com_insert com_update com_replace com_load com_delete_multi com_insert_select com_update_multi com_replace_select
extend mysqlsort /etc/snmp/mysql-stats /etc/snmp/mysql_cache/cache sort_rows sort_range sort_merge_passes sort_scan
extend mysqlselects /etc/snmp/mysql-stats /etc/snmp/mysql_cache/cache select_full_join select_full_range_join select_range select_range_check select_scan

5) Download and Import templates "cacti_host_template_mysql.xml" on Cacti monitoring host.

You have to import templates using Cacti web interface. In the Cacti web interface’s Console tab, click on the "Import Templates" link in the left sidebar. Browse to the directory containing the unpacked templates, select the XML file for the templates (cacti_host_template_mysql.xml) you’re installing, and submit the form.

Console->Import/Export->Import Templates

Or simply import templates from the command line:

Code: Select all

	wget https://raw.github.com/spions/cacti-mysql/master/cacti_host_template_mysql.xml

	php /dir_to_cacti/cacti/cli/import_template.php --filename=/dir_to_template/cacti_host_template_mysql.xml --with-user-rras='1:2:3:4'

		using RRA Daily (5 Minute Average)
		using RRA Weekly (30 Minute Average)
		using RRA Monthly (2 Hour Average)
		using RRA Yearly (1 Day Average)
		Read 234723 bytes of XML data
		** GPRINT Preset
		...

6) Creating Graphs.
In Cacti’s Console tab, browse to the "Devices" link in the sidebar and click on the device you’d like to graph.

The third item from the top of the screen should say Host Template. Change this to the name of the template you imported, such as "mysql". Scroll to the bottom of the page and click the Save button.

After the page loads, click on the "Create Graphs for this Host" link at the top of the page.

Download Template: https://github.com/spions/cacti-mysql/a ... master.zip

[olo]

I get "WARNING: Result from SNMP not valid. Partial Result:..." and it's like (seems like it's truncated):
- DBI connect(:localho
- U
- Cant call method pre.

I can see the graphs templates but nothing is being displayed (-nan). Graph Debug Mode status is RRDTool Says: OK.
How can I fix it?

[stevewalsh]

I've worked my way through this, but have hit a snag in generating the graphs. Whilst most work, there are some that don't update, and when Graph Debug is run, they report an error at RRD generate about a badly formed command.

--font UNIT:7: \
AREA:#FF0000FF:'Qcache Inserts' \
AREA:#00CF00FF:'Qcache Hits':STACK
RRDTool Says:

ERROR: parameter '#FF0000FF' does not represent a number in line AREA:#FF0000FF:Qcache Inserts

Comparing this to the ones that work, I can see what is missing;

AREA:a#00CF00FF:'Read Requests' \
AREA:b#FF0000FF:'Reads'

RRDTool Says:

OK

However, I'm at a loss at where I add the sequence character before the colour code for each part of the graphs. my graph templating is a bit weak, so a pointer would be appreciated

[Hyperlord]

Nothing is graphed, I seems impossible since that Percona stuff does the same.

Sad there's no response nor help