How do I monitor MySQL on a target host?

[slydog]

I am a relative newbie to Zabbix. We are running Zabbix 2.2.6 in a Red Hat Enterprise Linux 6.5 environment. We are also running MySQL 5.1.16 on the hosts I describe below. We have zabbix-agent 2.2.6 installed on the target host.

On the Zabbix server itself, all the Items in Template_App_MySQL are enabled.

It is not entirely clear to me what needs to be setup on the Zabbix server and what needs to be setup on the target host to get this to monitor MySQL on the target host. When I attach the Template_App_MySQL to the target only two Items register as "enabled", those with KEYs: mysql.version and mysql.ping

All the KEYs mysql.status[<cmd>] show as "not supported". I have added a user to MySQL on the target host; so that now

telnet targethost 3306

from the Zabbix server's CLI succeeds. But all those KEYs still show as "not supported".

Having read through the documentation I have noted the need for mysql-connector-odbc: but which server does it need to be installed on? Both? Which server should have odbc.ini on it?

What MySQL privileges does my user on the target host need to have? Any help will be greatly appreciated.

[tchjts1]

This thread will help you: https://www.zabbix.com/forum/zabbix-...bx_unsupported
Maybe this post also: https://www.zabbix.com/forum/zabbix-...nitoring-mysql

Basically you will need to have the Zabbix agent on you host, zabbix_agentd.conf (Which has a call to this next conf) as well as userparameter_mysql.conf

And you will need to configure the userparameter_mysql.conf correctly to pick up the data you are looking for.

[slydog]

This thread will help you: https://www.zabbix.com/forum/showthread.php?t=44520

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

Basically you will need to have the Zabbix agent on you host, zabbix_agentd.conf (Which has a call to this next conf) as well as userparameter_mysql.conf

And you will need to configure the userparameter_mysql.conf correctly to pick up the data you are looking for.

I have followed this, I hope. But still see "Not supported" for all but two items on the MySQL template. When I look on the target host in the /var/log/zabbix/zabbix_agentd.log I find this error over and over again:

mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'XXXXXXX'@'localhost' (using password: NO)'

where XXXXXXX is my username??

I did make myself an account on the Zabbix server with my username, and we use LDAP authentication. But why would the zabbix server processes run as me???

[tchjts1]

But why would the zabbix server processes run as me???

Zabbix is reading the username from your my.cnf file and trying to use those credentials to connect to your MySql DB.

The first line of userparameter_mysql.conf tells you this:
For all the following commands HOME should be set to the directory that has my.cnf file with password information.

The important line of that conf file is this, which for me, my.cnf is located in /etc so this is my variables line:

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

And then you have to make sure that in your zabbix_agentd.conf file, it has a call to include that userparameter_mysql.conf file, like this:

### 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/
Include=/usr/local/etc/userparameter_mysql.conf

And any changes that are made to either of those files, you must restart the Zabbix agent. Check that you have your HOME path set to where your my.cnf file is...

[slydog]

I tried all that you suggested; but was getting the same results.

BUT, my problem has morphed now: I have been asked to set-up Zabbix in our production environment - this means I have installed Zabbix on the same server I was trying to remotely monitor before. It has an existing MySQL installation which other applications also use.

Yet I am getting the same errors in the zabbix-agentd.log

==> /var/log/zabbix/zabbix_agentd.log <==
 30696:20141216:115623.667 Starting Zabbix Agent [Zabbix server]. Zabbix 2.2.6 (revision 48483).
 30696:20141216:115623.667 using configuration file: /etc/zabbix/zabbix_agentd.conf
 30698:20141216:115623.669 agent #0 started [collector]
 30699:20141216:115623.669 agent #1 started[listener #1]
 30701:20141216:115623.670 agent #3 started[listener #3]
 30700:20141216:115623.671 agent #2 started[listener #2]
 30702:20141216:115623.671 agent #4 started [active checks #1]

==> /var/log/zabbix/zabbix_server.log <==
 10521:20141216:115638.322 resuming Zabbix agent checks on host "Zabbix server": connection restored

==> /var/log/zabbix/zabbix_agentd.log <==
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'df37732'@'localhost' (using password: NO)'

But I personally do not have a my.cnf or .my.cnf file on this system.

When I did the installation I was sudo'd in as the root user; and when I start the zabbix-server and zabbix-agent services I do so as the root user. I have mimicked the installation and configuration of zabbix and zabbix-agentd on this production server to match the lab server it has been running on. Yet it only shows mysql.ping and mysql.version as supported; and I get those log entries that I am denied access. I am very confused at this point.

[aib]

One more time:
to get any data from MySQL you have to provide correct credentials. (username and password)

If you don't do it - you will get error message:

mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'df37732'@'localhost' (using password: NO)'

Correct credentials can be get from Database Administrator, who has to create an user in MySQL (not in OS, not in LDAP) and give some premissions for that user.

The second options is to get Database Administrator credentials (which is not secure) and write it in my.cnf file.

OK?

[slydog]

What purpose is mysql serving in this line [after HOME=/etc and before -N]?

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

Is it a username? A CLI command? Something else?

[aib]

mysql in that place is the name of program, which serve your request, sent by ECHO command.

BTW, check this thread. It's also about MySQL monitoring

https://www.zabbix.com/forum/showthread.php?t=41659

[slydog]

Problem resolved. Thanks tchjts1 and aib

Thanks to both tchjts1 and aib!

All of your links were helpful in ironing out my configurations. aib's link to https://www.zabbix.com/forum/showthread.php?t=41659 was the final piece when I realized I did not have a [client] tag and section in my /etc/my.cnf - I had been messing with the wrong "user=" line.

[aib]

You are welcome!