Php – Connect to a MySQL server over SSH in PHP

MySQLPHPssh

I have my database on remote Linux machine, and I want to connect using SSH and PHP functions (I am currently using ssh2 library for that). I tried using mysql_connect, but it gives me can't 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". How can I resolve this?

Best Answer

SSH Tunnel Solution

Set up an SSH tunnel to your MySQL database server (through a Jumpbox proxy for security).


(A) GUI Tools

Depending on your requirements, you can use a GUI MySQL client with SSH Tunnelling support built-in such as Visual Studio Code Forwarding a port / creating SSH tunnel, TablePlus or use PuTTY to setup local port forwarding.

On macOS, I like Secure Pipes or TablePlus.


(B) Command Line

Step 1.

ssh -fNg -L 3307:10.3.1.55:3306 username@ssh-jumpbox.com 

The key here is the '-L' switch which tells ssh we're requesting local port forwarding.

I've chosen to use port 3307 above. All traffic on my local machine directed to this port will now be 'port-forwarded' via my ssh client to the ssh server running on the host at address ssh-jumpbox.com.

The Jumpbox ssh proxy server will decrypt the traffic and establish a network connection to your MySQL database server on your behalf, 10.3.1.55:3306, in this case. The MySQL database server sees the connection coming in from your Jumpbox' internal network address.


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

<local_workstation_port>:<database_server_addr_remote_end_of_tunnel>:<database_server_port_remote_end> username@ssh_proxy_host.com

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)

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

-i /path/to/private-key

Step 2.

Tell your local MySQL client to connect through your SSH tunnel via the local port 3307 on your machine (-h 127.0.0.1) which now forwards all traffic sent to it through the SSH tunnel you established 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.


Security note
Don’t tunnel directly to your database server. Having a database server directly accessible from the internet is a huge security liability. Make the tunnel target address the internet address of your Jumpbox/Bastion Host (see example in step 1) and your database target the internal IP address of your database server on the remote network. SSH will do the rest.


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 detailing ssh command line tunnelling for MySQL connectivity.