| View previous topic :: View next topic |
| Author |
Message |
notageek
Tux's lil' helper
Joined: 05 Jun 2008
Posts: 135
Location: India
|
 Posted: Thu Jan 27, 2011 7:58 am Post subject: How does one join two tables from different MySQL server? |
 |
|
This may not be the most efficient or the most elegant way of doing things but there is a requirement to join two tables from two different servers. So far my search has revealed that one would need FEDERATED tables.
Anyone know if that will work? Is there another way to do this?
_________________
"Defeat is a state of mind. No one is ever defeated, until defeat has been accepted as a reality." -- Bruce Lee |
|
| Back to top |
|
 |
lxg
Veteran
Joined: 12 Nov 2005
Posts: 1019
Location: Aachen, Germany
|
| Posted: Fri Feb 04, 2011 11:15 pm Post subject: |
|
|
Uhm, whatever your actual problem is, but joining tables from different serves sounds like totally the wrong approach, even a completely wrong architecture. 
May I ask what the underlying problem is?
If you need to create relations across different databases, something's really wrong with you architecture (and, by the way, prone to horrible errors). This sounds like something that would rather need a service API or something in that area.
_________________
lxg.de – codebits and tech talk |
|
| Back to top |
|
|
skutnar
n00b
Joined: 13 Jun 2006
Posts: 65
|
| Posted: Sat Feb 05, 2011 3:44 am Post subject: |
|
|
@lxg: The scenario is not automatically a wrong approach or wrong architecture. This is not an uncommon situation in a corporate environment made up of several business units. In some cases, these business units need to have their completely separate databases talk to one another, regardless of location, architecture, or technology.
Unfortunately, I can't help with the question at hand about MySQL, but I know SQL Server can leverage linked servers and/or use OPENQUERY. There may be more options. |
|
| Back to top |
|
|
lxg
Veteran
Joined: 12 Nov 2005
Posts: 1019
Location: Aachen, Germany
|
| Posted: Sat Feb 05, 2011 1:21 pm Post subject: |
|
|
Ok, I admit that my statement may have been a bit presumtuous. However, I don't know and can hardly imagine environments where business units do data exchange directly at database level. There always should be an application/business logic layer. Anyway, sorry for getting off-topic.
_________________
lxg.de – codebits and tech talk |
|
| Back to top |
|
|
cayenne
l33t
Joined: 17 Oct 2002
Posts: 945
Location: New Orleans
|
| Posted: Sat Feb 05, 2011 3:43 pm Post subject: |
|
|
| lxg wrote: | | Ok, I admit that my statement may have been a bit presumtuous. However, I don't know and can hardly imagine environments where business units do data exchange directly at database level. There always should be an application/business logic layer. Anyway, sorry for getting off-topic. |
Well, I've seen this done with Oracle databases..using database links...not completely uncommon.
I found this on a bit of searching...it might give you some leads:
| Quote: |
1. use fully-qualified-table-names when querying for the external table. MySQL supports the dbname.tablename`-syntax to access tables outside the current database scope. This requires that the currently connected user has the appropriate rights to read from the requested table in another physical db.
2. if your external database is running on a different MySQL server (either on the same machine or via a network connection) you could use replication to constantly update a read-only copy of the remote table. Replication is only possible if you're running two separate MySQL instances.
3. use the FEDERATED MySQL storage engine to virtually import the table into your current database. This lifts the requirement of giving the current user access rights into the second database as the credentials are given with the CREATE TABLE-statement when using the FEDERATED storage engine. This also works with the databases running on different physical servers or different MySQL instances. I think that this will be the least performant option and does have some limitations - more or less important depending on your usage scenario and your requirements.
4. This is an extension to method 1. Instead of having to specify the fully-qualified-table-names every time you request information from your external table, you simply can create a view inside your current database based on a simple SELECT <<columns>> FROM <<database>>.<<table>>. This resemble the way, the FEDERATED-method works, but is limited to tables on the same MySQL instance.
|
Looking...maybe #1 or #4 might be something to look at?
Quoted from : http://stackoverflow.com/questions/1565993/oracle-database-link-mysql-equivalent
HTH,
cayenne
_________________
Light travels faster than sound. This is why some people appear bright until you hear them speak......... |
|
| Back to top |
|
|
notageek
Tux's lil' helper
Joined: 05 Jun 2008
Posts: 135
Location: India
|
| Posted: Sun Feb 06, 2011 9:38 am Post subject: |
|
|
Thanks cayenne.
#3 is more appropriate because the two databases are actual two physical databases (meaning boxes, separated by location).
Unfortunately I do not have a lot of details regarding the architecture, because a guy from another department asked me for help and I pointed him to FEDERATED tables as one of the solutions.
Not having done it myself, I was wondering if I pointed him to the right direction.
Thank you all for your replies.
_________________
"Defeat is a state of mind. No one is ever defeated, until defeat has been accepted as a reality." -- Bruce Lee |
|
| Back to top |
|
|
notageek
Tux's lil' helper
Joined: 05 Jun 2008
Posts: 135
Location: India
|
| Posted: Thu Feb 10, 2011 8:20 am Post subject: |
|
|
Turns out the most feasible solution was for the said business unit to provide nightly dumps of their database, so that it can be uploaded at a local place.
_________________
"Defeat is a state of mind. No one is ever defeated, until defeat has been accepted as a reality." -- Bruce Lee |
|
| Back to top |
|
|
|
Portage & Programming
