Below are instructions on how to install configure a Percona DB Cluster on Ubuntu (14.04 at time of writing this).

*Carry out on all nodes (servers)

Remove apparmor as it prevents cluster nodes talking to each other:

sudo /etc/init.d/apparmor stop
sudo update-rc.d -f apparmor remove
sudo apt-get remove apparmor apparmor-utils -y

Disable SELinux by editing /etc/selinux/config and add/alter line to disable it:

SELINUX=disabled

Add the following keys to the repository:

apt-key adv –keyserver keys.gnupg.net –recv-keys 1C4CBDCDCD2EFD2A

Add Percona sources:

vim /etc/apt/sources.list
deb http://repo.percona.com/apt trusty main
deb-src http://repo.percona.com/apt trusty main

Update and install Percona:

apt-get update
sudo apt-get install percona-xtradb-cluster-56

Access MySQL creating user and privileges:

CREATE USER 'sstuser'@'localhost' IDENTIFIED BY 'password';
GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost';
FLUSH PRIVILEGES;

Stop MySQL:

service mysql stop

Create a custom my.cnf (remove the current my.cnf if in place):

vim /etc/mysql/my.cnf

Insert the following configuration:

# Path to Galera library
wsrep_provider=/usr/lib/galera3/libgalera_smm.so

# Cluster connection URL
wsrep_cluster_address=gcomm://10.10.10.10,10.10.10.11

# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW

# MyISAM storage engine has only experimental support
default_storage_engine=InnoDB

# This changes how |InnoDB| autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2

# Authentication information for SST. Use the username/password you created in the previous step.
wsrep_sst_auth="sstuser:password"

# Data directory
datadir=/var/lib/mysql

# Temp directory
tmpdir=/tmp

# MySQL User
user=mysql

# Node address
wsrep_node_address=10.10.10.10

# Node name
wsrep_node_name=webserver.example.com

# SST method
wsrep_sst_method=xtrabackup-v2

# Cluster name
wsrep_cluster_name=webserver-cluster

*Replace the following sections from above with your own config:
wsrep_cluster_address=gcomm://10.10.10.10,10.10.10.11
wsrep_sst_auth=”sstuser:password
wsrep_node_address=10.10.10.10
wsrep_node_name=webserver.example.com
wsrep_cluster_name=webserver-cluster

*On Node 1 only

Run the bootstrap command (initial data state for Percona):

/etc/init.d/mysql bootstrap-pxc

Login to MySQL and check the status of the Cluster:

show status like '%wsrep%';

Something similar should be shown if all working as expected:

| wsrep_local_state | 1
| wsrep_local_state_comment | Synced
| wsrep_cluster_size | 1
| wsrep_cluster_state_uuid | c9f5cc91-4abc-21c8-4b83-f68fikkrf0vb
| wsrep_cluster_status | Primary
| wsrep_connected | ON
| wsrep_ready | ON

*On second Node

Start MySQL:

/etc/init.d/mysql start

Login to MySQL and run the show status command:

show status like '%wsrep%';

Something similar should be shown if all working as expected:

| wsrep_local_state | 4
| wsrep_local_state_comment | Synced
| wsrep_cluster_size | 2
| wsrep_cluster_state_uuid | c9f5cc91-4abc-21c8-4b83-f68fikkrf0vb
| wsrep_cluster_status | Primary
| wsrep_connected | ON
| wsrep_ready | ON

Now create databases, tables etc. on both nodes to test the replication is working both ways.

TROUBLESHOOTING

– Add log_error = /var/log/mysql/error.log to /etc/mysql/my.cnf to check logs
– Open port 4567 if restricted (Amazon EC2 restricted by default)

Credit

 

 

Written by Matt Cooper
Hi, I'm Matt Cooper. I started this blog to pretty much act as a brain dump area for things I learn from day to day. You can contact me at: matt@linuxtutorial.co.uk.