Zabbix hostname: localhost or local ipaddress

[dunnsland]

Greetings All,

I have what I think should be a simple question.
I am trying to add mysql monitoring into my zabbix instance. I have done everything that is in the tutorial but when I open the item MYSQL.Version, and click on get value, mine returns nothing.
What I noticed is the local ipaddress is listed in the host address field. If I change this manually to localhost, and click get value, then data is returned.
How can I have zabbix look at both the localhost and the local ipaddress. Or how can force it to look at localhost?

Thanks
Lawrence

[dunnsland]

OK, after digging around and trying various options I have now found out how to resolve this issue.

It is important to follow the steps included in the template.
So, create the file /etc/zabbix/zabbix_agentd.d/template_db_mysql.conf

Add the following content.
UserParameter=mysql.ping[*],mysqladmin -h"$1" -P"$2" ping
UserParameter=mysql.get_status_variables[*],mysql -h"$1" -P"$2" -sNX -e "show global status"
UserParameter=mysql.version[*], mysqladmin -s -h"$1" -P"$2" version
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"


I have no clue why, but the template install instructions tell you to use the user 'zbx_monitor'@'localhost
This is NOT correct and it does not work. All errors refer to the following user. 'zabbix'@'localhost'
I created the user 'zabbix'@'localhost' into the database.

CREATE USER 'zabbix'@'localhost' IDENTIFIED BY '<PASSWORD>';
GRANT REPLICATION CLIENT,PROCESS,SHOW DATABASES,SHOW VIEW ON *.* TO 'zabbix'@'localhost';

No sure if this is relevant, but I changed the file .my.cnf as follows. Not sure if the head [mysqld] means or does anything.
NB: remove the single quotes.

[mysqld]
[client]
protocol=tcp
user=zabbix
password=<password>

make sure that you update the /etc/passwd file to add the zabbix user $HOME directory.

zabbix:x:115:122::/var/lib/zabbix:/usr/sbin/nologin

after saving the file, set the correct permissions.

sudo chmod 640 /var/lib/zabbix/.my.cnf

to test the database connection as the zabbix user, use the following command.

sudo -u zabbix mysqladmin -h"127.0.0.1" -P"3306" ping

After doing all of the above, my data started being loaded into zabbix_server and the dachboard started being populated.

Lawrence

[cyber]

It may aswell be the mysql config... Where it listens, localhost or also "outside"... I think it is a default behaviour to listen on localhost only...
as I see (never used the template myself)... it uses following to get version

mysql.version["{$MYSQL.DSN}","{$MYSQL.USER}","{$MYSQL.PASSWORD}"]

Docs say /https://www.zabbix.com/integrations/mysql#mysql_agent2)

{$MYSQL.DSN}

System data source name such as <tcp://host: port or unix:/path/to/socket)/>

So, if you set it to "tcp://localhost:3360" (or whatever that mysql port was), what happens?

[dunnsland]

cyber
Thank you for responding but I am no clearer in getting a solution.
I am also not using zabbix_agentd2 I am using zabbix_agent 6.0.38.
What I am not clear on, if I open an item that is related to the zabbix_agent,
the item always opens with the hostname being the ipaddress allocated to the VM.
If I then change the ipaddress to localhost, then I can run the test successfully.
if I go Configuration
/ Hosts
/ {select items for the VM in question} /
/ filter items for only mysql
/ select item type Zabbis Agent
/ select either mysql.version or mysql.ping
/ both of these items open with the ipaddress of the VM, if I run a test, both fail. See images below.
If i then change the hostname to localhost, then both tests are successful. See images below

mysql.status

with ipaddress



with localhost




Mysql Version

with ipaddress




with localhost



I hope that this is clearer ...

Also I hope that if I can force the localhost to be used by zabbix or if I can have mysql accept queries from the ipaddress.

Thanks
Lawrence

[cyber]

Well I only assumed you use the OOB options... not describing what you actually use, leads to misinterpretations..
I still have no clue, what method you use for all of this... TBH, I have no mysql DB around me to test anything also, so I better shut up.

[dunnsland]

cyber
Much appreciate your trying to help, I am very sorry for the misleading info, but I am very new to this all and am not really clear what is relevant and not.

I am not sure if I have missed something with the implementation of the template, but what I do not understand is the following.
The instructions uses the db user name zbx_monitor, but looking at the error below, it seems to be having issues connecting with the user zabbix@localhost. Should I add this with the necessary permissions onto teh database and not use zbx_monitor

Preprocessing failed for: ERROR 1045 (28000): Access denied for user 'zabbix'@'localhost' (using password: NO)
1. Failed: cannot extract XML value with xpath "/resultset/row[field/text()='Created_tmp_files']/field[@name='Value']/text()": cannot parse xml value: Start tag expected, '<' not found


Preprocessing failed for: ERROR 1045 (28000): Access denied for user 'zabbix'@'localhost' (using password: NO)
1. Failed: cannot extract XML value with xpath "/resultset/row[field/text()='Innodb_os_log_written']/field[@name='Value']/text()": cannot parse xml value: Start tag expected, '<' not found

Thanks
Lawrence

[dunnsland]

cyber I am pretty sure that it is something that I have done incorrectly ...

[cyber]

You still don't want to say anything about what template you are using? What method you are using (now I just assume ODBC??, which I don't know anything about... )

[dunnsland]

cyber
I am sorry ... I am using the following template.


running on zabbix_server 6.0.38
and zabbix_agent 6.0.38

According to the instructions, this should work without much intervention.
Only providing the db privileges and importing the template. by default there is already the template within the template library.
After have the original issues, I deleted the template, went online and downloaded the template for the zabbix_server 6.0 and then imported that into the system.
This did not change anything.
The items that are of zabbix_agent type, failed if the vm ipaddress is loaded, but if I change that to localhost then the item test work.
All items that have the type Dependent item, all show errors. The errors are all related to the error I showed earlier with the zabbix@localhost on the DB.
I am lost.
IS there anything more that you need from me to clarify?
Lawrence

[dunnsland]

zz0.yscx8nynzezz\
I have carried out my tests both as the root user running mysqladmin as the user zabbix and the user zbx_monitor.
Both commands connect to the database and delivered results, Only I cannot see how to run the test without using a password.




I also su to zabbix and ran the same mysqladmin commands successfully.

Something that I have seen, with all the errors showing on the zabbix dashboard, all port references are indicated with a capital P instead of the lowcase p to select a port on the database.
(not sure if this is relative. Any ideas would be most welcome.)

[solution]

in mysql user@localhost is different from [email protected]
check documentation and add as needed:
https://dev.mysql.com/doc/refman/8.4...on-access.html

Wellington

[dunnsland]

OK, after digging around and trying various options I have now found out how to resolve this issue.

It is important to follow the steps included in the template.
So, create the file /etc/zabbix/zabbix_agentd.d/template_db_mysql.conf

Add the following content.
UserParameter=mysql.ping[*],mysqladmin -h"$1" -P"$2" ping
UserParameter=mysql.get_status_variables[*],mysql -h"$1" -P"$2" -sNX -e "show global status"
UserParameter=mysql.version[*], mysqladmin -s -h"$1" -P"$2" version
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"


I have no clue why, but the template install instructions tell you to use the user 'zbx_monitor'@'localhost
This is NOT correct and it does not work. All errors refer to the following user. 'zabbix'@'localhost'
I created the user 'zabbix'@'localhost' into the database.

CREATE USER 'zabbix'@'localhost' IDENTIFIED BY '<PASSWORD>';
GRANT REPLICATION CLIENT,PROCESS,SHOW DATABASES,SHOW VIEW ON *.* TO 'zabbix'@'localhost';

No sure if this is relevant, but I changed the file .my.cnf as follows. Not sure if the head [mysqld] means or does anything.
NB: remove the single quotes.

[mysqld]
[client]
protocol=tcp
user=zabbix
password=<password>

make sure that you update the /etc/passwd file to add the zabbix user $HOME directory.

zabbix:x:115:122::/var/lib/zabbix:/usr/sbin/nologin

after saving the file, set the correct permissions.

sudo chmod 640 /var/lib/zabbix/.my.cnf

to test the database connection as the zabbix user, use the following command.

sudo -u zabbix mysqladmin -h"127.0.0.1" -P"3306" ping

After doing all of the above, my data started being loaded into zabbix_server and the dachboard started being populated.

Lawrence