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

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 2

The tunnel must be keep open during the course of the SQL action(s). The following example from RJMetrics explains:

Here's the generic SSH command syntax:

ssh -f -L bind-ip-address:bind-port:remote-ip-address:remote-port \
username@remote-server [command] >> /path/to/logfile

Here's how to securely establish a remote database connection in just 2 lines of PHP code:

shell_exec("ssh -f -L 127.0.0.1:3307:127.0.0.1:3306 [email protected] sleep 60 >> logfile");  
$db = mysqli_connect("127.0.0.1", "sqluser", "sqlpassword", "rjmadmin", 3307);

We use the shell_exec() function to create the tunnel with a 60 second opening window, and then use the mysqli_connect() function to open a database connection using the forwarded port. Note that we must use the "mysqli" library here because mysql_connect() does not allow us to specify a port and mysql_* functions are deprecated.

sleep 60: When it comes to tunnel connections, we basically have two options: leave the connection open all the time or open it and close it as needed. We prefer the latter, and as such we don't specify the -N option when establishing a tunnel, which would leave it open until the process is manually killed (bad for automation). Since -N is not specified, our tunnel will close itself as soon as its SSH session isn't being used for anything. This is ideal behavior, except for the few seconds between when we create the tunnel and when we get a MySQL connection up and running via the tunnel. To buy us some time during this period, we issue the harmless sleep 60 command when the tunnel is created, which basically buys us 60 seconds to get something else going through the tunnel before it closes itself. As long as a MySQL connection is established in that timeframe, we are all set.