How do I access another MySQL Database from another IP Address with PHP?

Question

Ok, If you can answer this question, you deserve the nobel peace prize. Anyways, here's the situation.

I'm using Slicehost dot net, and I have 2 slices (two different IPs). One slice is my mail server and the other is my normal website. I'm running Ubuntu (8.04 or 8.10, something like that, it shouldn't matter). What I'm trying to do is access the MySQL database on the Mail server from the other slice. I'm trying to do that with PHP. I really appreciate any help.

Answer 1

mysql_connect()

$resource = mysql_connect('other-server-address.com', 'username', 'password');

The first parameter is the mysql server address.

Server Param

The MySQL server. It can also include a port number. e.g. "hostname:port" or a path to a local socket e.g. ":/path/to/socket" for the localhost.

If the PHP directive mysql.default_host is undefined (default), then the default value is 'localhost:3306'. In SQL safe mode, this parameter is ignored and value 'localhost:3306' is always used.

Unless I'm misunderstanding... this setup is pretty common. Any trouble you're having might be related to the following:

• Firewall settings
• Grant access to the mysql user to connect from the other host
• my.ini settings not allowing outside connections

Some other related SO questions:

• Connecting to MySQL from other machines
• How do I enable external access to MySQL Server?
• php access to remote database
• How to make mysql accept connections externally
• Remote mysql connection

Answer 2

mysql_connect() returns a link identifier if the connection is successful. Also you have to do is keep the references to both links.

When you want to use which ever link, simply include the link as an argument.

$link1 = mysql_connect($host1, $username1, $password1);
$link2 = mysql_connect($host2, $username2, $password2);
$r = mysql_query(QUERY, $link1);

Simple as that.

Answer 3

Assuming your mail server is at IP 192.168.1.20 and web server is 192.168.1.30

First of all you need to allow the web server to access the mysql database on your Mail server . On 192.168.1.20 you run the mysql command and grant access on the database needed to your web server

mysql>  grant all on mydb.* to 'someuser'@'192.168.1.30' identified by 'secretpass;

Your PHP code connects to that database with something like:

$conn = mysql_connect('192.168.1.20', 'someuser', 'secretpass');