<?php
$ssh_host = "1.2.3.4";
$ssh_user = "my_ssh_username";
$ssh_tunnel_local_port = "3307";
$ssh_tunnel_remote_host = "127.0.0.1"; # DB Server IP on the remote network if not local to the remote system
$ssh_tunnel_remote_port = "3306";
$local_mysql_host = "127.0.0.1";
$mysql_user = "my_mysql_username";
$mysql_pass = "my_secret_password";
$mysql_db_name = "my_database_name";
$mysql_charset = 'utf8';



// Create background process for ssh tunnel and capture process id
$ssh_opt = "-o UserKnownHostsFile=/dev/null -o StrictHostKeyChecking=no -o LogLevel=quiet -o ExitOnForwardFailure=yes";
$ssh_command = "ssh $ssh_opt -N -L $ssh_tunnel_local_port:$ssh_tunnel_remote_host:$ssh_tunnel_remote_port $ssh_user@$ssh_host";
$pid = shell_exec($ssh_command . " > /dev/null 2>&1 & echo $!");



// Wait for tunnel to establish before attemtping to initiate a MySQL connection
echo "Waiting for tunnel to open...\n";
date_default_timezone_set("UTC");
$start_time = time();
$connected = false;
while ($connected === false && time() < $start_time+60) {
  // Test if port is able to be opened
  $fp = fsockopen($local_mysql_host, $ssh_tunnel_local_port, $errno, $errstr, 10);
  if (!$fp) {
    echo "$errstr ($errno)\n";
    echo "retrying...\n";
  } else {
    $connected = true;
    echo "ssh tunnel established\n";
    fclose($fp);
  }
  sleep(1); # Wait for ssh tunnel creation
}



// Establish MySQL PDO connection and query available databases
$dsn = "mysql:host=$local_mysql_host;port=$ssh_tunnel_local_port;dbname=$mysql_db_name;charset=$mysql_charset";
$opt = [
  PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
  PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
  PDO::ATTR_EMULATE_PREPARES   => false,
];
try {
  echo "dsn: $dsn\n";
  echo "mysql_user: $mysql_user\n";
  echo "mysql_pass: $mysql_pass\n";
  $pdo = new PDO($dsn, $mysql_user, $mysql_pass, $opt);

  $stmt = $pdo->prepare("SHOW DATABASES");
  $stmt->execute();
  var_dump($stmt->fetchAll());
} catch (PDOException $e) {
  echo 'Connection failed: ' . $e->getMessage() . "\n";
}



// Close SSH tunnel using the process id captured earlier
echo "Checking connection for termination\n";
try {
  // Check to see if the process is still running
  $result = shell_exec(sprintf('ps %d', $pid));
  if (count(preg_split("/\n/", $result)) > 2) {
    echo "pid: $pid\n";
    echo "$result\n";
    echo "ssh connection is still running, attemtping to close connection.\n";
    shell_exec("kill $pid");
  } else {
    echo "ssh tunnel was already closed.\n";
  }
} catch(Exception $e) {
  echo 'Something bad happened: ' . $e->getMessage() . "\n";
}