13

I want to connect to remote MySQL via ssh tunnel with user that has 'localhost' access.

I use this to make a tunnel:

ssh -f -N -L 33306:localhost:3306 user@remote-host

and this to connect to host:

mysql -h 127.0.0.1 -P 33306 -uuser -ppassword

The error i get is:

ERROR 1045 (28000): Access denied for user 'user'@'remote-host' (using password: YES)

The problem is that user 'user'@'remote-host' (or 'user'@'%') does not exist, only 'user'@'localhost' does.

Is there a way to force remote host, without server-side modifications into thinking that i come from localhost? That's the only reason I would do the connection via ssh tunnel anyway.

Note:

If I want to connect with this command:

mysql -h localhost -P 33306 -uuser -ppassword

I get this error: 

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

Additional data:

On remote server in /etc/hosts the values are like this:

127.0.0.1       localhost
remote-ip       remote-host
alesf
  • 143
  • 1
  • 8
  • This sounds fishy. MySQL should already see the traffic `user@127.0.0.1`. Can you open the local port via telnet after establishing the tunnel? `telnet localhost 33306` You should see something like `q>~mysql_native_password` – Michael Berkowski Jan 30 '13 at 14:55
  • @MichaelBerkowski it says: [ales@host ~]$ telnet localhost 33306 Trying 127.0.0.1... Connected to localhost. Escape character is '^]'. 4 5.0.75? *Q/5ZJBK2,W?uQv|.kS – alesf Jan 31 '13 at 10:33
  • @MichaelBerkowski our sysadmin told me that the tunnel here is useful for bypassing the firewall (no need to open additional ports) but that the mysql user 'user'@'%' should still exist. – alesf Jan 31 '13 at 10:36
  • @alesf did you verify that you used the correct pasword/user name and that you have permissions to connect to the database? – schadr Nov 25 '15 at 06:34
  • @schadr I suspect I did, but I asked this question more than two years ago and I don't really know how I solved it or if I did at all. Thank you for taking the time. – alesf Nov 25 '15 at 10:04

2 Answers2

7

The simple way to create MySQL Tunnel to REMOTE HOST:

$ ssh -fNL TEMP_PORT:localhost:MYSQL_SERVER_PORT USER@SERVER_NAME

Test:

$ mysql -u root -p -h 127.0.0.1 -P TEMP_PORT
Cubiczx
  • 1,005
  • 11
  • 10
6

Please note that localhost and 127.0.0.1 are treated differently in mysql on unix. Quoting:

On Unix, MySQL programs treat the host name localhost specially, in a way that is likely different from what you expect compared to other network-based programs. For connections to localhost, MySQL programs attempt to connect to the local server by using a Unix socket file http://dev.mysql.com/doc/refman/5.7/en/connecting.html:

Furthermore, mysql client would silently try to use a socket file even if you explicitly specify -P on your command line:

This occurs even if a --port or -P option is given to specify a port number. To ensure that the client makes a TCP/IP connection to the local server, use --host or -h to specify a host name value of 127.0.0.1

Effectively, using this command mysql -h localhost -P 33306 -uuser -ppassword you're simply trying to connect to your local mysqld which is missing

Considering this, your question boils down to connecting to a remote server available over a domain socket.

If installing additional software meets your requirements 'without server-side modifications' you could use socat as described here: https://www.debian-administration.org/users/dkg/weblog/68.

Tailored for mysql, it could work as follows:

  1. install socat on both ends
  2. socat "UNIX-LISTEN:your_local_path/mysql.sock,reuseaddr,fork" EXEC:'ssh user@remote-host socat STDIO UNIX-CONNECT\:/your_server_path/mysql.sock"
  3. mysql -S your_local_path/mysql.sock -u user
Oleg Kuralenko
  • 11,003
  • 1
  • 30
  • 40