Skip to content

# Year: 2019

## Quick hack for GTID_OWN lack

MySQL GTID Replication December 11, 2019

One of the benefits of MySQL GTIDs is that each server remembers all GTID entries ever executed. Normally these would be ranges, e.g. `0041e600-f1be-11e9-9759-a0369f9435dc:1-3772242` or multi-ranges, e.g. `24a83cd3-e30c-11e9-b43d-121b89fcdde6:1-103775793, 2efbcca6-7ee1-11e8-b2d2-0270c2ed2e5a:1-356487160, 46346470-6561-11e9-9ab7-12aaa4484802:1-26301153, 757fdf0d-740e-11e8-b3f2-0a474bcf1734:1-192371670, d2f5e585-62f5-11e9-82a5-a0369f0ed504:1-10047`.

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.

Continue reading » “Quick hack for GTID_OWN lack”

## Un-split brain MySQL via gh-mysql-rewind

MySQL Open Source Replication March 5, 2019March 5, 2019

We are pleased to release gh-mysql-rewind, a tool that allows us to move MySQL back in time, automatically identify and rewind split brain changes, restoring a split brain server into a healthy replication chain.

I recently had the pleasure of presenting `gh-mysql-rewind` at FOSDEM. Video and slides are available. Consider following along with the video.

## Motivation

Consider a split brain scenario: a “standard” MySQL replication topology suffered network isolation, and one of the replicas was promoted as new master. Meanwhile, the old master was still receiving writes from co-located apps.

Once the network isolation is over, we have a new master and an old master, and a split-brain situation: some writes only took place on one master; others only took place on the other. What if we wanted to converge the two? What paths do we have to, say, restore the old, demoted master, as a replica of the newly promoted master?

The old master is unlikely to agree to replicate from the new master. Changes have been made. `AUTO_INCREMENT` values have been taken. `UNIQUE` constraints will fail.

A few months ago, we at GitHub had exactly this scenario. An entire data center went network isolated. Automation failed over to a 2nd DC. Masters in the isolated DC meanwhile kept receiving writes. At the end of the failover we ended up with a split brain scenario – which we expected. However, an additional, unexpected constraint forced us to fail back to the original DC.

We had to make a choice: we’ve already operated for a long time in the 2nd DC and took many writes, that we were unwilling to lose. We were OK to lose (after auditing) the few seconds of writes on the isolated DC. But, how do we converge the data?

Backups are the trivial way out, but they incur long recovery time. Shipping backup data over the network for dozens of servers takes time. Restore time, catching up with changes since backup took place, warming up the servers so that they can handle production traffic, all take time.

Could we have reduces the time for recovery?

Continue reading » “Un-split brain MySQL via gh-mysql-rewind”