[SOLVED] Remote mysql connection can not be established / host is not allowed

Remote mysql connection can not be established / host is not allowed

I have laravel web application and mysql running under same ip.
web is encrypted with ssl certificate

firewall is ufw and ufw is set up as following

To                         Action      From
--                         ------      ----
Nginx Full                 ALLOW       Anywhere
OpenSSH                    ALLOW       Anywhere
443/tcp                    ALLOW       Anywhere
3306/tcp                   ALLOW       Anywhere
3306                       ALLOW       Anywhere
Nginx Full (v6)            ALLOW       Anywhere (v6)
OpenSSH (v6)               ALLOW       Anywhere (v6)
443/tcp (v6)               ALLOW       Anywhere (v6)
3306/tcp (v6)              ALLOW       Anywhere (v6)
3306 (v6)                  ALLOW       Anywhere (v6)

namp check from remote machine shows that port is open

 nmap  161.54.46.221    Starting Nmap 7.80 ( https://nmap.org ) at
 2022-09-07 07:52 UTC    Nmap scan report for
 static.221.46.54.161.clients.your-server.de (161.54.46.221) Host is
 up (0.0045s latency). Not shown: 996 filtered ports.
 PORT      STATE     SERVICE
 22/tcp    open      ssh 
 80/tcp    open      http 
 443/tcp   open      https
 3306/tcp  open      mysql 
 Nmap done: 1 IP address (1 host up) scanned in 4.77 seconds

mysql connection locally using ip as host is also working

>:~$ mysql -u readUser -h 161.54.46.221 -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 74
Server version: 8.0.30-0ubuntu0.20.04.2 (Ubuntu)
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
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>

In mysql setting /etc/mysql/mysql.conf.d/mysqld.cnf, bind address is also set up correctly

My setting with \s command is given below

Connection id:          25
Current database:
Current user:           sharma@static.221.48.54.161.clients.your-server.de
SSL:                    Cipher in use is TLS_AES_256_GCM_SHA384
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         8.0.30-0ubuntu0.20.04.2 (Ubuntu)
Protocol version:       10
Connection:             161.54.48.221 via TCP/IP
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
TCP port:               3306
Binary data as:         Hexadecimal
Uptime:                 29 min 2 sec
Threads: 4  Questions: 5857  Slow queries: 0  Opens: 180  Flush tables: 3  Open tables: 99   
Queries per second avg: 3.362

-I can connect mysql server with telnet locally

Trying 161.54.46.221...
Connected to 161.54.46.221.
Escape character is '^]'.
[
 u▒k#^oI_~(Bh?caching_sha2_password

However If try to connect from remote to my sql server , the error is "host is not allowed to connect the mysql server".

For example, If I try to connect form a remote server then it says host is not allowed. What can be the reason ?

telnet  161.54.48.221 3306
Trying 161.54.48.221...
Connected to 161.54.48.221.
Escape character is '^]'.
@Host 'bernstein.store' is not allowed to connect to this MySQL serverConnection closed by 
foreign host.

Could you please help regarding this problem? I would be thankful

Solution

I got the answer now . The way how I went for remote connection process was all correct. It was actually easy. But I spent a huge amount time on it for such a small thing
I set host as the ip address in the user table
The user was saved as 'readUser'@'161.54.46.221'. What I just had to do is to save user as 'readUser'@'%'. So use the following command to change your host as following and then all should work now.
```
USE mysql; 
Update user set host='%' where user='readUser'; 
FLUSH PRIVILEGES;
```