Percona Xtradb Recovery

Luke Hebert

- 4 minutes read - 660 words

With the newest release of Percona XtraDB Cluster, I want to highlight a really nice ability: recovering a node and bringing it back to the cluster with an incremental transfer after a crash.

This feature was available in previous releases, but I want to give some details on how it works.

MySQL crashes from time to time, and that is a fact of life. A high-availability solution is exactly what you need to deal with a single node failure and allow the whole cluster to keep working.

The idea is straightforward: if a node crashes, after it recovers, we just transfer all the changes that happened in the cluster while the node was down. It sounds easy in words, but it is proven hard when it comes to implementation. It all comes down to one question: if mysqld crashes, how do we know what the last executed transaction was? For a single InnoDB instance it is easy, because there is always an LSN used for recovery. But in a cluster, all nodes have their individual LSNs. Instead, the cluster uses a Global Transaction ID (GTID) in the form:

50176f05-69b5-11e2-0800-930817fe924a:8549230

So, how can we store the GTID so it is available after a crash? Of course, there is always the straightforward way to store it in a separate file, which however will require an additional fsync call for each transaction, and that is a known performance killer.

Instead, we store the GTID in the InnoDB system area, which is updated for each transaction. So even if the system crashes, we can access information about the last committed transaction.

In XtraDB Cluster you can access this information by calling mysqld with the option:

mysqld --wsrep-recover

Having this information, we can force the node to start using the initial GTID:

mysqld --wsrep_start_position=50176f05-69b5-11e2-0800-930817fe924a:8549230

In fact, the same methodology can be used to restore nodes from a backup. We can start all nodes from an identical starting position, so they all assume they are starting on identical data. You can do it even on non-identical data, but you know that you do not have a consistent cluster in that case.

As all of this may sound complicated, the logic behind --wsrep-recover / --wsrep_start_position= is implemented in the mysqld_safe script, so you have it out of the box.

Here is how the start process looks in the error log:

130129 23:01:22 mysqld_safe Starting mysqld daemon with databases from /mnt/data/mysql
130129 23:01:22 mysqld_safe WSREP: Running position recovery with --log_error=/tmp/tmp.l1zf2BIDja
130129 23:01:45 mysqld_safe WSREP: Recovered position 50176f05-69b5-11e2-0800-930817fe924a:8549230
130129 23:01:45 [Note] WSREP: wsrep_start_position var submitted: '50176f05-69b5-11e2-0800-930817fe924a:8549230'
130129 23:01:45 [Note] WSREP: Read nil XID from storage engines, skipping position init
130129 23:01:45 [Note] WSREP: wsrep_load(): loading provider library '/usr/local/mysql/lib/libgalera_smm.so'
130129 23:01:45 [Note] WSREP: wsrep_load(): Galera 2.3(r143) by Codership Oy <info@codership.com> loaded successfully.
...
130129 23:01:45 [Note] WSREP: Setting initial position to 50176f05-69b5-11e2-0800-930817fe924a:8549230
130129 23:01:56 [Note] WSREP: Shifting OPEN -> PRIMARY (TO: 8549242)
130129 23:01:56 [Note] WSREP: State transfer required:
        Group state: 50176f05-69b5-11e2-0800-930817fe924a:8549242
        Local state: 50176f05-69b5-11e2-0800-930817fe924a:8549230
....
130129 23:01:58 [Note] WSREP: Prepared IST receiver, listening at: tcp://208.88.225.242:4568
130129 23:01:58 [Note] WSREP: Node 5 (node3) requested state transfer from '*any*'. Selected 0 (node1)(SYNCED) as donor.
130129 23:01:58 [Note] WSREP: Shifting PRIMARY -> JOINER (TO: 8549306)
130129 23:01:58 [Note] WSREP: Requesting state transfer: success, donor: 0
130129 23:01:58 [Note] WSREP: SST complete, seqno: 8549230
...
130129 23:02:01 [Note] WSREP: Receiving IST: 12 writesets, seqnos 8549230-8549242
130129 23:02:01 [Note] /usr/local/mysql/bin/mysqld: ready for connections.

So what happens there? Basically, at start the node detects that the last transaction had GTID 50176f05-69b5-11e2-0800-930817fe924a:8549230. But when joining the cluster, it figures out that the cluster is already at position 50176f05-69b5-11e2-0800-930817fe924a:8549242. To catch up, the node has to receive 12 events, which successfully happen:

130129 23:02:01 [Note] WSREP: Receiving IST: 12 writesets, seqnos 8549230-8549242

After applying those 12 events locally, the node is ready and successfully joins the cluster.

References