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.
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.