Resolving "Access Denied for user 'xxxx'@'xxx.xx.xxx.x' (Using password: YES)" in MySQL

 

When managing a MySQL database, it's common to encounter the "Access Denied for user 'xxxx'@'xxx.xx.xxx.x' (Using password: YES)" error. This issue typically arises when a MySQL user’s host does not have the necessary permissions to access the database from a remote machine.

Understanding the Error

This error is triggered when the MySQL server refuses a connection attempt from a user due to host restrictions. The error message usually looks like this:

ERROR 1045 (28000): Access denied for user 'xxxx'@'xxx.xx.xxx.x' (using password: YES)

If you encounter this issue, it could be because the MySQL user is restricted to accessing the database only from specific IP addresses.

 

Error:

  

To resolve this issue, you can modify the MySQL user’s host settings, allowing access from different remote machines. Here's a step-by-step guide on how to change the MySQL user host.

Solution:

1.      Log into the MySQL console

     mysql -u root -p

2.      Check Available databases using the following command

    SHOW DATABASES;

3.      Check MySQL User’s existing host using the following command

     SELECT Host, User FROM mysql.user WHERE User = 'username';

4.      Change MySQL sanga user host. Then sanga can access the database using any remote machine.

             ALTER USER 'sanga'@'localhost' TO 'sanga'@'%';

                you can update the host field. Run the following command to update the host

                UPDATE mysql.user SET Host = '%' WHERE User = 'sanga';

                 The '%' wildcard allows access from any IP address.

 

5.      Finally, apply the changes by flushing the privileges. This ensures that MySQL reloads the user table with the new host settings:

     FLUSH PRIVILEGES;