CODEWITHSUNDEEP
phpmysqlssh
kheugel
kheugel

Reputation: 73

Querying a MySQL database on a remote webserver through SSH

We have an internal MySQL database that our customer service department uses to do quotes, and an external MySQL database on our website that our customers use to do quotes. I have a PHP report that I've written that gives me the information from the internal database. Now I'd like to modify the report to include the data from the external database as well. I need to connect to the external database by using a SSH connection. I've been looking into using cURL or SSH2, but I'm not sure if either are the right way to do this. Has anyone out there queried a SSH MySQL database on a webserver before, or does anyone know what I'd need to do to make this work? Thanks for any help you can give!

Upvotes: 0

Views: 1569

Answers (3)

Romain
Romain

Reputation: 12839

I actually use a MySQL GUI client called "Sequel Pro", which does offer the option to connect to MySQL through a SSH connection. This is totally doable, though if you have the option to connect to the DB without it (e.g. you can connect to it from "outside" the host), you should prefer this.

the easiest way I conceive you could query your DB through SSH is to use SSH port-forwarding, so you'd spawn a process that would do ssh user@host-that-has-the-db -L3306:localhost:3306, and then you'd have your MySQL connector connect on localhost.

The -L argument to SSH instructs it to do local port forwarding, so SSH will listen on a local port and forward what it receives there directly to the specified remote host/port from the other end. obviously my example uses port 3306 on both ends, but that can be changed as appropriate (RTM to have more about this)

Upvotes: 1

nos
nos

Reputation: 229324

You set up a tunnel from some available port on the local server to the mysql port on the remote server:

ssh -L 3307:localhost:3306 someuser@remoteserver

That creates a tunnel listening on port 3307 on the local system, which connects to port 3306 (MySQL) on the remoteserver.

Then point your PHP code to connect to port 3307 instead of 3306 , and it'll get tunneled to mysql on the remoteserver.

Upvotes: 0

Laith Shadeed
Laith Shadeed

Reputation: 4321

Yes you can query mysql from bash over SSH but this is not the best and scalable way to go. The best solution would be to create a REST Web Service on the production servers. Then your internal system will query that remote service with authentication.

Upvotes: 0

Related Questions