How did you set it up to failover? Our MySQL database is very large, and I'm not sure of the best way to ensure it is up to date on another host.
Site B, insure any writes use even numbers for unique field.
This avoids conflict.
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.
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:
https://www.digitalocean.com/community/tutorials/how-to-set-... http://plusbryan.com/mysql-replication-without-downtime
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).
Hmm, may have to give it a try! Thanks.
It can be installed as a plugin [1] and there are patched 9.4 packages available [2].
I set up a POC with databases in UK and US (east coast) last year. It was fairly straight forward and seemed to work well.
1: https://2ndquadrant.com/en/resources/bdr/ 2: http://bdr-project.org/docs/stable/index.html
I switch between either master with just a DNS update that propagates within 5 minutes.
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.