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

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!