PDO via SSH2 in PHP?

Question

I'm trying to access a remote MySQL Database which is only reachable locally (localhost).

My Code looks like this:

$connection = ssh2_connect('IP to Server', 22);

    if (ssh2_auth_password($connection, 'User', 'Password')) {
        echo "Authentication Successful!\n";
    } else {
        die('Authentication Failed...');
    }

    $tunnel = ssh2_tunnel($connection, '127.0.0.1', 3306);

    try {
        $this->_connection = new PDO($dsn, $config['login'], $config['password'], $flags);
        $this->connected = true;
        if (! empty($config['settings'])) {
            foreach ($config['settings'] as $key => $value) {
                $this->_execute("SET $key=$value");
            }
        }
    } catch (PDOException $e) {
        throw new MissingConnectionException(array(
                'class' => get_class($this),
                'message' => $e->getMessage()
        ));
    }

The script is successful till $tunnel. The PDO connection fails with "Connection refused". I think PDO is trying to connect to MAMP? The $dsn variable looks like "mysql:host=127.0.0.1;port=3306;dbname=mydb".

How can I tell MAMP, that 127.0.0.1 is on the remote server connected through SSH?

Thank you!

Answer 1

As you already noted, your script is attempting to connect to your local MySQL server instead of the remote one. In order for PDO to establish a connection with the remote server, you could establish an SSH tunnel and forward a local port to the server port. As shown here https://brettrawlins.com/blog/mysql-ssh-tunnel.

To run the ssh command in php: shell_exec(ssh code here)

You might want to see: Connect to a MySQL server over SSH in PHP

However as noted in there as well, this method is relatively slow as you will have to create a new tunnel each time you query the database, making your queries take quite a bit longer. I suppose for a development environment it's a valid option since you don't have a static IP, but I don't see this working in production.

If you are trying to do this for the sake of encryption, I would recommend connecting to your database with SSL. However note that SSL for PDO might not be supported by all PHP versions.