Zabbix 4.2.3 Template DB MySQL Access denied for user 'zabbix' error

[mmmmay]

Hi,

I'm new to Zabbix and I just installed Zabbix 4.2.3 and Zabbix agent with the same version to monitor the databases for my internship project. Now I'm just testing to monitor my Zabbix backend mySQL database with the template that comes with the installation called "Template DB MySQL" and it's giving me Preprocessing failed for: ERROR 1045 (28000): Access denied for user 'zabbix'@'localhost' (using password: YES) errors on frontend UI and on command line when I debug using zabbix-get.


I can login to mysql from command line using the same zabbix username and mysql server is up and running.

I have created zabbix database and zabbix user in mysql with the following privileges.

mysql> show grants for 'zabbix'@'localhost';
+------------------------------------------------------------------------------+
| Grants for zabbix@localhost                                                  |
+------------------------------------------------------------------------------+
| GRANT SELECT ON *.* TO `zabbix`@`localhost` WITH GRANT OPTION                |
| GRANT ALL PRIVILEGES ON `zabbix`.* TO `zabbix`@`localhost` WITH GRANT OPTION |
+------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Versions for my system and the softwares are as follows:
System: RHEL 7
zabbix_server (Zabbix) 4.2.3
zabbix_agentd (daemon) (Zabbix) 4.2.3


I have followed this tutorial https://www.youtube.com/watch?v=755gDTuHVjM and created .my.cnf file under /var/lib/zabbix directory.

.my.cnf

[mysql]
user=zabbix
password=Testing1!
host=localhost

userparameter_mysql.conf under /etc/zabbix/zabbix_agentd.d/ directory

# 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=/var/lib/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[*],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=/var/lib/zabbix mysql -N'

UserParameter=mysql.ping,HOME=/var/lib/zabbix mysqladmin -s ping | grep -c alive || true
UserParameter=mysql.version,mysql -V

zabbix.conf.php under /etc/zabbix/web/ directory are:

<?php
// Zabbix GUI configuration file.
global $DB;

$DB['TYPE']     = 'MYSQL';
$DB['SERVER']   = 'localhost';
$DB['PORT']     = '0';
$DB['DATABASE'] = 'zabbix';
$DB['USER']     = 'zabbix';
$DB['PASSWORD'] = 'Testing1!';

// Schema name. Used for IBM DB2 and PostgreSQL.
$DB['SCHEMA'] = '';

$ZBX_SERVER      = 'localhost';
$ZBX_SERVER_PORT = '10051';
$ZBX_SERVER_NAME = 'UAT Monitoring';

$IMAGE_FORMAT_DEFAULT = IMAGE_FORMAT_PNG;

zabbix_agentd.conf

...
Server=127.0.0.1
ServerActive=127.0.0.1
Hostname=BUMPOCV1 
Include=/etc/zabbix/zabbix_agentd.d/*.conf
...

zabbix_server.conf

...
DBName=zabbix
DBUser=zabbix
DBPassword=Testing1!
...

I have tried every possible solution that I have found online:
- created new user and password in mysql and changed respectively in all config files
- moved .my.cnf file to new directory and changed HOME directory in userparameter_mysql.conf
- changed

[mysql]

to

[client]

in .my.cnf
- restarted zabbix server, zabbix agent and mysqld multiple times
- rebooted the whole server itself

All of them are not working and I am having a really hard time to troubleshoot and figure out what's wrong.
I would really appreciate if someone could advise me on how to get rid of that access denied error and start collecting data in the frontend.
So sorry for the very lengthy post and thank you in advance!

May

[poponeis]

I spent almost a week trying to solve this issue, and finally I reached this setup:
I changed my .conf file to:

UserParameter=mysql.ping, mysqladmin --defaults-extra-file='/var/lib/zabbix/.my.cnf' ping | grep -c alive
UserParameter=mysql.get_status_variables, mysql --defaults-extra-file='/var/lib/zabbix/.my.cnf' -sNX -e "show global status"
UserParameter=mysql.version, mysql -V
UserParameter=mysql.db.discovery, mysql --defaults-extra-file='/var/lib/zabbix/.my.cnf' -sN -e "show databases"
UserParameter=mysql.dbsize[*], mysql --defaults-extra-file='/var/lib/zabbix/.my.cnf' -sN -e "SELECT SUM(DATA_LENGTH + INDEX_LENGTH) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='$1'"
UserParameter=mysql.replication.discovery[*], mysql --defaults-extra-file='/var/lib/zabbix/.my.cnf' -sNX -e "show slave status"
UserParameter=mysql.slave_status[*], mysql --defaults-extra-file='/var/lib/zabbix/.my.cnf' -sN -e "show slave status"

My /var/lib/zabbix/.my.cnf
[client]
user=zabbix
password=pass

And my Grants are:

Grants for zabbix@localhost |
+---------------------------------------------------------------------------------------------+
| GRANT PROCESS, SHOW DATABASES, REPLICATION CLIENT, SHOW VIEW ON *.* TO `zabbix`@`localhost` |
| GRANT SELECT ON `sys`.* TO `zabbix`@`localhost` |
| GRANT SELECT ON `performance_schema`.* TO `zabbix`@`localhost` |
| GRANT SELECT ON `sys`.`statements_with_runtimes_in_95th_percentile ` TO `zabbix`@`localhost`