I have been developing a (MySQL) database in the cloud for the past few months to link in to a web based service. Following a change to the roadmap the initial clients will be running on standalone systems utilising a local (host) database. Moving forward there will be a need to merge these databases and I am still trying to work out a strategy for doing this. I have done something like this previously and handled it by using different offsets in the primary keys for each database included in the merge.
However this is further complicated that some clients may continue to run locally for a period after the initial merge and require a re-merge or incremental merge. One suggestion made to me was seeding primary keys for each client but not how to do this. I have offset primary keys when merging but am apprehensive about offsetting them on a live database as ‘overlapping primary keys’ look more like an ‘if’ then a ’when’!
The thought strikes me that maybe another SQL implementation may be more suitable than MySQL but if this is the case I need to migrate sooner rather than later.