I've been reading blogs on MySQL multi-master setup. The way I've used MySQL replication in the past was master-slave where if the master died (or was being taken down for maintenance) we would shut down the master, promote the slave to master, point the configs to the old slave, and do our work. The new master would then become the slave (or we would fail back).
I'm trying to design a master-master setup. I will not be using it in the performance sense of writing to both of them primarially, however I just want the secondary master to be ready as a backup and avoid the whole deal of promoting the slave to master (just point the configs to the secondary master).
I've seen some blogs describing this setup, but they didn't look too professional. Basically your replica set is set up like a traditional master-slave, however Server 1 is both a master and slave of Server 2 and Server 2 is a master and slave of Server 1.
Are there any issues or caveats with this setup? Is it recommended? Will this allow me to direct writes to the secondary master, take down the primary, bring it back up, have it catch back up, then point my configs back to the original primary master?
Best Answer
The biggest caveat with only 2 machines in a cluster is the possibility of "split-brain", in which the nodes lose communication with one another, and each assumes that they are now the Master, possibly getting out of sync with the other. This risk is mitigated if you can absolutely ensure that writes will not go to the 2 Masters separately while they are not in communication. It is generally advisable to have at least 3 (and always preferably an odd number of nodes) to prevent these types of situations.
I did something similar to what you are proposing (with 3 nodes, not just two) as a MariaDB/Galera Cluster. In actuality, it is a Multi-Master setup, in which a write to any server will be propagated to the others, but in practice I treat it as a Master-Slave with automatic failover.
For Context, I will describe how I do my Master-Slave automatic failover using HAProxy:
I use HAProxy on my application servers and set up two ports, one that my application treats as the Master for writes, and one that the application treats as the Slave for reads. In my HAProxy, the Master port is set to use only the Master, but fail to the Slaves in a particular order. If the Master goes down, HAProxy detects it and redirects writes to the next server, which becomes the de facto Master.
My HAProxy Slave port balances between the Slave nodes (you could include the Master, too) using one of several different balancing algorithms. If one of Slaves goes down, HAProxy continues balancing with whatever is available until the Slave comes back up.
If the Master goes down, HAProxy re-directs and the application just works. When the Master comes back up, it syncs with the other machines before accepting connections. When it begins accepting connections, then HAProxy starts sending writes to it, and it becomes the Master again, and all is right with the world.
In your situation, you can do something similar, using HAProxy as your connection/failover tool. You do not have to split out your reads and writes, but I do so as to take advantage of the extra machines. I do like the MariaDB/Galera solution. I would hesitate only having 2 nodes. It feels dangerous.