I've been wondering about this lately. Is it really feasible for a small (one man?) team to keep master-master MySQL replication over WAN running smoothly?
If you want things to work smoothly; dual-master, single-active is the way to go.
If you use mysql's read_only flag and application users don't have SUPER permissions, you can easily prevent writes to the wrong server; set read_only = 1 in my.cnf and manually set it to 0 on exactly one of the masters. Use the read_only flag to drive automation for which server to send writes to.
Manual failover is set old server read_only, kill existing connections (read_only flag is cached), wait for replication to catch up, set new server read_only = 0. You can make a script to do this with one button, but I wouldn't recommend making it autonomous: flapping between servers is disruptive and could lead to data inconsistency if you switch when replication is behind; data inconsistency is usually way worse than write downtime until someone logs in to flip the switch.
Try to have half your slaves off each master, so if a master is down, you still have 50% capacity. (I've seen some patches from google a while ago to keep binary logs in sync between masters, and make switching masters easy: If that's available, you may be able to have slaves just follow the current active master)
If you have budget for it, an extra slave off each master can be helpful: You can cron them to shutdown MySQL, tar up the directory, and restart. If you untar that on a new slave, it'll continue replicating from that point in time. If you rotate out the backups, you also have some ability to restore data from the past, if there is a bad update.
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".
