MySQL Query

[slence]

Hi,

I'm using some node with mysql server. What I need to do is to run a query and get a value. Like 1, if it's not 1, send a trigger.

What I have:

I've created an item:

- key: mysql.query[SELECT 1 FROM dual]

But i get "NOTSUPPORTED"

I've added this to zabbix_agent.conf:

UserParameter=mysql.query,echo "SELECT 1 FROM dual"| mysql -N -user -pass zabbix

from the host: mysql -user -pass -e"SELECT 1 FROM dual"

+---+
| 1 |
+---+
| 1 |
+---+

from the zabbix server:

zabbix_get -s IP -k mysql.query[SELECT 1 FROM dual]

ZBX_NOTSUPPORTED

Can someone give me some hint?

thanks.

[coreychristian]

Try just 'zabbix_get -s IP -k mysql.query[]'

The query it looks like is in your user parameter, so you should just need to trigger the key.

[slence]

Still the same...gives me ZBX_NOTSUPPORTED :\

[slence]

Ok. I solve this by changing UserParameter to:

UserParameter=mysql.query[*],(echo "SELECT 1 FROM dual"| mysql -N -user -pass)

Now everything works fine

[IanHavelock]

If you don't mind me asking, how did you get MySQL authentication to work? I've tried the .my.cnf method on the agent and the agent refuses to start/restart?

I've asked the question here: https://www.zabbix.com/forum/showthread.php?t=51521 a few days ago but so far have received no answers, so any help would be appreciated.

[slence]

Hi,

in zabbix_agent.conf:

UserParameter=mysql.query[*],(echo "SELECT 1 FROM dual"| mysql -N -user -pass)

change user and pass to your authentication in mysql

[IanHavelock]

Hi,

in zabbix_agent.conf:

UserParameter=mysql.query[*],(echo "SELECT 1 FROM dual"| mysql -N -user -pass)

change user and pass to your authentication in mysql

Does that just run 'Select 1 from DUAL' or can you pass in arbitrary SQL in your item key?

Also, what does your item key look like?

I've tried the /etc/zabbix/zabbix_agentd/.my.cnf method suggested by the Zabbix team (in order to use the default MySQL Template) but the agent refuses to start if I do that. I really want to use something similar to the base MySQL Template, preferably without having to create it from scratch using a User parameter.

[slence]

That run in mysql and gives me a number. so my item key is mysql.query (configured in zabbix).

[IanHavelock]

My /etc/zabbix/zabbix_agentd.conf on my agent host contains the following:

UserParameter=mysql.query[*],(echo "SELECT 1 FROM dual"| mysql -N -mysql_username -mysql_password)

And my item is configured as:



I have tried the item as both passive and active, and I have restarted the agent since adding the User parameter.

However, I get item "not supported"

[slence]

Change the key to mysql.query[] , and then try it again.

[IanHavelock]

I have fixed it (tested with zabbix_get) - the issue was with the User parameter.

It needs to be:

UserParameter=mysql.query[*],(echo "SELECT 1 FROM dual"| mysql -N -umysql_username -pmysql_password)

In your example it wasn't clear that the -u and -p had to remain before the username and password. Using zabbix_get I was able to determine what the issue was as it told me I was specifying an incorrect parameter of -r, which was the first letter of the username.

The downside of this is that I'd need to define each and every query in the User Parameter, rather than in Zabbix as intended.

[ingus.vilnis]

Hi,

Let's have a look at the documentation.

https://www.zabbix.com/documentation/2.4/manual/config/items/userparameters

You can pass different flexible parameters to your items.
Say you define UserParameter in zabbix_agentd.conf which will accept any parameters you pass via item key.

UserParameter=mysql.query[*],echo "$1"

And then in Zabbix web interface you create as many items as you like with one key but different parameters.

mysql.query[select value1 from DB]
mysql.query[select value35 from DB]
etc...

I'm not in the testing mood now but hope you got the idea. Try what you can do with it.

In the latest default agents you have a file for MySQL monitoring /etc/zabbix/zabbix_agentd.d/userparameter_mysql.conf and in frontend you have Template App MySQL which are great examples how that can be done very flexibly.

Hope this helps!

Best Regards,
Ingus

[IanHavelock]

Hi,

Let's have a look at the documentation.

https://www.zabbix.com/documentation/2.4/manual/config/items/userparameters

...

In the latest default agents you have a file for MySQL monitoring /etc/zabbix/zabbix_agentd.d/userparameter_mysql.conf and in frontend you have Template App MySQL which are great examples how that can be done very flexibly.

I have read the documentation, and that is exactly what I am trying to implement. However, this is where the problem lies.

I want to use the default MySQL Template, and I want to use /etc/zabbix/zabbix_agentd.d/userparameter_mysql.conf.

My /etc/zabbix/zabbix_agentd.conf Includes the correct directory, i.e. /etc/zabbix/zabbix_agentd.d/ but when I add a .my.cnf file in here for MySQL authentication, it doesn't work - in fact the agent refuses to start. If I change the Include line to specify the file 'userparameter_mysql.conf' it doesn't throw the error, but that leads me onto the next issue.

Zabbix itself shows "not supported" for the items, so I'm using zabbix_get from the server to debug.

When I run:

stevenh@zabbixserver:~$ zabbix_get -s 10.2.30.12 -k mysql.status[Com_begin]
Enter password: ERROR 1045 (28000): Access denied for user 'zabbixmysql'@'localhost' (using password: NO)
stevenh@zabbixserver:~$

You can see that it isn't passing authentication/password to MySQL.

When I hard-code username and password into userparameter_mysql.conf, restart the agent and run:

stevenh@zabbixserver:~$ zabbix_get -s 10.2.30.12 -k mysql.status[Com_begin]
Enter password: ERROR 1045 (28000): Access denied for user 'zabbixmysql'@'localhost' (using password: YES)
stevenh@zabbixserver:~$

You can see that it does try to authenticate but is still being refused, even though I can login to MySQL on the agent host with the exact same credentials, and run "show global status where Variable_name ='Com_begin';" and get results?
"

[catalyst]

I have read the documentation, and that is exactly what I am trying to implement. However, this is where the problem lies.

I want to use the default MySQL Template, and I want to use /etc/zabbix/zabbix_agentd.d/userparameter_mysql.conf.

My /etc/zabbix/zabbix_agentd.conf Includes the correct directory, i.e. /etc/zabbix/zabbix_agentd.d/ but when I add a .my.cnf file in here for MySQL authentication, it doesn't work - in fact the agent refuses to start. If I change the Include line to specify the file 'userparameter_mysql.conf' it doesn't throw the error, but that leads me onto the next issue.

Zabbix itself shows "not supported" for the items, so I'm using zabbix_get from the server to debug.

When I run:

stevenh@zabbixserver:~$ zabbix_get -s 10.2.30.12 -k mysql.status[Com_begin]
Enter password: ERROR 1045 (28000): Access denied for user 'zabbixmysql'@'localhost' (using password: NO)
stevenh@zabbixserver:~$

You can see that it isn't passing authentication/password to MySQL.

When I hard-code username and password into userparameter_mysql.conf, restart the agent and run:

stevenh@zabbixserver:~$ zabbix_get -s 10.2.30.12 -k mysql.status[Com_begin]
Enter password: ERROR 1045 (28000): Access denied for user 'zabbixmysql'@'localhost' (using password: YES)
stevenh@zabbixserver:~$

You can see that it does try to authenticate but is still being refused, even though I can login to MySQL on the agent host with the exact same credentials, and run "show global status where Variable_name ='Com_begin';" and get results?
"

So - I had this same issue and finally found the answer in a combination of a few sources:
* https://stackoverflow.com/questions/...ssword-written
* https://www.zabbix.com/forum/zabbix-...he-my-cnf-file

Essentially - what you want to do is create a login-path setting for your "zabbix" mysql user. But know that when it's run from zabbix-agent, it's going to be running it from the zabbix username on your *nix server...so you'll have to:
1) create the mysql_config_editor's settings (saved in .mylogin.cnf) from the zabbix username on the server
2) modify the UserParameter in /etc/zabbix/zabbix_agent.conf with "HOME={the_home_dir_where_".mylogin.cnf"_exist s}" before the mysql command and add the --login-path=zabbix option directly after the mysql command operator
3) restart zabbix-agent

Example (where mycustomtable is a new table I created in the zabbix database):
zabbix_agent.conf -- UserParameter=mysql.query[*],(HOME=/etc/zabbix mysql --login-path=zabbix -NBe "$1")
Item setup in GUI (Key) -- mysql.query[select mycolumn from zabbix.mycustomtable where HOST_NAME = '{HOST.NAME}']

Now, you don't get any warnings from mysql about entering the user/pass on the command line being unsafe, and you can pass in whatever query you want (specified within the item's key).