problem connecting to external mysql database

hispanus

hello,

is it possible to connect to a mysql database, on a different server than the one which stores the calling PHP script ?

( using the function "mysql_connect()", i use something like "sql.mydomain.com" as server name, but in this case, it works fine only if the PHP script AND the mysql database are on the same system/server/host...)

devinemke

yes, you can supply any server you want to mysql_connect(). be advised that some commercial hosting companies (like mine) do not allow remote database connections, meaning you can only connect to their database server from their webserver.

TheDefender

Hey devinemke, can you elaborate or point me in the direction I need to look into in order to do this? I have our company's internet and intranet I develop. The data is typically not needed from one to the other. Recently, however, I was asked to copy one of my functions from the internet to the intranet, and rather than trying to duplicate the data, I want to connect to the internet site (which is on our own server) from the intranet. I can't wrap my head around how to make the connections since I use localhost as my host on both servers... Can you assist in this?

richie

If you're not using any kind of replication, why not just do a dump of the database table you are copying. That's the way I've always copied databases, and it works everytime.

There are several ways of doing a dump, but I use phpmyadmin, and save it as a file. You can also just use mysqldump:

mysqldump -u Username -pPassword dbName.tableName > DUMPEDFILE.sql

I then go to the destination computer and do:

mysql -h localhost -u USERNAME -pPASSWORD --database=DATABASE < DUMPEDFILE.sql

So, let's say you have a user called "remoteaccess" with password "RemotePass39", and you want to populate a database called "regions" with queries located in "regions.sql", your query will be:

mysql -h localhost -u remoteaccess -pRemotePass39 --database=regions < regions.sql

Note: make sure that the database exists, if not create it first before running the query.

Connecting remotely...

If you don't like the manual move above, you can do this:

You must have a login account on the remote server of course, and that account must be able to connect outside of localhost.

Let's say the ip address of the remote machine is xx.xx.xx.xx, the database is called regions, username/password is remoteaccess/RemotePass39. Your query then will be:

mysqldump --opt -u remoteaccess -pRemotePass39 regions | mysql -u remoteaccess -pRemotePass39 --host=xx.xx.xx.xx -C regions

Basically, you are pipping the output of mysqldump over to mysql

Richie.

cheese

........and probably stating the obvious, don't forget that the site that you are calling the database from MUST support PHP scripting.

Here is a example that I have built as a TEST SITE


$sql_server = "xxx.xxx.xxx.xxx"; //IP address
$sql_username = "externaluser";
$sql_password = "externalpassword";
$sql_database = "databasename";
global $sql_server;
global $sql_username;
global $sql_password;
global $sql_database;
$adate = date("Y/ m/ d");
$link = mysql_connect($sql_server, $sql_username, $sql_password)
        or die("Could not connect : " . mysql_error());
    mysql_select_db($sql_database) or die("Could not select database");
$query = mysql_query("SELECT * FROM news ORDER BY Date DESC LIMIT 3 ");

The php is run on my ISP's server but the database is on my Linux web server in the office at my home.

Cheers,

Neil.