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.
