Mysqli Construct using SSH tunnel

[ttbakiatwoam]

Ok folks here we go,

Webserver2 (New VPS Server) needs to access our Production Database.
Webserver1 (Old Hardware Server) has direct access to the Database through port 3306 on the LAN.
Database1 (On a third Separate Hardware Server No public IPs) on same LAN as Webserver1

I can successfully create an SSH connection to webserver1 from webserver2 now using that connection I need to connect to the MySQL database hosted on Database1. Everything functions from the shell, I just need help getting the mysql connection to use the ssh tunnel.

from shell it works like so…

Login via ssh to webserver1
use mysql -h database1 -u username -p to access database.

Here is the code so far…

[php]<?php
$connection = ssh2_connect(‘yoursite.com’, 2222, array(‘hostkey’=>‘ssh-rsa’));

if (ssh2_auth_pubkey_file($connection, ‘username’,
‘id_rsa.pub’,
‘id_rsa’, ‘’)) {
echo “Public Key Authentication Successful\n”;
} else {
die(‘Public Key Authentication Failed’);
}

$tunnel = ssh2_tunnel($connection, ‘database1’, 22);
//I understand this is another ssh connection and as such fails but I cannot find a good example of using mysql_connect with the SSH2 connection.

?>[/php]

[ErnieAlex]

You have a MySQL database on a server on Webserver1
( Doesn’t that mean that this server is able to post requests from queries? )

The new Webserver2 needs access to Webserver1 to be able to receive access to the database
( Normal AJAX should be able to place posts to Webserver1 )

Have you already set up the tunneling on Webserver2? I think it is tricky, here is a link that talks about how
to set it up. http://chxo.com/be2/20040511_5667.html

But, if you already got the SSH working, I found this sample of how to create a SSH “stream” which is used
to go thru the tunnel and connect and run the query…
[php]
$connection = ssh2_connect($remotehost, ‘22’);
if (ssh2_auth_password($connection, $user,$pass)) {
echo “Authentication Successful!\n”;
} else {
die(‘Authentication Failed…’);
}

$stream=ssh2_exec($connection,‘echo “select * from tables.users where id=“123”;” | mysql’);
stream_set_blocking($stream, true);
while($line = fgets($stream)) {
flush();
echo $line."\n";
}
[/php]
This was a sampler on a site where they were just testing login’s, but, gives you some SSH exec examples.
So, look at " ssh2_exec( ) " function: http://php.net/manual/en/function.ssh2-exec.php …
Let us know when you get stuck… Good luck!

[ttbakiatwoam]

That will not work because the database is on a third server it’s not on Webserver1. You have to make the connection to the database first through the tunnel we cannot just access the database

The Connection flow looks like this from the command line

Webserver2–ssh–>webserver1–mysql -h database1 -u username -p–>database1

Since Database1 is not available via mysql connection from webserver2 we need to use the ssh tunnel to get the connection.

[ttbakiatwoam]

ErnieAlex you did however give me the answer I was looking for using steam. Here is the code that is working for us.

[php]$connection = ssh2_connect(‘webserver1’, 2222, array(‘hostkey’=>‘ssh-rsa’));

if (ssh2_auth_pubkey_file($connection, ‘username’,
‘id_rsa.pub’,
‘id_rsa’, ‘’)) {
echo “Public Key Authentication Successful”;
} else {
die(‘Public Key Authentication Failed’);
}

echo ‘
’;

$mysql = “mysql -h database1 -u username -p’”.‘password’."’ -sse “SELECT * FROM table”";
#echo $mysql;
// execute a command
if (!($stream = ssh2_exec($connection, $mysql ))) {
echo “fail: unable to execute command\n”;
} else {
// collect returning data from command
stream_set_blocking($stream, true);
$data = “”;
while ($buf = fread($stream,4096)) {
$data .= $buf;
}
fclose($stream);
echo $data;
}
[/php]

[ErnieAlex]

Great! Glad I could help! Always nice to solve a programming puzzle…

By the way, what I was talking about is it is extremely easy to use AJAX to post to a second server.
All you need to do is create a hidden page on the first server that responds to a post. Then, using
AJAX, you send a post to the second server which then returns the results from the database. All that
you need is one server that can get to the database and then a simple page to handle the posts to the
database. (In other words the queries…) The results is sent back tot he calling page as AJAX data in the
form of, I think, Jason formatted arrays. It allows accessing hidden databases as long as you can place
a small file on the first server. Did that make sense ? We actually solved that here for someone else, but,
I do not remember the post number… Just FYI…

[ttbakiatwoam]

Unfortunately AJAX would just be another added complication for us, but I will keep that in mind for other applications.