I've been trying to figure out why I keep getting this error when I try to connect to the MySQL server with the following commands.

$~ mysql -u username -h SQLserver -p
Enter password:
ERROR 1045 (28000): Access denied for user 'username'@'myIP' (using password: YES)

I've done the following:
Port is open in the firewall other wise I wouldn't get the error it'd just timeout.
MySQL server not running with skip-networking or bind-address
username has host as '%' and I can connect locally so the password is correct.

GRANT USAGE ON *.* TO username@% IDENTIFIED BY 'password';

I wanted to know if anyone had ideas or ran into this issue before and solved it? As you can see I'm able to login locally but when I try to do -h server I get the error.

mysql> SELECT user(),Current_user();
| user()             | Current_user() |
| username@localhost | username@%     |
1 row in set (0.00 sec)

Results for comments:

mysql> select user, host from mysql.user where user='username';
|   user   | host |
| username | %    |
1 row in set (0.00 sec)

mysql> show grants for 'username';
| Grants for username@%                                                                                            |
| GRANT ALL PRIVILEGES ON `DB2`.* TO 'username'@'%'                                                           |
2 rows in set (0.00 sec)
  • mysql> select user, host from mysql.user where user='username';? – quanta Sep 8 '12 at 3:22
  • @quanta I've included the sql results. – LF4 Sep 8 '12 at 3:27
  • mysql> show grants for 'username'@'%'; – quanta Sep 8 '12 at 3:28
  • @quanta Updated with results – LF4 Sep 8 '12 at 3:36
  • What OS are you using? Is SELinux involved? – Safado Sep 8 '12 at 3:40

I seem to be recalling from the deep recesses of my memory that in order for a 'user'@'%' to work, you must also have a 'user'@'localhost'.

Try making a localhost host user with the same user name.

I could be really wrong though.

| improve this answer | |
  • Added localhost but still get the same error. – LF4 Sep 8 '12 at 3:32
  • dev.mysql.com/doc/refman/5.5/en/adding-users.html -- 3rd bullet point down is where I got that from, in case anyone wonders. But apparently it would treat the user as an guest, not deny them access. So obviously not your solution. – Safado Sep 8 '12 at 3:36

It may be out of scope, but I prefer to never leave a db listen on a public port and this could help you to avoid the issue.

I setup all remote databases I ever need with [auto]ssh to listen on the localhost of the machine, I want to access the db. This:

autossh N -f -L 3307: yourserver

would bind port on of yourserver to the localhost port 3307 of the machine your call from. You don't even need sudo privileges to bind this port.

When you now access the server with

mysql -u user -P 3307 -h -p      

you are a "localhost" user on the remote host and there would be no longer a need for a 'user'@'remote' or 'user'@'%' directive. But don't use

mysql -u user -P 3307 -h localhost -p 

because this way the mysql client tries to connect to the socket, which we have not forwarded this way and therefore is not accessible.

Thought SSH is fast after a connection setup, you should feel no overhead. autossh tries endlessly to reestablish the connection after a dropout in comparision to ssh, which will just terminate.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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