I currently have a simple setup where an application server connects to a database server (both used for live system). The latter replicates on to a data warehouse server (as seen in File0.pdf). The issue is that we have three databases running on three separate MySQL instances on the live server which makes me replicate the biggest db on the datawarehouse instance and dump and import all the other databases daily. Given that this approach is quite crude and time consuming, I am redisigning the setup (always keeping my datawarehouse necessities in mind) in order to remove the daily dump / load in order to get the up to date data.
One of my concerns is that I want to be able to disconnect any live db (for restart or whatever) without impacting the other two live databases.
I am attaching File0.pdf which is the current setup, File1.pdf and File2.pdf which are the two options being considered.
Which setup would you go for?
PS 1 There is already a disaster recovery site being replicated using mysql's replication and in hot standby for shift in case of emergency.