Zabbix 4.4 - Template DB MySQL - Account isn't read from agent conf

[Frogg]

Hi, i know it is a recurent post about Mysql Template, but i didn't find any answer in other posts.
So i am sorry after so many tries and researches i finally come there to try to find a solution for my trouble:

The Mysql Server is distant (with zabbix agent) to zabbix server, and Zabbix server seems to try to connect with info server side instead of client side (zabbix agent) credentials


1 - Environement

Zabbix server version 4.4.6 (now updated to 5.2.4)
Zabbix agent version 3.2.11
Using agent on port 10050
Template name Template DB MySQL (original not modified)
Other templates are working well

Server side

appliance@zabbix:~$ zabbix_server --version
zabbix_server (Zabbix) 5.2.4
Revision e9f58de09c 25 January 2021, compilation time: Jan 7 2021 13:16:25

Copyright (C) 2021 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

Client side

└─ # zabbix_agentd --version
zabbix_agentd (daemon) (Zabbix) 3.2.11
Revision 76339 27 December 2017, compilation time: Dec 27 2017 15:13:43

Copyright (C) 2017 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.

2 - Configuration

• zabbix-agent side: (agent has been restart after conf has been set)

Added user to Mysql server credentials

[FONT=Calibri][FONT=Consolas]CREATE USER 'zabbixagent'@'%' IDENTIFIED BY 'mypass';[/FONT][/FONT]
[FONT=Calibri][FONT=Consolas]GRANT REPLICATION CLIENT,PROCESS,SHOW DATABASES,SHOW VIEW ON *.* TO 'zabbixagent'@'%';[/FONT][/FONT]
[FONT=Calibri][FONT=Consolas]FLUSH PRIVILEGES;[/FONT][/FONT]

Credentials added to .my.cnf (path /var/lib/zabbix checked in cat /etc/passwd | grep zabbix)

[FONT=Calibri][FONT=Consolas]mkdir /var/lib/zabbix[/FONT][/FONT]
[FONT=Calibri][FONT=Consolas]nano /var/lib/zabbix/.my.cnf[/FONT][/FONT]
[FONT=Calibri][FONT=Consolas]chown -R zabbix:zabbix /var/lib/zabbix/[/FONT][/FONT]

With content

[FONT=Calibri][FONT=Consolas][client][/FONT][/FONT]
[FONT=Calibri][FONT=Consolas]user='zabbixagent'[/FONT][/FONT]
[FONT=Calibri][FONT=Consolas]password='mypass'[/FONT][/FONT]
[FONT=Calibri][FONT=Consolas]socket=/var/lib/mysql/mysql.sock[/FONT][/FONT]

The file /etc/zabbix/zabbix_agentd.d/userparameter_mysql.conf is present with content

# 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=/var/lib/zabbix mysql -N | awk '{print $$2}'

# 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[*],bash -c 'echo "select sum($(case "$3" in both|"") echo "data_length+index_length";; data|index) echo "$3_length";; free) echo "data_free";; esac)) from information_schem$

UserParameter=mysql.ping,HOME=/var/lib/zabbix mysqladmin ping | grep -c alive
UserParameter=mysql.version,mysql -V

UserParameter=mysql.get_status_variables[*], mysql -h"$1" -P"$2" -sNX -e "show global status"
UserParameter=mysql.db.discovery[*], mysql -h"$1" -P"$2" -sN -e "show databases"
UserParameter=mysql.dbsize[*], mysql -h"$1" -P"$2" -sN -e "SELECT COALESCE(SUM(DATA_LENGTH + INDEX_LENGTH),0) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='$3'"
UserParameter=mysql.replication.discovery[*], mysql -h"$1" -P"$2" -sNX -e "show slave status"
UserParameter=mysql.slave_status[*], mysql -h"$1" -P"$2" -sNX -e "show slave status"

• zabbix web interface:

Host configuration
added the template Template DB MySQL
added the macro {$MYSQL.HOST} with value the IP of the Server with MySQL & zabbix-agent

3 - Checks

Web interface don't warn about no data collected for 30 min
When i try the commands agent side they seems to works:

zabbix_agentd -t "mysql.ping"
mysql.ping [t|1]

zabbix_agentd -t "mysql.status[Bytes_sent]"
mysql.status[Bytes_sent] [t|212012265274]

4 - Errors

Zabbix agent side i have no errors


Zabbix server side i have errors like zabbix server can't connect to mysql using wrong credential

parser error : Start tag expected, '<' not found
ERROR 1045 (28000): Access denied for user 'zabbix'@'server.host.name' (
^
noname.xml:1: parser error : Start tag expected, '<' not found
ERROR 1045 (28000): Access denied for user 'zabbix'@'server.host.name' (
^
noname.xml:1: noname.xml:1: parser parser error : error : Start tag expected, '<' not found
Start tag expected, '<' not found
ERROR 1045 (28000): Access denied for user 'zabbix'@'server.host.name' (
ERROR 1045 (28000): Access denied for user 'zabbix'@'server.host.name'

But i think it shoud not be zabbix but zabbix-agent from agent /var/lib/zabbix/.my.cnf
And it should not be server.host.name (Host {HOST.DNS} or {HOST.NAME} ) but {$MYSQL.HOST} instead from macro



If you have any idea about what i am doing wrong, thanks a lot in advance.




==> Finally SOLVED... the agent version must match a client version

[tim.mooney]

Hey Frogg, you did a very good job of providing background information about your setup and environment. I wish everyone that asked questions here did as well as you.

I can't find a copy of the exact "Template DB MySQL" that you're using since the 5.x templates have changed (and improved!) greatly, so I can't be sure about this, but I have at least one idea.

When you created the MySQL database user, you gave it a username of 'zabbix-agent'. I don't know if '-' is allowed in database usernames, but let's assume it is.

I think it may be a problem that in the .my.cnf, you specified 'user=zabbix'. The 'user=' entry in .my.cnf should be the database user you want to default to using, so in your case, I think it should be 'zabbix-agent', not 'zabbix'.

Also, if the template works the way I think it does, I think you could have used {$MYSQL.HOST} set to 'localhost'. I don't think that's the problem you're running into, but if you get it working after changing the user= in .my.cnf, might also be worth experimenting with {$MYSQL.HOST} set to localhost, and see if it still works. If it does, it might be worth switching to using that.

[Frogg]

Hi Tim,

Thanks for your answer.

I can't find a copy of the exact "Template DB MySQL" that you're using since the 5.x templates have changed (and improved!) greatly, so I can't be sure about this, but I have at least one idea.

The template is the template in Zabbix 4.4 version by default.

When you created the MySQL database user, you gave it a username of 'zabbix-agent'. I don't know if '-' is allowed in database usernames, but let's assume it is.

I changed "zabbix-agent" to "zabbixagent" (in config & mysql user) to check if "-" could cause a trouble
But server side it still use 'zabbix'@'sub.domain.ext'

I think it may be a problem that in the .my.cnf, you specified 'user=zabbix'. The 'user=' entry in .my.cnf should be the database user you want to default to using, so in your case, I think it should be 'zabbix-agent', not 'zabbix'.

It is an error in the post, in the conf file the user is the good one

Also, if the template works the way I think it does, I think you could have used {$MYSQL.HOST} set to 'localhost'. I don't think that's the problem you're running into, but if you get it working after changing the user= in .my.cnf, might also be worth experimenting with {$MYSQL.HOST} set to localhost, and see if it still works. If it does, it might be worth switching to using that.

noname.xml:1: parser error : Start tag expected, '<' not found
ERROR 1045 (28000): Access denied for user 'zabbix'@'localhost'

if i set back the IP it user the domaine name of the server

noname.xml:1: parser error : Start tag expected, '<' not found
ERROR 1045 (28000): Access denied for user 'zabbix'@'sub.domain.ext'

seems the user isn't read at all


In the template description seems to be as i did:

Requirements for template operation:
1.Install Zabbix agent and MySQL client.
2.Copy Template_DB_MySQL.conf into folder with Zabbix agent configuration (/etc/zabbix/zabbix_agentd.d/ by default). Don't forget restart zabbix-agent.
3.Create MySQL user for monitoring. For example:
CREATE USER 'zbx_monitor'@'%' IDENTIFIED BY '<password>';
GRANT USAGE,REPLICATION CLIENT,PROCESS,SHOW DATABASES,SHOW VIEW ON *.* TO 'zbx_monitor'@'%';
For more information read the MYSQL documentation https://dev.mysql.com/doc/refman/8.0/en/grant.html , please.
4.Create .my.cnf in home directory of Zabbix agent for Linux (/var/lib/zabbix by default ) or my.cnf in c:\ for Windows. For example:
[client]
user=zbx_monitor
password=<password>

I even tried to remove the quotes

[client]
user=zabbixagent
password=mypass
socket=/var/lib/mysql/mysql.sock

Thanks anyway

[Frogg]

So, i updated the server to Zabbix 5.2


The template seems almost the same:

zabbix_export:
version: '5.2'
date: '2021-01-28T22:39:18Z'
groups:
-
name: Templates/Databases
templates:
-
template: 'Template DB MySQL'
name: 'Template DB MySQL'
description: |
Requirements for template operation:
1.Install Zabbix agent and MySQL client.
2.Copy Template_DB_MySQL.conf into folder with Zabbix agent configuration (/etc/zabbix/zabbix_agentd.d/ by default). Don't forget restart zabbix-agent.
3.Create MySQL user for monitoring. For example:
CREATE USER 'zbx_monitor'@'%' IDENTIFIED BY '<password>';
GRANT USAGE,REPLICATION CLIENT,PROCESS,SHOW DATABASES,SHOW VIEW ON *.* TO 'zbx_monitor'@'%';
For more information read the MYSQL documentation https://dev.mysql.com/doc/refman/8.0/en/grant.html , please.
4.Create .my.cnf in home directory of Zabbix agent for Linux (/var/lib/zabbix by default ) or my.cnf in c:\ for Windows. For example:
[client]
user=zbx_monitor
password=<password>

There is something i dont understand:

The serveur send the error:

noname.xml:1: parser error : Start tag expected, '<' not found
ERROR 1045 (28000): Access denied for user 'zabbix'@'sub.domain.ext'

But isnt the agent who is collecting datas and should have the error ?
Why the user is zabbix instead of user from .my.cnf ?
Why server ip is change by it s DNS name ?
I dont know where i could debug thoose informations

I tried to:
Update version to 5.2 > No success
Open the port 3306 > No success
Adding /var/lib/zabbix/.my.cnf server side > No success


any help would be appreciated

[Frogg]

Finally SOLVED... i t was the agent version, i updated to 5.2

zabbix_agentd (daemon) (Zabbix) 5.2.4
Revision e9f58de09c 25 January 2021, compilation time: Jan 25 2021 09:56:43

Copyright (C) 2021 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.0.2k-fips 26 Jan 2017
Running with OpenSSL 1.0.2k-fips 26 Jan 2017

In centos edit the file /etc/yum.repos.d/zabbix.repo
And change the line (7 for centos 7change for your version ..., you can test the url in a browser)

baseurl=http://repo.zabbix.com/zabbix/[B]5.2[/B]/rhel/7/$basearch/

then launch the command

yum upgrade zabbix-agent

To verify agent version:

zabbix_agentd --version

[onlineramarao]

If any issue with .my.cnf issue, please add that file in the Userparameter file itself. That will definetly read the file contents of user name and password.

Ex:
# view /usr/local/etc/zabbix5/zabbix_agentd.conf.d/template_db_mysql.conf
UserParameter=mysql.ping[*], /usr/local/bin/mysqladmin --defaults-file=/usr/local/etc/zabbix5/.my.cnf ping