File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes MySql synchronisation Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "MySql synchronisation" Watch "MySql synchronisation" New topic
Author

MySql synchronisation

Priya Bindra
Ranch Hand

Joined: Sep 22, 2009
Posts: 51
I am developing a Java application which will be used in 3 physical locations. Of these 3, on is the head office. I need to design in such a way that the head office application can see data from all 3 locations. My design is as follows:

I am not making a web app with the DB hosted on web server.
I will install the same DB at 3 locations. The headoffice will have the DBs of other 2 locations as well.
I want to write a component in java which can update the daily changes in DBs of other 2 locations to their backup/master DB instances at the headoffice computer.

Is my design correct?

How can i implement the synchronization in MySQL? Has anyone used Maatkit (mk-table-sync)
Deepak Bala
Bartender

Joined: Feb 24, 2006
Posts: 6662
    
    5

This can end up getting crazy. As your database gets bigger, transferring all that data over a network will not be efficient.

Put all databases on the same network. You can build a web app that can query these databases and return the result. Even if you transfer database delta logs instead of the entire DB, it can be quite an undertaking depending on the rate of changes to the DB.

Synchronizing the database just so the head office can access it, is an overkill. Do you not have a private network / VPN between these offices ?


SCJP 6 articles - SCJP 5/6 mock exams - More SCJP Mocks
Priya Bindra
Ranch Hand

Joined: Sep 22, 2009
Posts: 51
Deepak Bala wrote:This can end up getting crazy. As your database gets bigger, transferring all that data over a network will not be efficient.

Put all databases on the same network. You can build a web app that can query these databases and return the result. Even if you transfer database delta logs instead of the entire DB, it can be quite an undertaking depending on the rate of changes to the DB.

Synchronizing the database just so the head office can access it, is an overkill. Do you not have a private network / VPN between these offices ?


this is a very small app for a art shop at multiple locations. So no VPN here. Also there is budget crisis, so i dont think i can host the DB at a purchased space on a web server. Another requirement is that the client doesnt always need the 3 locations to be in sync for the day time operations at any one centre. So i want a solution that the client can fetch data from other centres at his convenience at day end.

imagine that we only have internet to our rescue. what solution can we offer then?
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18987
    
    8

Priya Bindra wrote:imagine that we only have internet to our rescue. what solution can we offer then?


That's a better question. In your original post you said you wanted to write something in Java, and I think that was a premature choice. I would look for MySQL features which allow you to synchronize separate databases before I considered writing customized Java code.
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Database synchronization over a WAN is far harder to achieve than using a single site, particularaly if you allow independent updates on all three sites. Unless you have access to some impossibly cheap development and maintenance resources I'd imagine its going to be exponentially more expensive to do than renting some space on a shared server. You also expose your application to a much higher likelyhood of errors in your data.

If you were doing master to slave synchronisation you could do this OK, but master to master? I'd forget this solution now and think of something else.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Deepak Bala
Bartender

Joined: Feb 24, 2006
Posts: 6662
    
    5

What parts of this data need to be fetched ? Just a few rows to display on a page ? A report ? Read only data ?

What is the maximum speed that your internet connection will offer and what is the expected ping between the head office and the branch ?

Is the data fetched only during the end of the day ?

Should data be available across all three centers from any center ?
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: MySql synchronisation