I'm running two load balanced servers for one website, and I'd like the databases to be synchronized. Queries may be run on either of the two servers because they are both production sites, so the replication can't just work one way.
It doesn't have to be in real-time, just fairly accurate so people don't notice a difference when they get switched to a different server.
As other people mentioned, what you are talking about is multi-master replication. There are several open-source tools out there like Maatkit or MMM to help w/multi-master replication but even w/these you are still asking for a world of hurt.
I would make sure that you truly need your writes to happen synchronously (aka instantly). If you can stand to have a couple seconds to a minute of lag between when a user performs an action on the site and data is inserted or updated in the DBs, I would consider setting up some sort of offline logging process. For example, wherever you had the insert/update statement, instead put something that writes to a file - or an in memory DB if you can't take the hit of writing to disk - and have some process/daemon that sits on your web head(s), reads in the log file, and writes the appropriate data to a single master; which then replicates it to the other load balanced machine.
Take this for what it is worth though b/c I have never had to actively maintain multi-master replication.
I too would try to not use circular replication.
If possible, use a standard replication, distribute read only queries on both nodes and read/write queries to the master. Works for wikipedia, could work for you :)
Another approach could be to setup a two-node active-passive setup (drbd and some clustering). Easier on the app (it just sees a single mysql server to connect to), needs some attention on the HA side if you're not used to that kind of stuff. You don't get the performance bonus of having two different db servers.
To make a choice you need to decide if your primary interest is lowering downtime (standard replication or active/passive cluster) or achieving better performance (standard replication, circular replication, etc...) and what are your constraints (can you modify the app, and how much can you modify it, needed performance, allowed budget, etc...)
To get better performance out of your hardware (or just to compensate the lack of performance if you go the active/passive way) don't forget about memcached and similar things.
Why not simplify things a bit. Have both frontends use the same backend and then use something like DRBD & Heartbeat to ensure that your database doesn't become a single point of failure. Dual master replication almost always ends in tears.
