Why Can't I Connect to My MySQL Server: 'Host 'xxx.xx.xxx.xxx' is not allowed'?

Cannot Connect to MySQL Server: "Host 'xxx.xx.xxx.xxx' is not allowed"

In an attempt to establish a remote connection to a MySQL server, you encounter the error message: "Host 'xxx.xx.xxx.xxx' is not allowed to connect to this MySQL server." Despite having a user entry with both 'localhost' and '%' as host values, the problem persists.

Explanation

This error typically arises as a security measure. To ensure the security of your database, it is recommended to limit access to specific hosts and restrict the use of wildcards ('%' or '_'). The default privileges for the 'root' user often include an entry with Host='localhost' and User='', which takes precedence over entries with more general Host values.

Solution

To resolve this issue, consider the following steps:

1. Create a New Administrator Account:

Create a new administrative user with specific host access, such as 'localhost' or '%%' (for all hosts). Grant the necessary privileges to this user.

mysql> CREATE USER 'monty'@'localhost' IDENTIFIED BY 'some_pass';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost'
    ->     WITH GRANT OPTION;
mysql> CREATE USER 'monty'@'%' IDENTIFIED BY 'some_pass';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%'
    ->     WITH GRANT OPTION;

2. Limit Access to Specific Hosts:

When creating users, specify the exact hosts or IP addresses that they are permitted to connect from. This ensures that unauthorized access is restricted.

3. Delete Wildcard Entries:

If there are any user entries with wildcard hosts ('%' or '_'), it is recommended to remove them to enhance security. Once removed, issue a FLUSH PRIVILEGES statement to reload the grant tables.

By implementing these measures, you can establish a secure connection to your MySQL server and mitigate the risk of unauthorized access.

The above is the detailed content of Why Can't I Connect to My MySQL Server: 'Host 'xxx.xx.xxx.xxx' is not allowed'?. For more information, please follow other related articles on the PHP Chinese website!