mySQL monitoring HowTo Zabbix 2.0

[thesubmitter]

This is a howto for mysql monitoring with zabbix 2.0




I've attached a zip file that has:
-php script that goes on the mysql server you want to monitor (it is the latest version ATM - and you can always grab it from the percona mysql monitoring plugin toolkit in the "scripts" folder")

-zabbix user parameters file for use on the agent to grab all the values from the mysql server via the script above


I've also attached an XML file to import to the zabbix server that will create the template. I generated it as per the instructions on the link I provided at the beginning but I needed to add <delay_flex></delay_flex> to each item for zabbix succesfully import the template. I also erased all the triggers due to import problems.

________________________________

Instructions

On the Server
-Import the XML File template

On the Agent
you can refer to the instructions from the link above:

Agent side server:

Make sure PHP5 is installed.
Make sure MySQL plugin for PHP is installed.
Make sure php-cli is installed (command line PHP).


Create a sub-dir (for example): /usr/local/zabbix/plugins/
Copy the ss_get_mysql_stats.php to this dir.
Copy the zabbixmysql.conf file to /etc/zabbix/.

Edit the /etc/zabbix/zabbix_agent.conf file.
Add: Include=/etc/zabbix/zabbixmysql.conf
(you might need to enable the "unsafe operators" also)

Create a MySQL database user:
GRANT PROCESS ON *.* TO 'zabbix'@'localhost' identified BY 'some_password';

Edit the ss_get_mysql_stats.php file, change the:
$mysql_user = 'zabbix';
$mysql_pass = 'some_password';

Do a Find and Replace in zabbixmysql.conf
REPLACE: /usr/local/share/zabbix/plugins/ss_get_mysql_stats.php
WITH: path to the location of your ss_get_mysql_stats.php script

Restart the agent.

To Test
You can do things like
(on agent)
zabbix_agentd -t mysql.Sort_scan

(on server)
zabbix_get -s agentip -k mysql.Sort_scan

To ensure you get output.

Add the template to a host. I actually cloned the linux template (removed the mysql process counter and LINKED the mysql template to it) .

I hope this helps.

[thesubmitter]

Reference

A lot of my post was based on this blog posting by Roni Alboim unfortunately the Zabbix forums aren't letting me put the link

However the below is a mangled version (missing the .com)
buzznt.blogspot. /2011/09/install-zabbix-mysql-plugin-imported.html

_______________________

Install Zabbix MySQL plugin (imported from Cacti)
While the MySQL plugin for Zabbix is limited the Cacti plugin is very detailed and maintenance by Percona.

Thanks to PalominiDB the Cacti plugin is ported to Zabbix.

The installation process is very simple, the most complicated part is creating the XML for the template from the Zabbix plugin download , all U have to do is open the file and:

cd appaloosa-zabbix-templates
perl tools/gen_template.pl defs/mysql.pl mysql.xml


Which will create the mysql.xml file needed for the Zabbix Server to install the MySQL template.

Next, create the config file for the agent by editing (using sed command) the conf/mysql_agentd.conf file and creating mysql.conf file ( the /usr/local/zabbix/plugins is a path U will have to create on the agent server - see below) :

sed -e 's|$ZABBIX_AGENT_PATH|/usr/local/zabbix/plugins|' conf/mysql_agentd.conf > mysql.conf


Now U need to get the PHP file from the Cacti template, download the latest version. Open the file, the file U need is: ss_get_mysql_stats.php


Agent side server:

Make sure PHP5 is installed.
Make sure MySQL plugin for PHP is installed.
Make sure php-cli is installed (command line PHP).
U need an installation dir like: /usr/local/zabbix/
Create a sub-dir: /usr/local/zabbix/plugins/
Copy the ss_get_mysql_stats.php to this dir.
Create a sub-dir: /usr/local/zabbix/agent.d/
Copy the mysql.conf file to this dir.
Edit the /etc/zabbix/zabbix_agentd.conf file.
Add: Include=/usr/local/zabbix/agent.d/
Create a MySQL database user:

GRANT SELECT, SUPER, PROCESS ON *.* TO 'zabbix'@'localhost' identified BY 'some_password';

Edit the ss_get_mysql_stats.php file, change the:
$mysql_user = 'zabbix'; $mysql_pass = 'some_password';
Restart the agent.

import mysql.xml into Zabbix, and associate Template_MySQL with each database server.

Possible problem:
If your server use the Zabbix Linux template it include the check for "mysqld" process so U can't add the Tamplate_Mysql to this server, workaround: create a new server for this tamplate.

[richlv]

maybe you would like to write this as a howto on zabbix.org ? that will allow to add some nice screenshots as well

(btw, you should be able to link to blogpost pages now)

[sithiuz]

Hi,

I'm trying to get this to work, but there seems to be some problems with the returned values. All the values are returned as text, containing some PHP info text. So Zabbix gives error on the items.

Returned values looks like this:

PHP/5.3.10
text/html

64476842

Do you have any suggestion on how to easily modify the script so that the values does not contain the PHP text?

Thank you in advance.

[thesubmitter]

expose_php = Off in your php.ini files maybe?

[Felicitus]

I just successfully added this on Zabbix 1.8.2, so this is not 2.0.0 exclusive. Good Work!

I'm wondering if the "SUPER" privilege is really necessary. I have skipped that, and it still seems to work (haven't gone through each item monitored).

Great work!

[thesubmitter]

Yeah, I actually also removed the SELECT and just left process....

[caraconan]

grant REPLICATION CLIENT

Hi.

First of all thanks for the post, very useful.

2nd 'grant REPLICATION CLIENT' is needed in order to have below zabbix items working:

mysql.slave_running
mysql.slave_stopped
mysql.Slave_retried_transactions
mysql.slave_lag
mysql.Slave_open_temp_tables

Regards

[nutella]

question

where are the triggers?

[thesubmitter]

there are no triggers

[Joby]

mysql query monitoring

Hi All,

May I know whether there is a way to monitor the number of occurrence of a particular query in mysql with the help of userparameter option available in the zabbix agent. Since I urgently needs to collect the count of a particular query from our mysql server.

[kaba]

hi


where test this command on the root dans the prompt of mysql because when a excute in the root i have an error


To Test
You can do things like
(on agent)
zabbix_agentd -t mysql.Sort_scan

(on server)
zabbix_get -s agentip -k mysql.Sort_scan

To ensure you get output.

[kaba]

hello
I wonder if it is possible to administer the mysql database from a remote machine with zabbix server without being installed zabbix agent?

[token]

@thesubmitter Your MySQL monitor is awesome!

I have made some adjustments to the template, notably there are many places where the "Store value:" is set to "Delta (simple change)" which causes the item/graph values to display the change in the value over the update interval (usually 2 minutes).

If "Delta (per second)" is used then the item/graph values are the change in the value per second.

Some items like mysql.Innodb_row_lock_waits are set to "as is" but they are counting cumulative values and need "Delta (per second)" or they will display as ever-increasing numbers.

This test:

      elseif(strpos($line, 'Adaptive hash index ') === 0 ) {
         #   Adaptive hash index 1538240664     (186998824 + 1351241840)
         $results['adaptive_hash_memory'] = to_int($row[3]);
      }

does not appear to ever match 'Adaptive hash index ' in MySQL 5.5, 5.6 or 5.7. Is it specific to the Percona Server?

[token]

         # MySQL 5.5 replaces the 'Locked' state with a variety of "Waiting for
         # X lock" types of statuses.  Wrap these all back into "Locked" because
         # we don't really care about the type of locking it is.
         $state = preg_replace('/^(Table lock|Waiting for .*lock)$/', 'Locked', $state);

But MySQL 5.1 (Red Hat 6, CentOS 6, etc.) has "User lock".

diff --git a/ss_get_mysql_stats.php b/ss_get_mysql_stats.php
index de09d0a..a44ee52 100755
--- a/ss_get_mysql_stats.php
+++ b/ss_get_mysql_stats.php
@@ -448,7 +448,7 @@ function ss_get_mysql_stats( $options ) {
          # MySQL 5.5 replaces the 'Locked' state with a variety of "Waiting for
          # X lock" types of statuses.  Wrap these all back into "Locked" because
          # we don't really care about the type of locking it is.
-         $state = preg_replace('/^(Table lock|Waiting for .*lock)$/', 'Locked', $state);
+         $state = preg_replace('/^(User lock|Table lock|Waiting for .*lock)$/', 'Locked', $state);
          $state = str_replace(' ', '_', strtolower($state));
          if ( array_key_exists("State_$state", $status) ) {
             increment($status, "State_$state", 1);

UPDATED:

A number of other cases (MySQL 5.5 source)

$ grep -r thd_proc_info . | grep -i lock\"
./sql/lock.cc:  thd_proc_info(thd, "System lock");
./sql/sql_insert.cc:    thd_proc_info(client_thd, "waiting for handler lock");
./sql/sql_insert.cc:    thd_proc_info(client_thd, "got handler lock");
./sql/sql_insert.cc:  thd_proc_info(&thd, "upgrading lock");
./sql/item_func.cc:  thd_proc_info(thd, "User lock");