CODEWITHSUNDEEP
mysqlsshlocalhosttunnel
alesf
alesf

Reputation: 143

Connect to MySQL via ssh tunnel to localhost

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

Upvotes: 13

Views: 10757

Answers (2)

Cubiczx
Cubiczx

Reputation: 1115

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

Upvotes: 8

Oleg Kuralenko
Oleg Kuralenko

Reputation: 11553

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

Upvotes: 6

Related Questions