We have MySQL 5.1 databases at 5 separate locations that we need to keep as synced as possible. Each office needs to read/write from/to the local server at that office, but I need the DB at each office to reflect changes made at all offices. Changes to data are made only during typical 9-5ish office hours, and WAN speeds are slow (1-5 Mbps).
I'm not able to have writes from all sites go to a single master, and my understanding from the docs and from other Qs&As here is that master-master replication is not appropriate for this kind of situation and would likely result in lost data.
Here's the approach I'm leaning towards taking:
set up triggers to audit inserts, updates (only changed fields), and deletes, and write these to 1 location at the end of the day.
a job runs nightly at each office that downloads the entire audit log for all offices, and writes the most recent changes based on the audit trail timestamp -- this is a simplistic approach, but we don't have many constraints to worry about and the app using the data creates GUIDs for new records so no auto-increment worries.
Is there a better way than this manual approach, or is there a better way to do it manually? Am I missing something regarding multi-master replication? This is far from ideal, but it at least gets us synced for the start of every day. I would welcome any suggestions.