{"id":7088,"date":"2014-10-27T11:55:39","date_gmt":"2014-10-27T09:55:39","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=7088"},"modified":"2014-10-27T16:33:38","modified_gmt":"2014-10-27T14:33:38","slug":"refactoring-replication-topologies-with-pseudo-gtid-a-visual-tour","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/refactoring-replication-topologies-with-pseudo-gtid-a-visual-tour","title":{"rendered":"Refactoring replication topologies with Pseudo GTID: a visual tour"},"content":{"rendered":"

Orchestrator<\/em> 1.2.1-beta<\/a> supports Pseudo GTID (read announcement<\/a>): a means to refactor the replication topology and connect slaves even without direct relationship; even across failed servers. This post illustrates two such scenarios and shows the visual way of mathcing\/re-synching slaves.<\/p>\n

Of course, orchestrator is not just a GUI tool; anything done with drag-and-drop is also done via web API (in fact, the drag-and-drop invoke the web API) as well as via command line. I’m mentioning this as this is the grounds for failover automation planned for the future.<\/p>\n

Scenario 1: the master unexpectedly dies<\/h4>\n

The master crashes and cannot be contacted. All slaves are stopped as effect, but each in a different position. Some managed to salvage relay logs just before the master dies, some didn’t. In our scenario, all three slaves are at least caught up with the relay log (that is, whatever they managed to pull through the network, they already managed to execute). So they’re otherwise sitting idle waiting for something to happen. Well, something’s about to happen.<\/p>\n

\"orchestrator-pseudo-gtid-dead-master\"<\/a><\/p><\/blockquote>\n

Note the green “Safe mode” button to the right. This means operation is through calculation of binary log files & positions with relation to one’s master. But the master is now dead, so let’s switch to adventurous mode; in this mode we can drag and drop slaves onto instances normally forbidden. At this stage the web interface allows us to drop a slave onto its sibling or any of its ancestors (including its very own parent, which is a means of reconnecting a slave with its parent). Anyhow:<\/p>\n

\"orchestrator-pseudo-gtid-dead-master-pseudo-gtid-mode\"<\/a><\/p><\/blockquote>\n

We notice that orchestrator is already kind enough to say which slave is best candidate to be the new master (127.0.0.1:22990<\/strong>): this is the slave (or one of the slaves) with most up-to-date data. So we choose to take another server and make it a slave of 127.0.0.1:22990<\/strong>:<\/p>\n

\"orchestrator-pseudo-gtid-dead-master-begin-drag\"<\/a><\/p><\/blockquote>\n

And drop:<\/p>\n

\"orchestrator-pseudo-gtid-dead-master-drop\"<\/a><\/p><\/blockquote>\n

There we have it: although their shared master is inaccessible, and the two slave’s binary log file names & position mean nothing to each other, we are able to correctly match the two and make one child of the other:<\/p>\n

\"orchestrator-pseudo-gtid-dead-master-refactored-1\"<\/a><\/p><\/blockquote>\n

Likewise, we do the same with 127.0.0.1:22988<\/strong>:<\/p>\n

\"orchestrator-pseudo-gtid-dead-master-begin-drag-2\"<\/a><\/p><\/blockquote>\n

And end up with our (almost) final topology:<\/p>\n

\"orchestrator-pseudo-gtid-dead-master-refactored-2\"<\/a><\/p><\/blockquote>\n

Notice how the two slaves 22988<\/strong>, 22989<\/strong> are happily replicating from 22990<\/strong>. As far as they’re concerned, there is no problem in the topology any more. Now it’s your decision: do you decommission the old master? You will need to RESET SLAVE<\/strong> on 22990<\/strong> (can do via orchestrator<\/em>), turn off 22990<\/strong>‘s read_only<\/strong> (can do via orchestrator<\/em>) and change DNS entries (or what have you).<\/p>\n

Scenario 2: a local master (“relay-master”) unexpectedly dies<\/h4>\n

In this scenario we have a deep nested topology<\/a>, and a local master died. What of its slaves?<\/p>\n

\u00a0\"orchestrator-pseudo-gtid-dead-relay-master\"<\/a><\/p><\/blockquote>\n

We choose one of the children and drag it over onto the master, which is up and healthy:<\/p>\n

\"orchestrator-pseudo-gtid-dead-relay-master-begin-drag\"<\/a><\/p><\/blockquote>\n

As you can see we are allowed (green instances are allowed drop places) to drop 22989<\/strong> on its sibling and on its grandparent, the latter bypassing a broken connection. There is no<\/em> connection between the two!<\/p>\n

\"orchestrator-pseudo-gtid-dead-relay-master-drop\"<\/a><\/p><\/blockquote>\n

And we get a new topology:<\/p>\n

\"orchestrator-pseudo-gtid-dead-relay-master-refactored-1\"<\/a><\/p><\/blockquote>\n

22989<\/strong> is now lagging, but on the right path! Let’s do the same for 22988<\/strong>:<\/p>\n

\"orchestrator-pseudo-gtid-dead-relay-master-begin-drag-2\"<\/a><\/p><\/blockquote>\n

And finally:<\/p>\n

\"orchestrator-pseudo-gtid-dead-relay-master-refactored-2\"<\/a><\/p><\/blockquote>\n

Great success! 22989<\/strong> already caught up, 22988<\/strong> on the way, victory is ours!<\/p>\n

The real fun, of course, is to execute with –debug<\/strong> and review the DEBUG messages as orchestrator seeks, finds, matches and follows up on Pseudo GTID entries in the binary logs. We each have our pleasures.<\/p>\n","protected":false},"excerpt":{"rendered":"

Orchestrator 1.2.1-beta supports Pseudo GTID (read announcement): a means to refactor the replication topology and connect slaves even without direct relationship; even across failed servers. This post illustrates two such scenarios and shows the visual way of mathcing\/re-synching slaves. Of course, orchestrator is not just a GUI tool; anything done with drag-and-drop is also done […]<\/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":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[5],"tags":[108,115,8],"class_list":["post-7088","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-orchestrator","tag-pseudo-gtid","tag-replication"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-1Qk","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/7088","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=7088"}],"version-history":[{"count":14,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/7088\/revisions"}],"predecessor-version":[{"id":7143,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/7088\/revisions\/7143"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=7088"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=7088"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=7088"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}