How to connect to remote mysql via php+ssh?

Member

by darion , in category: PHP General , 9 months ago

How to connect to remote mysql via php+ssh?

Facebook Twitter LinkedIn Telegram Whatsapp

1 answer

by raven_corwin , 9 months ago

@darion 

To connect to a remote MySQL server via PHP and SSH, you can follow these steps:

  1. Install an SSH library for PHP, such as phpseclib, which will allow you to establish an SSH connection from your PHP script.
  2. Use the SSH library to establish an SSH connection to the remote server. Here's an example code snippet to connect to the remote server:
1
2
3
4
5
6
require 'vendor/autoload.php';

$ssh = new phpseclibNetSSH2('remote-server-ip');
if (!$ssh->login('username', 'password')) {
    exit('Login Failed');
}


Replace 'remote-server-ip' with the IP address or hostname of the remote server, 'username' with your SSH username, and 'password' with your SSH password.

  1. Once the SSH connection is established, you can use the SSH tunneling feature to forward the MySQL port (usually 3306) from the remote server to your local machine. This can be done using the tunnel() method of the SSH library. Here's an example code snippet:
1
$ssh->tunnel('<local-ip>', '<local-port>', '<remote-ip>', '<remote-port>');


Replace '' with the IP address of your local machine, '' with a port number that is not in use on your local machine (e.g., 8888), '' with the IP address of the remote server, and '' with the MySQL port on the remote server (usually 3306).

  1. Once the SSH tunnel is established, you can use PHP to connect to the MySQL server on the remote machine as if it were running locally. Here's an example code snippet to connect to the MySQL server:
1
$mysqli = new mysqli('127.0.0.1', 'mysql_username', 'mysql_password', 'database_name');


Replace 'mysql_username', 'mysql_password', and 'database_name' with your MySQL server credentials and database name.

  1. You can now run MySQL queries using the $mysqli object just like you would if the MySQL server were running on your local machine.


It is important to ensure that the SSH connection is securely established and that you have proper permissions to connect to the MySQL server on the remote machine. Additionally, you should consider setting up SSH keys for authentication instead of using passwords for increased security.