Setup Galera with SST XtraBackup method
SST stands for State Snapshot Transfer, this method is used while adding a new node to the cluster.The current methods are availables:
mysqldump: the donor node performs a mySQL dump and export it to the joiner node, you explicitly need the user and password of your MySQL database administor account
rsync: the donor node rsync the database files to the joiner node
xtrabackup: the donor uses the Percona binary to dump and export on fly the content of the database to the joiner
It’s really important to understand the big picture of adding a new node to a cluster. When a node is about to join the cluster, it sends a request through the gcomm url to a current member of the cluster. The chosen node status will changed from JOINED to DONOR and apparently a
FLUSH TABLES WITH READ LOCK is performed by the SST method. During the process only the DONOR node is locked, that should be ok if your cluster is not really busy but if all the nodes are intensively in used you may consider one method rather than another. Indeed the SST XtraBackup isn’t a blocking method.
If you are familiar with MySQL and Galera you will notice that the installation and configuration are very trivials.
Add the Percona repository to your
deb http://repo.percona.com/apt squeeze main deb-src http://repo.percona.com/apt squeeze main
Add the repository key:
$ sudo gpg --keyserver hkp://keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A
Install the related packages:
$ sudo apt-get update && sudo apt-get install percona-xtradb-cluster-client-5.5 percona-xtradb-cluster-server-5.5 percona-xtrabackup
Create a new configuration file called
my.cnf and put the following content:
[mysqld] # mysql general option bind-address = 0.0.0.0 user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp lc-messages-dir = /usr/share/mysql skip-external-locking # galera related options wsrep_provider=/usr/lib/libgalera_smm.so wsrep_cluster_name="mon_beau_galera_cluster" wsrep_cluster_address="gcomm://0.0.0.0" wsrep_sst_auth=root:password wsrep_certify_nonPK=1 wsrep_convert_LOCK_to_trx=0 wsrep_auto_increment_control=1 wsrep_drupal_282555_workaround=0 wsrep_causal_reads=0 wsrep_sst_method=xtrabackup
Finally start the service:
$ sudo service mysql start
Check if this node is ready to add member:
mysql> SHOW status LIKE 'wsrep%';
wsrep_ready value in ON, great!
Check the name of your cluster, it must be the same as the one you put in your
mysql> SHOW variables LIKE 'wsrep_cluster_name';
Let say that the IP address of the current node is 10.0.0.1, repeat the same installation as above except that you need to change the gcomm:// url, you will put something like this:
After that change the URL of the first node, the address should be
gcomm:// so change it to
gcomm://10.0.0.2, according to the IP address of the second node. Finally repeat the process to add more node.
As you add node, you will see this variable growing according to the number of node:
+--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | wsrep_cluster_size | 3 | +--------------------+-------+