Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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';
FLUSH PRIVILEGES;

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 *.* TO 'username'@'%' IDENTIFIED BY PASSWORD '*F42AD03PASSWORDHASHADF4021C86B' |
| GRANT ALL PRIVILEGES ON `DB2`.* TO 'username'@'%'                                                           |
+----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
share|improve this question
    
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
show 3 more comments

2 Answers

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:127.0.0.1:3306 yourserver

would bind port on 127.0.0.1:3306 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 127.0.0.1 -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.

share|improve this answer
add comment

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.

share|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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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