Trying to install an HDP cluster on 3 centos7 nodes (for testing / evaluation purposes) and am encountering errors at the part when am asked to configure DBs for Ranger and Ranger KMS.
When running the connection tests for automated DB and DB user setup by Ambari, seeing errors indicating that nodes are not able to connect.
2019-07-18 16:22:02,448 - Check db_connection_check was unsuccessful. Exit code: 1. Message: ERROR: Unable to connect to the DB. Please check DB connection properties. java.sql.SQLException: null, message from server: "Host 'HW02.co.local' is not allowed to connect to this MySQL server"
Traceback (most recent call last): File "/var/lib/ambari-agent/cache/custom_actions/scripts/check_host.py", line 546, in <module> CheckHost().execute() File "/usr/lib/ambari-agent/lib/resource_management/libraries/script/script.py", line 352, in execute method(env) File "/var/lib/ambari-agent/cache/custom_actions/scripts/check_host.py", line 207, in actionexecute raise Fail(error_message) resource_management.core.exceptions.Fail: Check db_connection_check was unsuccessful. Exit code: 1. Message: ERROR: Unable to connect to the DB. Please check DB connection properties. java.sql.SQLException: null, message from server: "Host 'HW02.co.local' is not allowed to connect to this MySQL server"
I have a freshly installed MySQL server on a datanode and can run
mysql -u root -p -h localhost`
but getting errors for
mysql -u root -p -h <some remote cluster node>`
throws
ERROR 2003 (HY000): Can't connect to MySQL server on '' (111)
The mysql confing file looks to be binding to all interfaces...
[root@HW03 ~]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
bind-address=0.0.0.0
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
so not sure what the problem could be.
Not very experienced with MySQL or DBA. Any debugging suggestions or fixes for this?
Following an answer from the Hortonworks community forums is what ultimately allowed the Ambari connection test to pass:
On the mysql server, running...
[root@HW03 ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 51
Server version: 5.6.44 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, 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 mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
mysql> use mysql;
Database changed
mysql>
mysql> CREATE USER 'root'@'%' IDENTIFIED BY '<root's password, THIS IS NOT LITERAL>';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
Though, still does not solve the problem of not being able to access mysql from the other remote hosts, ie. still getting errors
mysql -u root -p -h <some remote cluster node>`
ERROR 2003 (HY000): Can't connect to MySQL server on '' (111)