mjhjmtu

Please explain the full use of this template.link template

I searched very much on Google, I have configured as described but i can't use this yet.
Can you fully explain?
i have two centos 7. zabbix version 3.4.
config zabbix-agent:
/etc/zabbix/zabbix_agentd.d/userparameter_mysql.conf

UserParameter=mysql.status[*],echo "show global status where Variable_name='$1';" | HOME=/etc/zabbix mysql -N | awk 'print $$2'

UserParameter=mysql.size[*],bash -c '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$([[ "$1" = "all" || ! "$1" ]] || echo " where table_schema="$1"")$([[ "$2" = "all" || ! "$2" ]] || echo "and table_name="$2"");" | HOME=/etc/zabbix mysql -N'

UserParameter=mysql.ping,HOME=/etc/zabbix mysqladmin ping | grep -c alive
UserParameter=mysql.version,mysql -V
UserParameter=mysql[*],/etc/zabbix/mysql_check.pl $1 $2 $3 $4

i create user mysql.

CREATE USER 'david'@'%' IDENTIFIED BY 'david123';
 ---------------------------------------------------------------
MariaDB [(none)]> select user,host from mysql.user;
+--------------+-----------+
| user | host |
+--------------+-----------+
| cluster_user | % |
| david | % |
| dav | 127.0.0.1 |
| root | 127.0.0.1 |
| root | ::1 |
| dav | db1 |
| root | db1 |
| root | db3 |
| dav | localhost |
| fazeli | localhost |
| root | localhost |
+--------------+-----------+
11 rows in set (0.00 sec)
------------------------------------------------
 MariaDB [(none)]> show databases; 
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
 ------------------------------------------------
 MariaDB [(none)]> show grants for 'david'@'%'; 
+---------------------------------------------------------------------------------------------------------------+
| Grants for david@% |
+---------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'david'@'%' IDENTIFIED BY PASSWORD '*76E1DED67C484EF41716EFA3545C12098380B713' |
+---------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
------------------------------------------------

i create .my.cnf in /etc/zabbix

[mysql]
user=david
password=david123
[mysqladmin]
user=david
password=david123

my perl script mysql_check.pl on /etc/zabbix

use strict;
use Data::Dumper;
use DBI;
my $user = shift;
my $password = shift;
my $type = shift;
my $search_like = shift;
my $dsn = "DBI:mysql:database=mysql;host=db2";
my $tmp_file = '/tmp/zbx_mysql.status';
my $zbx_sender_file = '/usr/bin/zabbix_sender';
my $zabbix_sender = '/usr/bin/zabbix_sender';

i set macro.(look screenshot).

permission files:

[root@db2 zabbix]# ll
total 92
-rw-r--r-- 1 root root 78654 Jan 24 06:20 mysql_check.pl
-rw-r--r-- 1 root root 11124 Jan 21 10:28 zabbix_agentd.conf
drwxr-xr-x 2 root root 38 Jan 21 09:52 zabbix_agentd.d

PLEASE.THANKS.

Note: if your screenshot included the actual root password for your MySQL database, please change it as soon as possible!

This UserParameter line suggests that mysql_check.pl takes four parameters that are probably specified by the template:

UserParameter=mysql[*],/etc/zabbix/mysql_check.pl $1 $2 $3 $4

The original mysql_check.pl that comes with the template has this in the beginning:

#!/usr/bin/perl

use strict;
use Data::Dumper;
use DBI;

my $user = shift;
my $password = shift;

my $type = shift;

my $search_like = shift;

my $dsn = "DBI:mysql:database=mysql;host=<replace mysql host>";
my $tmp_file = '/tmp/zbx_mysql.status';
my $zbx_sender_file = '/tmp/zbx_mysql.sender';

[...continues...]

I assume that you've posted only the important/edited lines of this part in your question. I see two problems:

1.) the lack of proper quoting that was also noted by cas:

one obvious error is that the string dav in mysql_check.pl needs to be quoted: my $user = 'dav';. don't know much about zabbix so can't help beyond that. – cas

I assume you already fixed this.

2.) the four shift commands are supposed to pick the command line parameters received by mysql_check.pl and place them into variables, in order.

In the original version, the first parameter gets stored to variable $user, the second to $password, and so on. But you've replaced the first two of the shift commands with hard-coded values: this will cause the first parameter (which apparently is the MySQL username from your screenshotted macro, passed through the template) to be assigned to $type instead of $user, and the password to $search_like instead of $password. The 3rd and 4th command line parameters will be left unused. And those two parameters would tell the mysql_check.pl what to check.

Looking further into the mysql_check.pl script code, your modified version of it will end up doing nonsensical things because it's trying to interpret the username as the $type value, so it will return either nothing at all or something the template won't be able to parse.

Don't remove any shift commands from mysql_check.pl. If you need to hard-code the username and password for some reason, do it like this:

[...]
use DBI;

my $user = shift;
my $password = shift;

#hard-coded user info for testing only; these should come from the zabbix macro instead
$user = 'dav';
$password = 'dav123456';

my $type = shift; 
my $search_like = shift;
[...]

Also, it looks like your Zabbix macros $MYSQL_USER and $MYSQL_PWD have the values 'dav' and 'dav123456' respectively assigned at the global level, but 'root' and '' at the host level. The host-level assignments will override the global ones, so the template will be using the wrong values.