How to Connect to a MySQL Server Over SSH in PHP: Troubleshooting and Best Practices

Connect to a MySQL Server Over SSH in PHP

Overview

Connecting to a remote MySQL server over SSH requires establishing a secure tunnel between your local machine and the MySQL database server. This guide outlines the steps involved in setting up the SSH tunnel and connecting to the database using PHP.

Error Resolution

The error "mysqli_connect() expects parameter 6 to be string, resource given" occurs when the sixth parameter of mysqli_connect() is not a string representing the MySQL tunnel. To resolve this issue, change the sixth parameter to be the output of the ssh2_tunnel function, as shown below:

<code class="php">$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, stream_get_contents($tunnel))
    or die ('Fail: '.mysql_error());</code>

Secure Port Forwarding

The recommended solution for connecting to a MySQL server over SSH is to establish an SSH tunnel. Here are two options for setting up the tunnel:

GUI Tools

Use GUI MySQL clients like Visual Studio Code or TablePlus that offer built-in SSH tunneling support.

Command Line

Set up local port forwarding using the following command:

<code class="bash">ssh -fNg -L 3307:10.3.1.55:3306 [email&#160;protected] </code>

Once the tunnel is established, connect to the MySQL server through the local port (3307) in your PHP script:

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

Security Considerations

It's crucial to establish the tunnel through a jumpbox server to prevent exposing your database server directly to the internet. Additionally, consider using SSH key authentication instead of password authentication for enhanced security.

The above is the detailed content of How to Connect to a MySQL Server Over SSH in PHP: Troubleshooting and Best Practices. For more information, please follow other related articles on the PHP Chinese website!