Discussion thread for official Zabbix Template DB MySQL

[AlexL]

This thread is designed to provide grounds for discussion of the official Zabbix Template for MySQL database.
The template and details of the template are available in GIT repository.
Agent https://git.zabbix.com/projects/ZBX/...db/mysql_agent
ODBC: https://git.zabbix.com/projects/ZBX/.../db/mysql_odbc

Zabbix is always looking for ways to improve our services and to make our users happier.
We pride ourselves on doing our best each and every day, but we know that there is always something more to learn.
We would like to hear back from you to know what have you liked and what would you improve in the template.

[kloczek]

Kind of suggestions:
- drop "MySQL" from all items names (and "PostgreSQL" in case of PostgreSQL template as well)
in "Last data" view all items are grouped by Application name and by this is known that those items are about MySQL. I case of raw Items view all items names are prefixed by template/LLD/master item name.
- in template_db_mysql.conf in comment is:

#template_db_mysql.conf created by Max Chudinov for Zabbix 4.2 "Template DB MySQL"
#For OS Linux: You need create .my.cnf in zabbix-agent home directory (/var/lib/zabbix by default)
#For OS Windows: You need add PATH to mysql and mysqladmin and create my.cnf in %WINDIR%\my.cnf,C:\my.cnf,BASEDIR\my.cnf https://dev.mysql.com/doc/refman/5.7/en/option-files.html
#The file must have three strings:
#[client]
#user=zbx_monitor
#password=<password>

Placing password in global my.cnf file is not a good practice
Even for 5.7 is recommended to use mysql_config_editor command and not use plain text ~/.my.cnf
- 5.7 by default uses old names some variables representing state of the engine. It means that this template not well suited for future MySQL versions. In my template https://github.com/kloczek/zabbix-te...ervice%20MySQL I've switched to use new convention by show_compatibility_56=OFF. Correct use that param is even guarded by monitoring that variable and activate trigger recalling to show_add compatibility_56=OFF. Using that guarantees that the same template could be used across more new versions of the MySQL.
https://github.com/kloczek/zabbix-te...%20MySQL#notes

[orwin.lopes]

Hi i am new & have zabbix 4.2.3 running... i have configured the .my.sql.cnf file & template_db_mysql.conf. Do i need to import the template as well ...when i try to import the template it gives an error

• Cannot read XML: (41) Specification mandate value for attribute data-initially-rendered [Line: 43 | Column: 73].

[AlexL]

Hi i am new & have zabbix 4.2.3 running... i have configured the .my.sql.cnf file & template_db_mysql.conf. Do i need to import the template as well ...when i try to import the template it gives an error

• Cannot read XML: (41) Specification mandate value for attribute data-initially-rendered [Line: 43 | Column: 73].

Try downloading the raw file from git or clone the repo it seems that the file you have downloaded is corrupt in a way.
Direct link for XML

https://git.zabbix.com/projects/ZBX/repos/zabbix/raw/templates/db/mysql/template_db_mysql.xml?at=refs%2Fheads%2Fmaster

[orwin.lopes]

Ok thanks...but i cannot see any data....wher to look for logs ? /var/log/zabbix ?

[orwin.lopes]

discovery says unsupported item key

[shoemocker]

So I just got Zabbix 4.4 installed and it has this template as the official MySQL DB template. However for the life of me I have not be able to make it work. I have set everything up correctly according to the guide and nothing seems to work. It seems like there is something wrong with the actual template.

I have tried MySQL 8, MariaDB 10.3, MariaDB 10.4

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

If you look at zabbix01 it shows some random issues that I have no idea what they mean.

I can confirm that I have the .my.cnf in place and it works.

--------------------------------------------------------------------------------------------------------------------------------
[zabbix@zabbix-test ~]$ pwd
/var/lib/zabbix

[zabbix@zabbix-test ~]$ ls -alh
-rw-------. 1 zabbix zabbix 67 Oct 10 19:09 .my.cnf

[root@zabbix-test zabbix]# sudo su - zabbix
Last login: Thu Oct 10 19:08:59 CDT 2019 on pts/0

[zabbix@zabbix-test ~]$ cat .my.cnf
[client]
user='zabbix'
password='m&#NkgO#zo&9GQ6$znzn51bdLBg84yDR'

[zabbix@zabbix-test ~]$ mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 551
Server version: 10.3.11-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>
--------------------------------------------------------------------------------------------------------------------------------

Any assistance with this would be terrific as I would love to use 4.4 but this is preventing me from moving forward.

[nadenisov]

So I just got Zabbix 4.4 installed and it has this template as the official MySQL DB template. However for the life of me I have not be able to make it work. I have set everything up correctly according to the guide and nothing seems to work. It seems like there is something wrong with the actual template.

I have tried MySQL 8, MariaDB 10.3, MariaDB 10.4

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

If you look at zabbix01 it shows some random issues that I have no idea what they mean.

I can confirm that I have the .my.cnf in place and it works.

--------------------------------------------------------------------------------------------------------------------------------
[zabbix@zabbix-test ~]$ pwd
/var/lib/zabbix

[zabbix@zabbix-test ~]$ ls -alh
-rw-------. 1 zabbix zabbix 67 Oct 10 19:09 .my.cnf

[root@zabbix-test zabbix]# sudo su - zabbix
Last login: Thu Oct 10 19:08:59 CDT 2019 on pts/0

[zabbix@zabbix-test ~]$ cat .my.cnf
[client]
user='zabbix'
password='m&#NkgO#zo&9GQ6$znzn51bdLBg84yDR'

[zabbix@zabbix-test ~]$ mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 551
Server version: 10.3.11-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>
--------------------------------------------------------------------------------------------------------------------------------

Any assistance with this would be terrific as I would love to use 4.4 but this is preventing me from moving forward.

I have the exact same problem with the only difference that I have an update to 4.4 zabbix installation. Any help would be appreciated.

[Atsushi]

I checked Zabbix 4.4.0 userparameter_mysql.conf, but it doesn't explicitly set HOME environment variable. This may prevent Zabbix agent from browsing the .my.cnf file placed in the zabbix user's home directory.

[AlexL]

I checked Zabbix 4.4.0 userparameter_mysql.conf, but it doesn't explicitly set HOME environment variable. This may prevent you from seeing the .my.cnf file placed in the zabbix user's home directory.

Thank you for your report. The issue will be resolved in future minor versions of agent packages.
There is an issue on JIRA you can use to track progress: https://support.zabbix.com/browse/ZBX-16629

[shoemocker]

After reviewing the ZBX-16629 issue you posted I was able to temporarily fix this issue by adding the following to /usr/lib/systemd/system/zabbix-agent.service under [Service]

User=zabbix
Group=zabbix

This looks to have fixed the home environment variable mentioned above. Everything is working great now with the template!

Thanks for all the help!

[jbrodergtsi]

Hi!

So I've looked at the JIRA post that AlexL provided... And it didn't help.

My Zabbix server and client are on a Raspberry Pi running Raspbian Buster and the path for the "zabbix-agent.service" is slightly different: /lib/systemd/system/zabbix-agent.service

When I go into the file to edit it the following lines already exist:

User=zabbix
Group=zabbix

However when I run the command as suggested at the beginning of the post "sudo -u zabbix env | grep HOME" --OR-- "sudo -Hu zabbix env | grep HOME"
I get the following response:

HOME=/nonexistent

So, I'm still stuck with this particular issue.

Any ideas on how to fix this?

Thanks much in advance!

[Isuaven]

Hi!
Have somebody fork of this template with support of multimaster replication? This is dont work with it

[Isuaven]

Hi!
Have somebody fork of this template with support of multimaster replication? This is dont work with it

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)

[max.ch.88]

Hi!

So I've looked at the JIRA post that AlexL provided... And it didn't help.

My Zabbix server and client are on a Raspberry Pi running Raspbian Buster and the path for the "zabbix-agent.service" is slightly different: /lib/systemd/system/zabbix-agent.service

When I go into the file to edit it the following lines already exist:

User=zabbix
Group=zabbix

However when I run the command as suggested at the beginning of the post "sudo -u zabbix env | grep HOME" --OR-- "sudo -Hu zabbix env | grep HOME"
I get the following response:

HOME=/nonexistent

So, I'm still stuck with this particular issue.

Any ideas on how to fix this?

Thanks much in advance!

Check the home dir of the user zabbix into /etc/passwd.

[bigadmin]

Thank you max.ch.88 , that was a great hint.
The home directory in /etc/passwd is /var/lib/zabbix
I created the file .my.cnf in that directory

One important thing before it can work is you need change the ownership of the file to zabbix user "chown zabbix.zabbix .my.cnf"

And all things work perfectly.

Thank you again for the hint.