Feature in details: Incremental state transfer after a node crash in Percona XtraDB Cluster

One thing that isn’t always clear is when you need to recover the wsrep position. Typically the answer is “if you know the node crashed”, but that may not always be clear and to avoid an SST, you want to be very sure your node has the best possible change of ISTing before you issue a restart.

Why? Because as soon as PXC decides to do an SST, it starts dutifully overwriting your datadir and once that starts, you have to continue with a full SST.

To do this, you can check your grastate.dat:

[root@node3 ~]# cat /var/lib/mysql/grastate.dat
# GALERA saved state
version: 2.1
uuid: 8d211006-5bf5-11e2-0800-067f71542765
seqno: -1
cert_index:

On a running node, the seqno is always ‘-1’. If the node crashes, this remains -1, and this will trigger a full SST on restart unless you intervene. However, if you see: a positive integer there, then this would be from mysqld being shut down cleanly.

In addition to starting mysqld or mysqld_safe directly on the command line as Vadim suggests, you can modify the grastate.dat directly to match your recovered sequence number directly and just restart mysql via your init script:

[root@node3 ~]# cat /var/lib/mysql/grastate.dat
# GALERA saved state
version: 2.1
uuid: 8d211006-5bf5-11e2-0800-067f71542765
seqno: -1
cert_index:

So we can see our seqno is -1, this is asking for an SST (this assumes mysqld is not still running). Let’s find out where it left off using –wsrep-recover:

[root@node3 ~]# mysqld_safe –wsrep-recover
130131 12:44:17 mysqld_safe Logging to ‘/var/lib/mysql/error.log’.
130131 12:44:17 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
130131 12:44:17 mysqld_safe WSREP: Running position recovery with –log_error=/tmp/tmp.YLnmVW4zAl
nohup: ignoring input and appending output to `nohup.out’
130131 12:44:23 mysqld_safe WSREP: Recovered position 8d211006-5bf5-11e2-0800-067f71542765:314773
130131 12:44:28 mysqld_safe mysqld from pid file /var/lib/mysql/node3.pid ended

So I know the position, now I can carefully edit grastate.dat (I use vi) and update the uuid and seqno fields):

[root@node3 ~]# cat /var/lib/mysql/grastate.dat
# GALERA saved state
version: 2.1
uuid: 8d211006-5bf5-11e2-0800-067f71542765
seqno: 314773
cert_index:

[root@node3 ~]# service mysql start
Starting MySQL (Percona XtraDB Cluster).. SUCCESS!
[root@node3 ~]# grep IST /var/lib/mysql/error.log | tail -n 1
130131 12:49:10 [Note] WSREP: IST received: 8d211006-5bf5-11e2-0800-067f71542765:318688

The caveat to relying on –wsrep-recover is that it pulls it’s state from the Innodb redo logs. Any updates to any MyISAM tables, or any ongoing DDL (ALTER or similar) are not transactional. It’s probably the case that wsrep-recovery can be relied on most of the time since those types of changes are normally rare, but it is not a silver bullet.