Over a network how can I synchronize my MySQL and SQLite database so that changes in MySQL will be reflected in SQLite?
-
2Database synchronisation is a very complex and broad area. There are numerous techniques and tools, all of which involve compromises of one sort or another. In short, there is no short answer to this, especially between different database engines.Marcelo Cantos– Marcelo Cantos2010-11-02 08:14:09 +00:00Commented Nov 2, 2010 at 8:14
Add a comment
|
1 Answer
I have a similar problem of 2-way syncing between an Android sqlite DB and a central MySql DB. The problem really comes when inserts are made, because it admits the possibility of replicated primary keys.
I did build a system a while back that used hardware and time-based GUID primary keys instead of simple, monotonically increasing primary keys. The theory is that if new records are inserted either on the handset using sqlite, or in the central server using mysql, the keys will never conflict because they are globally unique.
Unfortunately, this means you have get in the primary key management business instead of just taking the automatic one that comes with both DB engines. But in theory this approach allows data from many different handsets to be melded easily in the central database and redistributed back out to all the handsets. I am considering this approach for my Android problem although I'm still hoping to find a solution already crafted out there.
1 Comment
joecascio
Followup: I built out a guid key implementation on Android and a complementary one on the web service using django and python. I make my own keys from the Android ID and a combination of System.currentTimeMillis() and System.nanoTime(). While the resulting keys aren't sequential, at least they're monotonic, which I'm hoping will improve clustering. The guids look like this:
A000002276D37D-01339e06f7ab-5be854e. The 1st bit is the Android ID, 2nd is currentTimeMillis, 3rd is last 7 hex digits of nanoTime. I also use a static 'lastGuid' to make sure I never repeat an id.