MySQL Forums
Forum List  »  Synchronization

Synchronisation from HQ to multiple slave sites
Posted by: Dan Lay
Date: December 22, 2011 04:35AM

Morning all,

I notice that there is a lot of talk in this forum on different ways to implement data synchronisation between multiple locations. I can't seem to find an answer to my problem.

I have an application that traditionally runs on one site with one database. My client is now expanding and is adding 2 additional branches.

Currently I am using MySQL 5.5 community at the HQ site, all communication with the database is done through MySQL

What I plan to do is serialise any UPDATE/INSERT/DELETE queries including parameters passed into XML in the database tier, then make the XML data available to other sites on a publish/subscribe framework.

I think this will work fine as I am currently doing exactly the same thing for other parts of the system with files. I poll a web service for items in a queue.
When the remote site gets a database sync job, it deserialises the details and re-constructs the statement exactly as it was.

My issue lies with unique ID's - presently I use Auto Increment columns to give each record in each table a unique id. This is fine when there is just one Database but when there are multiple databases it creates an issue.

Scenario 1
1. Add product to HQ db (master), unique id is assigned to the item.
2. Sync job is created to sync this query to all remote sites.
3. Remote site gets the sync job and applies it to the db (slave).
So far so good

The problem comes when a product record is added to the remote site db (slave). The identity column then increments by one. Then, when another sync job comes in, it applies another INSERT statement however this time the product ID is different and does not match master.

Then, any subsequent UPDATE statements for this product will update the wrong record as the id's are one out.

I've considered adding another ID to the product table that will remain consistent across all sites but this then prevents other UPDATE statements I have collected from processing normally as the statement will update by the master's id.

I am aware that MySQL does support replication but I cant seem to find a solution that will work across multiple sites like this. The connection between the site can go up and down and I would prefer to synchronise by http web services.

Any possible suggestions would be great.


Options: ReplyQuote

Written By
Synchronisation from HQ to multiple slave sites
December 22, 2011 04:35AM

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.