Discussion thread for official Zabbix Template DB MySQL

[stissot]

Hello there,

I'm using Zabbix 5.0 LTS with the Agent2 and the MySQL plugin to monitor a MariaDB 10.3 server.

One request I have is to monitor which processes are active, not only the numbers of threads, but the actual list of processes that are returned by the SHOW PROCESSLIST query, with their state and users.

Would it be possible to add an item that make this information available in JSON format through the agent ? This would make possible to generate graphs that shows the numbers of threads per user

As an alternative, add an item that allows to execute a custom query like available in the Oracle plugin

mysql.custom.query[<connString>,<user>,<password>,<database>, queryName, <args…>]

[Heidistein]

Saluto!
At the moment I am adding a bunch of extra metrics to the template, namely the query-cache. I would like to share this tedious work with all, but very uncertain how. Any pointers?

[stepassia]

Hi! I can't import the last version of template. After select file, the browser remain in loading status... any error on erro log... Anyone can help me? I've zabbix 5.4.
Thanks in advance
Stefano

[leodigi]

This seems silly to ask, but the agent is not installed in the default location as suggested on CentOS 8. Where can I find it, or how can I find it?
Thanks in advance.

[NigelHorne]

I'm having trouble getting the mysql plugin for zabbix 5.4.3 to work. I'm seeing graphs but no data. I've followed the instructions at https://www.zabbix.com/integrations/mysql.

Both the Zabbix server and the MySQL servers are running Linux.

If I look at zabbix_server.log I see:

ERROR 1045 (28000): Access denied for user 'nhorne'@'localhost' (using password:
^

That's clearly wrong as it's trying to use my username (nhorne) rather than the user I put into the .my.cnf file on the MySQL machine.

# cat /var/lib/zabbix/.my.cnf
[client]
user='zbx_monitor'
password='xxxxx'

[onlineramarao]

I'm having trouble getting the mysql plugin for zabbix 5.4.3 to work. I'm seeing graphs but no data. I've followed the instructions at https://www.zabbix.com/integrations/mysql.

Both the Zabbix server and the MySQL servers are running Linux.

If I look at zabbix_server.log I see:

ERROR 1045 (28000): Access denied for user 'nhorne'@'localhost' (using password:
^

That's clearly wrong as it's trying to use my username (nhorne) rather than the user I put into the .my.cnf file on the MySQL machine.

# cat /var/lib/zabbix/.my.cnf
[client]
user='zbx_monitor'
password='xxxxx'

--
I had the same issue in freebsd jail.
Just update the template_db_mysql.conf with extra option (Repeat the same for all the lines)

UserParameter=mysql.ping[*], /usr/local/bin/mysqladmin --defaults-file=/usr/local/etc/zabbix5/.my.cnf ping

[astol]

Would be great to monitor the DB size

[leodigi]

Hi, I'm having problems with monitoring MARIADB in another host (it isn't zabbix server).

All configurations are okay but i get this error message:


ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (13)

Someone knows what can i do to solve this problem?

I have the same issue CentOS 8. I can see I dont have the folder in this location. how did you fix it?

[Crewze]

I posted this on a general forum and got no help. Just found this section pertaining to the mySQL template. Hopefully someone can help me here.

I have two Ubuntu Servers running 20.04. I am running the Zabbix Server on a Raspberry Pi. I am using version 6.2 of Zabbix. I can collect basic stats from both Ubuntu Servers. I can collect mySQL data from one, but no data appears on the other. Both are running 6.2 agents. I cloned the host files (and and then changed the IP) to ensure they are the same. Both templates, agent config and .my.cnf files are the same.

The really strange thing is the error on the mySQL page is for no access to zabbix@localhost. I believe this is only used by the Zabbix Server not the agents. See screenshot below and error.

[Bob49122]

Hello,

I serached during some days but always the same result.
My check for dockerized mysql do not run.

Host: Debian 11 -> IP= 192.168.1.90 and the agent, on the Debian, is up and running.
docker containers:
-zabbix-server 172.25.0.1
-zabbix-agent 172.25.0.4
-mysql 172.25.0.2
-zabbix-web-nginx 172.25.0.3

In zabbix server GUI, I put the zabbix IP for all servers in the Agent tab and port is 10050;

The standard linux check works for debian 11 and the 4 containers.

I followed the procedure gave by zabbix.


Install Zabbix agent and MySQL client. If necessary, add the path to the mysql and mysqladmin utilities to the global environment variable PATH.
Copy template_db_mysql.conf into the folder with Zabbix agent configuration (/etc/zabbix/zabbix_agentd.d/ by default). Don't forget to restart Zabbix agent.
Create a MySQL user for monitoring (<password> at your discretion):

CREATE USER 'zbx_monitor'@'%' IDENTIFIED BY '<password>';
GRANT REPLICATION CLIENT,PROCESS,SHOW DATABASES,SHOW VIEW ON *.* TO 'zbx_monitor'@'%';

For more information, please see MySQL documentation https://dev.mysql.com/doc/refman/8.0/en/grant.html

Create .my.cnf in the home directory of Zabbix agent for Linux (/var/lib/zabbix by default ) or my.cnf in c:\ for Windows. The file must have three strings:

[client]
user=zbx_monitor
password=password


The mysqladmin and mysql are installed on Debian 11 and I can access to zabbix database.

I put in the MACRO the MYSQL.PASSWORD, MYSQL.USER, MYSQL.HOSTNAME informations.

I linked the template MYSQL by zabbix agent in the Debian 11 host 192.168.1.90 because the mysql client is installed on it.
in case,
I linked also the template MYSQL by zabbix agent in the mysql server 172.25.0.2.

Nothing appears.

What's wrong ?

thanks a lot.




and Crewze, I got the same screen shot than you have.

[yurtesen]

Hi,
1- Is there any plans to provide an zabbix-agent2 "active" version?
2- Will you add discard preprocessing to all the items? Now some items have it only? Why not all?
Thanks!

[lahiruabeysinghe]

If there's anyone who's having the same issue as noname.xml:1: parser error : Start tag expected, '<' not found, you might want to check the permission on .my.cnf in /var/lib/zabbix. I faced the same issue and followed above hints. Basically, change the ownership of .my.cnf to zabbix and reduce permission to read-only. It only works when permission is set less.

[cheneric]

Hi,
1- Is there any plans to provide an zabbix-agent2 "active" version?

https://git.zabbix.com/projects/ZBX/repos/zabbix/browse/templates/db/mysql_agent2

[Daniel Black]

Hi,

Just for the documentation on mysql_agent / mysql_agent2 that refers to needing REPLICATION CLIENT as a grant. From MariaDB-10.5, to run SHOW SLAVE/REPLICA STATUS, the REPLICA MONITOR is the grant required.

Thanks for the agent2.

[Daniel Black]

Also note for those using MariaDB-11.0+ because a) we deprecated the use of mysql/mysqladmin names (by outputting a stderr warning) in favour of mariadb/mariadb-admin the zabbix-agent (version <2), which takes both stdout and stderr together as its result, a change with be required to continue monitoring your MariaDB server.

This can be:
* using agent2 as its connection is straight from the agent and works beautifully; or
* modify the mysql_agent configuration in the configuration to use mariadb, mariadb-admin ; or
* with mysql_agent redirect stderr to null by appending "2>/dev/null" (without quotes) to all UserParameter, and this will be compatible if you monitor a mix of MySQL and MariaDB servers.

Sorry for the interruption caused by the change, as MariaDB starts to differ from MySQL it made sense to start at a pretty basic level to stop looking like it.

I hope to have time to make it back here and make some more contributions to the improved metrics of monitoring like:

* rate of mysql.opened_tables vs mysql.open_tables (needs an alert something like buffer_pool_efficiency for alerting of insufficient table cache)
* mult-source in replication (more than one source/channel)

based on user reports and experience in our infrastructure.

[marianogedisman]

Following this guide:

https://www.zabbix.com/integrations/mysql#mysql_agent

Getting this error:

9161:20241014:040938.329 Requested [mysql.get_status_variables["10.194.0.3","3306"]]

9161:20241014:040938.329 Sending back [ZBX_NOTSUPPORTED: Unsupported item key.]

9163:20241014:040940.330 Requested [mysql.ping["10.194.0.3","3306"]]

  9163:20241014:040940.330 Sending back [ZBX_NOTSUPPORTED: Unsupported item key.]

​

Obviously Zabbix server is not understanding the item keys, but why? I've done everything by the book.