Mysql – Error 1045 (28000): Access denied for user ‘theuser’@’localhost’ (using password: YES)

MySQL

This looks like a duplicate of many questions asked here. I haven't found the answer for my specific problem, so I'm posting it here.

I was trying to create a local database and a user with basic permissions to it:

CREATE DATABASE demodb;

Query OK, 1 row affected (0.00 sec)

USE demodb;

Database changed

CREATE TABLE demotable(id int, name VARCHAR(25));

Query OK, 0 rows affected (0.01 sec)

CREATE USER 'demouser' IDENTIFIED BY 'p';

Query OK, 0 rows affected (0.00 sec)

FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.01 sec)

GRANT SELECT ON demodb.demotable TO 'demouser';

Query OK, 0 rows affected (0.00 sec)

FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.00 sec)

With this setup, I wasn't able to log in:

mysql -u demouser -p demodb

Enter password:
ERROR 1045 (28000): Access denied for user 'demouser'@'localhost' (using password: YES)

mysql -u demouser -p -h 127.0.0.1 demodb

Enter password:
ERROR 1045 (28000): Access denied for user 'demouser'@'localhost' (using password: YES)

mysql -u demouser -p -h localhost demodb

Enter password:
ERROR 1045 (28000): Access denied for user 'demouser'@'localhost' (using password: YES)

I am using MySQL 5.6 on macOS using the official dmg installer.

Best Answer

The fix was to specify the IP address when creating the user and granting it permissions:

CREATE USER 'demouser'@'127.0.0.1' IDENTIFIED BY 'p';

Query OK, 0 rows affected (0.00 sec)

FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.01 sec)

GRANT SELECT ON demodb.demotable TO 'demouser'@'127.0.0.1';

Query OK, 0 rows affected (0.00 sec)

FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.01 sec)

Then you can log in this way:

mysql -u demouser -p -h 127.0.0.1 demodb

Credits to http://www.rathishkumar.in/2017/04/error-1045-28000-access-denied-for-user-user-host-using-password-YES.html

Related Question