CODEWITHSUNDEEP
phpmysqldatabasesynchronization
James P.
James P.

Reputation: 19607

Ideas for synchronizing records programmatically

I need to synchronize records, say a list of clients, between a local and distant database. The database on both sides has the same structure. I've thought of using some kind of marker (date field, hash/checksum on field values...) but what would you advise ?

Edit: Distant database is on web hosting so PHP will be needed for transferring data.

Upvotes: 2

Views: 532

Answers (2)

JvdBerg
JvdBerg

Reputation: 21856

If you want to sync two databases by code, you're looking for trouble.

First of all, you need to handle your own primary key generation, because when inserting records on different sites, primary keys can/will be the same. Unique primary key generation is not easy to accomplish.

Furthermore, you need some conflict resolution. Updates or Deletes made on either side have to be reflected to the other site. Often, you cannot solve conflicts without user intervention.

My advise: look at built-in (two-way) replication, native to the database. It will save you a lot of headache.

Upvotes: 1

Gregor
Gregor

Reputation: 4434

It's always a good idea to have a last change field (date) on your records. Keep in mind that, if you start to synchronize, you have to take care of conflicts.

If both sides can insert, use a partitioning scheme for your primary keys. Example: Site A starts from 1000000000, Site B from 2000000000 (make this numbers large enough or simply divide your primary key size by the number of concurrent sites).

Rough synchronize plot:

  1. Get the modified records from source A, beginning from the last sync cycle.
  2. For each record, check:
    1. Is it newly inserted in A? Then insert it in B
    2. Is it modified in A, but not in B: update B
    3. Is it modified in A and in B: resolve conflicts on a field level.
  3. Get the modified records from source B, beginning from the last sync cycle
  4. For each record, check:
    1. Is it newly inserted in B? Then insert it in A
    2. Is it modified in B, but not in A: update A
    3. Is it modified in B and in A: resolve conflicts on a field level.

Upvotes: 2

Related Questions