mysql database replication / clustering

[jeffrey lego]

Hi, new here didnt see any posts about this.

My question is this:

I have been employed in the IT department of a retail store,
the store has 4 locations in 3 different citys.

There current setup runs a centralized mysql server in the corporate location - they asked me how we could setup a server in each store so that loss of internet connection will not disrupt revenue and prevent a store from using pos ect.

my first suggestion was sql clustering - which i quickly discovered was not at all what i thought it would be.

then i came to mysql replication - but that requires a chain - so say store 3 was offline - so would store 1 and 2. (sorry for how breif summary is).


my current idea on this is to have a server in each store, redesign our pos software so that it checks for the main corporate server as its primary. if the primary is not available - it then checks for its local server, saving the info there. if the local server is not available, it checks for store cs server and so on.

then whenever the internet connection or primary server comes back - the data resyncs with the corporate server (each store will have unique ids, ex: 1.001 - corporate, 1.002 - store a, 1.003 - store b.

i figured i would check here to make sure im on the right track and to see if anyone has a possibly better solution for this.

thanks.