MySQL database setup - DataWarehouse


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.
Who is Participating?
IncisiveOneConnect With a Mentor Commented:
Maybe you are reading too much into my statement.  This is not a marketing exercise.  Take a look at the diags, there is a level of complication, duplication and replication that is completely unnecessary.  When you get something free, you tend to overuse it; put up more rather than less instances; that grows on you, so you replicate; then you want private copies, so you replicate more ... end result is the diagram.  Certainly it is deployment, but it is not architecture.  Three instances, actually four (not counting DR) and replicating the hell out of themselves.  Running into problems as a result of ordinary growth is no surprise.

Now when you have to pay for something, you treat it with a bit more respect, and replicate the least possible. It kind of forces you to think in architectural terms, how do I support the actual load and dbs on the least no of boxes and licences.

Perhaps I should have said: redraw the diagram using architectural and engineering principles.  Paper is cheap.  Have it vetted out here with the experts.  Then implement the solution using MySQL or whatever.  

And set aside this talk of instances here and instances there.  The database is defined as the central corporate data repository.  There should be only one version of the truth, and it lies in one place.  Of course a testing copy and a DR site are necessary, but no other copies.  Remember copies need to be kept up to date, which means replication, which means more grunt and more boxes; splitting up databases to overcome replication load issues.  Mushroom farm.

Finally, it is more than likely that the three (four) databases have not been normalised.  Fixing that problem will yield the biggest performance benefits (you will need less boxes to do the same job; less data being replicated).  Your symptomatic problem disappears.
Given you have "JBoss" written in picture let me recommend using C-JDBC( driver and bringing down only one database or server in cluster for unloading. C-JDBC handles situation, database becomes updated after restarting and nobody even notices.

Your solutions IMO contain complicated database peering, which somehow undermines diagnosing simple and easy problems.
I wonder if C-JDBC is same as Sequoia in your PDF... It does not need server, all features are in JDBC driver...
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

I am running C-JDBC in production environment with MySQL. I assure there is no specific MySQL setup done, and for some short emergency it even used XP as one db server while production Linux-es were replaced with Solaris-es. If you have questions regarding C-JDBC i'm happy to answre them.
Maybe it is time for the company to consider an enterprise db server that has none of these "problems", limitations or complications.  Three databases Plus the data warehouse in one single server instance; no replication except for the DR.  With the current config, you are replicating yourself silly.

If your databases are normalised, there is no technical reason for replicating between each of them or between them and the DW.
Why the hell enterprise DB server???
There are replication etc features of MySQL still unused, no other software will fix that.
gheistConnect With a Mentor Commented:
Given schemes in first post it koojs like our asker is in need of obvious diagrams since his superiors will not take Mr Ellisons or Mr Gates word that data are replicated. Thus straightforward and obvious solution that gives no interruptions, but keeps DR-related titual intact ;)
Since the seeker did not ask further questions and indicate direction, I suggest an even split between gheist and me.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.