Connect to a MySQL Server over SSH in PHP

Establishing a connection to a MySQL database hosted on a remote Linux machine via SSH using PHP functions can be challenging. The error "mysqli_connect() expects parameter 6 to be string, resource given" may occur when using the provided code.

Understanding the Issue

The code attempts to use the mysqli_connect() function to connect to the database through an SSH tunnel. However, the mysqli_connect() function expects a string as the sixth parameter (indicating the tunnel), whereas the ssh2_tunnel() function returns a resource.

Solving the Issue with SSH Tunnel

To resolve this issue, set up an SSH tunnel to your MySQL database server. One effective method is to use a Jumpbox proxy for security enhancements. This approach involves creating a local port forwarding tunnel using the SSH client, effectively creating a secure channel between your local machine and the database server.

Step-by-Step Guide

Using Command Line Tools (SSH Tunnel Setup):

1. From your local terminal, execute the following SSH command:

ssh -fNg -L 3307:10.3.1.55:3306 [email protected] 

• Replace '3307' with your preferred local port number.
• Replace '10.3.1.55:3306' with the IP address and port of your MySQL database server.
• Replace '[email protected]' with your Jumpbox login credentials.

Connecting to the Database:

1. In your PHP script, establish the database connection using mysqli_connect():

$mysqli = mysqli_connect("127.0.0.1:3307", "DB_USERNAME", "DB_PASSWORD", "dbname");

• Use the local port number (3307 in this example) specified in the SSH tunnel setup.

Additional Considerations:

• Using a GUI MySQL client with SSH tunneling support (e.g., Visual Studio Code) can simplify the setup process.
• Private key authentication can enhance security. Use the '-i' switch in the SSH command to specify your private key path.
• Tunnel traffic through a Jumpbox/Bastion Host to mitigate direct database access vulnerabilities.