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

Question

• 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

Answer 1

• 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;