Using mysql via SSH (php)

Question

Try to connect to remote mysql via ssh2 and php.

$ssh_server='';
$ssh_port='';
$ssh_user='';
$ssh_password='';

$ssh_connection=ssh2_connect($ssh_server, $ssh_port);
$ssh_auth=ssh2_auth_password($ssh_connection, $ssh_user, $ssh_password);
$ssh_tunnel = ssh2_tunnel($ssh_connection, $ssh_server, $ssh_port);

$db_hostname = 'localhost';
$db_database = '';
$db_username = '';
$db_password = '';
$db_port = '3306';

$connection_mysql = new mysqli($db_hostname, $db_username, $db_password, $db_database, $db_port);

Tunnel is ok, but I see, that mysql use my local mysql DB, not remote via SSH. So, how can i use mysql via SSH?

OS windows with WAMP on it.

@Fred-ii- 127.0.0.1 it is like localhost. I try it, but I again trying connect to local db, not remote. I turnoff my local db, but it is not changed anyting. — autumnrustle, Feb 23 '17 at 20:35
Here are a few links you may want to look at http://stackoverflow.com/q/23658689/1415724 --- http://stackoverflow.com/q/26880008/1415724 --- http://stackoverflow.com/q/9294653/1415724 --- http://stackoverflow.com/q/11061621/1415724 --- http://quintagroup.com/services/support/tutorials/mysql-windows — Funk Forty Niner, Feb 23 '17 at 20:38

score 1 · Answer 1 · answered Feb 23 '17 at 20:50

1

create private/public key and setup key authentication with ssh(aka passwordless authentication).

then run this code in your php script

shell_exec("ssh -f -L 3307:localhost:3306 myuser@10.0.0.1 sleep 10 >> logfile");

then create database connection like:

$pdo = new PDO("mysql:host=$servername;dbname=$dbname;port=3307", $username, $password);

answered Feb 23 '17 at 20:50

Dimi

1,255
11
20

it works if you install cygwin and append cygwin's bin folder to your path variable. – Dimi Feb 23 '17 at 20:53

score -1 · Accepted Answer · answered Aug 17 '17 at 08:48

$ssh_connection = ssh2_connect($ssh_server, $ssh_port);
ssh2_auth_password($ssh_connection, $ssh_user, $ssh_password);
$query="SET CHARACTER SET utf8; use ${db_database}; ${query};";
$query=str_replace('"', '\'', stripslashes($query));
$ssh_query="ssh -L 3307:${ssh_server}:${ssh_port}; echo \"${query}\" | mysql -u ${db_username} -h ${db_hostname} --password=${db_password}";
$result=ssh2_exec($ssh_connection, $ssh_query);

Using mysql via SSH (php)

2 Answers2