Connecting to a Remote MySQL Database via PHP with SSH Tunneling

Connecting to a remote MySQL database through PHP is a common task in web development. However, for security reasons, it's often advisable to establish this connection using SSH tunneling.

This technique allows you to securely connect to a remote MySQL server while keeping your data encrypted during transmission.

In this comprehensive guide, we'll walk you through the steps to connect to a remote MySQL database via PHP with SSH tunneling.

What Is SSH Tunneling?

SSH tunneling, also known as SSH port forwarding, is a method for securely transmitting data between two networked devices through an encrypted connection.

It can be used to access services that are typically protected by firewalls or other security measures.

In the context of connecting to a remote MySQL database, we'll create an SSH tunnel that forwards the MySQL port from the remote server to your local machine, allowing you to interact with the database securely.

Prerequisites

Before you begin, make sure you have the following:

  1. Access to a Remote Server: You should have SSH access to the remote server where the MySQL database is hosted.

  2. PHP Installed: PHP should be installed on your local machine or web server.

  3. SSH Key Pair (Recommended): Using SSH keys for authentication is more secure than using passwords. If you haven't already set up an SSH key pair, consider doing so.

Step 1: Establish an SSH Connection

First, you need to establish an SSH connection to the remote server. You can do this using the PHP shell_exec() function or by using the ssh2 extension if it's enabled on your PHP installation. Here, we'll demonstrate using shell_exec():

// SSH connection information
$sshHost = 'your-ssh-server.com';
$sshUser = 'ssh-username';
$sshPassword = 'ssh-password';

// Remote MySQL server information
$dbHost = 'localhost'; // Because the SSH tunnel will forward the connection locally
$dbPort = 3306; // Default MySQL port
$dbUser = 'mysql-username';
$dbPassword = 'mysql-password';

// Create an SSH tunnel
$command = "ssh -L {$dbPort}:{$dbHost}:{$dbPort} {$sshUser}@{$sshHost} -N -f -L";
shell_exec($command);

In the code above:

This command establishes an SSH tunnel that forwards connections from your local machine's port 3306 to the remote MySQL server ($dbHost and $dbPort). Replace the placeholders with your specific information.

Step 2: Connect to MySQL via PHP

After establishing the SSH tunnel, you can connect to the remote MySQL server using PHP. Use the mysqli extension or PDO to create the MySQL connection:

// MySQL connection
$mysqli = new mysqli($dbHost, $dbUser, $dbPassword, $dbName);

// Check the connection
if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

In this code:

Make sure to replace the placeholders with your actual MySQL connection details.

Step 3: Perform Database Operations

Once connected to the remote MySQL database, you can perform various database operations such as querying, inserting, updating, or deleting data using PHP.

// Example query
$query = "SELECT * FROM my_table";
$result = $mysqli->query($query);

// Process the query result
if ($result) {
    while ($row = $result->fetch_assoc()) {
        // Process each row
    }
} else {
    echo "Error: " . $mysqli->error;
}

// Close the MySQL connection when done
$mysqli->close();

Step 4: Close the MySQL Connection and SSH Tunnel

After completing your database operations, remember to close the MySQL connection and the SSH tunnel:

// Close the MySQL connection
$mysqli->close();

// Close the SSH tunnel (stop the SSH process)
$sshTunnelPid = shell_exec("pgrep -o -f 'ssh -L {$dbPort}:{$dbHost}:{$dbPort}'");
shell_exec("kill -9 $sshTunnelPid");

This code finds and stops the SSH tunnel process using its process ID ($sshTunnelPid).

Conclusion

Connecting to a remote MySQL database via PHP with SSH tunneling is a secure and efficient way to interact with your database. By establishing an SSH tunnel and using PHP's database extensions, you can safely transmit data between your local machine and the remote server while keeping it encrypted. Remember to follow security best practices, such as using SSH keys and limiting access to your server, to enhance the security of your database connections.