Troubleshooting MySQL Error 1130 (HY000) – Host is not allowed to connect to this MySQL server

MySQL DBA, MySQL Internals, MySQL Performance, MySQL Troubleshooting

Introduction

MySQL Error 1130 (HY000): "Host is not allowed to connect" is a common issue that arises when a MySQL server rejects a connection attempt from a client. This problem is typically related to MySQL's user authentication and privilege system. Here are steps to troubleshoot and resolve this error.

Runbook to Troubleshoot MySQL Error 1130

1. Verify User and Host

  • Check User Existence: Ensure the user trying to connect exists in the MySQL user table.
  • Host Matching: MySQL privileges are host-specific. The host from which you're trying to connect must be permitted for the given user.

2. Grant Proper Privileges

  • Connect to the MySQL server as a root or another user with sufficient privileges.
  • Execute the GRANT command to allow access. For example:
  • Replace database_name, username, client_host, and password with your specific details. Use % as the host to allow connections from any host, but be cautious as it's less secure.

3. Check for Skip-Networking and Bind-Address

  • Ensure the MySQL server is not started with the -skip-networking flag, which disables TCP/IP networking.
  • Check the bind-address in the MySQL configuration file (my.cnf or my.ini). If it's set to 127.0.0.1 (localhost), it will only accept local connections. To allow external connections, change it to 0.0.0.0 or the specific server IP.

4. Firewall and Network Restrictions

  • Verify that there are no firewall rules blocking MySQL connections. The default MySQL port is 3306.
  • In cloud environments (like AWS), ensure security groups or network ACLs allow traffic on MySQL’s port from the client’s IP.

5. DNS Issues

  • If you're using hostnames instead of IP addresses, ensure that DNS resolution is working correctly. Sometimes, using IP addresses instead of hostnames can help circumvent DNS-related issues.

6. User Authentication Plugin

  • If you're using authentication plugins (like caching_sha2_password in MySQL 8.0), ensure the client supports the authentication method.

7. Flush Privileges

  • After making changes to user privileges, run FLUSH PRIVILEGES; to apply the changes immediately.

8. Check MySQL Error Logs

  • Review the MySQL error logs for any additional clues that might indicate why the connection is being refused.

9. Test with a Different User

  • As a diagnostic step, try connecting with a different user or from a different host to isolate the issue.

10. Version Compatibility

  • Ensure both MySQL server and client are compatible, especially if using different versions.

Note

  • Always be cautious with granting privileges, especially with GRANT ALL. Limit privileges to the minimum required for each user.
  • Regularly reviewing and auditing user privileges can prevent security issues.

Conclusion

If the problem persists after these steps, it might be a more unusual issue requiring a deeper investigation into both the MySQL configuration and the network setup. Please refer to the MySQL documentation and learn more about how to troubleshoot other MySQL errors here.

About Shiv Iyer 465 Articles
Open Source Database Systems Engineer with a deep understanding of Optimizer Internals, Performance Engineering, Scalability and Data SRE. Shiv currently is the Founder, Investor, Board Member and CEO of multiple Database Systems Infrastructure Operations companies in the Transaction Processing Computing and ColumnStores ecosystem. He is also a frequent speaker in open source software conferences globally.
Website Facebook Twitter LinkedIn

Related Articles