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

[#1]

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 '<theremote cluster node>' (111)

Not very experienced with MySQL or DBA. Looking at resources online, can find solutions that appear to recommend manually creating users in the DB for each specific remote host. This seems very inefficient if I were wanting to install a large cluster, so am assuming that this is not what Hortonworks expects users to do. Can anyone suggest debugging advice or fixes for this?

[#2]

@Reed Villanueva

Have you already run the following queries mentioned to setup the RangerDB in advance?

https://docs.hortonworks.com/HDPDocuments/HDP3/HDP-3.1.0/installing-ranger/content/configure_mysql_d...

MySQL users needs to be setup and GRANT privileges needs to be executed else the connection test from a remote host will not work.

CREATE USER 'rangerdba'@'localhost' IDENTIFIED BY 'rangerdba;
GRANT ALL PRIVILEGES ON *.* TO 'rangerdba'@'localhost;
CREATE USER 'rangerdba'@'%' IDENTIFIED BY 'rangerdba;
GRANT ALL PRIVILEGES ON *.* TO 'rangerdba'@'%;
GRANT ALL PRIVILEGES ON *.* TO 'rangerdba'@'localhost' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'rangerdba'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

.

Just in case if you want to change/reset your MySQL "root" user password then you can change/reset it
https://dev.mysql.com/doc/refman/5.7/en/resetting-permissions.html

[#3]

@Reed Villanueva

Also as you are not able to connect to MySQL remotely.

# mysql -u root -p -h <some remote cluster node>
ERROR 2003 (HY000): Can't connect to MySQL server on '<theremote cluster node>' (111)

This also happens if for the "root" user the remote Privilleges are not setup correctly.

So please do SSH to the MySQL Server host then connect (locally) to run the metntioned queries.

From MySQL DB server host:

# netstat -tnlpa | grep 3306
# service iptables stop

# mysql -u root -p 
Enter Password: 

mysql> use mysql;
mysql> CREATE USER 'root'@'%' IDENTIFIED BY 'xxxxxxxxxxxxxxxxxxxxxxxxxx';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;

Please check the Port 3306 is accessible remotely or not?

# telnet  <MYSQL_HOST>  3306

If your MySQL listening on localhost? or on all interfaces?

# grep 'bind-address'  /etc/my.cnf

https://www.tecmint.com/fix-error-2003-hy000-cant-connect-to-mysql-server-on-127-0-0-1-111/

.

[#4]

@Jay Kumar SenSharma

Following this post helped pass the Ambari connection test, thanks. Was able to find a bit of explaination for this fix here: https://stackoverflow.com/a/47558163/8236733. However, contrary to what your answer seems to imply, despite the connection test now passing, still getting the

# mysql -u root -p -h <some remote cluster node>
ERROR 2003 (HY000): Can't connect to MySQL server on '<theremote cluster node>' (111)

errors. Any suggestions?

[#5]

The above question and the entire response thread below were originally posted in the Community Help track. On Sat Jul 20 16:40 UTC 2019, a member of the HCC moderation staff moved it to the Security track. The Community Help Track is intended for questions about using the HCC site itself.