Development Multi-Machine Database Question

This is simply something that i am curious about.

If i have 2 databases on 2 different machines and i need to join tables from the 2 databases how would i go about doing something like that in PHP?

as it stands i have only ever worked on a single machine database, and as such i know that to connect to it i would need

Code:

	$LINK = mysql_connect($dbhost, $dbuser, $dbpass) or die("Error Connecting to Database");
	mysql_select_db($database, $LINK) or die("Could not select database");

and my querys look something like this

Code:

$result = mysql_query("SELECT `tableA`.*, `tableB`.`something` FROM `tableA`
                       INNER JOIN `tableB` on `tableA`.`reference` = `tableB`.`id`
                       WHERE 1", $LINK);

with the database "link" in the mysql_query function to point PHP to the proper database connection.

but how would i do the same thing if tableA was on server1 and tableB was on server2
what link identifier would i pass to the mysql_query function?


just something i have been wondering about.

thanks for any input

 

A (mysql) query can only involve one server - simple as that. You can't do what you're wondering about. You can't really even do what you're asking for on Oracle servers either, but you can get close to it.

So for clarity - you can have multiple databases involved in one query just like you can tables, but only where the databases are on one unique server instance.

Why don't you use an abstraction layer rather than the horrific mysql_* functions?

 

I like my mysql_* functions

what if i were to set up a MYSQL server on one machine that had remote access to the other machines hosting databases.

kind of treat it like a database cluster.

could that be done?

 

my php skills are somewhat limited but can you not just query each database seperately (using sql) then combine the results of the two queries (using php)?

 

you can however using MYSQL to do it is much more efficient. this is what the database is ment to do, sort and collate data. doing the same thing in PHP would be a rather large task.
unless of course there is something i have missed

 

Fair enough, but we've all kind of moved on, you should at least check an API like PEAR:B out.

define "remote access", but I can pretty much guarantee the answer will be "no". Read into replication.

 

I will look into that API.

there has to be a way to do this, even if it means creating something new from the ground up.
nothing is imposable, only improbable

 

Federated tables

 

MySQL Cluster, though to the best of my knowledge, the database nodes can't be accessed standalone..

 

they may not be able to be accessed stand alone but it would distribute the data and work load, correct?

essentialy doing what im asking about just not in the way im asking

 

Care to elaborate? After having seen what Rails does (and don't start with me on .net) I don't really believe in working with or anywhere near frameworks. Is it any different than just writing a general query() function that will fire off the appropriate mysql/mysqli/sqlite/etc query based on some define or db connection or whatever? More importantly, why? I've already got my queries and their results isolated into functions or classes so they're never used inline on a page...

 

The reason why it's not easy to do is that you run into loads of synchronisation issues when you use multiple servers that aren't clustered. If the clocks are off then a query may never run, delays are exaggeratted and more.

Easiest thing to do is to lock the tables on both machines, run your queries seperately, then unlock the tables for other transactions.

 

http://www.mysql.com/products/database/cluster/mysql-cluster-datasheet.pdf
read that.. basically it seams it clones the database across all the nodes. and does load balancing.. also the added benefit of raid 1 functionality should a node fail.

I'm not sure if it spreads a single query across multiple nodes. i'd probably say no.. but if you fire off a few queries (read a few people connect to the site @ once) you'd still see the performance gain i think your looking to get... (provided the actual node boxes arn't connected to one another over the internet.)

 

AFAIK, clustering still only has "one" database, it's just more of a load balancing thing (indeed, almost RAID1). It's what facebook uses, among plenty of other resource-hungry sites. I suppose you could throw both databases in the cluster, point your queries at whichever machine is acting as the load balancer, and it'll figure out where to send the query.

Actually on second thought... I'm not sure whether clustering automatically does the load balancing. You may need to set up a load balancer independently then use the clustering to replicate DB contents between the different nodes.