Multi-master file replication

Master-master replication is asynchronous, hence it will definitely break if you write to both servers at once.

Even if the auto-increments are working, any other unique index and many other situations can break it - it's too brittle to be used.

But it is possible to use master-master as PART of a HA solution, you just need to ensure that applications only ever write to one of the pair and in a "clean" failover situation, e.g. admin failing over, it waits for the slave to catch up before switching.

This is not extremely difficult in practice, but a bit inconvenient.

Your main other option is to use DRBD, which is also not massively difficult to set up - but in this case, the 2nd machine is not even usable as a read replica - it just sits there being a hot spare. DRBD synchronously replicates the underlying storage, so everything is written safely to both machines.

There are some applications which are specially designed to tolerate the multi-master problems - these need to be designed VERY carefully with that exact situation in mind - in which case, it's ok. You can't use applications not designed for it though.

auto-increment is not the only, or the main problem.

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.