3

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.

1 Answer 1

2

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

1
  • Trying this on mysql 8 @ ubuntu 20 doesn't seem to work
    – GerardJP
    Oct 3, 2020 at 12:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.