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

Question

I encountered a problem with the mysql non root / admin user, I follow the instructions below to create the user and his privileges, correct me if I am wrong

I install mysql in RHEL 5.7 64bit , the packages are listed below, as soon as I did rpm install we

• creating mysql db using mysql_install_db , then
• start mysql service then
• using mysql_upgrade , we also make a server.

After this process, I can log in as root , but with a non-root user I cannot log in to the server:

 [root@clustertest3 ~]# rpm -qa | grep MySQL MySQL-client-advanced-5.5.21-1.rhel5 MySQL-server-advanced-5.5.21-1.rhel5 [root@clustertest3 ~]# cat /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 # Disabling symbolic-links is recommended to prevent assorted security risks; # to do so, uncomment this line: # symbolic-links=0 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid [root@clustertest3 ~]# ls -ld /var/lib/mysql/mysql.sock srwxrwxrwx 1 mysql mysql 0 Nov 30 11:09 /var/lib/mysql/mysql.sock mysql> CREATE USER 'golden'@'%' IDENTIFIED BY 'password'; Query OK, 0 rows affected (0.00 sec) mysql> GRANT ALL PRIVILEGES ON * . * TO 'golden'@'%'; Query OK, 0 rows affected (0.00 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) mysql> SELECT USER(),CURRENT_USER(); +----------------+----------------+ | USER() | CURRENT_USER() | +----------------+----------------+ | root@localhost | root@localhost | +----------------+----------------+ 1 row in set (0.00 sec) [root@clustertest3 ~]# mysql -ugolden -p Enter password: ERROR 1045 (28000): Access denied for user 'golden'@'localhost' (using password: YES) 

This is the problem I am facing, is there any solution?

Answer 1

Do not grant all privileges for all databases to a non-root user, he is insecure (and you already have "root" with this role)

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

This operator creates a new user and grants the privileges selected to him. I.e:.

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

Check out the docs to see the details of all privileges.

EDIT: you can find additional information on this request (log in as "root"):

 select Host, User from mysql.user; 

To find out what happened

Answer 2

If you are connecting to MySQL using a remote machine (workbench example), etc., use the following steps to resolve this error on the 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 helped me fix this error.

Answer 3

Try:

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

Or even better to use: mysql_setpermission to create a user

Answer 4

It looks like you are trying to make the user "golden" @ "%", but another user named "golden" @ "localhost" interferes / has priority.

Run this command to see users:

 SELECT user,host FROM mysql.user; 

You should see two entries:

1) user = golden, host =%

2) user = golden, host = local

Run these commands:

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

Restart MySQL Workbench.

Then run the original commands again:

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

Then, when you try to enter MySQL, enter it like this:

Click "Test Connection" and enter your password "password".

Answer 5

First I created a user using:

 CREATE user user@localhost IDENTIFIED BY 'password_txt'; 

After googling and seeing this , I updated my user password with:

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

and I could connect later.

Answer 6

For everyone who has done all the advice, but the problem still persists.

Check the stored procedure and view the DEFINERS. These identifiers may no longer exist.

My problem arose when we changed the main pattern (%) to an IP address, making the database more secure. Unfortunately, there are some views that still use "user" @ "%", although "user" @ "172 ...." is technically correct.

Answer 7

I also have a similar problem, and later I discovered that it was because I changed my hostname (not localhost ).

So I enable it by specifying --host=127.0.0.1

 mysql -p mydatabase --host=127.0.0.1 

Answer 8

According to how you create your user, MySQL interprets a different way. For example, if you create a user as follows:

 create user user01 identified by 'test01'; 

MySQL expects you to grant some privilege using grant all on <your_db>.* to user01;

Do not forget flush privileges;

But if you create such a user (passing the IP address), you must change it to:

 create user 'user02'@'localhost' identified by 'teste02'; 

therefore, to give some privileges, you must do this:

 grant all on <your_db>.* to user02@localhost; flush privileges; 

Answer 9

Verify that the user has a localhost entry in the users table. That was my problem. EX:

 CREATE USER 'username'@'localhost' IDENTIFIED BY 'password'; 

Answer 10

In my case, the same error occurs because I tried to use mysql by simply typing "mysql" instead of "mysql -u root -p"

Answer 11

connect your server from mysqlworkbench and run this command-> ALTER USER 'root' @ 'localhost', identified as 'your password';

Answer 12

For annoying searches that get here after searching for this error message:

Access denied for someuser @where user (using password: YES)

For me, the problem was not to quote the password. eg. I needed to use -p'password' instead of -ppassword

Answer 13

Just add the computer name instead of "localhost" in the hostname or address of the MySQL host.