Ad Widget

**vkhaliev** · 10-01-2022, 11:17

I guess that you are using non-standard port at MySQL.
You can check it by this command:

Code:

# mysql -u 'username' -p'password' -e "show global variables like 'port';"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port | 3306 |
+---------------+-------+

If you are using another port you need to change macro {$MYSQL.PORT} on your MySQL host in zabbix

**joepadmiraal** · 11-01-2022, 22:51

Thanks for responding!
Unfortunately I am using the standard port so it must be something else:

Code:

$ sudo mysql -e "show global variables like 'port';"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port | 3306 |
+---------------+-------+

**tim.mooney** · 12-01-2022, 00:27

Originally posted by joepadmiraal

Zabbix server 1:5.0.19-1+bionic
Zabbix agent: 1:5.0.19-1+focal

I imported the `MySQL by Zabbix agent` template for Zabbix 5.0 from here.

Originally posted by joepadmiraal

Code:

$ zabbix_agentd -t "mysql.ping"
mysql.ping [t|mysqladmin: connect to server at '-P' failed
error: 'Unknown MySQL server host '-P' (-2)'
Check that mysqld is running on -P and that the port is 3306.
You can check this by doing 'telnet -P 3306']

So it seems like it's trying to feed the -P parameter as the MySQL host.

I agree, that does seem like the problem. For that to be happening, it seems like the {$MYSQL.HOST} template default of "localhost" is not being correctly evaluated. Can you examine the template and look at its macros and make sure that it's set? What about at the host level, is there a {$MYSQL.HOST} setting that might be overriding it?

**vkhaliev** · 12-01-2022, 11:06

I agree with Tim. Probably something wrong with macros values at the template or host.
Also you can try:

Code:

zabbix_agentd -t "mysql.ping[localhost,3306]"

If it will be successful then you need to verify configurated values of macros {$MYSQL.HOST} and {$MYSQL.PORT} on your MySQL host in zabbix for sure. In this case, maybe you should check another template macros too.

**joepadmiraal** · 12-01-2022, 11:54

I changed my username with XXXXX in this snippet:

Code:

$ zabbix_agentd -t "mysql.ping[localhost,3306]"
mysql.ping[localhost,3306] [t|mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'XXXXX'@'localhost' (using password: NO)']

So it does seem to get a different error compared to just doing a 'mysql.ping'.

When looking in the Zabbix web page at the host configuration it shows no macros are set.
The inherited macros look like this:

The template macros look like this:

**vkhaliev** · 12-01-2022, 12:41

Looks like macros are OK.
Did you setup step 3 from instruction to template?

Code:

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

Username and password must be the same in the 3rd and 4th steps from setup.

**joepadmiraal** · 12-01-2022, 13:10

Yes I did.
This part of my question indicates that the zbx_monitor user has enough rights to do the ping command:

Originally posted by joepadmiraal

Zabbix server 1:5.0.19-1+bionic

Code:

$mysqladmin version -u zbx_monitor -p ping
Enter password:
mysqladmin Ver 8.0.27-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))
Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Server version 8.0.27-0ubuntu0.20.04.1
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/run/mysqld/mysqld.sock
Uptime: 5 hours 16 min 46 sec

Threads: 16 Questions: 123461 Slow queries: 0 Opens: 621 Flush tables: 3 Open tables: 540 Queries per second avg: 6.495
mysqld is alive

Is it possible to get a full log of the commands that the agent is executing?
I tried adding DebugLevel=3 to /etc/zabbix/zabbix_agentd.conf and restarting the zabbix-agent deamon.
But /var/log/zabbix/zabbix_agentd.log does not seem to get any details on what the agent is doing, other than it's startup log.

**vkhaliev** · 12-01-2022, 13:12

Also right path to username/password file must be:
/var/lib/zabbix/.my.cnf

I tried to move this file and got the same error

Code:

# mv /var/lib/zabbix/.my.cnf /var/lib/zabbix/my.cnf
# sudo -u zabbix zabbix_agentd -t "mysql.ping[localhost,3306]"
mysql.ping[localhost,3306] [t|mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'XXXXX'@'localhost' (using password: NO)']
#
# mv /var/lib/zabbix/my.cnf /var/lib/zabbix/.my.cnf
# sudo -u zabbix zabbix_agentd -t "mysql.ping[localhost,3306]"
mysql.ping[localhost,3306] [t|mysqld is alive]

**joepadmiraal** · 12-01-2022, 14:08

Ah thanks,
I had that wrong indeed.
However does not seem to help on my machine:

Code:

sudo cp /var/lib/zabbix/my.cnf /var/lib/zabbix/.my.cnf
sudo -u zabbix zabbix_agentd -t "mysql.ping[localhost,3306]"
mysql.ping[localhost,3306] [t|mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'XXXXX'@'localhost' (using password: NO)']

So that brings me to the question, how does the agent knows it has to look in /var/lib/zabbix/.my.cnf?
I can imagine /var/lib/zabbix is a default directory, but .my.cnf seems template specific.
I checked template_db_mysql.conf and template_db_mysql_agent.xml but these files do not seem to specify that.
Other than it being mentioned in the comments.

**vkhaliev** · 12-01-2022, 14:56

I think agent search for file .my.cnf in zabbix user home directory.
Here is the link to installation from sources. For Debian based systems like Ubuntu default zabbix home directory must be /var/lib/zabbix/
Maybe in your installation zabbix home directory is different?

Code:

# grep zabbix /etc/passwd | awk -F ":" '{print $6}'
/var/lib/zabbix/

**joepadmiraal** · 12-01-2022, 15:19

Thanks!

Code:

grep zabbix /etc/passwd | awk -F ":" '{print $6}'
/nonexistent

I've installed the agent via the Ubuntu/Debian packages.
So it seams these packages won't set the home directory (as recommended on the 'install from source' link you provided).

Adding the home directory fixes the issue:

Code:

sudo usermod -d /var/lib/zabbix zabbix

grep zabbix /etc/passwd | awk -F ":" '{print $6}'
/var/lib/zabbix

sudo -u zabbix zabbix_agentd -t "mysql.ping[localhost,3306]"
mysql.ping[localhost,3306] [t|mysqld is alive]

I now also see the values in the Zabbix web page.

The only value that is not reported correctly is `MySQL: Size of database`:

template_db_mysql.conf shows:

Code:

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'"

I verified I can do the SELECT statement when logged in on MySQL with the zbx_monitor user.
But for some reason it tries to use the zabbix user without a password?

**vkhaliev** · 12-01-2022, 15:29

Reference to MySQL documentation about option files.

~/.my.cnf

User-specific options

**vkhaliev** · 12-01-2022, 15:45

Is it real database name "ERROR 1045..."?
Maybe you should drop this item... Configuration - Hosts - Discovery on your MySQL host - Database discovery - Keep lost resources period = 0d - Update. Then "Execute now" at this discover rule.

**joepadmiraal** · 12-01-2022, 16:17

No I dont have a database called ERROR 1045.
Might be coming from the issues I had earlier.
I changed the discovery and executed it, I now do see my real databases with the correct size values.
The error row is still there, but for me that's not a big issue.

Thanks for all your help!

Ad Widget

Can't get MySQL template working

Can't get MySQL template working

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment