Setting up XtraDB Cluster (aka Galera) is straight forward…or so I was told. To do this correctly, there are some tricks. Some published. Some disparate. I’m going to save you HOURS of Googling for answers and break down the process here. Just know that from version to version, dependency changes have a HUGE impact. I have to thank Percona for documenting the process for 5.5, but it’s changed a bit for 5.6 and needs to be updated…hence this post. 😉

This post assumes you have a newly installed version of CentOS 7 on two nodes.

[root@localhost ~]# uname -a
Linux localhost.localdomain 3.16.7-x86_64-linode49 #3 SMP Fri Nov 14 16:55:37 EST 2014 x86_64 x86_64 x86_64 GNU/Linux
[root@localhost ~]# date
Wed Dec 31 00:09:51 EST 2014

Manual Process

Setting up a cluster is a methodical, step by step process. I prefer to setup the environment prior to MySQL installation to avoid the extra steps of stops and starts. This starts by unifying the logging folder and building the /etc/my.cnf.

Step 1 – Set Logging Folder

Do this on node1 & node2.

#!/bin/bash
mkdir /var/log/mysql
chmod 777 /var/log/mysql

Step 2 – Install Percona XtraDB Cluster

Do this on node1 & node2.

#!/bin/bash
rpm -Uhv http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm
yum install -y Percona-XtraDB-Cluster-full-56 nc

Step 3 – Setup Pre-MySQL-Install /etc/my.cnf

Do this on node1 & node2.

[mysqld]

binlog_format                   =ROW
default_storage_engine          =InnoDB
innodb_autoinc_lock_mode        =2
innodb_locks_unsafe_for_binlog  =1
innodb_buffer_pool_size         =256M

# LOGGING #
log_slave_updates
log-bin                        = /var/lib/mysql/bin.log
log_error                      = /var/log/mysql/mysql-error.log
slow_query_log                 = 1
slow_query_log_file            = /var/log/mysql/mysql-slow.log
long_query_time                = 1

Step 4 – Start & Prep MySQL

Do this on node1 & node2.

#!/bin/bash
systemctl start mysql
mysql -u root -e "CREATE USER 'sstuser'@'localhost' IDENTIFIED BY 'secret'"
mysql -u root -e "GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost'"
mysql -u root -e "FLUSH PRIVILEGES"
mysql -u root -e "CREATE USER 'sstuser'@'127.0.0.1' IDENTIFIED BY 'secret'"
mysql -u root -e "GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'sstuser'@'127.0.0.1'"
mysql -u root -e "FLUSH PRIVILEGES"
mysql -u root -e "CREATE USER 'sstuser'@'192.168.%' IDENTIFIED BY 'secret'"
mysql -u root -e "GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'sstuser'@'192.168.%'"
mysql -u root -e "FLUSH PRIVILEGES"
systemctl stop mysql

Step 5 – Bootstrap the cluster

The first/donor node:
As of the date of this article, setting the “wsrep_sst_method” config parameter isn’t necessary because the default is Xtrabackup V2 (read more).
Add this to your /etc/my.cnf [mysqld] section:

# GALERA #
wsrep_provider                 =/usr/lib64/libgalera_smm.so
wsrep_slave_threads            =4
wsrep_cluster_address          =gcomm://
wsrep_node_address             =192.168.1.101
wsrep_node_name                =host1
wsrep_cluster_name             =cluster1
wsrep_sst_auth                 =\"sstuser:secret\"

Then run the bootstrap service the first boot:

systemctl start [email protected]

you should be able to see the joiner node join the cluster by watching the mysql-error.log messages and by reviewing the wsrep status variables.

mysql> show status like 'wsrep%';
| wsrep_incoming_addresses     | 192.168.1.101:3306  |
...
| wsrep_cluster_size           | 1                                        |

The 2nd/joiner node:

# GALERA #
wsrep_provider                 =/usr/lib64/libgalera_smm.so
wsrep_slave_threads             =4
wsrep_cluster_address          =gcomm://192.168.1.101
wsrep_node_address             =192.168.1.102
wsrep_node_name                =host2
wsrep_cluster_name             =cluster1
wsrep_sst_auth                 ="sstuser:secret"

Now start MySQL on the joiner node:

systemctl start mysql

Step 6 – Testing

On the donor/1st node, you should be able to see the joiner node join the cluster by watching the mysql-error.log messages and by reviewing the wsrep status variables.

mysql> show status like 'wsrep%';
| wsrep_incoming_addresses     | 192.168.1.101:3306,192.168.1.102:3306 |
...
| wsrep_cluster_size           | 2                                        |

Then simply create some database objects and test they are replicated across nodes.

mysql> CREATE DATABASE testDB1;
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| testDB1            |
+--------------------+
5 rows in set (0.00 sec)

Update (2015-11-05):

Due to some configuration differences across products, xtrabackup will assume your mysql data directory is ‘/’ (and crash SST!!) unless your /etc/my.cnf is explicitly configured as follows:

[mysqld]
datadir                        = /path/to/your/mysql