{"id":2824,"date":"2010-08-12T14:12:16","date_gmt":"2010-08-12T12:12:16","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=2824"},"modified":"2010-08-12T14:38:34","modified_gmt":"2010-08-12T12:38:34","slug":"mmm-for-mysql-single-reader-role","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/mmm-for-mysql-single-reader-role","title":{"rendered":"MMM for MySQL single reader role"},"content":{"rendered":"

The standard documentation and tutorials on MMM for MySQL<\/a>, for master-master replication setup, suggest one Virtual IP for the writer<\/em> role, and two Virtual IPs for the reader<\/em> role. It can be desired to only have a single virtual IP for the reader role, as explained below.<\/p>\n

The two IPs for the reader role<\/h4>\n

A simplified excerpt from the mmm_common.conf<\/strong> sample configuration file, as can be found on the project’s site and which is most quoted:<\/p>\n

\n
...\r\n<host db1>\r\n  ip\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 192.168.0.11\r\n  mode\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 master\r\n  peer\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 db2\r\n<\/host>\r\n\r\n<host db2>\r\n  ip\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 192.168.0.12\r\n  mode\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 master\r\n  peer\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 db1\r\n<\/host>\r\n...\r\n<role writer>\r\n  hosts\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 db1, db2\r\n  ips\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 192.168.0.100\r\n  mode\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 exclusive\r\n<\/role>\r\n\r\n<role reader>\r\n  hosts\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 db1, db2\r\n  ips\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 192.168.0.101, 192.168.0.102\r\n  mode\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 balanced\r\n<\/role>\r\n<\/pre>\n<\/blockquote>\n
In the above setup db1<\/strong> & db2<\/strong> participate in master-master active-passive replication. Whenever you need to write something, you use 192.18.0.100<\/strong>, which is the virtual IP for the writer role. Whenever you need to read something, you use either 192.168.0.101<\/strong> or 192.168.0.102<\/strong>, 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.<\/p>\n
One IP for reader role<\/h4>\n
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.<\/p>\n
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”).<\/p>\n
Anyway, neither 192.168.0.101<\/strong> nor 192.168.0.102<\/strong> relate to a particular machine’s role status. That is, the fact that one of the machines is in writer<\/em> mode or not does not affect these virtual IPs.<\/p>\n
The solution is a minor change to the configuration file. Real minor:<\/p>\n
\n
<role reader>\r\n  hosts\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 db1, db2\r\n  ips\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 192.168.0.101\r\n  mode\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 balanced\r\n<\/role>\r\n<\/pre>\n<\/blockquote>\n
In this new setup the two nodes compete for a single reader<\/em> role virtual IP. There is no 192.168.0.102<\/strong> 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.<\/p>\n
There is nothing to suggest in the above that the IPs 192.168.0.100<\/strong> & 192.168.0.101<\/strong> will be distributed between the two machines. But you would like<\/em> them to. And MMM does that. It makes sure that, if possible, one of the machines (say db1<\/strong>) gets the writer<\/em> role, hence 192.168.0.100<\/strong>, and the other (db2<\/strong>) the reader<\/em> role, hence 192.168.0.101<\/strong>.<\/p>\n
Moreover, it prefers that situation over a current known situation: say db1<\/strong> went down. The writer<\/em> role moves to db2<\/strong>. When db1<\/strong> is up again, MMM acts smartly: it does not<\/em> give it back the writer<\/em> role (since moving the active master around is costly, after all), but does<\/em> give it the reader<\/em> role, along with the 192.168.2.101<\/strong> IP. So it takes care not to leave a server without a role, while preferring to move the writer<\/em> role as little as possible.<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[5],"tags":[11,62,57,8],"class_list":["post-2824","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-configuration","tag-high-availability","tag-open-source","tag-replication"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-Jy","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/2824","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/comments?post=2824"}],"version-history":[{"count":12,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/2824\/revisions"}],"predecessor-version":[{"id":2836,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/2824\/revisions\/2836"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=2824"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=2824"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=2824"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}