Search code examples
ambariapache-ranger

"Host 'xxxx is not allowed to connect to this MySQL server" when installing Ranger DBs via Ambari

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.

enter image description here

When running the connection tests for automated DB and DB user setup by Ambari, seeing errors indicating that nodes are not able to connect.

enter image description here

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?

Solution

  • 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

    and now can see... enter image description here

    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)