Skip navigation links

MySQL Forums :: Synchronization :: Sync data between multiple database


Advanced Search

Re: Sync data between multiple database
Posted by: Shane McCusker ()
Date: May 16, 2007 05:20AM

Thank you for your reply. Unfortunately I don't think that either of these methods will achieve the result I am looking for.

Let me better describe what I would like: I have people in different offices and other people with laptops all of whom want to use the same database, adding, modifying and deleting records. They want to be able to use the system off line. This means that each location will have its own database and I need automatic synchronization whenever that computer is online. The Synchronization process should happen in the background and not interfere with the use of the database.
The databases I work with are not particularly large (200MB – 1GB) and the users tend not to make many modifications as most actions create new records.
When a user modifies a record it would be nice to have field level modification of all other databases but it would be acceptable to have record level synchronization if field level is not possible.

In the past I developed a system to do this for an MS Access Database that worked well so I have some understanding of the processes involved in the synchronization and handling conflicts. I hoped that this would be a sufficiently common problem that someone may have developed a good solution that would be commercially available.

My pervious work with MS Access used two databases on the local computer. One was the live user database and the other contained the data at the last time of synchronization. By comparing the two databases a SQL string was created to convert from the live to the copy. This was uploaded to an ftp site and was then available to all other sites to download and apply to their local database.

Am I correct in thinking that a binary log file contains the queries that have been applied to create the database? If so, applying this to a master database would not necessarily give the same result as applying it to a local database. Particularly because I am likely to have more than one database in use at any one point in time.

Similarly to only sync created and modified records would not produce synchronization as very obviously the deleted records would still be in the main database.

I assume that you are suggesting this because with only two database it is not clear wither a unique record represents an insert or a delete. Using a third database as a reference this can be detected.

With MS Access I can set an autonumber field to generate a random number as the primary key and this is how I was able to avoid conflicting primary keys. Is it possible to do this with MySQL? If not is there any effective way to ensure that two databases for not generate the same primary key?

Thank you for your help.

Shane

Options: ReplyQuote


Subject Views Written By Posted
Sync data between multiple database 69021 Shane McCusker 05/11/2007 08:15AM
Re: Sync data between multiple database 46424 Baron "Xaprb" Schwartz 05/13/2007 07:29AM
Re: Sync data between multiple database 31836 Shane McCusker 05/16/2007 05:20AM
Re: Sync data between multiple database 21201 Baron "Xaprb" Schwartz 05/16/2007 06:08AM
Re: Sync data between multiple database 15828 Shane McCusker 05/16/2007 02:34PM
Re: Sync data between multiple database 15000 lakshmi Gurusamy 07/04/2007 05:25AM
Re: Sync data between multiple database 16088 Jimson Reyes 07/21/2007 04:36PM
Re: Sync data between multiple database 13987 Alice Qiao 12/05/2008 07:19PM
Re: Sync data between multiple database 10988 Robert Caelara 01/20/2009 01:20PM
Re: Sync data between multiple database 12550 Julian Brooks 09/26/2007 07:58AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.