Access denied for user 'test_user'@'test_host' (using password: YES)

Question

I have a mysql user called test_user

GRANT all privileges on *.* to 'test_user'@'%' identified by 'test_passwd'

I can use this user remotely from my JAVA code. When i run the code in the server, i got

Access denied for user 'test_user'@'test_host' (using password: YES)

I already ran FLUSH PRIVILEGES; after grant all privileges for test_user and restarted the mysql service.

I tried to login mysql in the server using console

mysql -utest_user -ptest_passwd
ERROR 1045 (28000): Access denied for user 'test_user'@'localhost' (using password: YES)

It seems i can only using test_user remotely but not from the server itself. Any suggestions to fix this?

-------New test----------

I tried to do not provide password in the server, it is working

mysql -utest_user

I need to use password for both remotely and locally. Any suggestions?

score 0 · Answer 1 · edited May 23 '17 at 12:06

0

It should be:

CREATE USER 'monty'@'%' IDENTIFIED BY 'some_pass';

and then

GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost'
    ->     WITH GRANT OPTION;

If it doesn't work, try logging in with root and see:

SHOW GRANTS FOR 'admin'@'localhost';

Replace your values everywhere

Try this post: SO solution to this problem

edited May 23 '17 at 12:06

Community

1
1

answered Apr 28 '15 at 19:05

ACV

9,964
5
76
81

When you do: mysql -utest_user and then SHOW GRANTS, what does it show you? – ACV Apr 28 '15 at 19:13
Try the link I've added – ACV Apr 28 '15 at 19:14
i saw `GRANT ALL PRIVILEGES ON *.* TO 'test_user'@'localhost' WITH GRANT OPTION` – Tester Apr 28 '15 at 19:16
Restart mysql server. – ACV Apr 28 '15 at 19:23
After removed `anonymous user`. The problem is solved. `DELETE FROM mysql.user WHERE User='';` – Tester Apr 28 '15 at 19:25

BK435 · Accepted Answer · 2015-04-28T19:27:26.427

I have run into a similar problem before.

Having % should allow a user to connect from anywhere including localhost assuming you granted privileges and flushed privileges. However, If you did not make a secure install of mysql you most likely have a ghost localhost left on your machine. So not only would you be able to just run mysql with no user, or password provided from localhost command line but the ghost localhost for some reason conflicts with any other users (besides root) that have been created from logging in on the localhost.

If you do select user, password, host from mysql.user; You will likely see a user with no name, no password and a host localhost.

Delete that user using drop user ''@'localhost;

flush privileges again.

Try to connect using your other user now on localhost and it should work.

Access denied for user 'test_user'@'test_host' (using password: YES)

2 Answers2