Two way, multi-site replication in MySQL

Question

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.

Thanks.

Answer 1

If you're going with this approach, a potential tradeoff for speed could be using flags for new and dirty records (new could be default for INSERTed rows, dirty could be set by trigger on UPDATE) but depends on the workflow and auditing requirements. Rather than replicating all of the repeated changes to a record over the day, only the final value would be updated on all of the other systems and then the flag would be cleared. You'd still need a table for deletes (unless you want to go with the ever-so-popular "deleted_on" timestamp and likely change all of your code). If records don't change much once written you won't get a lot out of it.

This would require that the server be up at the end of the day, though. With an audit table, you could write the audit table to a file somewhere else periodically (rather than just at the end of the day) in the event of DB failure.

Answer 2

Galera is a synchronous multi-master solution that you might want to look into. I don't really know much about it myself and consider it esoteric but it might be something you want to look at more closely.

Cheers

Answer 3

Depending on the application, there are things that break with replication, namely collisions between auto_increment key inserts.

Replication will work, circular or star, and it will transmit only the operations requiring writes. If it receives a write packet it has already handled, it'll ignore it. Since bandwidth between the sites is low, if there are no collision issues, you could stop the slave replication during business hours to save bandwidth and start it at the end of the day. If your code is not aware that it is doing updates, you're probably going to end up having a number of times when a slave stops on an error and needs to be attended to.

If you have enough space, you could set your auto_increment start value for each of the remote offices far enough apart that they wouldn't overrun each other.

Replication isn't ideal for your situation, and since your system isn't really aware that replication is happening, you might have some issues, but, it might work well enough.