I’m dealing with a situation where I need to merge information from two MySQL tables that exist in completely separate database instances. Both databases run on InnoDB but they’re not set up as federated databases, which means I can’t use standard JOIN operations directly on the server side.
What I’m trying to achieve is combining this data and making it available to my web app. The challenge is that I can’t migrate either table to share the same database since they belong to different applications that need to stay independent.
I’m currently using Google Cloud Platform for my infrastructure and I’m fine with leveraging any of their managed services if that helps solve this problem.
Has anyone tackled a similar data integration challenge before? What approaches worked best for you?
I’ve dealt with this exact problem on legacy systems that couldn’t be touched. Here’s what worked for me: set up a Cloud SQL replica for reads and use BigQuery as your middle layer. Schedule regular exports from both databases into BigQuery datasets, then run your joins there with SQL. Cache the results or push them to a unified table that feeds your web app. Your source databases stay completely separate, but you get the combined view you’re after. There’s some latency based on sync frequency, but most business apps can handle the delay just fine. If you need fancier scheduling, Cloud Composer works great for orchestrating the whole pipeline.
i totally get it! u can merge the data in ur app by fetching from both dbs then combine them there. it ain’t ideal, but hey, it works if u can’t touch the dbs. also, if syncing’s on ur mind, look into GCP Dataflow!