Connect to a MySQL server over SSH in PHP
mysql, php, ssh
Solution
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.
Problem
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?