Monitor MySQL Database on agent - Not supported

[vintagegamingsystems]

Hello All,
This is my first time posting here. I must say that Zabbix is pretty cool. Here is some background information about the systems that I have networked together.
The platform that I am running on my Zabbix Server is CentOS 6.3 and my Zabbix server is running Zabbix Version 2.0.6.
The agent is running CentOS 6.3.
I also must say that I am having a hard time getting the Zabbix agent to monitor its own MySQL database. I am not sure where to start on this. I have set up a Zabbix agent host on the php-front-end and added the MySQL Template but I do not get any readings. I get a red flag on the right and "not supported" in the status column in the items menu. I was wondering which yum packages I need to install and which files to edit in order to get the agent to monitor the database data and send readings back to the Zabbix Server. I am at a loss as where to start. I also want to monitor whether MySQL is running or not and then make a graph of that. If anyone needs additional information, please send me a message.
Thanks.

[tchjts1]

See this post: https://www.zabbix.com/forum/showthread.php?t=40870

You need to adjust your userparameter_mysql.conf file on the host that has MySql. restart your agent after any changes.

This is the line you need to look at, starting with this: UserParameter=mysql.status[*]

And then near the bottom of that file is the parameter to detect whether MySql is alive. This is my complete line for that:
UserParameter=mysql.ping,HOME=/etc/ mysqladmin ping | grep -c alive

[vintagegamingsystems]

This is what I have in the userparameter_mysql.conf file

There doesn't seem to be a file called zabbix in the /var/lib/ directory. Is that a problem? Or is it not pointed to the correct directory? I have a /var/lib/mysql/ directory?

[vintagegamingsystems]

My /etc/my.cnf file looks like this.

[client]
port = 3306
[mysqld]
bind-address = 127.0.0.1
port = 3306
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[vintagegamingsystems]

[m|ZBX_NOTSUPPORTED]

I am also getting [m|ZBX_NOTSUPPORTED] when I run the following command.
azbbix_agentd -t "mysql.version."

[tchjts1]

There doesn't seem to be a file called zabbix in the /var/lib/ directory. Is that a problem? Or is it not pointed to the correct directory? I have a /var/lib/mysql/ directory?

That parameter needs to point to wherever your my.cnf file resides.
My complete line reads like this:

UserParameter=mysql.status[*],echo "show global status where Variable_name='$1';" | HOME=/etc/ mysql -N | awk '{print $$2}'

So, you really just need to have HOME= point to the dir where you have your my.cnf and restart the agent.

Before you do that though... your zabbix_agentd.conf file needs to be aware of that file when the agent is loaded. On your MySql host, look at your zabbix_agentd.conf file and make sure you are seeing a section like this near the end of the file. If it doesn't exist.. just insert that "Include=" line with the proper path.

### Option: Include
#       You may include individual files or all files in a directory in the configuration file.
#       Installing Zabbix will create include directory in /usr/local/etc, unless modified during the compile time.
#
# Mandatory: no
# Default:
# Include=

# Include=/usr/local/etc/zabbix_agentd.userparams.conf
# Include=/usr/local/etc/zabbix_agentd.conf.d/
[B]Include=/usr/local/etc/userparameter_mysql.conf[/B]

[vintagegamingsystems]

This is what I have in the /etc/zabbix/zabbix_agentd.d/userparameter_mysql.conf file

Here is what I have fo the userparameter_mysql.conf file.
# For all the following commands HOME should be set to the directory that has .my.cnf file with password information.

# Flexible parameter to grab global variables. On the frontend side, use keys like mysql.status[Com_insert].
# Key syntax is mysql.status[variable].
UserParameter=mysql.status[*],echo "show global status where Variable_name='$1';" | HOME=/etc mysql -N | awk '{print $$2}' # My line
#UserParameter=mysql.status[*],echo "show global status where Variable_name='$1';" | HOME=/var/lib/zabbix mysql -N | awk '{print $$2}' #default

# Flexible parameter to determine database or table size. On the frontend side, use keys like mysql.size[zabbix,history,data].
# Key syntax is mysql.size[<database>,<table>,<type>].
# Database may be a database name or "all". Default is "all".
# Table may be a table name or "all". Default is "all".
# Type may be "data", "index", "free" or "both". Both is a sum of data and index. Default is "both".
# Database is mandatory if a table is specified. Type may be specified always.
# Returns value in bytes.
# 'sum' on data_length or index_length alone needed when we are getting this information for whole database instead of a single table
UserParameter=mysql.size[*],echo "select sum($(case "$3" in both|"") echo "data_length+index_length";; data|index) echo "$3_length";; free) echo "data_free";; esac)) from information_schema.tables$([[ "$1" = "all" || ! "$1" ]] || echo "$

#Default below
#UserParameter=mysql.ping,HOME=/var/lib/zabbix mysqladmin ping | grep -c alive #default
UserParameter=mysql.ping,HOME=/etc mysqladmin ping | grep -c alive #My line
UserParameter=mysql.version,mysql -V
UserParameter=mysql.ping,mysqladmin -u zabbix --password=password ping | grep alive | wc -l | tr -d " "
UserParameter=mysql.uptime,mysqladmin -u zabbix --password=password status | cut -f2 -d ":" | cut -f1 -d "T" | tr -d " "
UserParameter=mysql.threads,mysqladmin -u zabbix --password=password status | cut -f3 -d ":" | cut -f1 -d "Q" | tr -d " "
UserParameter=mysql.questions,mysqladmin -u zabbix --password=password status | cut -f4 -d ":"|cut -f1 -d "S" | tr -d " "
UserParameter=mysql.slowqueries,mysqladmin -u zabbix --password=password status | cut -f5 -d ":" | cut -f1 -d "O" | tr -d " "
UserParameter=mysql.qps,mysqladmin -u zabbix --password=password status | cut -f9 -d ":" | tr -d " "
UserParameter=mysql.version,mysql -V

[vintagegamingsystems]

Here is what I have for my /etc/my.cnf file

Here is what I have for my /etc/my.cnf file.
client]
user=root
password=zabbixpassword
port=3306
[mysqld]
bind-address=127.0.0.1
port=3306
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[tchjts1]

UserParameter=mysql.status[*],echo "show global status where Variable_name='$1';" | HOME=/etc mysql -N | awk '{print $$2}' 

  > snip <

#Default below
#UserParameter=mysql.ping,HOME=/var/lib/zabbix mysqladmin ping | grep -c alive #default
UserParameter=mysql.ping,HOME=/etc mysqladmin ping | grep -c alive #My line
UserParameter=mysql.version,mysql -V
UserParameter=mysql.ping,mysqladmin -u zabbix --password=password ping | grep alive | wc -l | tr -d " "
UserParameter=mysql.uptime,mysqladmin -u zabbix --password=password status | cut -f2 -d ":" | cut -f1 -d "T" | tr -d " "
UserParameter=mysql.threads,mysqladmin -u zabbix --password=password status | cut -f3 -d ":" | cut -f1 -d "Q" | tr -d " "
UserParameter=mysql.questions,mysqladmin -u zabbix --password=password status | cut -f4 -d ":"|cut -f1 -d "S" | tr -d " "
UserParameter=mysql.slowqueries,mysqladmin -u zabbix --password=password status | cut -f5 -d ":" | cut -f1 -d "O" | tr -d " "
UserParameter=mysql.qps,mysqladmin -u zabbix --password=password status | cut -f9 -d ":" | tr -d " "
UserParameter=mysql.version,mysql -V

See the statement above the snip line in the above box? That is basically the same thing as all the Userparameters that are listed below that snip line. Not to mention you have duplicates within the section itself below the dotted line.

To start out with, comment out all those Userparameter= lines that are below the snip line. Restart your agent after that. Give it a few minutes and see if the items start reporting in.

The asterisk in the line beginning with: UserParameter=mysql.status[*]
Covers all this stuff that is in Template APP MySQL as seen here: (Except for ping and version)

[vintagegamingsystems]

The version number is coming up now.

This is what I am still getting. Although the MySQL version is showing up now.

[tchjts1]

The top metric is also showing up. Give it some more time...

[vintagegamingsystems]

This is staying up too. mysql.status[Com_update]

This is staying up too.
mysql.status[Com_update]

[vintagegamingsystems]

Now they are down again.

They are down again.

[tchjts1]

It is working off of the value setting that you have for "Refresh Unsupported Items". To see that setting, go to Administration --> General and change the upper right dropdown box to "Other". Then you will see that setting.

An alternative is to simply click on "Unsupported" for each item and that will force it to go to supported.

[tchjts1]

They are down again.

If they are going back to unsupported, then you still have an issue with your path somewhere in one of the zabbix conf files.