Connect to a MySQL server over SSH in PHP

by

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 [email protected]

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.

More Related Contents:

  1. Connect to a MySQL server over SSH in PHP
  2. php+mysql Fatal error: Call to undefined function mysql_connect()
  3. Using PHP data in MySQL [closed]
  4. drop down menu using php and mysql [closed]
  5. php code for multiple search box after connecting to mysql [closed]
  6. Checking the availability of rooms
  7. How to sort user data by id
  8. mysql_fetch_array()/mysql_fetch_assoc()/mysql_fetch_row()/mysql_num_rows etc… expects parameter 1 to be resource
  9. Using PHP 5.5’s password_hash and password_verify function
  10. Example of how to use bind_result vs get_result
  11. mysqli::query(): Couldn’t fetch mysqli
  12. I have an array of integers, how do I use each one in a mysql query (in php)? [duplicate]
  13. Levenshtein: MySQL + PHP
  14. mysql PDO how to bind LIKE
  15. I need my PHP page to show my BLOB image from mysql database
  16. How to test if a MySQL query was successful in modifying database table data?
  17. jQuery UI Sortable, then write order into a database
  18. How do i “echo” a “Resource id #6” from a MySql response in PHP?
  19. Delete multiple rows by selecting checkboxes using PHP
  20. Special characters in PHP / MySQL
  21. Fetching single row, single column with PDO
  22. IP address storing in MySQL database using PHP [duplicate]
  23. “No such file or directory” or “No such host is known” when running migrations
  24. Password_Hash not working on my PHP login
  25. Select last 20 order by ascending – PHP/MySQL
  26. Formatting an SQL timestamp with PHP
  27. mysqli – fetch_Array error call to a member function fetch_array() on a non-object mysqli [duplicate]
  28. Replacing \r\n with PHP
  29. How to check if a value already exists to avoid duplicates?
  30. How to use MySQL Found_Rows() in PHP?

Leave a Comment