Geek Answers Handbook

Connect to MySQL via ssh tunnel in localhost

I want to connect to remote MySQL through an ssh tunnel with a user with "localhost" access.

I use this to create a tunnel:

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

and to connect to the host:

 mysql -h 127.0.0.1 -P 33306 -uuser -ppassword

The error I am getting is:

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

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

Is there a way to make the remote host without changes on the server side think that I came from localhost? This is the only reason I could connect through the ssh tunnel.

Note:

If I want to connect to 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 a remote server in / etc / hosts, the values ​​are as follows:

 127.0.0.1 localhost remote-ip remote-host
+6
source share
2 answers

Note that localhost and 127.0.0.1 handled differently in mysql on unix. Citation:

On Unix, MySQL programs handle the localhost host name in a way that is likely to be different than expected compared to other network programs. To connect to localhost, MySQL programs try to connect to the local server using the Unix socket file http://dev.mysql.com/doc/refman/5.7/en/connecting.html :

In addition, the mysql client silently tried to use the socket file, even if you explicitly specified -P on your command line:

This happens even if the -port or -P option is specified to specify the port number. For the client to establish a TCP / IP connection with the local server, use -host or -h to specify the host name value 127.0.0.1

Effectively using this command mysql -h localhost -P 33306 -uuser -ppassword you are just trying to connect to a local mysqld that is missing

Given this, your question boils down to connecting to a remote server accessible through a domain socket.

If the installation of additional software meets your requirements without any changes on the server side , you can use socat as described here: https://www.debian-administration.org/users/dkg/weblog/68 .

To work with mysql, it can work as follows:

  • install socat at both ends
  • socat "UNIX-LISTEN:your_local_path/mysql.sock,reuseaddr,fork" EXEC:'ssh user@remote-host socat STDIO UNIX-CONNECT\:/your_server_path/mysql.sock"
  • mysql -S your_local_path/mysql.sock -u user
+1
source

An easy way to create a MySQL Tunnel for a 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
+1
source

More articles:


All Articles