Skip to content

# Tag: replic

## Pseudo GTID

MySQL GTID orchestrator Pseudo GTID replic October 22, 2014August 20, 2015

Pseudo GTID is a method to implement a GTID-like solution where slaves are easily connected to one another. This blog post and the following ones will describe work in progress (some 80% completed), where simulation of GTID makes for a good enough basis for refactoring replication topologies. I’m coding this in orchestrator, which already provides a substantial infrastructure support for this.

The final goal: orchestrator will allow you to move a slave below another, using only the data available by those two slaves. The usage is obvious:

- Easy master failover (master dead? Orchestrator will choose the most advanced slave to promote and make it master of its siblings)
- Slave promotion in complex topologies (with deep nested topologies, be able to move a slave up the hierarchy even if its local master is corrupted).

This can all happen with your normal, non GTID, MySQL replication, using your normal binary log files & positions.

This work in progress is inspired by Sam Lambert at GitHub, who has worked on a similar solution with different implementation. I also recall discussions with other DBAs having similar solution.

#### Pseudo GTID

First thing’s first, the basis for proposed solution is a pseudo-GTID. A unique entry in the binary logs (not necessarily sequential; not necessarily in ascending order). While in GTID implementations we have a unique identifier for each entry in the binary log, with pseudo-GTID we accept an occasional (or frequent) unique entry in the binary log.

There are many ways to do so. Certainly a client can generate a unique Id and invoke some statement on MySQL involving that ID. That would serve as valid grounds for the proposed solution. But I like things to be contained within MySQL. Consider, for example, the following event, which would be my preferred choice in Statement Based Replication (for RBR solution, see next post):

>     drop table if exists test.pseudo_gtid;
>     create table if not exists test.pseudo_gtid (
>       id int unsigned not null primary key,
>       ts timestamp,
>       gtid varchar(64) charset ascii
>     );

>     drop event if exists test.update_pseudo_gtid_event;

>     delimiter ;;
>     create event if not exists
>       test.update_pseudo_gtid_event
>       on schedule every 10 second starts current_timestamp
>       on completion preserve
>       enable
>       do
>         begin
>           set @pseudo_gtid := uuid();
>           insert into test.pseudo_gtid (id, ts, gtid) values (1, NOW(), @pseudo_gtid) on duplicate key update ts=NOW(), gtid=VALUES(gtid);
>         end
>     ;;

>     delimiter ;

The above is based on Making UUID() and RAND() replication safe. What do we get? Once in 10 seconds (or what have you), a unique entry is written to the binary log.

Continue reading » “Pseudo GTID”