If you are trying to connect mysql database from your ip address. For this you use the below command:
# mysql -u database_username -p -h 168.24.86.7
As you execute the above command. You see the following error displayed on the terminal or command line:
ERROR 1130 (00000): Host '168.24.86.7' OR 'xxx.xx.xxx.xxx' is not allowed to connect to this MySQL server
In this tutorial, you will learn how to fix the “ERROR 1130 (HY000): Host x.x.x.x is not allowed to connect to this MySQL server” error in MySQL/MariaDB database deployment on a Linux.
Host is not allowed to connect to this MySQL server
By following these steps, you can troubleshoot and resolve the “Host is not allowed to connect to this MySQL server” error:
- Step 1: Connect to MySQL Server
- Step 2: To Check Host
- Step 3: Enable MySQL Access for the Remote
- Step 4: Give Remote Access from All Host
- Step 5: Verify by Trying to Remotely Connect to the MySQL Database
Step 1: Connect to MySQL Server
Firstly, open your terminal and execute the following command to connect mysql server:
# mysql -u root -p
Step 2: To Check Host
Once you have connected to mysql server, execute the following SQL commands to check the user’s host:
MariaDB [(none)]> SELECT host FROM mysql.user WHERE user = "database_username";
After that, you can see the output on the terminal or command line of above-given command, the user is only allowed to connect to the database server from localhost. So, you need to update the user’s host as follows.
Step 3: Enable MySQL Access for the Remote
Once you have checked, you need to execute the following GRANT command to enable MySQL access for the remote user from a remote host.
MariaDB [(none)]> GRANT ALL ON database_name.* to 'database_username'@'168.24.86.7' IDENTIFIED BY 'database_password'; MariaDB [(none)]> FLUSH PRIVILEGES; MariaDB [(none)]> SELECT host FROM mysql.user WHERE user = "database_username";
Note that, please replace “168.24.86.7” with the IP address of the remote system, and “database_password” to the password that you want “database_username” to use:
Step 4: Give Remote Access from All Host
Then execute the following command on the terminal or command line to grant a user remote access from all hosts on a network:
MariaDB [(none)]> GRANT ALL ON database_name.* to 'database_username'@'168.24.86.7.%' IDENTIFIED BY 'database_password';
Step 5: Verify by Trying to Remotely Connect to the MySQL Database
If you have followed the saree steps given above. So now you can connect to remote MySQL database from your ip address. For this you use the command given below:
# mysql -u database_username -p -h 168.24.86.7
Note that, please replace “168.24.86.7” with the IP address of the remote system.
Conclusion
That’s all, in this tutorial you have learned how to fix the “Host is not allowed to connect to this MySQL server” error.
