Zabbix Remote DB implementation

[krishnaraobv]

OS:Centos 7.8 / Database Mysql 8.0.3 / Zabbix 5.0
Successfully installed and able to preview the Zabbix website perfectly but on Zabbix GUI it is displaying ---Zabbix server is running --- NO
and iam getting the error as Connection to database ''zabbixdb'' failed : {1045} Access denied for user ''root @x.x.x.x
Iam trying to install Zabbix Server/Front end on one server and Database is on another server.
Please guide me in this regard.

[tim.mooney]

The documentation for the initial database creation ( https://www.zabbix.com/documentation...all/db_scripts ) assumes that the database will be hosted on the same system as the zabbix server and the web front end. That's a good assumption for most sites, but in your case, it doesn't apply.

You need to modify both the 'CREATE USER' and the 'GRANT' statement in that example so that instead of 'localhost' it specifies either the IP address or fully qualified DNS name (if your DNS is properly configured and correctly resolves your Zabbix server) of the server where the Zabbix server and web front end are installed.

Next, you need to configure both the zabbix-server processes (via /etc/zabbix/zabbix_server.conf) and the web front end (via the zabbix/conf/zabbix.conf.php file) to connect to the correct remote database host, as the 'zabbix' user (assuming you used 'zabbix' in your CREATE USER and GRANT statements).

If your database server is running a host-based firewall, you also need to ensure that the MySQL server port allows connections from your Zabbix server.

Also, if your MySQL install defaults to a non-password-based authentication mechanism for freshly created accounts, for simplicity's sake you'll want to override that at the start and make certain the 'CREATE USER' statement creates a user that uses password-based authentication. Once you have other stuff working, you can experiment with using one of the other authentication plugins, but when just starting out, probably keep it simple.

[krishnaraobv]

Thanks for the reply
we have tried with FQDN of database and updated hosts files of both the servers and reinstalled zabbix-server and zabbix-agent and still no luck, still zabbix website is pointing sto localhost (FQDN) only.
We also tried given permissions at MySQL for FQDN even though no luck.
I am attaching notepad for reference.

[tim.mooney]

I looked through your attached file and everything you have configured seems correct. Three things I would try, to further debug what the problem may be:

1. On your mysql server, if you have the mysql setting 'log-error' pointing to a log file (or into the database itself), you may want to temporarily set the 'log-warnings' setting to '2', to log aborted connections and access denied errors from the mysql server's point of view. That should help identify whether the server is seeing connections but rejecting them or just not seeing the connections at all.
2. you used the 'mysql' command to test a connection from your web front end system to the mysql server as the 'zbxadmin' user, which is a very good test and I'm glad you included it. That doesn't actually guarantee that that user can connect to the 'zabbixdb' database, though. What I would like you to try is to re-run the same mysql command from the web server, but have it connect to the 'zabbixdb' database. Either one of these commands (they're the same thing, just different syntax) should test that:

   1. mysql -h x.x.17.80 --database=zabbixdb --username=zbxadmin -p

   2. mysql -h x.x.17.80 -u zbxadmin -p   zabbixdb

      (make sure there's a space between the -p and the database name at the end of the command line, if you use this syntax).
3. since I only have experience with using a local (same system) database with Zabbix, I don't know if this is necessary, but it might be worth setting the "DBPort=" setting in zabbix_server.conf and the "$DB['PORT']" setting in the php config file, to see if that makes any difference. I don't think that's the issue, but I think you should try it and at least rule it out, whether it matters or not. Assuming you're using default mysql config, the port would be 3306 .

[krishnaraobv]

Please find attached we have validated as per the suggestion given please check the notepad attached

[tim.mooney]

You're a little more likely to get help if you post the results inline (with appropriate CODE block formatting), rather than requiring someone to download and view a separate file, but I did review your 2nd attachment.

You didn't complete the MySQL logging steps I suggested in part one, but maybe what I was suggesting wasn't clear. You've verified that 'log-error' is set (it's pointing to /var/log/mysqld.log), but to get authentication failures to be logged to that file, you also need to set the 'log-warnings' setting to be '2'. This can be done without having to edit my.cnf, to have it apply just to the currently running server. Once you've set log-warnings=2, you need to attempt a connection from zabbix-server or the zabbix web front end, then look in the log file that log-error is pointing to, to see if anything was logged. This will tell you whether the error is because Zabbix is not connecting or because MySQL is rejecting the connection.

For question 2, after you've connected directly to the 'zabbixdb', instead of running 'show databases', run the command 'show tables'.

For question 3, you set those values in both places correctly. Did it make any difference? Are either the zabbix_serverd or the Zabbix web front end able to connect after you've set the port? What was the result of making that change.

[krishnaraobv]

################################################## ##########################

Q1: You also need to set the 'log-warnings' setting to be '2'

mysql> show variables like '%log_%';
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/binlog |
| log_bin_index | /var/lib/mysql/binlog.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| log_error | /var/log/mysqld.log |
| log_error_services | log_filter_internal; log_sink_internal |
| log_error_suppression_list | |
| log_error_verbosity | 2 |
| log_output | FILE |
| log_queries_not_using_indexes | OFF |
| log_raw | OFF |
| log_slave_updates | ON |
| log_slow_admin_statements | OFF |
| log_slow_extra | OFF |
| log_slow_slave_statements | OFF |
| log_statements_unsafe_for_binlog | ON |
| log_throttle_queries_not_using_indexes | 0 |
| log_timestamps | UTC |

Note:"Mysql 8.0, The deprecated log_warnings system variable and --log-warnings server option have been removed.
Use the 'log_error_verbosity' system variable instead"
Reference: https://dev.mysql.com/doc/relnotes/m...ews-8-0-3.html


Mysql Log:
#### tail /var/log/mysqld.log
2020-09-25T06:50:23.899350Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.21) MySQL Community Server - GPL.
2020-09-25T06:50:59.394474Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.21) starting as process 5631
2020-09-25T06:50:59.403801Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-09-25T06:50:59.679570Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2020-09-25T06:50:59.824286Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
2020-09-25T06:50:59.950150Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-09-25T06:50:59.950847Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2020-09-25T06:50:59.985489Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.21' socket: '/var/lib/mysql/mysql.sock' port: 3306


################################################## ################################

Q2:Instead of running 'show databases', run the command 'show tables'


[root@wl-zbxweb01]# mysql -h x.x.17.80 -u zbxadmin -p zabbixdb
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3140
Server version: 8.0.21 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

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

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

mysql> use zabbixdb;
Database changed
mysql> show tables;
+----------------------------+
| Tables_in_zabbixdb |
+----------------------------+
| acknowledges |
| actions |
| alerts |
| application_discovery |
| application_prototype |
| application_template |
| applications |
| auditlog |
| auditlog_details |
| autoreg_host |
.
.
.
| trends |
| trends_uint |
| trigger_depends |
| trigger_discovery |
| trigger_tag |
| triggers |
| users |
| users_groups |
| usrgrp |
| valuemaps |
| widget |
| widget_field |
+----------------------------+
166 rows in set (0.01 sec)

################################################## ########################

Q3: you set those values in both places correctly. Did it make any difference? Are either the zabbix_serverd or the Zabbix web front end
able to connect after you've set the port? What was the result of making that change ?

After setting PORT "3306" no change. We can able to view the frontend without any issues.
But in frontend its still displaying error as "Zabbbix server is not running the information may not be correct"
Frontend error was attached FYR.

Still in zabbix-server log, Server is pointing to local host only:

[root@wl-zbxweb01]# tail /var/log/zabbix/zabbix_server.log
18387:20200925:165140.565 [Z3001] connection to database 'zabbixdb' failed: [1045] Access denied for user 'zbxadmin'@'wl-zbxweb01.vvv.com' (using password: YES)
18387:20200925:165140.565 database is down: reconnecting in 10 seconds
18387:20200925:165150.568 [Z3001] connection to database 'zabbixdb' failed: [1045] Access denied for user 'zbxadmin'@'wl-zbxweb01.vvv.com' (using password: YES)
18387:20200925:165150.568 database is down: reconnecting in 10 seconds
18387:20200925:165200.573 [Z3001] connection to database 'zabbixdb' failed: [1045] Access denied for user 'zbxadmin'@'wl-zbxweb01.vvv.com' (using password: YES)


################################################## #########################

[Rushings]

Zabbix uses several options for collecting metrics, including agentless monitoring of user services and client-server architecture. To collect server metrics, it uses a small agent on the monitored client to gather data and send it to the Zabbix server Upsers

[tim.mooney]

I wasn't aware that MySQL 8.0 had deprecated 'log-warnings' and replaced it with 'log_error_verbosity'. You did the right thing by including that info. My site is currently using MariaDB 10.2.x with Zabbix, and that version of MariaDB is still using the older name for that setting.

Unfortunately, MySQL also made slight changes to not just the name of the setting but how it behaves. With the new setting, you need to use a value of '3' to get it to log access denied and aborted connections. See the MySQL documentation here for more information: https://dev.mysql.com/doc/refman/8.0...filtering.html

Now that I know that, please change the log_error_verbosity to 3 and then try use the Zabbix web front end. What we want to see is if there are messages in mysqld.log about connections from either the web front end or from zabbix_serverd. With log_error_verbosity=3, mysqld should log every time it sees a connection but rejects it.

The other test you performed (show tables when connected to the database) just verified that the 'zbxadmin' has access to the necessary database and prove that you've created the tables. That all looks good.