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:

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

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

[html]
SELINUX=disabled
[/html]

Add the following keys to the repository:

[html]
apt-key adv –keyserver keys.gnupg.net –recv-keys 1C4CBDCDCD2EFD2A
[/html]

Add Percona sources:

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

Update and install Percona:

[html]
apt-get update
sudo apt-get install percona-xtradb-cluster-56
[/html]

Access MySQL creating user and privileges:

[html]
CREATE USER ‘sstuser’@’localhost’ IDENTIFIED BY ‘password’;
GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO ‘sstuser’@’localhost’;
FLUSH PRIVILEGES;
[/html]

Stop MySQL:

[html]
service mysql stop
[/html]

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

[html]
vim /etc/mysql/my.cnf
[/html]

Insert the following configuration:

[html]
# 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
[/html]

*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):

[html]
/etc/init.d/mysql bootstrap-pxc
[/html]

Login to MySQL and check the status of the Cluster:

[html]
show status like ‘%wsrep%’;
[/html]

Something similar should be shown if all working as expected:

[html]
| 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
[/html]

*On second Node

Start MySQL:

[html]
/etc/init.d/mysql start
[/html]

Login to MySQL and run the show status command:

[html]
show status like ‘%wsrep%’;
[/html]

Something similar should be shown if all working as expected:

[html]
| 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
[/html]

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@matthewc424.sg-host.com.