0

Hey I'm a bit new to this kind of thing, I hope the title makes sense.

Basically I have a mysql server running on a LAMP stack, with some mean legacy code, by which all connections to the database are using mysql_connect("localhost", "user", "pass"). (I know it's bad, I didn't write it!).

I would like to run my own local version of our website for debugging/dev purposes, but I can't figure out how to connect to our database (we have a test-database running remotely that is updated every day with the live data). I have tried remotely connecting but I can't figure it out.

I was wondering if I keep the code (with "localhost" connection written in), and use some kind of SSH tunneling to "trick" the code into connecting to the remote database? Or would it perhaps be easier to set up some kind of mysql 'server' on my local machine? Using phpstorm's in built "database" functionality, I managed to connect to our database using ssh tunneling, but from there I'm completely lost. Again I'm new to this, so I apologise if this sounds very naive.

Any kind of help for a solution would be great.

k4kuz0
  • 1,045
  • 1
  • 10
  • 24
  • You should be able to connect to the remote database, most likely the remote server that has the test database need to be configured to allow the connection from your local environment. – Epodax Jul 15 '16 at 09:48
  • I did set up my user to have full "grants" `'k4kuz0'@'%'`, which I believe means that I can connect from "anywhere". But I'm not even sure if it can accept remote connections generally? In the services list, `mysql`is running but `mysqld`is not? Which I had taken to be the "remote" connection part? – k4kuz0 Jul 15 '16 at 09:49
  • It's not only the database user but the server properly has a firewall as well, you need to make sure you can connect to it. – Epodax Jul 15 '16 at 09:50
  • hmm i dont know if i understood you correct. is your web server and mysql server in same server? if no you shoud set mysql bind address. read link below: https://help.ubuntu.com/community/ApacheMySQLPHP – Parviz Eyvazov Jul 15 '16 at 09:52
  • I still have `bind-address = 127.0.0.1` sitting in my `/etc/mysql/my.cnf`, does that need to be commented out...? – k4kuz0 Jul 15 '16 at 09:53
  • @ParvizEyvazov Yes they are. The web server code connects to "localhost". So I assume that means that the web server and mysql server are one and the same? – k4kuz0 Jul 15 '16 at 09:53
  • localhost probably means that both are in same server. so what is a problem? // try code to debug and write the result here – Parviz Eyvazov Jul 15 '16 at 09:58
  • @ParvizEyvazov I have attempted this a few times, and I'm not sure if I have written the info wrong, or if something is configured wrong, but I couldn't get it to work. For example, should the `$servername` be written such as: `mysql://:3306`, or how? – k4kuz0 Jul 15 '16 at 10:01
  • noooo, servername is localhost. like $conn = mysqli_connect("localhost", "root", "password"); this – Parviz Eyvazov Jul 15 '16 at 10:03
  • @ParvizEyvazov I think you have misunderstood. I have no issues connecting to the database from my webserver, I want to run the same code from home, and connect to the database. I can't use "localhost" from home. – k4kuz0 Jul 15 '16 at 10:05
  • okay then servername should be like "mysite.com:3306" or "12.34.56.78:3306" then you should check if firewall is set to permit packets that sent to 3306 port. read this: http://stackoverflow.com/questions/1935314/connecting-to-remote-mysql-server-using-php – Parviz Eyvazov Jul 15 '16 at 10:13
  • I have just attempted again, and got the error: `Could not connect: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.` – k4kuz0 Jul 15 '16 at 10:34
  • You bascially just need SSH tunnel, which says port XXXX on my localhost machine (your working PC) corresponds to myserver IP port 3306 (standard port). On linux you just use ssh user@myserver.com -L 3307:localhost:3306 if you have ssh connection properly setup. – Srneczek Jun 03 '20 at 12:52

1 Answers1

0

If you are on Linux you must install Stunnel on both Client and Server.

Take a look here: https://blog.thesysadmins.co.uk/using-stunnel-to-encrypt-unsecure-connections.html

By configuring properly Stunnel you will be able to use mysqli_connect with "locahost" while actually referring to a remote server.

D.Intziadis
  • 336
  • 1
  • 2
  • 8