MySQL by Zabbix agent 2 and Linux by Zabbix Agent templates problem

[nems]

Hello,

I am testing out Zabbix as a monitoring solution for our Linux environment and i ran into some issues with this template.

Zabbix server:

root@zabbix-01:~# zabbix_server --version
zabbix_server (Zabbix) 6.0.4
Revision 3d787ff402e 3 May 2022, compilation time: Apr 6 2022 13:22:31

Copyright (C) 2022 Zabbix SIA
License GPLv2+: GNU GPL version 2 or later <http://gnu.org/licenses/gpl.html>.
This is free software: you are free to change and redistribute it according to
the license. There is NO WARRANTY, to the extent permitted by law.

This product includes software developed by the OpenSSL Project
for use in the OpenSSL Toolkit (http://www.openssl.org/).

Compiled with OpenSSL 1.1.1f 31 Mar 2020
Running with OpenSSL 1.1.1f 31 Mar 2020

root@zabbix-01:~# lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description: Ubuntu 20.04.4 LTS
Release: 20.04
Codename: focal

Zabbix agent:

root@mysql01:~# zabbix_agentd --version
zabbix_agentd (daemon) (Zabbix) 6.0.4
Revision 3d787ff402e 3 May 2022, compilation time: Apr 6 2022 13:22:31

Copyright (C) 2022 Zabbix SIA
License GPLv2+: GNU GPL version 2 or later <http://gnu.org/licenses/gpl.html>.
This is free software: you are free to change and redistribute it according to
the license. There is NO WARRANTY, to the extent permitted by law.

This product includes software developed by the OpenSSL Project
for use in the OpenSSL Toolkit (http://www.openssl.org/).

Compiled with OpenSSL 1.1.0l 10 Sep 2019
Running with OpenSSL 1.1.0j 20 Nov 2018
root@mysql01:~# lsb_release -a
No LSB modules are available.
Distributor ID: Debian
Description: Debian GNU/Linux 9.8 (stretch)
Release: 9.8
Codename: stretch

I have configured mysql according to the steps outline here: Browse Zabbix / Zabbix - ZABBIX GIT
Tested logging into mysql with agent user and pw and it works as expected.

The following errors pop up in the serverlogs:

20632:20220523:123004.396 discovery rule "mysql01:mysql.db.discovery["{$MYSQL.DSN}","{$MYSQL.USER}","{$MYSQL.PASSWO RD}"]" became not supported: Invalid discovery rule value: cannot parse as a valid JSON object: invalid object format, expected opening character '{' or '[' at: 'mysql: [ERROR] Unknown suffix 'z' used for variable 'port' (value 'zabbix-agent')
mysql: [ERROR] mysql: Error while setting value 'zabbix-agent' to 'po
20631:20220523:123006.396 discovery rule "mysql01:mysql.replication.discov ery["{$MYSQL.DSN}","{$MYSQL.USER}","{$MYSQL.PASSWO RD}"]" became not supported: Invalid discovery rule value: cannot parse as a valid JSON object: invalid object format, expected opening character '{' or '[' at: 'mysql: [ERROR] Unknown suffix 'z' used for variable 'port' (value 'zabbix-agent')
mysql: [ERROR] mysql: Error while setting value 'zabbix-agent' to 'po

To my untrained eye it looks like its trying to set the string 'zabbix-agent' as the port.. I dont know why that would be, i have not configured any macro related to port (there isnt one as far as i can see)..

My configuration in Zabbix:



On server with agent:

root@mysql01:~# ls -lah /etc/zabbix/
total 36K
drwxr-xr-x 3 root root 4.0K May 23 14:15 .
drwxr-xr-x 91 root root 4.0K May 12 14:27 ..
-rw-r--r-- 1 root root 48 May 12 15:01 .my.cnf
-rw-r--r-- 1 root root 17K May 23 14:07 zabbix_agentd.conf
drwxr-xr-x 2 root root 4.0K May 23 14:03 zabbix_agentd.d


root@mysql01:~# cat /etc/zabbix/.my.cnf
[client]
user='zabbix-agentuser'
password='mypass'

Any advice?

[nems]

Just a quick update as i did some more troubleshooting while this post was waiting for moderation.

So i removed Username and password from zabbix macros and i enabled debug level 5 in order to get more data on the agent.

Before removing credentials:

40413:20220524:082618.107 Requested [mysql.get_status_variables["unix:/run/mysqld/mysqld.sock","myuser","mypass"]]
40413:20220524:082618.108 In zbx_popen() command:' mysql -h"unix:/run/mysqld/mysqld.sock" -P"myuser" -sNX -e "show global status"'
40413:20220524:082618.118 execute_str() command:' mysql -h"unix:/run/mysqld/mysqld.sock" -P"myuser" -sNX -e "show global status"' len:154 cmd_result:'mysql: [ERROR] Unkno'
40413:20220524:082618.118 Sending back [mysql: [ERROR] Unknown suffix 'z' used for variable 'port' (value 'myuser')
mysql: [ERROR] mysql: Error while setting value 'myuser' to 'port']

After removing credentials:

42038:20220524:082918.113 Requested [mysql.get_status_variables["unix:/run/mysqld/mysqld.sock","",""]]
42038:20220524:082918.113 In zbx_popen() command:' mysql -h"unix:/run/mysqld/mysqld.sock" -P"" -sNX -e "show global status"'
42038:20220524:082918.223 execute_str() command:' mysql -h"unix:/run/mysqld/mysqld.sock" -P"" -sNX -e "show global status"' len:137 cmd_result:'mysql: [Warning] opt'
42038:20220524:082918.223 Sending back [mysql: [Warning] option 'port': value -sNX adjusted to 0
ERROR 2005 (HY000): Unknown MySQL server host 'unix:/run/mysqld/mysqld.sock' (0)]

So in the first instance where credentials are mapped to macros it tries to use the username as a port..
In the second instance i removed the macros from the host (they are unset globally) and it sends empty strings as the second and third index in the request, and this failing again because it somehow expects this to contain the port.

So i tried changing the username to the port:

36470:20220524:112418.907 Requested [mysql.get_status_variables["/run/mysqld/mysqld.sock","3306",""]]
36470:20220524:112418.907 In zbx_popen() command:' mysql -h"/run/mysqld/mysqld.sock" -P"3306" -sNX -e "show global status"'
36470:20220524:112418.977 execute_str() command:' mysql -h"/run/mysqld/mysqld.sock" -P"3306" -sNX -e "show global status"' len:75 cmd_result:'ERROR 2005 (HY000): '
36470:20220524:112418.977 Sending back [ERROR 2005 (HY000): Unknown MySQL server host '/run/mysqld/mysqld.sock' (0)]
36472:20220524:112420.972 Requested [mysql.ping["/run/mysqld/mysqld.sock","3306",""]]
36472:20220524:112420.972 In zbx_popen() command:' mysqladmin -h"/run/mysqld/mysqld.sock" -P"3306" ping'
36472:20220524:112420.985 execute_str() command:' mysqladmin -h"/run/mysqld/mysqld.sock" -P"3306" ping' len:280 cmd_result:'mysqladmin: connect'
36472:20220524:112420.985 Sending back [mysqladmin: connect to server at '/run/mysqld/mysqld.sock' failed
error: 'Unknown MySQL server host '/run/mysqld/mysqld.sock' (0)'
Check that mysqld is running on /run/mysqld/mysqld.sock and that the port is 3306.
You can check this by doing 'telnet /run/mysqld/mysqld.sock 3306']

Then i changed connectionstring to 127.0.0.1:

37336:20220524:112718.398 Requested [mysql.get_status_variables["127.0.0.1","3306",""]]
37336:20220524:112718.398 In zbx_popen() command:' mysql -h"127.0.0.1" -P"3306" -sNX -e "show global status"'
37336:20220524:112718.498 execute_str() command:' mysql -h"127.0.0.1" -P"3306" -sNX -e "show global status"' len:84 cmd_result:'ERROR 1045 (28000): '
37336:20220524:112720.500 Requested [mysql.ping["127.0.0.1","3306",""]]
37336:20220524:112720.500 In zbx_popen() command:' mysqladmin -h"127.0.0.1" -P"3306" ping'
37336:20220524:112720.532 execute_str() command:' mysqladmin -h"127.0.0.1" -P"3306" ping' len:126 cmd_result:'mysqladmin: connect'
37336:20220524:112720.532 Sending back [mysqladmin: connect to server at '127.0.0.1' failed

note that this works when done manually:

root@mysql01:~# mysql -h127.0.0.1 -P3306 -sNX -e"show global status\G"
<?xml version="1.0"?>

<resultset statement="show global status" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<row>
<field name="Variable_name">Aborted_clients</field>
<field name="Value">27</field>
</row>

<row>
<field name="Variable_name">Aborted_connects</field>

So, whats wrong here really.. The instructions says to create the .my.cnf file, but i dont see this being used anywhere (root login without password is enabled).. And options doesnt seem to work even tho they create a valid command as seen in the last log output (and manual test)..

[obugrademir]

Can other graphics be viewed?
Can you monitor metrics like CPU/MEM, not just MySQL?

* if the answer is no:
network check on ZabbixServer shell --> telnet hostIP agentPort (default: 10050)

* if the answer is yes:
Can you follow these steps?

* Create user and add role:
** in MySQL
*** CREATE USER 'yourZabbixMonitorUsername'@'%' IDENTIFIED BY '<yourPassword>';
*** GRANT REPLICATION CLIENT,PROCESS,SHOW DATABASES,SHOW VIEW ON *.* TO 'yourZabbixMonitorUsername'@'%';
* Network check
** on Zabbix Server shell --> telnet hostIP mysqlPort (default: 3306)
* User access check:
** on Zabbix Server shell --> mysql -h<hostIP> -P3306 ... etc.

after...

* Template: "MySQL by Zabbix agent 2"
* Macros:
** {$MYSQL.DSN} --> tcp://hostIP:3306
** {$MYSQL.USER} --> yourZabbixMonitorUsername
** {$MYSQL.PASSWORD} --> yourPassword


Source: https://www.zabbix.com/integrations/mysql#mysql_agent2

I think these steps are correct.
You will be successful.

If that works, you can monitor via tcp.

tcp monitoring ok but your use to unix..
However, if you want to use sockets
please check mysql socket, socket path and authority .


Best

[nems]

Communication between zabbix server and agent works as you can see in my examples.
I followed the template for setting this up to the letter, the result is above..

mysql user is setup according to guide, and since i made the .my.cnf it should not require user/pass in macros, however as you can see above in post#2 i tried all that, and it parses the arguments wrong. No idea why.
This shows user is working with connecting to mysql (im using the zabbix user and password)

root@mysql01:~# mysql -h127.0.0.1 -P3306 -uusername -ppassword -sNX -e"show global status\G"
mysql: [Warning] Using a password on the command line interface can be insecure.
<?xml version="1.0"?>

<resultset statement="show global status" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<row>
<field name="Variable_name">Aborted_clients</field>
<field name="Value">28</field>
</row>

<row>
<field name="Variable_name">Aborted_connects</field>

[gsalvin]

Hello nems, do you solved the issue ?

[nems]

No, and we didnt go with Zabbix so i didnt pursue it any further, sorry

[capsx]

Hi!


In short

Zabbix Server GUI:

Menu -> Data Collection -> Templates -> search and open "Template DB MySQL by Zabbix agent 2" (this path is for Zabbix server: 6.4.x)

Open Macros and in Macro {$MYSQL.DSN} add Value = mysql

Zabbix agent2:

MySQL:

CREATE USER 'zabbix_server'@'%' IDENTIFIED BY 'xxx';
GRANT REPLICATION CLIENT,SLAVE MONITOR,PROCESS,SHOW DATABASES,SHOW VIEW ON *.* TO 'zabbix_server'@'%';
FLUSH PRIVILEGES;

In file /etc/zabbix/zabbix_agent2.d/plugins.d/mysql.conf:

Plugins.Mysql.Sessions.mysql.Uri=unix:/run/mysqld/mysqld.sock
Plugins.Mysql.Sessions.mysql.User=zabbix_server
Plugins.Mysql.Sessions.mysql.Password=xxx

Plugins.Mysql.Default.Uri=/run/mysqld/mysqld.sock
Plugins.Mysql.Default.User=zabbix_server
Plugins.Mysql.Default.Password=xxx

I guess this Default is like a fallback, but I changed them as well.

Monitoring is working fine except this one: MySQL: Service is down (when I will fix this false error, will add here how

Zabbix agent: 6.4.4 (Ubuntu 20.04.6 LTS)
Zabbix server: 6.4.4 (Ubuntu 22.04.2 LTS)