Connect to a MySQL server over SSH in PHP

Question

I'd like to establish an ssh tunnel over ssh to my mysql server.

Ideally I'd return a mysqli db pointer just like I was connecting directly.

I'm on a shared host that doesn't have the SSH2 libraries but I might be able to get them installed locally using PECL.

If there's a way that uses native commands that would be great.

I was thinking something like this, but without those libraries it won't work.

$connection = ssh2_connect('SERVER IP', 22); 

ssh2_auth_password($connection, 'username', 'password');

$tunnel = ssh2_tunnel($connection, 'DESTINATION IP', 3307);

$db = new mysqli_connect('127.0.0.1', 'DB_USERNAME', 'DB_PASSWORD', 
                         'dbname', 3307, $tunnel)
    or die ('Fail: '.mysql_error());

Anyone have any ideas? I'm running a shared CentOS linux host at liquidweb.

Any thoughts on making the tunnel persistent? Is it possible to establish it with another script and just take advantage of it in PHP?

Thanks.

Answer 1

Eric,

I think you are out of luck on this one. You can either use the ssh extension in your PHP code, or if you have access to the server, you could try to create a ssh tunnel on the command-line.

You probably need special permissions to do that, though. It also looks like you don't have ssh access to this hosting account.

--Joao

Answer 2

I think your best bet is to find a different hosting provider; I suggest a VPS solution (Virtual Private Server) which gives you full control over your web host. THat way, if you don't like the default configuration, you can upgrade it yourself.

Answer 3

I would use the autossh tool to create a persistent ssh tunnel to your mysql database. Then all you have to do in your PHP code is point it to localhost.

You can test this (without automatic restart) by doing:

ssh -L 3306:localhost:3306 [email protected]

Setup a ssh key so that you don't need to use passwords, and tweak the .ssh/authorized_keys file on the mysql system to only allow it to be used for port forwarding.

For more info on ssh tricks see Brian Hatch's excellent series on SSH and port forwarding.

Answer 4

It is possible, but why? It's more complicated than it needs to be, and error prone.

Can you not run the database locally? If not, can you not use something like SQLite, XML files or something else that doesn't require a separate server daemon?

You really do not want to initialise the tunnel inside the PHP scripts. Initialising an SSH tunnel takes a long time (can easily be a second or two), so that will mean every page that connects to the database will have a 2 seconds delay while loading..

If you have to do this (which I strongly recommend against), the best method would be to have a script that maintains the connection in the background..

Setup a SSH keypair. Then using autossh, or a simple script which would execute the required SSH command, wait till the process died and start it again. It could be more intelligent and try and run a command every 10-20 seconds, reconnecting if it fails.

Using the SSH keypair, you wouldn't have to hardcode the remote users password in the script. I would recommend limiting what the remote user can do (by using a dedicated tunnel user, and giving it a restricted shell, see this question)

In short, I would strongly suggest trying SQLite first, then see how feasible it to store data using XML files, then try bugging your web-host about getting a local MySQL server, then look into the SSH tunnelling option.

Answer 5

Hey , even i tried it by doing ssh both by root credentials and and public private key pair, but it allows me to conect through command line but not through php code. I tried by creating tunnel also(by using ssh2 functions),ans running shell commands from php code(system,exec etc), nothing worked. Finally i tried ssh2 function to execute shell command and it finally worked :) Here is code, if it helps you:----

$connection = ssh2_connect($remotehost, '22'); if (ssh2_auth_password($connection, $user,$pass)) { echo "Authentication Successful!\n"; } else { die('Authentication Failed...'); }

$stream=ssh2_exec($connection,'echo "select * from zingaya.users where id=\"1606\";" | mysql'); stream_set_blocking($stream, true); while($line = fgets($stream)) { flush(); echo $line."\n"; }

it worked for me try this if want to use php functions specifically.