I’m dealing with a tricky situation here. I’ve got two MySQL tables sitting in completely different databases. Both are running on InnoDB and there’s no federation setup between them, which means I can’t do a regular join operation.
What I need to do is merge data from both tables and display the combined results in my web app. The catch is that I can’t relocate either table since they belong to separate applications that need to stay independent. I’m using Google Cloud Platform for my infrastructure and I’m fine with leveraging any managed solutions they offer.
Anyone here tackled a similar challenge before? What approach worked best for you?
i had the same issue! what i did was use Cloud SQL Proxy. connect to both dbs from the app, run the queries separately, then merge results in code. its more work but gives you control over the data merging. trust me, it works!
Been there with a multi-tenant setup where client data was split across different MySQL instances. What saved me was a data sync layer using Cloud Functions triggered by database changes. I set up triggers on both source tables to push updates to a centralized staging table in Cloud SQL, then had the app query from there instead of trying to coordinate across multiple databases. Latency’s minimal with near real-time sync, and you keep data independence while getting your merged view. Just handle conflict resolution if both tables can modify the same records.
yea, Spark SQL could do the trick! also, i set up a PySpark job like that a while back. it pulls from the DBs, merges data, then dumps to GCS. you can even schedule it or trigger it when needed. lots of setup but worth it!
Hit this exact same issue migrating legacy systems to GCP. Created a lightweight ETL pipeline with Cloud Dataflow that pulls from both MySQL databases, transforms everything to match what I needed, then dumps it all into BigQuery. Just pointed my web app at BigQuery instead of juggling two separate databases. Performance was way better than expected - BigQuery crushes analytical queries. You could dump to a third MySQL instance if you want to stick with relational databases. Best part is your source apps stay completely untouched while you get one clean view for reporting.