The standard documentation and tutorials on MMM for MySQL, for master-master replication setup, suggest one Virtual IP for the writer role, and two Virtual IPs for the reader role. It can be desired to only have a single virtual IP for the reader role, as explained below.
The two IPs for the reader role
A simplified excerpt from the mmm_common.conf sample configuration file, as can be found on the project’s site and which is most quoted:
... <host db1> ip 192.168.0.11 mode master peer db2 </host> <host db2> ip 192.168.0.12 mode master peer db1 </host> ... <role writer> hosts db1, db2 ips 192.168.0.100 mode exclusive </role> <role reader> hosts db1, db2 ips 192.168.0.101, 192.168.0.102 mode balanced </role>
In the above setup db1 & db2 participate in master-master active-passive replication. Whenever you need to write something, you use 192.18.0.100, which is the virtual IP for the writer role. Whenever you need to read something, you use either 192.168.0.101 or 192.168.0.102, which are the virtual IPs of the two machines, this time in read role. Logic says one wishes to distribute reads between the two machines.
One IP for reader role
I have a few cases where the above setup is not satisfactory: there is a requirement to know the IP of the passive (read-only) master. Reason? There are queries which we only want to execute on the slave (reporting, long analysis), and only execute on the active master when this isn’t possible. Sometimes we might even prefer waiting for a slave to come back up rather than execute a query on the master.
This may involve an application level solution, or a connection-pool level solution (“get me a slave’s connection, or, if that’s not possible, get me the master’s”).
Anyway, neither 192.168.0.101 nor 192.168.0.102 relate to a particular machine’s role status. That is, the fact that one of the machines is in writer mode or not does not affect these virtual IPs.
The solution is a minor change to the configuration file. Real minor:
<role reader> hosts db1, db2 ips 192.168.0.101 mode balanced </role>
In this new setup the two nodes compete for a single reader role virtual IP. There is no 192.168.0.102 anymore. Although it does not reflect from the configuration file, it turns out MMM acts in a smart way; the way you would expect it to run.
There is nothing to suggest in the above that the IPs 192.168.0.100 & 192.168.0.101 will be distributed between the two machines. But you would like them to. And MMM does that. It makes sure that, if possible, one of the machines (say db1) gets the writer role, hence 192.168.0.100, and the other (db2) the reader role, hence 192.168.0.101.
Moreover, it prefers that situation over a current known situation: say db1 went down. The writer role moves to db2. When db1 is up again, MMM acts smartly: it does not give it back the writer role (since moving the active master around is costly, after all), but does give it the reader role, along with the 192.168.2.101 IP. So it takes care not to leave a server without a role, while preferring to move the writer role as little as possible.
MMM is pretty smart like that. The official rule is that no host will have more then 2 more roles then any other host. Using that knowledge, you can even work out a scheme to have three roles: reader, writer and passive-master-reader if you will. It takes a bit of tinkering, but you can make it so that whenever possible, the writer and passive-master-reader role will always be on different master hosts whenever possible.
Walter,
“The official rule is that no host will have more then 2 more roles then any other host…”
It appears as if the rule is that no host will have 2 or more roles more than any other host — am I wrong here?
Shlomi, you are correct. Clumsy wording on my part, haha!