Connect to MySQL via ssh tunnel to localhost
localhost, mysql, ssh, tunnel
Solution
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:
- install `socat` on 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`
Problem
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 ```