MySQL: Access denied for user 'test'@'localhost' (using password: YES) except root user

Solution 1:

Do not grant all privileges over all databases to a non-root user, it is not safe (and you already have "root" with that role)

GRANT <privileges> ON database.* TO 'user'@'localhost' IDENTIFIED BY 'password';

This statement creates a new user and grants selected privileges to it. I.E.:

GRANT INSERT, SELECT, DELETE, UPDATE ON database.* TO 'user'@'localhost' IDENTIFIED BY 'password';

Take a look at the docs to see all privileges detailed

EDIT: you can look for more info with this query (log in as "root"):

select Host, User from mysql.user;

To see what happened

Solution 2:

If you are connecting to the MySQL using remote machine(Example workbench) etc., use following steps to eliminate this error on OS where MySQL is installed

mysql -u root -p

CREATE USER '<<username>>'@'%%' IDENTIFIED BY '<<password>>';
GRANT ALL PRIVILEGES ON * . * TO '<<username>>'@'%%';
FLUSH PRIVILEGES;

Try logging into the MYSQL instance.
This worked for me to eliminate this error.

Solution 3:

Try:

CREATE USER 'golden'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON * . * TO 'golden'@'localhost';
FLUSH PRIVILEGES;

Or even better use: mysql_setpermission to create the user

Solution 4:

It looks like you're trying to make a user 'golden'@'%' but a different user by the name of 'golden'@'localhost' is getting in the way/has precedence.

Do this command to see the users:

SELECT user,host FROM mysql.user;

You should see two entries:

1) user= golden, host=%

2) user= golden, host=localhost

Do these Command:

DROP User 'golden'@'localhost';
DROP User 'golden'@'%';

Restart MySQL Workbench.

Then do your original commands again:

CREATE USER 'golden'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON * . * TO 'golden'@'%';

Then when you go to try to sign in to MySQL, type it in like this:

enter image description here

Hit 'Test Connection' and enter your password 'password'.

Solution 5:

First I created the user using :

CREATE user user@localhost IDENTIFIED BY 'password_txt';

After Googling and seeing this, I updated user's password using :

SET PASSWORD FOR 'user'@'localhost' = PASSWORD('password_txt');

and I could connect afterward.