Link to home
Start Free TrialLog in
Avatar of Matt Kendall
Matt KendallFlag for United States of America

asked on

Connect to a mysql database on another webserver?

Hello, I have a web server running a php web app off a MySQL database that I created a while back. (the database is constantly being updated every minute or so) There's a second website hosted on another server (different location and ip) that I want to pull some information from the first server and display (and possibly modify). I thought I'd just open the mysql port on the first server's firewall (available only to the second server's IP), and connect via PHP directly to the first server from the second, but I've never done this before so I wanted to see if anyone who's had experience doing this type of thing knows if this is the most secure/efficient way to do what I'm trying to do, or if there's a better way to do this.
ASKER CERTIFIED SOLUTION
Avatar of DrDamnit
DrDamnit
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You can setup MySQL to take connections on any port, you don't need to use the standard 3306.

Also you can setup to only allow connections from certain IP addresses, so here you could specify your other webserver.

Setup a new user on your MySQL server with only access allowed from the second IP.

Usually MySQL users are localhost only.
<?php

$link = mysql_connect('otherdomain.com:14307', 'mysql_user', 'mysql_password');
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully';
mysql_close($link);
?>

Open in new window

You can also restrict was statements the remote user can execute.

"The GRANT statement specifies that this user is only allowed to run a limited amount of statements on the MySQL server. This user will be allowed to: select records, insert records, update records, delete records, create databases, and DROP DATABASEs. More importantly, this user is not permitted to create users and set privileges.

You could further reduce a user's privileges by removing other items from the GRANT statement. The best policy here is that the user should only be given permission to access the functions that are necessary to perform their tasks."

http://www.devarticles.com/c/a/MySQL/Creating-Users-and-Setting-Permissions-in-MySQL/2/

http://dev.mysql.com/doc/refman/5.1/en/grant.html
Avatar of Matt Kendall

ASKER

Thanks