I'm a one man operation keeping a master-slave setup with a manual failover and it's been pretty smooth sailing once I got it setup. Don't know how much more complex master-master would be.
Same here, and as long as you understand how mysql replication works it's not too much effort to deal with. Performing the initial sync without downtime is a bit tricky, but can be done with a well-designed database and some thought. Basically you need to at least temporarily make the bulk of your data read-only, so that you can do most of the data transfer while things are running, and then only briefly lock tables on the source server for long enough to copy the stuff that has changed since the dump, and grab the binlog position. Then you copy that stuff over to the slave as well, update the slave to the correct position, and then start the slave.
That's master/slave, but to get master/master, all you need to do is start a slave on the original master and point it at the current master position on the original slave (which should be static since it isn't yet accepting any queries directly). These posts may be helpful:
Once it's running, as long as you're not running autoincrement queries or other things that can conflict on both servers at the same time, without taking appropriate precautions, it should chug away without any intervention.
If something does go wrong, you can often figure it out by looking at the slave status, fixing the inconsistency manually, skipping the bad query, and then starting the slave. If not though, you can always just re-synchronize from scratch. Or even better, run your databases off of an LVM volume, then take regular snapshots. (IE snapshot, make a tarball of the snapshot of the mysql directory, then remove the snapshot.) That will give you a consistent backup, even with the server running. On an SSD, the temporary added latency probably won't be noticed, especially if done off-peak. Then if anything goes wrong, you can restore from the snapshot, and it should catch back up to the master from the snapshot's position automatically (as long as your expire_logs_days setting in my.cnf is longer than the duration since the snapshot was taken).
No need to take production offline when using the percona xtrabackup tool to set up the slave. It's super easy to use, and I've done it multiple times on databases in the hundreds of gigabytes.
That does look like a great tool! If we were on all InnoDB I'd probably try switching over to it instead of LVM snapshot-based backups right now. (Since it can do incremental, mostly.) We have a bunch of large MyISAM tables though (MyISAM used because the tables are read-only, so read speed is the only real consideration), so those would have to be handled separately. I could always xtrabackup all the innodb stuff and then just file copy the myisam tables separately though, since I know they won't be changing.
It's pretty much the same. You almost never want writes on both sides (now in a failover plan anyway), so as long as you have a switch for which side receives the writes, it's simple.
Or allow writes in both datacenters with randomized tokens as keys. If you need datacenter-affinity for certain events, use one of the token bytes to encode the author datacenter. Updates that don't have to land in order can be written in an eventually consistent manner. Write a feed of changes in each datacenter and have the peers consume this update feed. Viola, partition-tolerant master-master with failover.
This is necessary but not sufficient to prevent issues. Sure, it will prevent auto increment key collisions, but unless you're using strict sessions everywhere you can run into other key consistency problems. For example if one master deletes a row while another updates it you'll end up with a missing key and stopped replication on the first one. (depends on the replication mode as well)
Doesn't this work only for an append-only structure?
If I'm updating existing records, and the MySQL master at Site A gets updated, then goes down before Site B is updated.. I've got an inconsistent setup.
Been thinking about Master-Master MySQL replication recently as we have a system that's duplicated and taken offline each summer (to run a summer camp), and looking for a way to sync changes in it back to the main 'live' MySQL database.
I do the same with tree nodes, works extremely well. Two active masters and one slave configured as master, conflicts are non-existant since I found out about this "trick".
