0

I have XAMPP on my local machine and am able to run PHP scripts without any issue and connect to my localhost mysql DB flawlessly.

I am interested in pulling data from a live MySQL DB (hosted elsewhere online with Media Temple) with my local script. There are certain aspects of our code we do not want to publish to a web directory for possible security purposes. Is this possible?

Note: To access this database locally with desktop software (like Navicat) requires use of SSH Tunneling of course. We do not allow direct root access to our DB.

JM4
  • 6,740
  • 18
  • 77
  • 125

5 Answers5

1

Yes, just specify the host name and port of the remote MySQL instance when connecting.

nobody
  • 19,814
  • 17
  • 56
  • 77
1

Absolutely, you can specify host, port, credentials, etc w/ the mysql_connect() function. See: http://us3.php.net/mysql_connect for more info.

niczak
  • 3,897
  • 11
  • 45
  • 65
1

Yes it is possible.

You'll need to setup the appropriate access on the hosted database, ie on Media Temple.

See this snippet from the MYSQL user manual (http://dev.mysql.com/doc/refman/5.5/en/adding-users.html)

CREATE USER 'monty'@'localhost' IDENTIFIED BY 'some_pass';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost';

Warning granting all privileges can be a security hazard.

In the above example, you would replace 'localhost' with the IP address of your PC where the script is running (ie XAMPP). Keep in mind if you do not have a static IP address then you might need to specify a wildcard. This can be dangerous but if you must the trick is to use a % sign.

so 'localhost' would change to '%'. For extra security in this case you should probably change what priv's the user gets, make it read only if that is possible?

Finally, specify the details in the mysql_connect() function. Host, Database name, username and password. http://php.net/manual/en/function.mysql-connect.php

Jason
  • 15,064
  • 15
  • 65
  • 105
1

You can connect to mysql over the network, and you can specify the hostname and port to connect to a remote server. However, MySQL is generally configured to not accept connections from outside the local network for security reasons. I wouldn't recomment opening your server up to connections from the Internet... if you do, you should at the very least encrypt your connection over SSL.

Honestly though, I'd just recommend deploying your admin tools to your server and making sure they use a good authentication system and HTTPS. Exposing your mysql server to the world to avoid security issues on the server feels a bit like taking your door off because you're afraid someone might pick the lock.

Vinay Pai
  • 7,432
  • 1
  • 17
  • 28
1

If you have to go through an SSH tunnel, this may be difficult. There are some previous questions on the topic, start here. You can set up an SSH tunnel in PHP which may be able to host the connection. Try starting here.

Note that, if you establish an SSH tunnel with each DB request, you're going to incur a lot of overhead. Keeping that SSH tunnel as persistent as possible is going to be important, but even then there's likely going to be some noticeable overhead.

Community
  • 1
  • 1
David
  • 208,112
  • 36
  • 198
  • 279
  • Thank you all for your answers - based on the possible overhead and security thread it provides doing so, I think I will hold off on moving forward with this type of activity. I may open a different question which really is "I take registration information over the internet, some of which is private information I do not want stored on an off source mySQL DB. I do want to use pieces of that information online however, giving my members access to update their information, and for our customer service agents to be able to update that information on their own as well. How do I get around this? – JM4 Jan 04 '11 at 01:42