How to Resolve 'Host 'xxx.xx.xxx.xxx' is not allowed to connect to this MySQL server'?

Error: MySQL Host Connection Denied

This issue occurs when attempting to connect to a remote MySQL server with an unrecognized host IP address, resulting in the error: "Host 'xxx.xx.xxx.xxx' is not allowed to connect to this MySQL server."

Causes:

The error indicates that the MySQL server is configured to restrict connections from specific hosts. This is commonly a security measure to prevent unauthorized access.

Solution:

To rectify this issue, it is necessary to grant access to the connecting host. Here are two possible approaches:

Option 1: Create a New Administrative Account

Create a new user with administrative privileges, granting it access from both localhost and the remote host. This method allows for comprehensive control over user permissions and access.

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;

Option 2: Grant Specific Permissions

Limit access to specific privileges and restrict it to the remote host only. This is a more secure approach, providing only the necessary permissions to the remote user.

mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON database_name.* TO 'monty'@'%'

MySQL FAQ Note:

According to the MySQL FAQ, if you encounter this error while attempting to connect from localhost, it is important to check for a rule with Host='localhost' and User=''. If it exists, delete it and then issue a FLUSH PRIVILEGES command to refresh the grant tables.

The above is the detailed content of How to Resolve 'Host 'xxx.xx.xxx.xxx' is not allowed to connect to this MySQL server'?. For more information, please follow other related articles on the PHP Chinese website!