One of the common problems in asynchronous replication is the issue of consistent reads. I’ve just written to the master. Is the data available on a replica yet? We have iterated on this, from reading on master, to heuristically finding up-to-date replicas based on heartbeats (see presentation and slides) via freno, and now settled, on some parts of our apps, to using GTID.

GTIDs are reliable as any replica can give you a definitive answer to the question: have you applied a given transaction or not?. Given a GTID entry, say f7b781a9-cbbd-11e9-affb-008cfa542442:12345, one may query for the following on a replica:

mysql> select gtid_subset('f7b781a9-cbbd-11e9-affb-008cfa542442:12345', @@global.gtid_executed) as transaction_found;
+-------------------+
| transaction_found |
+-------------------+
|                 1 |
+-------------------+

mysql> select gtid_subset('f7b781a9-cbbd-11e9-affb-008cfa542442:123450000', @@global.gtid_executed) as transaction_found;
+-------------------+
| transaction_found |
+-------------------+
|                 0 |
+-------------------+

Getting OWN_GTID

This is all well, but, given some INSERT or UPDATE on the master, how can I tell what’s the GTID associated with that transaction? There\s good news and bad news.

• Good news is, you may SET SESSION session_track_gtids = OWN_GTID. This makes the MySQL protocol return the GTID generated by your transaction.
• Bad news is, this isn’t a standard SQL response, and the common MySQL drivers offer you no way to get that information!

At GitHub we author our own Ruby driver, and have implemented the functionality to extract OWN_GTID, much like you’d extract LAST_INSERT_ID. But, how does one solve that without modifying the drivers? Here’s a poor person’s solution which gives you an inexact, but good enough, info. Following a write (insert, delete, create, …), run:

select gtid_subtract(concat(@@server_uuid, ':1-1000000000000000'), gtid_subtract(concat(@@server_uuid, ':1-1000000000000000'), @@global.gtid_executed)) as master_generated_gtid;

The idea is to “clean” the executed GTID set from irrelevant entries, by filtering out all ranges that do not belong to the server you’ve just written to (the master). The number 1000000000000000 stands for “high enough value that will never be reached in practice” – set to your own preferred value, but this value should take you beyond 300 years assuming 100,000 transactions per second.

The value you get is the range on the master itself. e.g.:

mysql> select gtid_subtract(concat(@@server_uuid, ':1-1000000000000000'), gtid_subtract(concat(@@server_uuid, ':1-1000000000000000'), @@global.gtid_executed)) as master_generated_gtid;
+-------------------------------------------------+
| master_generated_gtid                           |
+-------------------------------------------------+
| dc103953-1598-11ea-82a7-008cfa5440e4:1-35807176 |
+-------------------------------------------------+

You may further parse the above to extract dc103953-1598-11ea-82a7-008cfa5440e4:35807176 if you want to hold on to the latest GTID entry. Now, this entry isn’t necessarily your own. Between the time of your write and the time of your GTID query, other writes will have taken place. But the entry you get is either your own or a later one. If you can find that entry on a replica, that means your write is included on the replica.

One may wonder, why do we need to extract the value at all? Why not just select @@global.gtid_executed? Why filter only the master‘s UUID? Logically, the answer is the same if you do that. But in practice, your query may be unfortunate enough to return some:

select @@global.gtid_executed \G

e71f0cdb-b8ef-11e9-9361-008cfa542442:1-83331,
e742d87f-dea7-11e9-be6d-008cfa542c9e:1-18485,
e7880c0e-ac54-11e9-865a-008cfa544064:1-7331973,
e82043c6-c7d9-11e9-9413-008cfa5440e4:1-61692,
e902678b-b046-11e9-a281-008cfa542c9e:1-83108,
e90d7ff9-e35e-11e9-a9a0-008cfa544064:1-18468,
e929a635-bb40-11e9-9c0d-008cfa5440e4:1-139348,
e9351610-ef1b-11e9-9db4-008cfa5440e4:1-33460918,
e938578d-dc41-11e9-9696-008cfa542442:1-18232,
e947f165-cd53-11e9-b7a1-008cfa5440e4:1-18480,
e9733f37-d537-11e9-8604-008cfa5440e4:1-18396,
e97a0659-e423-11e9-8433-008cfa542442:1-18237,
e98dc1f7-e0f8-11e9-9bbd-008cfa542c9e:1-18482,
ea16027a-d20e-11e9-9845-008cfa542442:1-18098,
ea1e1aa6-e74a-11e9-a7f2-008cfa544064:1-18450,
ea8bc1bd-dd06-11e9-a10c-008cfa542442:1-18203,
eae8c750-aaca-11e9-b17c-008cfa544064:1-85990,
eb1e41e9-af81-11e9-9ceb-008cfa544064:1-86220,
eb3c9b3b-b698-11e9-b67a-008cfa544064:1-18687,
ec6daf7e-b297-11e9-a8a0-008cfa542c9e:1-80652,
eca4af92-c965-11e9-a1f3-008cfa542c9e:1-18333,
ecd110b9-9647-11e9-a48f-008cfa544064:1-24213,
ed26890e-b10b-11e9-a79d-008cfa542c9e:1-83450,
ed92b3bf-c8a0-11e9-8612-008cfa542442:1-18223,
eeb60c82-9a3d-11e9-9ea5-008cfa544064:1-1943152,
eee43e06-c25d-11e9-ba23-008cfa542442:1-105102,
eef4a7fb-b438-11e9-8d4b-008cfa5440e4:1-74717,
eefdbd3b-95b3-11e9-833d-008cfa544064:1-39415,
ef087062-ba7b-11e9-92de-008cfa5440e4:1-9726172,
ef507ff0-98b3-11e9-8b15-008cfa5440e4:1-928030,
ef662471-9a3b-11e9-bd2e-008cfa542c9e:1-954800,
f002e9f7-97ee-11e9-bed0-008cfa542c9e:1-5180743,
f0233228-e9a1-11e9-a142-008cfa542c9e:1-18583,
f04780c4-a864-11e9-9f28-008cfa542c9e:1-83609,
f048acd9-b1d2-11e9-a0b6-008cfa544064:1-70663,
f0573d8c-9978-11e9-9f73-008cfa542c9e:1-85642135,
f0b0a37c-c89c-11e9-804c-008cfa5440e4:1-18488,
f0cfe1ac-e5af-11e9-bc09-008cfa542c9e:1-18552,
f0e4997c-cbc9-11e9-9179-008cfa542442:1-1655552,
f24e481c-b5c4-11e9-aff0-008cfa5440e4:1-83015,
f4578c4b-be6d-11e9-982e-008cfa5440e4:1-132701,
f48bce80-e99f-11e9-94f4-a0369f9432f4:1-18460,
f491adf1-9b04-11e9-bc71-008cfa542c9e:1-962823,
f5d3db74-a929-11e9-90e8-008cfa5440e4:1-75379,
f6696ba7-b750-11e9-b458-008cfa542c9e:1-83096,
f714cb4c-dab7-11e9-adb9-008cfa544064:1-18413,
f7b781a9-cbbd-11e9-affb-008cfa542442:1-18169,
f81f7729-b10d-11e9-b29b-008cfa542442:1-86820,
f88a3298-e903-11e9-88d0-a0369f9432f4:1-18548,
f9467b29-d78c-11e9-b1a2-008cfa5440e4:1-18492,
f9c08f5c-e4ea-11e9-a76c-008cfa544064:1-1667611,
fa633abf-cee3-11e9-9346-008cfa542442:1-18361,
fa8b0e64-bb42-11e9-9913-008cfa542442:1-140089,
fa92234c-cc90-11e9-b337-008cfa544064:1-18324,
fa9755eb-e425-11e9-907d-008cfa542c9e:1-1668270,
fb7843d5-eb38-11e9-a1ff-a0369f9432f4:1-1668957,
fb8ceae5-dd08-11e9-9ed3-008cfa5440e4:1-18526,
fbf9970e-bc07-11e9-9e4f-008cfa5440e4:1-136157,
fc0ffaee-98b1-11e9-8574-008cfa542c9e:1-940999,
fc9bf1e4-ee54-11e9-9ce9-008cfa542c9e:1-18189,
fca4672f-ac56-11e9-8a83-008cfa542442:1-82014,
fcebaa05-dab5-11e9-8356-008cfa542c9e:1-18490,
fd0c88b1-ad1b-11e9-bf3a-008cfa5440e4:1-75167,
fd394feb-e4e4-11e9-bd09-008cfa5440e4:1-18574,
fd687577-b048-11e9-b429-008cfa542442:1-83479,
fdb18995-a79f-11e9-a28d-008cfa542442:1-82351,
fdc72b7f-b696-11e9-ade9-008cfa544064:1-57674,
ff1f3b6b-c967-11e9-ae04-008cfa544064:1-18503,
ff6fe7dc-c186-11e9-9bb4-008cfa5440e4:1-103192,
fff9dd94-ed95-11e9-90b7-008cfa544064:1-911039

This can happen when you fail over to a new master, multiple times; it happens when you don’t recycle UUIDs, when you provision new hosts and let MySQL pick their UUID. Returning this amount of data per query is an excessive overhead, hence why we extract the master‘s UUID only, which is guaranteed to be limited in size.

1. World peace
2. Good health to all
3. Relaxed GTID constraints

I’m still not using GTID, and still see operational issues with working with GTID. As a latest example, our new schema migration solution, gh-ost, allows us to test migrations in production, on replicas. The GTID catch? gh-ost has to write something to the binary log. Thus, it “corrupts” the replica with a bogus GTID entry that will never be met in another server, thus making said replica unsafe to promote. We can work around this, but…

I understand the idea and need for the Executed GTID Set. It will certainly come in handy with multi-writer InnoDB Cluster. However for most use cases GTID poses a burden. The reason is that our topologies are imperfect, and we as humans are imperfect, and operations are most certainly imperfect. We may wish to operate on a replica: test something, by intention or mistake. We may wish to use a subchain as the seed for a new cluster split. We may wish to be able to write to downstream replicas. We may use a 3rd party tool that issues a flush tables with read lock without disabling sql_log_bin. Things just happen.

For that, I would like to suggest GTID control levels, such as:

1. Strict: same as Oracle’s existing implementation. Executed sets, purged sets, whatnot.
2. Last executed: a mode where the only thing that counts is the last executed GTID value. If I repoint replica, all it needs to check is “hey this is my last executed GTID entry, give me the coordinates of yours. And, no, I don’t care about comparing executed and purged sets, I will trust you and keep running from that point on”
3. Declarative: GTIDs are generated, are visible in each and every binary log entry, but are completely ignored.

I realize Oracle MySQL GTID is out for some over 3 years now, but I’m sorry – I still have reservations and see use cases where I fear it will not serve me right.

How about my previous years wishes? World peace and good health never came through, however:

• My 2015 wish for “decent, operations friendly built in online table refactoring” was unmet, however gh-ost is a thing now and exceeds my expectations. No, really. Please come see Tom & myself present gh-ost and how it changed our migration paradigm.
• My 2012 wish for “decent, long waited for, implementation of Window Functions (aka Analytic Functions) for MySQL” was met by MariaDB’s window functions.
  Not strictly Window Functions, but Oracle MySQL 8.0 will support CTE (hierarchial/recursive), worth a mention.

See you in Amsterdam!

Please read Pseudo GTID and Pseudo GTID, RBR as introduction.

Consider the following case: the master dies unexpectedly, and its three slaves are all hanging, not necessarily at same binary log file/position (network broke down while some slaves managed to salvage more entries into their relay logs than others)

(Did you notice the “Candidate for master” message? To be discussed shortly)

GTID

With GTID each transaction (and entry in the binary log) is associated with a unique mark — the Global Transaction ID. Just pick the slave with the most advanced GTID to be the next master, and just CHANGE MASTER TO MASTER_HOST=’chosen_slave’ on the other slaves, and everything magically works. A slave knows which GTID it has already processed, and can look that entry on its master’s binary logs, resuming replication on the one that follows.

How does that work? The master’s binary logs are searched for that GTID entry. I’m not sure how brute-force this is, since I’m aware of a subtlety which requires brute-force scan of all binary logs; I don’t actually know if it’s always like that.

Pseudo GTID

We can mimick that above, but our solution can’t be as fine grained. With the injection of Pseudo GTID we mark the binary log for unique entries. But instead of having a unique identifier for every entry, we have a unique identifier for every second, 10 seconds, or what have you, with otherwise normal, non-unique entries in between our Pseudo GTID entries.

Recognizing which slave is more up to date

Given two slaves, which is more up to date?

• If both replicate(d) from same master, a SHOW SLAVE STATUS comparison answers (safe method: wait till SQL thread catches up with broken IO thread, compare relay_master_log_file, exec_master_log_pos on both machines). This is the method by which the above “Candidate for master” message is produced.
• If one is/was descendent of the other, then obviously it is less advanced or equals its ancestor.
• Otherwise we’re unsure – still solvable via bi-directional trial & error, as explained later on.

For now, let’s assume we know which slave is more up to date (has received and executed more relay logs). Let’s call it S1, whereas the less up-to-date will be S2. This will make our discussion simpler.

Prerequisites

• We require a Pseudo GTID in place: a periodic injection of a known-to-be-unique query, and which we know how to intercept
• We require log_slave_updates on all slaves. We will need to parse the binary logs on slaves. I have little trust in the availability of relay logs: these are flushed, rotated and auto-erased all too quickly. The proposed solution does not require any daemon running on the MySQL servers themselves. There will be nothing to back up the relay logs, so I can’t trust these to exist. Binary logs, on the other hand, have expiry period in days, and so I can trust them to exist for a duration of a few minutes.
• Normal replication. Not multi threaded. Not multi-source.

The process of rematching slaves

S1 is more up to date, hence we want to make S2 a slave of S1. We expect the statements/entries found in S2‘s binary logs to exist in S1, in the same order, but somewhere back in the past, padded by additional entries (zero or more) that are not found in S2. Steps are:

• Find latest Pseudo-GTID entry in S2‘s logs. This can be done by iterating S2‘s binary logs newest to oldest. The first (time DESC) binary log where such entry is found is to be searched for the last entry (latest). Keep record of the binlog file2:pos2 coordinates.
• Take note of the exact entry made in the above. This is the unique value.
• Search said unique value in S1‘s binary logs. Since it is unique, your method of search is arbirtary, you just need to find it. Brute-force wise you start looking at newest binary log moving back in time. Not found? Unlikely, since this means the lag diff between S1 and S2 is as long as the binlog expiry. We will be handling with failures and with immediate actions; we can expect slave lags in the seconds or in the minutes – we don’t even consider the possibility where the entry is not found.
• Take note of the coordinates file1:pos1 in S1 where we found the unique value.
• We now iterate S2‘s binary logs starting with the Pseudo GTID file2:pos2. We expect to find each entry in S1‘s binary logs, successively, starting file1:pos1. We verify the entries in both servers are identical. Exceptions above could be:
  • Meta-entries (start-of-log, end-of-log, shutdown), in which case we skip to the next entry (this is done in both S1 and S2)
  • Local statements executed directly on either S1 or S2, such as ANALYZE TABLE or whatever, which make no impact on data — we may skip these
  • Local, evil statements executed directly on the slaves,which make for data impact (INSERT, DELETE, …). We choose to fail the operation in such case
• After all entries in S2 (matched by entries in S1) are iterated, our S1 “cursor” now looks at the first statement that never made it to S2. This is file_win:pos_win, into which we will point S2.
• Or, we might find that upon iterating all entries in S2 we have exactly reached the end of binlog entries for S1: this means both S1 and S2 are actually in identical state. We point S2 into S1‘s next-binlog-position.
  • This is in fact no different than the previous case, but of particular interest.
• Or, we might run out of entries in S1. No, we can’t, because our assumption was that S1 is more advanced than (or equally advanced as) S2. But this answers the question: “what if didn’t know in advance who’s more advanced?” (no pun intended). In such case we conclude S2 is actually more advanced than S1 and we can try the other way around.

That last bullet is of importance: if you have two slaves whose “family connection” is complex, you can still match one below the other; you may try one way and fail, then try the other way around and succeed.

Comparison of the events following the Pseudo-GTID is a good way of sanity checking (some meta-stuff should be ignored, like transaction IDs, table IDs, these can vary across servers), and builds up confidence in the correctness of the operation.

The codebase is actually complete and pushed; I’ll release a BETA version or orchestrator next week, that supports Pseudo GTID. Let me tell you, doing this kind of crazy stuff with visual feedback (of course command line is available) is very very cool.

The solution offered in the last post does not work too well for row based replication. The binary log entries for the INSERT statement look like this:

# at 1020
# at 1074
#141020 12:36:21 server id 1  end_log_pos 1074  Table_map: `test`.`pseudo_gtid` mapped to number 33
#141020 12:36:21 server id 1  end_log_pos 1196  Update_rows: table id 33 flags: STMT_END_F

BINLOG '
lddEVBMBAAAANgAAADIEAAAAACEAAAAAAAEABHRlc3QAC3BzZXVkb19ndGlkAAMDBw8CQAAE
lddEVBgBAAAAegAAAKwEAAAAACEAAAAAAAEAA///+AEAAACL10RUJDg2ZmRhMDk1LTU4M2MtMTFl
NC05NzYyLTNjOTcwZWEzMWVhOPgBAAAAlddEVCQ4Y2YzOWMyYy01ODNjLTExZTQtOTc2Mi0zYzk3
MGVhMzFlYTg=
'/*!*/;

Where’s our unique value? Encoded within something that cannot be trusted to be unique. Issuing mysqlbinlog –verbose helps out:

BEGIN
/*!*/;
# at 183
# at 237
#141020 12:35:51 server id 1  end_log_pos 237   Table_map: `test`.`pseudo_gtid` mapped to number 33
#141020 12:35:51 server id 1  end_log_pos 359   Update_rows: table id 33 flags: STMT_END_F

BINLOG '
d9dEVBMBAAAANgAAAO0AAAAAACEAAAAAAAEABHRlc3QAC3BzZXVkb19ndGlkAAMDBw8CQAAE
d9dEVBgBAAAAegAAAGcBAAAAACEAAAAAAAEAA///+AEAAABt10RUJDc1MWJkYzEwLTU4M2MtMTFl
NC05NzYyLTNjOTcwZWEzMWVhOPgBAAAAd9dEVCQ3YjExZDQzYy01ODNjLTExZTQtOTc2Mi0zYzk3
MGVhMzFlYTg=
'/*!*/;
### UPDATE `test`.`pseudo_gtid`
### WHERE
###   @1=1
###   @2=1413797741
###   @3='751bdc10-583c-11e4-9762-3c970ea31ea8'
### SET
###   @1=1
###   @2=1413797751
###   @3='7b11d43c-583c-11e4-9762-3c970ea31ea8'

and that’s something we can work with. However, I like to do stuff from within MySQL, and rely as little as possible on external tools. How do the binary log entries look via SHOW BINLOG EVENTS? Not good.

master [localhost] {msandbox} (test) > show binlog events in 'mysql-bin.000058' limit 20;
+------------------+------+-------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos  | Event_type  | Server_id | End_log_pos | Info                                  |
+------------------+------+-------------+-----------+-------------+---------------------------------------+
| mysql-bin.000058 |    4 | Format_desc |         1 |         107 | Server ver: 5.5.32-log, Binlog ver: 4 |
| mysql-bin.000058 |  107 | Query       |         1 |         183 | BEGIN                                 |
| mysql-bin.000058 |  183 | Table_map   |         1 |         237 | table_id: 33 (test.pseudo_gtid)       |
| mysql-bin.000058 |  237 | Update_rows |         1 |         359 | table_id: 33 flags: STMT_END_F        |
| mysql-bin.000058 |  359 | Xid         |         1 |         386 | COMMIT /* xid=5460 */                 |
| mysql-bin.000058 |  386 | Query       |         1 |         462 | BEGIN                                 |
| mysql-bin.000058 |  462 | Table_map   |         1 |         516 | table_id: 33 (test.pseudo_gtid)       |
| mysql-bin.000058 |  516 | Update_rows |         1 |         638 | table_id: 33 flags: STMT_END_F        |
| mysql-bin.000058 |  638 | Xid         |         1 |         665 | COMMIT /* xid=5471 */                 |
| mysql-bin.000058 |  665 | Query       |         1 |         741 | BEGIN                                 |
| mysql-bin.000058 |  741 | Table_map   |         1 |         795 | table_id: 33 (test.pseudo_gtid)       |
| mysql-bin.000058 |  795 | Update_rows |         1 |         917 | table_id: 33 flags: STMT_END_F        |
| mysql-bin.000058 |  917 | Xid         |         1 |         944 | COMMIT /* xid=5474 */                 |
| mysql-bin.000058 |  944 | Query       |         1 |        1020 | BEGIN                                 |
| mysql-bin.000058 | 1020 | Table_map   |         1 |        1074 | table_id: 33 (test.pseudo_gtid)       |
| mysql-bin.000058 | 1074 | Update_rows |         1 |        1196 | table_id: 33 flags: STMT_END_F        |
| mysql-bin.000058 | 1196 | Xid         |         1 |        1223 | COMMIT /* xid=5476 */                 |
| mysql-bin.000058 | 1223 | Query       |         1 |        1299 | BEGIN                                 |
| mysql-bin.000058 | 1299 | Table_map   |         1 |        1353 | table_id: 33 (test.pseudo_gtid)       |
| mysql-bin.000058 | 1353 | Update_rows |         1 |        1475 | table_id: 33 flags: STMT_END_F        |
+------------------+------+-------------+-----------+-------------+---------------------------------------+

The representation of row-format entries in the SHOW BINLOG EVENTS output is really poor. Why, there’s nothing to tell me at all about what’s been done, except that this is some operation on test.pseudo_gtid. Obviously I cannot find anything unique over here.

Not all is lost. How about DDL statements? Those are still written in SBR format (there’s no rows to log upon creating a table). A solution could be somehow manipulating a unique value in a DDL statement. There could be various such solutions, and I chose to use a CREATE VIEW statement, dynamically composed of a UUID():

drop event if exists test.update_pseudo_gtid_rbr_event;

delimiter ;;
create event if not exists
  test.update_pseudo_gtid_rbr_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 test.pseudo_gtid_v as select \'', @pseudo_gtid, '\' from dual');
      PREPARE st FROM @_create_statement;
      EXECUTE st;
      DEALLOCATE PREPARE st;    
    end
;;

delimiter ;

And this is how it looks on runtime (running this new event along with the old one):

master [localhost] {msandbox} (test) > show binlog events in 'mysql-bin.000060' limit 20;
+------------------+------+-------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name         | Pos  | Event_type  | Server_id | End_log_pos | Info                                                                                                                                                                                  |
+------------------+------+-------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| mysql-bin.000060 |    4 | Format_desc |         1 |         107 | Server ver: 5.5.32-log, Binlog ver: 4                                                                                                                                                 |
| mysql-bin.000060 |  107 | Query       |         1 |         183 | BEGIN                                                                                                                                                                                 |
| mysql-bin.000060 |  183 | Table_map   |         1 |         237 | table_id: 33 (test.pseudo_gtid)                                                                                                                                                       |
| mysql-bin.000060 |  237 | Update_rows |         1 |         359 | table_id: 33 flags: STMT_END_F                                                                                                                                                        |
| mysql-bin.000060 |  359 | Xid         |         1 |         386 | COMMIT /* xid=5802 */                                                                                                                                                                 |
| mysql-bin.000060 |  386 | Query       |         1 |         638 | use `test`; CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`msandbox`@`localhost` SQL SECURITY DEFINER VIEW `pseudo_gtid_v` AS select '7d2d44ca-583e-11e4-9762-3c970ea31ea8' from dual |
| mysql-bin.000060 |  638 | Query       |         1 |         714 | BEGIN                                                                                                                                                                                 |
| mysql-bin.000060 |  714 | Table_map   |         1 |         768 | table_id: 33 (test.pseudo_gtid)                                                                                                                                                       |
| mysql-bin.000060 |  768 | Update_rows |         1 |         890 | table_id: 33 flags: STMT_END_F                                                                                                                                                        |
| mysql-bin.000060 |  890 | Xid         |         1 |         917 | COMMIT /* xid=5811 */                                                                                                                                                                 |
| mysql-bin.000060 |  917 | Query       |         1 |        1169 | use `test`; CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`msandbox`@`localhost` SQL SECURITY DEFINER VIEW `pseudo_gtid_v` AS select '83234b13-583e-11e4-9762-3c970ea31ea8' from dual |
| mysql-bin.000060 | 1169 | Query       |         1 |        1245 | BEGIN                                                                                                                                                                                 |
| mysql-bin.000060 | 1245 | Table_map   |         1 |        1299 | table_id: 33 (test.pseudo_gtid)                                                                                                                                                       |
| mysql-bin.000060 | 1299 | Update_rows |         1 |        1421 | table_id: 33 flags: STMT_END_F                                                                                                                                                        |
| mysql-bin.000060 | 1421 | Xid         |         1 |        1448 | COMMIT /* xid=5819 */                                                                                                                                                                 |
| mysql-bin.000060 | 1448 | Query       |         1 |        1700 | use `test`; CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`msandbox`@`localhost` SQL SECURITY DEFINER VIEW `pseudo_gtid_v` AS select '89193a09-583e-11e4-9762-3c970ea31ea8' from dual |
+------------------+------+-------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Scroll to the right to find the unique value injected into the view’s creation statement.

Does it replicate well? Looking at a slave’s binary logs:

slave3 [localhost] {msandbox} ((none)) > show binlog events in 'mysql-bin.000064';
+------------------+------+-------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name         | Pos  | Event_type  | Server_id | End_log_pos | Info                                                                                                                                                                                  |
+------------------+------+-------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| mysql-bin.000064 |    4 | Format_desc |       103 |         107 | Server ver: 5.5.32-log, Binlog ver: 4                                                                                                                                                 |
| mysql-bin.000064 |  107 | Query       |         1 |         166 | BEGIN                                                                                                                                                                                 |
| mysql-bin.000064 |  166 | Table_map   |         1 |         220 | table_id: 33 (test.pseudo_gtid)                                                                                                                                                       |
| mysql-bin.000064 |  220 | Update_rows |         1 |         342 | table_id: 33 flags: STMT_END_F                                                                                                                                                        |
| mysql-bin.000064 |  342 | Xid         |         1 |         369 | COMMIT /* xid=3184 */                                                                                                                                                                 |
| mysql-bin.000064 |  369 | Query       |         1 |         601 | use `test`; CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`msandbox`@`localhost` SQL SECURITY DEFINER VIEW `pseudo_gtid_v` AS select '7d2d44ca-583e-11e4-9762-3c970ea31ea8' from dual |
| mysql-bin.000064 |  601 | Query       |         1 |         660 | BEGIN                                                                                                                                                                                 |
| mysql-bin.000064 |  660 | Table_map   |         1 |         714 | table_id: 33 (test.pseudo_gtid)                                                                                                                                                       |
| mysql-bin.000064 |  714 | Update_rows |         1 |         836 | table_id: 33 flags: STMT_END_F                                                                                                                                                        |
| mysql-bin.000064 |  836 | Xid         |         1 |         863 | COMMIT /* xid=3194 */                                                                                                                                                                 |
| mysql-bin.000064 |  863 | Query       |         1 |        1095 | use `test`; CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`msandbox`@`localhost` SQL SECURITY DEFINER VIEW `pseudo_gtid_v` AS select '83234b13-583e-11e4-9762-3c970ea31ea8' from dual |
| mysql-bin.000064 | 1095 | Query       |         1 |        1154 | BEGIN                                                                                                                                                                                 |
| mysql-bin.000064 | 1154 | Table_map   |         1 |        1208 | table_id: 33 (test.pseudo_gtid)                                                                                                                                                       |
| mysql-bin.000064 | 1208 | Update_rows |         1 |        1330 | table_id: 33 flags: STMT_END_F                                                                                                                                                        |
| mysql-bin.000064 | 1330 | Xid         |         1 |        1357 | COMMIT /* xid=3198 */                                                                                                                                                                 |
| mysql-bin.000064 | 1357 | Query       |         1 |        1589 | use `test`; CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`msandbox`@`localhost` SQL SECURITY DEFINER VIEW `pseudo_gtid_v` AS select '89193a09-583e-11e4-9762-3c970ea31ea8' from dual |
+------------------+------+-------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Not as pretty; I hate DDL operations that are not strictly required; but this solves the problem, plus rewriting the view means we’re not littering the tablespace.

Next post will describe the steps towards achieving GTID-like behaviour based on the above.

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.

Consider that the event is already running by now, and the next conventional statements executed by the application:

master [localhost] {msandbox} (test) > create table test.vals(id int);
master [localhost] {msandbox} (test) > insert into test.vals (id) values (17);
master [localhost] {msandbox} (test) > insert into test.vals (id) values (18);
master [localhost] {msandbox} (test) > insert into test.vals (id) values (19);
master [localhost] {msandbox} (test) > insert into test.vals (id) values (23);

master [localhost] {msandbox} (test) > show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000036 |       531 |
| mysql-bin.000037 |      1269 |
| mysql-bin.000038 |      6627 |
| mysql-bin.000039 |      3313 |
+------------------+-----------+

Let’s look at the binary logs content:

master [localhost] {msandbox} (test) > show binlog events in 'mysql-bin.000039';
+------------------+------+-------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name         | Pos  | Event_type  | Server_id | End_log_pos | Info                                                                                                                                        |
+------------------+------+-------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------+
| mysql-bin.000039 |    4 | Format_desc |         1 |         107 | Server ver: 5.5.32-log, Binlog ver: 4                                                                                                       |
| mysql-bin.000039 |  107 | Query       |         1 |         183 | BEGIN                                                                                                                                       |
| mysql-bin.000039 |  183 | User var    |         1 |         263 | @`pseudo_gtid`=_utf8 0x37383435623633382D353631612D313165342D393135642D336339373065613331656138 COLLATE utf8_general_ci                     |
| mysql-bin.000039 |  263 | Query       |         1 |         461 | use `test`; insert into test.pseudo_gtid (id, ts, gtid) values (1, NOW(), @pseudo_gtid) on duplicate key update ts=NOW(), gtid=VALUES(gtid) |
| mysql-bin.000039 |  461 | Xid         |         1 |         488 | COMMIT /* xid=74 */                                                                                                                         |
| mysql-bin.000039 |  488 | Query       |         1 |         581 | use `test`; create table test.vals(id int)                                                                                                  |
| mysql-bin.000039 |  581 | Query       |         1 |         657 | BEGIN                                                                                                                                       |
| mysql-bin.000039 |  657 | User var    |         1 |         737 | @`pseudo_gtid`=_utf8 0x37653362616434382D353631612D313165342D393135642D336339373065613331656138 COLLATE utf8_general_ci                     |
| mysql-bin.000039 |  737 | Query       |         1 |         935 | use `test`; insert into test.pseudo_gtid (id, ts, gtid) values (1, NOW(), @pseudo_gtid) on duplicate key update ts=NOW(), gtid=VALUES(gtid) |
| mysql-bin.000039 |  935 | Xid         |         1 |         962 | COMMIT /* xid=82 */                                                                                                                         |
| mysql-bin.000039 |  962 | Query       |         1 |        1038 | BEGIN                                                                                                                                       |
| mysql-bin.000039 | 1038 | User var    |         1 |        1118 | @`pseudo_gtid`=_utf8 0x38343331396662332D353631612D313165342D393135642D336339373065613331656138 COLLATE utf8_general_ci                     |
| mysql-bin.000039 | 1118 | Query       |         1 |        1316 | use `test`; insert into test.pseudo_gtid (id, ts, gtid) values (1, NOW(), @pseudo_gtid) on duplicate key update ts=NOW(), gtid=VALUES(gtid) |
| mysql-bin.000039 | 1316 | Xid         |         1 |        1343 | COMMIT /* xid=84 */                                                                                                                         |
| mysql-bin.000039 | 1343 | Query       |         1 |        1411 | BEGIN                                                                                                                                       |
| mysql-bin.000039 | 1411 | Query       |         1 |        1512 | use `test`; insert into test.vals (id) values (17)                                                                                          |
| mysql-bin.000039 | 1512 | Xid         |         1 |        1539 | COMMIT /* xid=84 */                                                                                                                         |
| mysql-bin.000039 | 1539 | Query       |         1 |        1607 | BEGIN                                                                                                                                       |
| mysql-bin.000039 | 1607 | Query       |         1 |        1708 | use `test`; insert into test.vals (id) values (18)                                                                                          |
| mysql-bin.000039 | 1708 | Xid         |         1 |        1735 | COMMIT /* xid=85 */                                                                                                                         |
| mysql-bin.000039 | 1735 | Query       |         1 |        1803 | BEGIN                                                                                                                                       |
| mysql-bin.000039 | 1803 | Query       |         1 |        1904 | use `test`; insert into test.vals (id) values (19)                                                                                          |
| mysql-bin.000039 | 1904 | Xid         |         1 |        1931 | COMMIT /* xid=86 */                                                                                                                         |
| mysql-bin.000039 | 1931 | Query       |         1 |        2007 | BEGIN                                                                                                                                       |
| mysql-bin.000039 | 2007 | User var    |         1 |        2087 | @`pseudo_gtid`=_utf8 0x38613237376232352D353631612D313165342D393135642D336339373065613331656138 COLLATE utf8_general_ci                     |
| mysql-bin.000039 | 2087 | Query       |         1 |        2285 | use `test`; insert into test.pseudo_gtid (id, ts, gtid) values (1, NOW(), @pseudo_gtid) on duplicate key update ts=NOW(), gtid=VALUES(gtid) |
| mysql-bin.000039 | 2285 | Xid         |         1 |        2312 | COMMIT /* xid=89 */                                                                                                                         |
| mysql-bin.000039 | 2312 | Query       |         1 |        2380 | BEGIN                                                                                                                                       |
| mysql-bin.000039 | 2380 | Query       |         1 |        2481 | use `test`; insert into test.vals (id) values (23)                                                                                          |
| mysql-bin.000039 | 2481 | Xid         |         1 |        2508 | COMMIT /* xid=89 */                                                                                                                         |
| mysql-bin.000039 | 2508 | Query       |         1 |        2584 | BEGIN                                                                                                                                       |
| mysql-bin.000039 | 2584 | User var    |         1 |        2664 | @`pseudo_gtid`=_utf8 0x39303164373731612D353631612D313165342D393135642D336339373065613331656138 COLLATE utf8_general_ci                     |
| mysql-bin.000039 | 2664 | Query       |         1 |        2862 | use `test`; insert into test.pseudo_gtid (id, ts, gtid) values (1, NOW(), @pseudo_gtid) on duplicate key update ts=NOW(), gtid=VALUES(gtid) |
| mysql-bin.000039 | 2862 | Xid         |         1 |        2889 | COMMIT /* xid=92 */                                                                                                                         |
| mysql-bin.000039 | 2889 | Query       |         1 |        2965 | BEGIN                                                                                                                                       |
| mysql-bin.000039 | 2965 | User var    |         1 |        3045 | @`pseudo_gtid`=_utf8 0x39363133363965382D353631612D313165342D393135642D336339373065613331656138 COLLATE utf8_general_ci                     |
| mysql-bin.000039 | 3045 | Query       |         1 |        3243 | use `test`; insert into test.pseudo_gtid (id, ts, gtid) values (1, NOW(), @pseudo_gtid) on duplicate key update ts=NOW(), gtid=VALUES(gtid) |
| mysql-bin.000039 | 3243 | Xid         |         1 |        3270 | COMMIT /* xid=94 */                                                                                                                         |
| mysql-bin.000039 | 3270 | Rotate      |         1 |        3313 | mysql-bin.000040;pos=4                                                                                                                      |
+------------------+------+-------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------+

Marked in bold are the pseudo-GTID statements, aptly read “@`pseudo_gtid`=_utf8 0x…”, and which are the resulting entry of the set @pseudo_gtid := uuid(); statement. These are interleaved with our normal statements. In busier servers there could be hundreds or thousands of statements between any two pseudo-GTID entries.

We have a replicating slave to the above, which uses log_slave_updates. For reasons to be explained later, I prefer and require log_slave_updates, and will examine the slave’s binary logs (instead of directly looking at the slave’s relay logs):

slave3 [localhost] {msandbox} ((none)) > show master logs;

+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000046 |      1077 |
| mysql-bin.000047 |       126 |
| mysql-bin.000048 |       150 |
| mysql-bin.000049 |       150 |
| mysql-bin.000050 |     13860 |
| mysql-bin.000051 |       107 |
+------------------+-----------+

slave3 [localhost] {msandbox} ((none)) > show binlog events in 'mysql-bin.000051';
+------------------+------+-------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name         | Pos  | Event_type  | Server_id | End_log_pos | Info                                                                                                                                        |
+------------------+------+-------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------+
| mysql-bin.000051 |    4 | Format_desc |       103 |         107 | Server ver: 5.5.32-log, Binlog ver: 4                                                                                                       |
| mysql-bin.000051 |  107 | Query       |         1 |         174 | BEGIN                                                                                                                                       |
| mysql-bin.000051 |  174 | User var    |         1 |         254 | @`pseudo_gtid`=_utf8 0x37383435623633382D353631612D313165342D393135642D336339373065613331656138 COLLATE utf8_general_ci                     |
| mysql-bin.000051 |  254 | Query       |         1 |         452 | use `test`; insert into test.pseudo_gtid (id, ts, gtid) values (1, NOW(), @pseudo_gtid) on duplicate key update ts=NOW(), gtid=VALUES(gtid) |
| mysql-bin.000051 |  452 | Xid         |         1 |         479 | COMMIT /* xid=2141 */                                                                                                                       |
| mysql-bin.000051 |  479 | Query       |         1 |         572 | use `test`; create table test.vals(id int)                                                                                                  |
| mysql-bin.000051 |  572 | Query       |         1 |         639 | BEGIN                                                                                                                                       |
| mysql-bin.000051 |  639 | User var    |         1 |         719 | @`pseudo_gtid`=_utf8 0x37653362616434382D353631612D313165342D393135642D336339373065613331656138 COLLATE utf8_general_ci                     |
| mysql-bin.000051 |  719 | Query       |         1 |         917 | use `test`; insert into test.pseudo_gtid (id, ts, gtid) values (1, NOW(), @pseudo_gtid) on duplicate key update ts=NOW(), gtid=VALUES(gtid) |
| mysql-bin.000051 |  917 | Xid         |         1 |         944 | COMMIT /* xid=2150 */                                                                                                                       |
| mysql-bin.000051 |  944 | Query       |         1 |        1011 | BEGIN                                                                                                                                       |
| mysql-bin.000051 | 1011 | User var    |         1 |        1091 | @`pseudo_gtid`=_utf8 0x38343331396662332D353631612D313165342D393135642D336339373065613331656138 COLLATE utf8_general_ci                     |
| mysql-bin.000051 | 1091 | Query       |         1 |        1289 | use `test`; insert into test.pseudo_gtid (id, ts, gtid) values (1, NOW(), @pseudo_gtid) on duplicate key update ts=NOW(), gtid=VALUES(gtid) |
| mysql-bin.000051 | 1289 | Xid         |         1 |        1316 | COMMIT /* xid=2152 */                                                                                                                       |
| mysql-bin.000051 | 1316 | Query       |         1 |        1375 | BEGIN                                                                                                                                       |
| mysql-bin.000051 | 1375 | Query       |         1 |        1476 | use `test`; insert into test.vals (id) values (17)                                                                                          |
| mysql-bin.000051 | 1476 | Xid         |         1 |        1503 | COMMIT /* xid=2154 */                                                                                                                       |
| mysql-bin.000051 | 1503 | Query       |         1 |        1562 | BEGIN                                                                                                                                       |
| mysql-bin.000051 | 1562 | Query       |         1 |        1663 | use `test`; insert into test.vals (id) values (18)                                                                                          |
| mysql-bin.000051 | 1663 | Xid         |         1 |        1690 | COMMIT /* xid=2156 */                                                                                                                       |
| mysql-bin.000051 | 1690 | Query       |         1 |        1749 | BEGIN                                                                                                                                       |
| mysql-bin.000051 | 1749 | Query       |         1 |        1850 | use `test`; insert into test.vals (id) values (19)                                                                                          |
| mysql-bin.000051 | 1850 | Xid         |         1 |        1877 | COMMIT /* xid=2158 */                                                                                                                       |
| mysql-bin.000051 | 1877 | Query       |         1 |        1944 | BEGIN                                                                                                                                       |
| mysql-bin.000051 | 1944 | User var    |         1 |        2024 | @`pseudo_gtid`=_utf8 0x38613237376232352D353631612D313165342D393135642D336339373065613331656138 COLLATE utf8_general_ci                     |
| mysql-bin.000051 | 2024 | Query       |         1 |        2222 | use `test`; insert into test.pseudo_gtid (id, ts, gtid) values (1, NOW(), @pseudo_gtid) on duplicate key update ts=NOW(), gtid=VALUES(gtid) |
| mysql-bin.000051 | 2222 | Xid         |         1 |        2249 | COMMIT /* xid=2160 */                                                                                                                       |
| mysql-bin.000051 | 2249 | Query       |         1 |        2308 | BEGIN                                                                                                                                       |
| mysql-bin.000051 | 2308 | Query       |         1 |        2409 | use `test`; insert into test.vals (id) values (23)                                                                                          |
| mysql-bin.000051 | 2409 | Xid         |         1 |        2436 | COMMIT /* xid=2162 */                                                                                                                       |
| mysql-bin.000051 | 2436 | Query       |         1 |        2503 | BEGIN                                                                                                                                       |
| mysql-bin.000051 | 2503 | User var    |         1 |        2583 | @`pseudo_gtid`=_utf8 0x39303164373731612D353631612D313165342D393135642D336339373065613331656138 COLLATE utf8_general_ci                     |
| mysql-bin.000051 | 2583 | Query       |         1 |        2781 | use `test`; insert into test.pseudo_gtid (id, ts, gtid) values (1, NOW(), @pseudo_gtid) on duplicate key update ts=NOW(), gtid=VALUES(gtid) |
| mysql-bin.000051 | 2781 | Xid         |         1 |        2808 | COMMIT /* xid=2164 */                                                                                                                       |
| mysql-bin.000051 | 2808 | Rotate      |       103 |        2851 | mysql-bin.000052;pos=4                                                                                                                      |
+------------------+------+-------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------+

We can see the “@`pseudo_gtid`=_utf8 0x” entries are replicated well, and are identical throughout the topology (this continue to work well on second-level slaves etc.).

To be continued.