Typically “Access denied for user” error occurs while connecting to MySQL Database because the privileges are not properly set at the server.

Let us examine the following real-time scenario where a user from the host 192.168.1.1 is trying to connect to MySQL server running on 192.168.1.6.

      My PC                       MySQL Server
   192.168.1.1       ----------    192.168.1.6

Let us solve the problem if user encounters an error like the following while connecting to the server.

[neo@techpulp ~]# mysql -u root -p -h 192.168.1.6
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'192.168.1.1' (using password: YES)
[neo@techpulp ~]#

To solve this problem, enough privileges should be assigned for the user ‘root‘ so that the login is allowed from the host (192.168.1.1 in this case) we are trying to connect from.

First login to the MySQL server locally and connect to the database. and assign the privileges using GRANT statement as shown below. In this particular example, I am assigning full permissions to all databases for the user ‘root‘ logging in from the host 192.168.1.1. You need to extract the user name and the host name from the MySQL ERROR message to use with GRANT statement. i.e The user name and host information ‘root’@’192.168.1.1′ is taken from the MySQL error message. You can replace ‘mypassword’ with whatever password you like and it should be used while connecting from the host 192.168.1.1. Once the privileges are set in MySQL command shell, you need to run mysqladmin reload as shown below to let the live MySQL server to update the privileges.

[root@dbserver ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8 to server version: 5.0.22

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.1.1' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;
Query OK, 0 rows affected (0.11 sec)

mysql> quit
Bye
[root@dbserver ~]# mysqladmin reload -u root -p
Enter password:
[root@dbserver ~]# exit

So now you are all set to connect to MySQL database server from your host (192.168.1.1 in this case).

[neo@techpulp ~]# mysql -u root -p -h 192.168.1.6
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.0.22

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| wikidb             |
+--------------------+
3 rows in set (0.20 sec)

mysql> quit
Bye
[neo@techpulp ~]#