Change from rsync to xtrabackup - avoid read-only Donor during SST

This article describes how to change wsrep_sst_method=rsync to wsrep_sst_method=xtrabackup.

THIS ONLY WORKS WITH VERSION LATER OR EQUAL TO:

Galera 2.2.0 (mysql 5.5.28, wsrep 23.7)

Make sure you have a file called wsrep_sst_common (common places to look in follows below):

/usr/local/mysql/bin/wsrep_sst_common

or 

/usr/bin/wsrep_sst_common

See also here http://support.severalnines.com/entries/22969378-sst-with-xtrabackup

Advantage of using xtrabackup

The main advantage of using xtrabackup to synchronize the nodes is that the Donor is writeable during the synchronization process.

Bug: the web interface will still show the Donor being "read-only" despite the wsrep_sst_method is set to xtrabackup.

Setup

Install xtrabackup

You need to install xtrabackup on all nodes in the cluster.

On each node you can run:

s9s_backup --install -i <clusterid>
#for example:
s9s_backup --install -i 1
#verify it is installed:
which innobackupex
#the above should print out the following if everything is okay:
/usr/bin/innobackupex
# ubuntu/debian:
apt-get install nc
# redhat/centos:
yum install nc 

s9s_backup requires internet access to Percona's yum or apt repositories.

If you don't have s9s_backup, you can download it here:  http://www.severalnines.com/downloads/cmon/s9s_backup and place it in  /usr/bin on each node.

Read more about s9s_backup here http://support.severalnines.com/entries/22358923-backup-s9s-backup-and-installing-xtrabackup 

On all nodes download and install (PXC users should not have to do this step):

https://launchpad.net/codership-mysql/5.5/5.5.28-23.7/+download/wsrep_sst_xtrabackup

and place the wsrep_sst_xtrabackup in {mysql_basedir}/bin

Ubuntu/Debian (most likely location):

/usr/local/mysql/bin

Redhat/Centos:

/usr/bin/ 

Then do:

chmod u+x /usr/bin/wsrep_sst_xtrabackup
chown mysql:mysql /usr/bin/wsrep_sst_xtrabackup
#or
chmod u+x /usr/local/mysql/bin/wsrep_sst_xtrabackup
chown mysql:mysql /usr/local/mysql/bin/wsrep_sst_xtrabackup 

Update Configuration - Subscription Customers

Go into Config Mgmt , and update each my.cnf file and set:

wsrep_sst_method=xtrabackup

When saving, the configuration file will be exported to the node in question.

Now, the next time a node crashes and needs to do an SST the xtrabackup method will be used. 

Update Configuration - Community

On all nodes edit the  my.cnf file and set:

wsrep_sst_method=xtrabackup

Now, the next time a node crashes and needs to do an SST the xtrabackup method will be used.