Connect to remote MySQL database with PHP using SSH

Question

I have a remote database I'd like to connect to from PHP running locally. The database doesn't allow remote connections so ordinarily I SSH into the box and use it from the command line, but that's not really a long term solutions.

I have SSH access, I have MySQL access once I SSH in, but I don't know how to get PHP into that workflow. If I could make this work within MAMP, that would be great, too.

Answer 1

For developing or testing, you can use ssh command to setup tunnel first and then access the remote database as a local one. The steps are:

1) setup tunnel with ssh command. command format: ssh -L [local port]:127.0.0.1:[remote mysql port, by default, it is 3306] [user]@[remote mysql server ip]. sample: ssh -L 3307:127.0.0.1:3306 ford@134.11.21.89

2) keep the command window alive

3) You can access the remote database by mysql string: mysqli://[user]:[password]@127.0.0.1:3307/[database name]

Answer 2

If this is for development, the suggested solution by alex is the way to go; set up a ssh-tunnel.

The tunnel will redirect your 127.0.0.1:3306-requests to the remote machine. The remote machine will also belive the requests will come from 127.0.0.1 (locally).

However, you may encounter problems if your server (shared host? please specify) doesn't allow mysql-connections from 127.0.0.1 (quite commonly only localhost are allowed). There's a tiny difference in those, and it will inhibit your tunnel from reaching the remote mysqld.

Just google tunneling, set it up, and use 127.0.0.1 from your php-connection strings.

regards,

//t

Answer 3

You could set up a SSH tunnel and then point your php connection code to a local port which is forwarded through the tunnel. Under Windows you might use putty; for Mac there will be similar solutions.

Answer 4

Connect to a MySQL server over SSH in PHP