Skip to content

# MMM for MySQL single reader role

MySQL Configuration High availability Open Source Replication August 12, 2010August 12, 2010

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:

>     ...

>       ip                      192.168.0.11
>       mode                    master
>       peer                    db2

>       ip                      192.168.0.12
>       mode                    master
>       peer                    db1

>     ...

>       hosts                   db1, db2
>       ips                     192.168.0.100
>       mode                    exclusive

>       hosts                   db1, db2
>       ips                     192.168.0.101, 192.168.0.102
>       mode                    balanced

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:

>       hosts                   db1, db2
>       ips                     192.168.0.101
>       mode                    balanced

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.

- Twitter
- LinkedIn
- WhatsApp
- Email
- Facebook
- Print

###
3
Leave a Reply

3 Comment threads
0 Thread replies
0 Followers
Most reacted comment
Hottest comment thread
2 Comment authors
Recent comment authors

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Subscribe
Notify of
Guest
Walter Heck

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.

Author
shlomi

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?

Guest
Walter Heck

Shlomi, you are correct. Clumsy wording on my part, haha!

wpDiscuz
Send to Email Address Your Name Your Email Address Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.