How to solve: ERROR 1130 (HY000): Host ’90.xxx.xxx.101′ is not allowed to connect to this MySQL server

Scenario :

Getting error 1130 when trying to remotely connect MySQL server with ip address 172.xxx.201.xxx from my server 90.xxx.xxx.101 using the Database user ‘test_123’. 

To resolve the above error, first log into the MySQL server in 172.xxx.201.xxx and search for the user test_123, to ensure whether it exists on the server.

#mysql -u root -p

mysql>SELECT user,host FROM mysql.user WHERE user = 'test_123';

The above query is used for checking whether the user exists or not and if so, from which host or IP address they have permission to access.

+----------------+--------------+
| user           | host         |
+----------------+--------------+
| test_123       | localhost    |
+----------------+--------------+

So the user can only access the system via localhost. This is why it was unable to login to the server 90.xxx.xxx.101 with this user.

Now we can easily solve error by creating the user with permission to access MySQL server (172.xxx.201.xxx) from my server (90.xxx.xxx.101)

To do so, create the same user again with the host as 90.xxx.xxx.101:

mysql> CREATE USER 'test_123'@'90.xxx.xxx.101' IDENTIFIED BY 'password';

Then, grant privileges to the user using the GRANT PRIVILEGES query.

Here we will be  granting all privileges on the database “test_db1” to the user:

mysql> GRANT ALL PRIVILEGES ON test_db1.* TO 'test_123'@'90.xxx.xxx.101'; 
mysql> FLUSH PRIVILEGES;

For testing,  use the SELECT user,host FROM mysql.user WHERE user = ‘test_123’; query again, Now we could see: 

+----------------+---------------------+
| user           | host                |
+----------------+---------------------+
| test_123       | localhost 	      |
| test_123       | 90.xxx.xxx.101      |
+----------------+---------------------+

Now its showing that the user can access the MySQL server both via localhost and 90.xxx.xxx.101.

We can  now able to connect to the MySQL server remotely from 90.xxx.xxx.101 using the Database user ‘test_123’.

$mysql -h 90.xxx.xxx.101 -u test_123 -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is XXXX
Server version: 5.7.32 MySQL Community Server (GPL)


Copyright (c) 2000, 2023, 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>

Here’s how we could fix the ERROR 1130 (HY000): Host ’90.xxx.xxx.101′ which is not allowed to connect to the MySQL server, when trying to access mysql remotely. You could also create the user with the host as ‘%’, but be aware, this will grant access to the user from “any remote source”.

Our Support Team is available to help if you encounter any issues or errors.