Connecting two mysql tables from different databases

[Dormilich]

It is not allowing to use two database server connections in single query..

that is absolutely correct.

[bangue]

JOIN between 2 different servers with MySQL

Yes I am doing same.. Creating database connection variable for each database server and trying to use these connections in a query..

It is not allowing to use two database server connections in single query..

You must to use FEDERATED tables if you want to make JOIN with tables in different servers. You can search for this in MySQL docs(http://dev.mysql.com/doc/refman/5.0/...rated-use.html)

But be careful! The performance is not good. If you really need to do that you must prepare your servers rightly. Read the docs and share it with the infrastructure staff and network.

Cheers!

[Artnessde]

Well - nuff said by all the others but how about adding real mysql replication if all you wanna do is to sync records with different timestamps from server 1 to server 2?


Or - if this won't be possible in your infrastructure, go the SLOW way withoud federated tables and do something like this:


1) find a way to store a "last run" timestamp
2) select all records from server 1 where the timestamp is > the last run timestamp

3) in the while( ... ) loop execute your replace into ... to server 2 sql with the data read from server 1.

Depending on the amount of records and the frequency of synch ... but i would always prefer the build in replication (master/slave)

[Nadimuthu]

just you have to create two handle with two connection. If you can to select two databases with two different handle. If you want to access the particular db value depending upon the handle