Canot read mysql server from template

[LarryDeen]

Hi all,

From the command line on the zabbix server, I can read values from a mariadb host.

For example;
# zabbix_get -s db00.domain.com -k "mysql.questions"
95847870

However, I've configured the host on the zabbix server, trying the four available mysql templates but not once does the data come in.
The server is 5.0.7 on Centos 8 and the agent is 4.0.27 on Centos 7.

What am I missing?

[Zdenek_OMNISENSUIT]

Hello.

Please try this:

https://www.zabbix.com/integrations/mysql#tab:official2

There you can choose from different type of monitoring your MySQL/MariaDB database.
There is a good description and this is a good way how to start, very simple.


-------------------------
omnisensuit.com

[LarryDeen]

Hi,

Any chance you might know of a url/article that explains how to use this? Much of it seems to be for someone that already knows zabbix and I'm new to it.

The .my.cnf file contains the user/password to get into the DB server and the DB server DNS name is already set in the host config on the server. Do I really need to change the template macro settings? No other article mentions anything about this.

I also found this.
https://www.zabbix.com/documentation.../zabbix_agent2

I followed several examples of how to set this up and am using the Template DB MySQL by Zabbix agent 2 but nothing comes in. Not one article mentions anything about having to change macro's. However, if I edit the template, I won't be able to use it with any other db servers no? Am I supposed to clone it?

As mentioned, I can read the values of the DB server from the zabbix server command line.

# zabbix_get -s db00.domain.com -k "mysql.uptime"
179417

This seems to imply that the zabbix agent is working as it should bud something is missing in the zabbix servery GUI configuration.

[Zdenek_OMNISENSUIT]

Hello.

Oki, please try setup with agent2. It's more simple.
You will create zabbix user via documentation (you already have).
You will attache the Template for MySQL and agent2 to the host.
You will create this macros:
{$MYSQL.DSN} - tcp://localhost:3306
{$MYSQL.USER} - your zabbix databaze monitoring user
{$MYSQL.PASSWORD} - your zabbix databaze monitoring password
Macros you need to setup are palced in Host configuration page in Macros tab.
That's all.
Or you can set this values inside the agent2 config file:

https://git.zabbix.com/projects/ZBX/repos/zabbix/browse/src/go/plugins/mysql/README.md

(but the first option will be fine for now)
So, remove your current setup and do it again from scratch.
It's better than fix some.
(in this configuration you do not need setup my.cnf etc)


-------------------------
omnisensuit.com

[LarryDeen]

Thank you. I'll do as you suggest.

[LarryDeen]

I have done as you have suggested and the agent is now running. The only part I cannot solve is the macro.
Am I supposed to make a clone of the mysql agent2 template?
Meaning, when I edit the macro, am I editing for this host only or changing the default mysql agent2 template?

[LarryDeen]

I have spent much of this day just trying to find the information above and it is like a grand mystery, not obvious at all what one should do. I cannot find any articles, installation information that says anything about editing the default template or needing to make a clone or anything else. Everything makes it sound so simple yet it must frustrate a lot of people into giving up on zabbix. I am super frustrated myself at this point.

[tim.mooney]

Am I supposed to make a clone of the mysql agent2 template?
Meaning, when I edit the macro, am I editing for this host only or changing the default mysql agent2 template?

The piece of information you may be missing is that every host that you create has a "Macros" tab where you can set host-specific macros that override any macros with the same name that are inherited either from "Global macros" or any templates that are applied to the host.

So no, you do not and should not copy the template and make changes to the macros in the copy. What you do is

1. Apply (add) the "MySQL Agent 2" template to the host that you want to use for monitoring MySQL. This will add all the items and triggers and other stuff that are part of the template to the host, and (until you do the Step 2) they will be using the default values from the template for all the macros that the template needs. Some of the template macro values might be fine, but some likely are not.
2. Once you've applied the template to the host, go to the host and click on the "Macros" tab for that specific host. It's here that you can override the default macro values that are inherited from the template (and from global macros) with ones that are correct for this particular host. It may help to make things clearer if you click on the "Inherited and host macros" link, just to see all the macros that are set and where their values are originating from. That's also an easy way to get the names of macros, so you can copy e.g. {$MYSQL.HOST} from the "Inherited and host macros" then click back to just "host macros", click Add to add a host-specific override, paste in the macro name you want to override and then put in its value and click update. Then, just to verify, click back to the "Inherited and host macros", to see that now the macro is getting its value from the host, rather than the default from the template.

Well designed templates use lots of macros. The template has hopefully useful defaults for every macro, but even if it doesn't, every macro is a place where you can customize thresholds or other things on a host-by-host basis, without having to edit or duplicate templates.

[LarryDeen]

Now it all seems to make sense. I was nervous that editing anything in the template, even under that specific host would change it for any other hosts I use it with.
The macros are on a per host basis so I'll continue with that.

Yes, I have the agent2 on the DB server. Since it was a centos7, I had to compile it and the paths are always different in most articles you read .
On centos-8, it seems to be available as an RPM package.

It's up and running and it not accepting commands from the zabbix server command line so I'm guessing it's because there is nowhere the credentials are mentioned now while with the previous agent version, they were in the .my.cnf file.

zabbix_get -s db00.domain.com -k "mysql.questions"
ZBX_NOTSUPPORTED: Unknown metric mysql.questions

However, "agent.version" works and returns the right value, 5.0.2.

I'm guessing it can only work from the command line if there is a .my.cnf type file and will work from the GUI once I enter the credentials in the macro.

Thank you to all who have helped. I'm sure this will be helpful to someone else going through this.

[LarryDeen]

All good now and I've learned more about Zabbix so will play with this and try to learn more.
Thanks everyone.

[cyber]

If you edit template, it affects all hosts, where it is installed. If you override macro on host level, you only change it for this particular host. (https://www.zabbix.com/documentation...os/user_macros). Host-> macros -> inherited -> change.
It is always a good idea to adjust templates to you own need, so I would make a clone of it anyway and modify and experiment with that. Keeps a fresh copy to start again..

Just a question, did you install agent2 also? it is different from usual agent..

[LarryDeen]

Hi,

I moved the DB server to a new server, this time running rocky linux 8.7.
I re-installed agent2 and all seem to work but see this error in the zabbix agent log.

2021/10/18 13:44:02.578709 using configuration file: /etc/zabbix/zabbix_agent2.conf
2021/10/18 13:44:02.578840 cannot initialize user parameters: cannot register user parameter "mysql.ping,HOME=/etc/zabbix mysqladmin ping | grep -c alive": key already used

The parameters file looks like this.
I cannot find much info on what else I can get into zabbix server as in terms of mariadb stats.

# 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=/etc/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[*],echo "select sum($(case "$3" in both|"") echo "data_length+index_length";; data|index) echo "$3_length";; free) echo "data_free";; esac)) from information_schema.tables$([[$

#Default below
UserParameter=mysql.ping,HOME=/etc/zabbix mysqladmin ping | grep -c alive
UserParameter=mysql.uptime,HOME=/etc/zabbix mysqladmin status | cut -f2 -d ":" | cut -f1 -d "T" | tr -d " "
UserParameter=mysql.threads,HOME=/etc/zabbix mysqladmin status | cut -f3 -d ":" | cut -f1 -d "Q" | tr -d " "
UserParameter=mysql.questions,HOME=/etc/zabbix mysqladmin status | cut -f4 -d ":"|cut -f1 -d "S" | tr -d " "
UserParameter=mysql.slowqueries,HOME=/etc/zabbix mysqladmin status | cut -f5 -d ":" | cut -f1 -d "O" | tr -d " "
UserParameter=mysql.qps,HOME=/etc/zabbix mysqladmin status | cut -f9 -d ":" | tr -d " "
UserParameter=mysql.version,mysql -V

[Atsushi]

What is the detailed version of Zabbix you are using? mysql.ping is built into the agent for newer versions of Zabbix Agent 2, so you don't need to configure it in UserParameter.

[LarryDeen]

Server is 5.0.7 and the agent is agent2 5.0.9.
However, it started working and now it stopped and I don't see any reason since I wasn't making changes once it started working.

Here is all the info on the agent side.

In the /etc/zabbix I have;

-rw-r--r-- 1 root root 123 Jan 23 2021 .my.cnf (I read this is no longer needed?)
-rw-r--r-- 1 root root 22126 Oct 17 20:11 zabbix_agent2.conf
drwxr-xr-x 2 root root 24 Oct 18 13:41 zabbix_agent2.d

In the la zabbix_agent2.d/
-rw-r--r-- 1 root root 1914 Jan 23 2021 mysql.conf (this is the params file shown above)

Nothing needed to change on the server side when I got this going.

In the agent log, this is the error.

2021/10/19 08:24:43.078455 Starting Zabbix Agent 2 [db00.domain.com]. (5.0.9)
2021/10/19 08:24:43.079541 OpenSSL library (OpenSSL 1.1.1g FIPS 21 Apr 2020) initialized
2021/10/19 08:24:43.079645 using configuration file: /etc/zabbix/zabbix_agent2.conf
2021/10/19 08:24:43.079776 cannot initialize user parameters: cannot register user parameter "mysql.ping,HOME=/etc/zabbix mysqladmin ping | grep -c alive": key already used

[LarryDeen]

Now I'm trying the newer template but still nothing coming in.
Did something change on the server side that I need to update to match this version of the agent?

# cat template_db_mysql.conf
# 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=/etc/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[*],echo "select sum($(case "$3" in both|"") echo "data_length+index_length";; data|index) echo "$3_length";; free) echo "data_free";; esac)) from information_schema.tables$([[$

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"

Odd

# systemctl status zabbix-agent2.service
● zabbix-agent2.service - Zabbix Agent 2
Loaded: loaded (/usr/lib/systemd/system/zabbix-agent2.service; enabled; vendor preset: disabled)
Active: inactive (dead) (Result: exit-code) since Tue 2021-10-19 12:32:23 MST; 33min ago
Process: 1010517 ExecStop=/bin/kill -SIGTERM $MAINPID (code=exited, status=0/SUCCESS)
Process: 1211512 ExecStart=/usr/sbin/zabbix_agent2 -c $CONFFILE (code=exited, status=1/FAILURE)
Main PID: 1211512 (code=exited, status=1/FAILURE)

Searching the net on this is not coming up with very much.