database - MySQL db sync across locations -

I have a web app that stores data using MySQL, the web app is internal (available through the internet is not).

I now have to deploy this application in 3 (and possibly more) locations (branch offices) They need to be able to sync the data (entries made at one place should be available at other branch locations).

Requirements

• Local examples of web app + mysql database
• Every location is read / written locally / on its own network < / Li>
• The database syncs one day (one possibly through a script via SSH) so that each location has the same data.

  The only way I was able to understand it (to date) is to create a UUID (unique identifier) ​​and with each source a source bit (for original position) and sync bit To encode with (new = 0, synchronized = 1) to write a script where I add SSH and rows to every location = that is my source and 0 = synced in every place ... I know it's cruel and not flown He is elegant / reliable but I am not sure what else to do.

  The client feared that if the Internet connection fails, that app wants to be able to work locally, but between another branch (via a night script or otherwise ). As I said, there are already 3 locations in total and possibly coming later this year.

  Any advice on architecture would be appreciated.

  PS Because of security concerns I am not connecting the database to the Internet but all suggestions are open to the.

  Thank you.

  You are potentially spending the extra hardware, development, and with such an architectural decision Maintenance costs themselves a lot of money unless they have important data entry required of the mission, so it can be a very high expectation to replicate the hardware stack in all the branch locations.

  It is being said, they still migrate from the open Internet server to the server, as long as they do not have their own VPN, within which you are setting it to migrate Trying to do some daily process of doing / merge data should be a red flag in your mind and should be a total non-starter. This is a very complex problem. You need a master-master database replication cluster that needs you. Fortunately MySQL offers you the ability to do this. Unknowingly, installing it and getting it right can be complicated, perhaps something that might be a challenge for you if you have to ask this question here.

  As far as database security is concerned, there is no reason why you should be worried about being a hosted Internet MySQL database, as long as you are taking proper precautions to protect the server. This will probably start with the permission of the port-3306 (and 22 for SSH access) at the network level and only access to the IP address (SAS) of Web Application Server (S). It will include accepting traffic from only those specific known hosts to include MySQL user accounts from which you will access.