MySQL joins across databases on different servers

Question

So, I have an existing db with some tables for a class of users. We're building a more general app to handle multiple things the company does and this class of users, call them hosts, is a general type used by multiple programs in our company. We want to (eventually) migrate into a centralized app as now we have several. However, we don't have the time to do it completely right now. I need to build a login system for these hosts and I'd like to begin to migrate to this new system with that. I can't figure out a reasonable way to move those tables that are in the legacy DB to the new DB, which (of course) resides on a different server, with out wanting to stab my own eyes out after 30 seconds of having to deal with this. The legacy db has many reports the rely on joining on the current hosts tables.

The only things I can come up with don't seem like very good ideas. Those being, writing to both dbs from both apps (pointless data duplication prone to syncing problems), provide an API from the new app and mash the data coming back together with record sets (just seems... wrong).

Anyone have any ideas how to deal with this?

Answer 1

It has it's limitations, but the FEDERATED storage engine might be of assistance.