Remote connect to mysql server?

Question

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)

`mysql> select user, host from mysql.user where user='username';`? — quanta, Sep 08 '12 at 03:22
@Safado I'm using Slackware 13.37 and it doesn't have SElinux installed. — krizzo, Sep 08 '12 at 03:47
maybe `mysql -u username -h SQLserver -p DBNAME` will do the trick, because it will kick you directly into the db the user has rights for. Your also may want to have a look at the syntax of the user credentials. If you define "localhost" as source for valid authentication, you may get problems logging in from "remote" — moestly, Sep 08 '12 at 07:32
@ansi_lumen Yep tried multiple ways to connect still getting Access denied for user. I've always had bad luck with mysql allow % wildcard. — krizzo, Sep 09 '12 at 01:55

score 1 · Accepted Answer · answered Sep 08 '12 at 03:27

1

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.

answered Sep 08 '12 at 03:27

Safado

4,786
7
37
54

Added localhost but still get the same error. – krizzo Sep 08 '12 at 03:32
http://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 08 '12 at 03:36

moestly · Answer 2 · 2012-09-09T13:21:43.913

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.

Remote connect to mysql server?

2 Answers2