Discussion thread for official Zabbix Template DB MySQL

[max.ch.88]

Answering on my question. For working with multimaster replication need to change:
1. In template_db_mysql.conf:

UserParameter=mysql.replication.discovery[*], mysql -h"$1" -P"$2" -sNX -e "show slave status"
to
UserParameter=mysql.replication.discovery[*], mysql -h"$1" -P"$2" -sNX -e "show all slaves status"

and

UserParameter=mysql.slave_status[*], mysql -h"$1" -P"$2" -sNX -e "show slave status"
to
UserParameter=mysql.slave_status[*], mysql -h"$1" -P"$2" -sNX -e "show slave \"$3\" status"

2. In template in Replication discovery Preprocessing change Javascript to this:

var arr=[],a=""
while (a=value.match(/Master_Host.*>(.*)<.*/)) {
arr.push({"{#MASTERHOST}": a[1]})
value=value.substr(a.index+1)
}
return JSON.stringify(arr)

Unfortunately this solution is not universal. MySQL doesn't know command "show all slaves status".

[jbrodergtsi]

max.ch.88 Thanks for the reply!

I've looked at "/etc/passwd" as you suggested and found the following line already in the file:

zabbix:x:111:118::/var/lib/zabbix/:/usr/sbin/nologin

I would think this is what you are suggesting should be done. Am I wrong?

[Isuaven]

Unfortunately this solution is not universal. MySQL doesn't know command "show all slaves status".

Hmm, you are correct, its a feature of MariaDB... But in MariaDB "show slave status" not works when multi-database replication is used and returns empty answer..

[max.ch.88]

max.ch.88 Thanks for the reply!

I've looked at "/etc/passwd" as you suggested and found the following line already in the file:

zabbix:x:111:118::/var/lib/zabbix/:/usr/sbin/nologin

I would think this is what you are suggesting should be done. Am I wrong?

Everything should work. Let make a test: set EnableRemoteCommands=1 in zabbix_agentd.conf, restart agent and execute from the zabbix server zabbix_get -s <PI host> -k system.run["env"]. Show the output, please.

[jbrodergtsi]

max.ch.88 Sorry about the delay, got really busy.

Here's the output I get:

LANGUAGE=en_US.UTF-8
USER=zabbix
HOME=/nonexistent
LOGNAME=zabbix
JOURNAL_STREAM=8:12114
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin
INVOCATION_ID=349798de81ec47c19bb21b7f6021156b
LANG=en_US.UTF-8
PWD=/

[max.ch.88]

Hi jbrodergtsi
I've tested the zabbix-agent on Raspbian in this configuration:

zabbix_agentd -V
zabbix_agentd (daemon) (Zabbix) 4.2.7

cat /etc/os-release
PRETTY_NAME="Raspbian GNU/Linux 10 (buster)"

cat /etc/systemd/system/zabbix-agent.service.d/override.conf
[Service]
User=zabbix

grep zabbix /etc/passwd
zabbix:x:112:117::/var/lib/zabbix/:/bin/false

And I got the correct result - Homedir is /var/lib/zabbix/ as defined in /etc/passwd
zabbix_get -I 127.0.0.1 -s 192.168.6.75 -k system.run['env'] | grep HOME
HOME=/var/lib/zabbix/

Try to remove zabbix-agent and install it again.

[reedacus25]

I am seeing a strange behavior with MySQL slave/replication data.

Ubuntu 18.04
MySQL 5.7.27-0ubuntu0.18.04.1
zbx-agent 4.4.3-1+bionic

I was previously using the older 4.2 version of this check, and after updating one host to use the new 4.4 template, it broke the second host, so I updated it.

This second host is a slave to another host.

For the items/triggers:
mysql.slave_io_running["discoveredhostname"]
mysql.slave_sql_running["discoveredhostname"]

both have last values's of "YesYes"

I also see in the 'Zabbix Raw Value' Application an item:
mysql.slave_status["{$MYSQL.HOST}","{$MYSQL.PORT}","discoveredhostnam e"]
that spits out a big XML blob, of which includes:
<field name="Slave_IO_Running">Yes</field> <field name="Slave_SQL_Running">Yes</field> And my guess is somehow these are both getting lumped in together?

Anybody seen this or have an idea of how to fix it?
Only worrisome because it has an associated trigger in case slave replication goes down, and needs to be looked at, and I don't want to disable the trigger because of it.

TIA

[chojin]

As opposed to trying to fix the HOME environment, I would suggest putting the content of .my.cnf into /etc/zabbix/template_db_mysql_my.cnf and updating the userparams to add --defaults-extra-file=/etc/zabbix/template_db_mysql_my.cnf after every mysql/mysqladmin command.
This also omits problems with SELinux as by default SELinux denies file access for zabbix agent on /var/lib/...

Speaking of SELinux, I found that, in CentOS 7 I require extra SELinux rules to be able to use this template. SELinux sees the mysql process started by zabbix agent as a zabbix-agent process and forbids it from reading the mysql config and from connecting to the mysql socket.
I had to create and load this module to have SELinux agree with this template..:

module zabbix_agent_mysql 1.0;

require {
    type mysqld_etc_t;
    type zabbix_agent_t;
    type mysqld_t;
    class unix_stream_socket connectto;
    class file open;
    class file read;
}

#============= zabbix_agent_t ==============
allow zabbix_agent_t mysqld_etc_t:file open;
allow zabbix_agent_t mysqld_etc_t:file read;
allow zabbix_agent_t mysqld_t:unix_stream_socket connectto;

[max.ch.88]

As opposed to trying to fix the HOME environment, I would suggest putting the content of .my.cnf into /etc/zabbix/template_db_mysql_my.cnf and updating the userparams to add --defaults-extra-file=/etc/zabbix/template_db_mysql_my.cnf after every mysql/mysqladmin command.

Unfortunately this solution is not universal. The template will not work on OS Windows.
Thank you for solving the SELinux problem.

[chojin]

Unfortunately this solution is not universal. The template will not work on OS Windows.

True, but I assume on Windows you can change the --defaults-extra-file= path into C:\Program Files\Zabbix Agent\template_db_mysql_my.cnf ? And specify in the instructions that the user needs to change this path? Or provide 2 userparams.conf-files.. a version for *nix and a version for win ? Or even make it a parameter, and let the user specify this path in a host-level Macro in Zabbix ?

[max.ch.88]

True, but I assume on Windows you can change the --defaults-extra-file= path into C:\Program Files\Zabbix Agent\template_db_mysql_my.cnf ? And specify in the instructions that the user needs to change this path? Or provide 2 userparams.conf-files.. a version for *nix and a version for win ? Or even make it a parameter, and let the user specify this path in a host-level Macro in Zabbix ?

It is unfriendly to force the user to change something in the configuration files. And it is expensive to maintain two templates because of one key. Your suggestion to use a macro is probably a good idea. Thank you.
But the problem with SELinux in this case remains.

[chojin]

But the problem with SELinux in this case remains.

There is no SELinux problem reading that file if you place the my.cnf-file inside a directory where zabbix_agent is already allowed to read from by SELinux, as is /etc/zabbix (on CentOS at least)..

The other SELinux problem (where you need to allow mysql executed by the zabbix agent to be able to connect to the mysql socket and read the system-wide my.cnf) indeed remains, but I don't see why that should be a problem to just add this to the 'installation instructions' of this template. It won't work as it currently is on those distro's with SELinux enabled anyway. And you're already asking the user to manually put the userparams.conf on the machine, create a db user specifically for this template and to provide those credentials in a my.cnf file.

Ideally, of-course you want the user to only have to assign the template to a host, possibly have some customization/configuration macro's, and make it work like that. But that is already not the case here, so I don't think much harm will be done to add an extra instruction for SELinux enabled OS'es as the only other option I see is forcing users to just disable SELinux and that also would not be very friendly, in my opinion :-)

I myself solve all this with an Ansible playbook that puts the configs in place, creates the DB user, sets SELinux up according, restarts the agent to apply to config and at last assigns the template to the host in zabbix itself.

[max.ch.88]

The template description was updated.

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

[-sanches-]

Hello
A Zabbix server receive data from agent with log

172255:20200502:233142.112 error reason for "Zabbix server:mysql.status[Uptime]" changed: Value "Uptime 81576" of type "string" is not suitable for value type "Numeric (unsigned)"
172264:20200502:233231.147 error reason for "Zabbix server:mysql.status[Bytes_received]" changed: Preprocessing failed for: Bytes_received.1911572904
1. Failed: cannot calculate delta (speed per second) for value of type "string": cannot convert value to numeric type
172254:20200502:233232.618 error reason for "Zabbix server:mysql.status[Bytes_sent]" changed: Preprocessing failed for: Bytes_sent.6154555010
1. Failed: cannot calculate delta (speed per second) for value of type "string": cannot convert value to numeric type
172260:20200502:233233.619 error reason for "Zabbix server:mysql.status[Com_begin]" changed: Preprocessing failed for: Com_begin.748631
1. Failed: cannot calculate delta (speed per second) for value of type "string": cannot convert value to numeric type
172257:20200502:233234.619 error reason for "Zabbix server:mysql.status[Com_commit]" changed: Preprocessing failed for: Com_commit.748640
1. Failed: cannot calculate delta (speed per second) for value of type "string": cannot convert value to numeric type
172262:20200502:233235.619 error reason for "Zabbix server:mysql.status[Com_delete]" changed: Preprocessing failed for: Com_delete.80674
1. Failed: cannot calculate delta (speed per second) for value of type "string": cannot convert value to numeric type
172250:20200502:233236.149 error reason for "Zabbix server:mysql.status[Com_insert]" changed: Preprocessing failed for: Com_insert.264186
1. Failed: cannot calculate delta (speed per second) for value of type "string": cannot convert value to numeric type
172254:20200502:233238.622 error reason for "Zabbix server:mysql.status[Com_select]" changed: Preprocessing failed for: Com_select.6455838
1. Failed: cannot calculate delta (speed per second) for value of type "string": cannot convert value to numeric type
172262:20200502:233239.622 error reason for "Zabbix server:mysql.status[Com_update]" changed: Preprocessing failed for: Com_update.172752
1. Failed: cannot calculate delta (speed per second) for value of type "string": cannot convert value to numeric type
172257:20200502:233240.623 error reason for "Zabbix server:mysql.status[Questions]" changed: Preprocessing failed for: Questions.8564764
1. Failed: cannot calculate delta (speed per second) for value of type "string": cannot convert value to numeric type
172253:20200502:233242.780 error reason for "Zabbix server:mysql.status[Uptime]" changed: Value "Uptime 81636" of type "string" is not suitable for value type "Numeric (unsigned)"

What's mean?

[max.ch.88]

Hello
A Zabbix server receive data from agent with log
What's mean?

What version of Zabbix are you using?