{"id":7114,"date":"2014-10-27T11:51:51","date_gmt":"2014-10-27T09:51:51","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=7114"},"modified":"2014-10-27T11:51:51","modified_gmt":"2014-10-27T09:51:51","slug":"orchestrator-1-2-1-beta-pseudo-gtid-support-reconnect-slaves-even-after-master-failure","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/orchestrator-1-2-1-beta-pseudo-gtid-support-reconnect-slaves-even-after-master-failure","title":{"rendered":"Orchestrator 1.2.1 BETA: Pseudo GTID support, reconnect slaves even after master failure"},"content":{"rendered":"

orchestrator<\/em> 1.2.1 BETA is released<\/a>. This version supports Pseudo GTID<\/a>, and provides one with powerful refactoring of one’s replication topologies, even across failed instances.<\/p>\n

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

Depicted: moving a slave up the topology even though its local master is inaccessible<\/p><\/blockquote>\n

Enabling Pseudo-GTID<\/h4>\n

You will need to:<\/p>\n

    \n
  1. Inject a periodic unique entry onto your binary logs<\/li>\n
  2. Configure orchestrator<\/em> to recognize said entry.<\/li>\n<\/ol>\n

    Pseudo GTID injection example<\/h4>\n

    We will use the event scheduler<\/a> (must be enabled) to inject an entry every 10<\/strong> seconds, recognized both in statement-based and row-based replication.<\/p>\n

    \n
    create database if not exists meta;\r\n\r\ndrop event if exists meta.create_pseudo_gtid_view_event;\r\n\r\ndelimiter ;;\r\ncreate event if not exists\r\n\u00a0 meta.create_pseudo_gtid_view_event\r\n\u00a0 on schedule every 10 second starts current_timestamp\r\n\u00a0 on completion preserve\r\n\u00a0 enable\r\n\u00a0 do\r\n\u00a0\u00a0\u00a0 begin\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 set @pseudo_gtid := uuid();\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 set @_create_statement := concat('create or replace view meta.pseudo_gtid_view as select \\'', @pseudo_gtid, '\\' as pseudo_gtid_unique_val from dual');\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 PREPARE st FROM @_create_statement;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 EXECUTE st;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 DEALLOCATE PREPARE st;\r\n\u00a0\u00a0\u00a0 end\r\n;;\r\n\r\ndelimiter ;\r\n\r\nset global event_scheduler := 1;<\/pre>\n<\/blockquote>\n
    Make sure to enable event_scheduler<\/em> in your my.cnf<\/strong> config file.<\/p>\n
    An entry in the binary logs would look like this:<\/p>\n
    \n
    mysql [localhost] {msandbox} (meta) > show binlog events in 'mysql-bin.000002' LIMIT 2,1;\r\n+------------------+-----+------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+\r\n| Log_name\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Pos | Event_type | Server_id | End_log_pos | Info\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+------------------+-----+------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+\r\n| mysql-bin.000002 | 388 | Query\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 669 | use `meta`; CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`msandbox`@`localhost` SQL SECURITY DEFINER VIEW `pseudo_gtid_view` AS select '2f6ad653-5db3-11e4-b91d-3c970ea31ea8' as pseudo_gtid_unique_val from dual |\r\n+------------------+-----+------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+<\/pre>\n<\/blockquote>\n
    The above entry will be unique, and orchestrator will be able to find it in the binary log if configured with:<\/p>\n
    \n
    {\r\n\u00a0\u00a0\u00a0 ...\r\n\u00a0\u00a0\u00a0 \"PseudoGTIDPattern\": \"CREATE OR REPLACE .*? VIEW `pseudo_gtid_view` AS select\"\r\n}<\/pre>\n<\/blockquote>\n
    The value of “PseudoGTIDPattern”<\/strong> is a regular expression which must match the Pseudo GTID entries in the binary log, and nothing but those entries<\/em>.<\/p>\n
    Pre-release<\/h4>\n
    This is BETA quality; though I have high confidence in its safety: the process of matching the binary log entries makes for a self-validating mechanism. The process will abort on any mismatch or uncertainty.<\/p>\n
    Still there can be use cases I haven’t encountered yet. You input is appreciated!<\/p>\n","protected":false},"excerpt":{"rendered":"
    orchestrator 1.2.1 BETA is released. This version supports Pseudo GTID, and provides one with powerful refactoring of one’s replication topologies, even across failed instances. Depicted: moving a slave up the topology even though its local master is inaccessible Enabling Pseudo-GTID You will need to: Inject a periodic unique entry onto your binary logs Configure orchestrator […]<\/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":[57,108,115,8],"class_list":["post-7114","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-open-source","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\/7114","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=7114"}],"version-history":[{"count":15,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/7114\/revisions"}],"predecessor-version":[{"id":7138,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/7114\/revisions\/7138"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=7114"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=7114"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=7114"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}