Skip to content

# Orchestrator 1.2.1 BETA: Pseudo GTID support, reconnect slaves even after master failure

MySQL Open Source orchestrator Pseudo GTID Replication October 27, 2014

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:

1. Inject a periodic unique entry onto your binary logs
2. Configure orchestrator to recognize said entry.

#### Pseudo GTID injection example

We will use the event scheduler (must be enabled) to inject an entry every 10 seconds, recognized both in statement-based and row-based replication.

>     create database if not exists meta;

>     drop event if exists meta.create_pseudo_gtid_view_event;

>     delimiter ;;
>     create event if not exists
>       meta.create_pseudo_gtid_view_event
>       on schedule every 10 second starts current_timestamp
>       on completion preserve
>       enable
>       do
>         begin
>           set @pseudo_gtid := uuid();
>           set @_create_statement := concat('create or replace view meta.pseudo_gtid_view as select \'', @pseudo_gtid, '\' as pseudo_gtid_unique_val from dual');
>           PREPARE st FROM @_create_statement;
>           EXECUTE st;
>           DEALLOCATE PREPARE st;
>         end
>     ;;

>     delimiter ;

>     set global event_scheduler := 1;

Make sure to enable event_scheduler in your my.cnf config file.

An entry in the binary logs would look like this:

>     mysql [localhost] {msandbox} (meta) > show binlog events in 'mysql-bin.000002' LIMIT 2,1;
>     +------------------+-----+------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
>     | Log_name         | Pos | Event_type | Server_id | End_log_pos | Info                                                                                                                                                                                                               |
>     +------------------+-----+------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
>     | mysql-bin.000002 | 388 | Query      |         1 |         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 |
>     +------------------+-----+------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

The above entry will be unique, and orchestrator will be able to find it in the binary log if configured with:

>     {
>         ...
>         "PseudoGTIDPattern": "CREATE OR REPLACE .*? VIEW `pseudo_gtid_view` AS select"
>     }

The value of “PseudoGTIDPattern” is a regular expression which must match the Pseudo GTID entries in the binary log, and nothing but those entries.

#### Pre-release

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.

Still there can be use cases I haven’t encountered yet. You input is appreciated!

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

###
1
Leave a Reply

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

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

Subscribe
Notify of
Refactoring replication topologies with Pseudo GTID: a visual tour | code.openark.org

[…] Orchestrator 1.2.1 BETA: Pseudo GTID support, reconnect slaves even after master failure […]

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.