SSH Tunnel MySQL Connection with socket-connection via PhpStorm

user_1256697 0
Viewed 2099

By default, Database Manager from PhpStorm works well. But currently on a special Provider (1u1.de) I have some trouble to got this work.

I can connect to the Provider via SSH. If I want to connect to MySQL database, I have to use: 

mysql --host=localhost --user=dbo123123123 -S /tmp/mysql5.sock --password='123123123';

That's works well via CLI on Server, but I didn't find a way to connect via PhpStorm to this Database.

For me it seems that the "socket-connection" may be the Problem. Does anybody have a clue how to got this to work?

Part of the Solution (?!):

Maybe a first part of an solution, I found that you be able to forwarding an Socket to your local pc as own socket this way:

ssh -nNT -L $(pwd)/yourLocal.sock:/var/run/mysqlREMOTEMYSQL.sock user@somehost

Source of Information

This show me, that the Socket is established:

netstat -ln | grep mysql unix 2 [ ACC ] STREAM LISTENING 3713865 /myFolder/mysql5.sock

But I'm still unable to connect to this Socket with:

mysql -h localhost --protocol=SOCKET -u'username' -p'mypassword' -S /myFolder/mysql5.sock

Got this Error:

ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 95 "Operation not supported"
jetbrains-ide phpstorm ssh-tunnel sockets mysql
2 Answers

ssh -L /tmp/mysql.sock:/var/run/mysqld/mysqld.sock sshuser@remotehost

and then

mysql -h localhost --protocol=SOCKET -u'username' -p'mypassword' -S /tmp/mysql.sock

seems to work fine for me

user_5416340

Use SSH to setup a port forward, this will allow you to connect securely to your database without exposing it to the world.

On ssh, use the -L argument to establish the tunnel.

ssh -L <local_port>:<remote_host>:<remote_port> user@host

This will open <local_port> on your local machine, and then redirect all packets out the other side of the tunnel, destened for the <remote_host>:<remote_port>

In your case, you might want to try something like this:

ssh -L 3306:127.0.0.1:3306 user@mybox.1u1.de

After establishing the tunnel, you will be able to connect to the database through a local port.

From your local machine, not the 1u1 host,

mysql -u <user> -p --host 127.0.0.1 --port 3306

If this works properly, you should be able to configure PhpStorm to use the same address, 127.0.0.1:3306

The SSH tunnel will need to remain open the entire time you need to be connected to the database.

user_17906440
Related