MariaDB and Galera on AWS Amazon Linux

Using the repo from MariaDB to install Galera on an Amazon Linux machine (which is derived from CentOS) will most probably yield an error, due to the changes made to this OS fork.

1. Add the MariaDB repo (CentOS 6, x64, Maria 5.5) to /etc/yum.repos.d/maria.repo.
2. # yum install galera

===================================================================================================================
Package Arch Version Repository Size
===================================================================================================================
Installing:
galera x86_64 23.2.7-1.rhel6 mariadb 6.9 M

Transaction Summary
===================================================================================================================
Install 1 Package

Transaction check error:
file /usr/bin from install of galera-23.2.7-1.rhel6.x86_64 conflicts with file from package filesystem-2.4.30-3.8.amzn1.x86_64

The error
file /usr/bin from install of galera-23.2.7-1.rhel6.x86_64 conflicts with file from package filesystem-2.4.30-3.8.amzn1.x86_64
To analyze the problem, first download these rpm packages:
# yumdownloader filesystem galera

Now, list the files these packages expect:
# rpm -qlvp filesystem-2.4.30-3.8.amzn1.x86_64.rpm|grep /usr/bin
dr-xr-xr-x 2 root root 0 Jan 6 2012 /usr/bin
# rpm -qlvp galera-23.2.7-1.rhel6.x86_64.rpm|grep /usr/bin
drwxr-xr-x 2 root root 0 Sep 19 01:43 /usr/bin

# ls -lhd /usr/bin
dr-xr-xr-x 2 root root 20K Jan 9 10:01 /usr/bin

If you didn’t spot it, the actual chmod for /usr/bin is 555, while galera wants it as 755.

The workaround
# yumdownloader galera
# rpm -i --force galera-23.2.7-1.rhel6.x86_64.rpm
# rpm -q --whatprovides /usr/bin
galera-23.2.7-1.rhel6.x86_64
# yum reinstall filesystem
# rpm -q --whatprovides /usr/bin
filesystem-2.4.30-3.8.amzn1.x86_64

and resume installation:
# yum install MariaDB-Galera-server.x86_64 MariaDB-client.x86_64 MariaDB-common.x86_64

Before proceeding with the config, you should probably read these:
1. The number of minimum recommended nodes is 3; reasons are outlined here, under State Snapshot Transfer paragraph.
2. List of wsrep options and description
3. List of galera parameters which can be passed to wsrep_provider_options. Mind the format: option1=value1;option2=value2;option3=value3
4. States: IST and gcache, SST

As seen in the SST link above, there are 3 (actually 4) methods you can choose from:
– mysqldump
– rsync
– rsync-wan
– xtrabackup
Each has its con and pro, so choose carefully. In this how-to I’ve decided to go with xtrabackup because it requires a bit more config than the
others, which should work out of the box.
In order to use xtrabackup:
1. Add the Percona repo
2. # yum install percona-xtrabackup

Be sure to install percona-xtrabackup, else you will get
[ERROR] WSREP: Failed to read ‘ready ‘ from: wsrep_sst_xtrabackup –role ‘joiner’ –address ‘x.x.x.x’ –auth ‘root:’ –datadir ‘/var/lib/mysql/’ –defaults-file ‘/etc/my.cnf’ –parent ‘5683’
Read: ‘Can’t find innobackupex in the path’
[ERROR] WSREP: Process completed with error: wsrep_sst_xtrabackup –role ‘joiner’ –address ‘x.x.x.x’ –auth ‘root:’ –datadir ‘/var/lib/mysql/’ –defaults-file ‘/etc/my.cnf’ –parent ‘5683’: 22 (Invalid argument)
[ERROR] WSREP: Failed to prepare for ‘xtrabackup’ SST. Unrecoverable.
/usr/bin/wsrep_sst_xtrabackup complains that it cannot find /usr/bin/innobackupex.

The config
You can see one here.

wsrep_provider = /usr/lib64/galera/libgalera_smm.so #wsrep engine
wsrep_cluster_name = "galera-cluster" #cluster name
wsrep_node_name = "node1-192.168.0.1" #logical name
wsrep_cluster_address = "gcomm://192.168.0.2,192.168.0.3" #node addresses
wsrep_provider_options = "gcache.dir=/var/lib/mysql;gcache.size=512M;gcache.mem_size=256M" #custom galera configs
wsrep_replicate_myisam = ON #turn on myisam replication
wsrep_slave_threads = 4 #set to CPU number * 2
wsrep_sst_method=xtrabackup #method
wsrep_sst_auth=root:password #mysql authentication, not used for rsync method

binlog_format = ROW
innodb_flush_log_at_trx_commit = 2
innodb_autoinc_lock_mode = 2

For xtrabackup, my.cnf must be the same across all nodes. Of course, wsrep_node_name and wsrep_cluster_address have to be changed to the corresponding values.
At a minimum, you must use: wsrep_provider, wsrep_cluster_name, wsrep_cluster_address, wsrep_sst_method, wsrep_sst_auth, binlog_format=ROW, innodb_flush_log_at_trx_commit=2, innodb_autoinc_lock_mode=2.

Next commands start up the cluster:
Node1:
/etc/init.d/mysql bootstrap OR /etc/init.d/mysql start --wsrep-new-cluster.
mysql -p -e "show status like 'wsrep_cluster_size'"
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| wsrep_cluster_size | 1 |
+--------------------+-------+

Node2:
/etc/init.d/mysql start
mysql -p -e "show status like 'wsrep_cluster_size'"
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| wsrep_cluster_size | 2 |
+--------------------+-------+

Node3:
/etc/init.d/mysql start
mysql -p -e "show status like 'wsrep_cluster_size'"
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| wsrep_cluster_size | 3 |
+--------------------+-------+

To get the status of galera, do:
mysql -p -e "show global status like 'wsrep_%'" . Description of the output is found here.
One particular useful option configured in my.cnf is wsrep_notify_cmd, in the form wsrep_notify_cmd = /path/to/script, and is triggered every time the node changes status. You’ll have to write your own script; a proof of concept bash script looks like this:
#!/bin/bash

while [ $# -gt 0 ] ; do

case $1 in
--status)
STATUS=$2
shift
;;
--uuid)
CLUSTER_UUID=$2
shift
;;
--primary)
PRIMARY=$2
shift
;;
--index)
INDEX=$2
shift
;;
--members)
MEMBERS=$2
shift
;;
esac
shift
done

if [ "$STATUS" != "Synced" ]; then
echo Not synced, do something
fi

if [ "$STATUS" == "Synced" ]; then
echo Synced, all fine
fi

Some considerations:
When all nodes are stopped, for example all servers are rebooted at the same time, mysql won’t automatically start because there is no cluster initiator, aka bootstrap. Adding “?pc.wait_prim=no” to wsrep_cluster_address on each machine can be used to avoid this situation.
Like so: wsrep_cluster_address = "gcomm://192.168.0.2,192.168.0.3?pc.wait_prim=no"

Note that you will be able to connect to mysql, but you will not be able to run any query on the databases. Thus, on the node you want as the cluster initiator, you will have to execute SET GLOBAL wsrep_provider_options='pc.bootstrap=1'. The other nodes will then sync with it and, depending on the circumstances, become usable.
NOTE: This option does NOT work with xtrabackup. /etc/init.d/mysql start will hang indefinitely.