In-depth Analysis of MySQL Error 1130: Host Connection Permissions and Troubleshooting

Problem Phenomenon and Background Analysis

When working with MySQL databases, many developers encounter ERROR 1130 (HY000), which typically manifests as:

mysql -u root -ptest101 -h xxx.xxx.xxx.xxx
ERROR 1130 (HY000): Host 'xxx.xxx.xxx.xxx' is not allowed to connect to this MySQL server

This situation commonly occurs when attempting to connect to a MySQL server from a remote host, even though local connections work properly. The core issue lies in MySQL's strict restrictions on connection source hosts within its user privilege system.

Deep Dive into MySQL User Privilege System

MySQL employs a dual authentication mechanism based on both host and user. Each user account is bound to specific hosts, providing granular access control. In the mysql.user system table, user privilege records follow the format 'username'@'host', where the host field supports various formats:

• localhost - Allows connections only from localhost
• 127.0.0.1 - Allows connections only through IPv4 loopback address
• ::1 - Allows connections only through IPv6 loopback address
• % - Allows connections from any host (wildcard)
• 192.168.1.% - Allows connections from specified network segment
• specific_ip - Allows connections from specific IP address

Diagnostic Methods and Troubleshooting Steps

To accurately diagnose ERROR 1130, systematic examination of multiple configuration layers is required:

1. User Privilege Query

First, check the authorized host list for the target user:

SELECT host FROM mysql.user WHERE User = 'root';

The query results may show various scenarios:

+-------------+
| host        |
+-------------+
| %           |
| 127.0.0.1   |
| ::1         |
| localhost   |
+-------------+

If the results don't include the client's connection IP address or the wildcard %, connection refusal errors will occur.

2. Network Configuration Check

MySQL server's network configuration directly impacts connection capability:

# In my.cnf configuration file
bind-address = 0.0.0.0

The bind-address = 0.0.0.0 configuration allows the server to listen on all network interfaces, which is a prerequisite for remote connections. If set to 127.0.0.1, only local connections are permitted.

Solutions and Implementation Steps

Based on diagnostic results, provide targeted solutions:

Solution 1: Grant Privileges to Specific IP Address

If only specific hosts need connection permission, create user accounts for those IPs:

CREATE USER 'root'@'client_ip_address' IDENTIFIED BY 'secure_password';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'client_ip_address';
FLUSH PRIVILEGES;

Solution 2: Use Wildcard Authorization

If connections from any host need to be allowed (use with caution in production):

CREATE USER 'root'@'%' IDENTIFIED BY 'secure_password';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';
FLUSH PRIVILEGES;

Solution 3: Modify Host Permissions for Existing Users

If users exist but have overly restrictive host limitations:

UPDATE mysql.user SET Host = '%' WHERE User = 'root' AND Host = 'localhost';
FLUSH PRIVILEGES;

Security Best Practices

While resolving connection issues, security considerations are essential:

• Avoid using root'@'%' configuration in production environments
• Create dedicated users for different applications with minimal required privileges
• Regularly audit user privileges and remove unnecessary access grants
• Combine with firewall rules to further restrict access sources
• Implement strong password policies with regular password changes

Advanced Troubleshooting

If the above solutions don't resolve the issue, further investigation is needed:

• Check server firewall configuration to ensure port 3306 is open
• Verify network connectivity using telnet to test port accessibility
• Examine MySQL error logs for detailed error information
• Confirm client and server version compatibility
• Check DNS resolution issues, try connecting directly using IP addresses

Conclusion

The root cause of MySQL ERROR 1130 is the mismatch between user privilege configuration and connection source. Through systematic diagnosis and proper privilege configuration, this issue can be effectively resolved. Understanding MySQL's user privilege mechanism not only aids in troubleshooting but also forms the foundation for building secure database environments. In practical applications, appropriate authorization strategies should be chosen based on specific requirements, balancing convenience and security.