Connect to a MySQL server over SSH in PHP

Question

i read one post regarding this topic, i also want to do the same,

i have my database on remote linux machine, and i want to connect using ssh and php funcitons,(i am currently using ssh2 library for that) i tried using mysql_connect, but it gives me cant access (although i have granted permission) when i tried using this function:

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

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

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

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

i got error"mysqli_connect() expects parameter 6 to be string, resource given", can anyone please help me on this

Answer 1

Unfortunately, the ssh2 tunnel offered by php doesn't seem able to handle a remote mysql connection as you cannot specify the local port to tunnel (it only works with port 22 or whatever ssh port your remote server is running on). My solution to this is to just open the tunnel via exec() operator and connect as usual from there:

exec('ssh -f -L 3307:127.0.0.1:3306 [e-mail] sleep 10 > /dev/null');
$mysqli = new mysqli('127.0.0.1', 'user', 'password', 'database', '3307');

Answer 2

I believe that the reason I (and I suppose most people) have a problem getting this to work is because the user in the mysql server is set to only allow from "localhost" and not 127.0.0.1, the IP address of localhost.

I got this to work by doing the following steps:

Step 1: Allow 127.0.0.1 host for target user

SSH normally into your server, and log in with the mysql root user, then issue the command:

GRANT ALL ON yourdbname.* TO yourdbuser@127.0.0.1 IDENTIFIED BY 'yourdbpassword';

The key of course, is specifying 127.0.0.1 above.

Step 2: Start local SSH tunnel to MySQL

You can now start your local SSH tunnel to the remote MySQL server, like so:

ssh -vNg -L 33306:127.0.0.1:3306 [e-mail]

-v makes ssh operate in verbose mode, which kind of helps to see what's happening. For example, you'll see debugging output like this in your terminal console when you attempt a connection:

debug1: client_input_global_request: rtype [e-mail] want_reply 0
debug1: Connection to port 33306 forwarding to 127.0.0.1 port 3306 requested.

and output like this when you close the connection:

debug2: channel 2: is dead
debug2: channel 2: garbage collecting
debug1: channel 2: free: direct-tcpip: listening port 33306 for 127.0.0.1 port 3306, connect from 127.0.0.1 port 52112 to 127.0.0.1 port 33306, nchannels 3

-N makes ssh issue no commands, and just wait instead after establishing connection.

-g allows remote hosts to connect to local forwarded ports. Not completely sure if this is necessary but it might be useful for multiplexing multiple connections through the same SSH tunnel.

-L This is the main parameter that specifies the local port 33306 to connect to the remote host's local IP address 127.0.0.1 and the remote host's mysql port, usually 3306.

You can use whatever mechanisms / other parameters needed after this to connect through SSH to your remote host. In my case, I use key files configured in my ~/.ssh/config so I just need to specify user@host to get in.

Issuing the command like this runs SSH in the foreground, so I can easily close it with Ctrl + C. If you want to run this tunnel in a background process you can add -f to do this.

Step 3: Connect from PHP / other mysql compatible methods

The SSH tunnel running from above on your localhost will behave exactly like as if your mysql was running on 127.0.0.1. I use port 33306 (note the triple 3) which lets me run my local sql server at its normal port. You can now connect as you would normally do. The mysql command on the terminal looks like this:

mysql -h 127.0.0.1 -P 33306 -u yourmysqluser -p

where -P (capital P) specifies the port where your SSH tunnel's local end is accepting connections. It's important to use the 127.0.0.1 IP address instead of localhost because the mysql cli will try to possibly use the linux socket to connect.

For PHP connection strings, my data source name string (for my Yii2 config) looks like this:

'dsn' => 'mysql:host=127.0.0.1;dbname=yourdbname;port=33306',

Passwords, and usernames are specified as normal.

Answer 3

Try this:

Step 1.

To set up a tunneled SSH connection to a MySQL server at remotehost.com

ssh -fNg -L 3307:127.0.0.1:3306 [e-mail]

The key here is -L which says we're doing local port forwarding.

Local Port Frowarding Syntax
The syntax is a little tricky but can be seen as:

<local_port>:<server_addr_remote_end>:<server_port_remote_end> [e-mail] 

If you're interested in the other switches, they are: -f (go to background) -N (do not execute a remote command) -g (allow remote hosts to connect to local forwarded ports)

Public Key Authentication, add (-i) switch to above:

-i /path/to/public/key

Step 2.

Now tell the local MySQL client to connect to port 3307 on your machine (which is forwarded via ssh as connect to '127.0.0.1:3306' at remotehost.com) over the SSH tunnel setup in Step 1.

mysql -h 127.0.0.1 -P 3307 -u dbuser -p passphrase

Data exchange between client and server is now sent over the encrypted ssh connection and is secure.

Step 3.

Now connect up your PHP application with:

<?php
      $smysql = mysql_connect( "127.0.0.1:3307", "dbuser", "passphrase" );
      mysql_select_db( "db", $smysql ); 
?>

Credit to Chris Snyder's great article at http://chxo.com/be2/20040511_5667.html

GUI Tools

Depending on your requirements, an alternative approach would be to use a GUI MySQL client with SSH Tunnelling support such as http://www.sequelpro.com/ or use PuTTY to setup the port forwarding.

UPDATE 1 Today, I reviewed a viable alternative macOS GUI ssh tunnelling tool called secure pipes which you may find useful.

Answer 4

According to the docs, that last parameter is supposed to be a socket or pipe name, something like '/var/run/mysql/mysql.sock'. Since you're not connecting using a UNIX socket, that doesn't apply to you... so try just leaving it out.

Answer 5

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.

Answer 6

Make sure that your username and password that you are connecting with has the right hostname permissions. I believe you can use '%' for a wildcard. Also if you are connecting to remote machine (which I would assume you are if you are trying to ssh into it) that is not on your local network you will have to forward the ports on your router where the server is for outside traffic to be able to connect to it.

http://www.lanexa.net/2011/08/create-a-mysql-database-username-password-and-permissions-from-the-command-line/