Blog/ How to set up Percona XtraDB Cluster on Ubuntu

Using Percona XtraDB Cluser with at least 3 nodes is a good option when you want to use MySQL in a high-availability mode. The advantage is that even if one of the nodes goes down, the application will still be able to read and write to the MySQL database. In this article, we'll show you the steps to install Percona XtraDB Cluster 5.6 on Ubuntu 12.04. Here are the steps:

1) Percona packages are signed with a key. First we need to add the key to apt. Execute the following command on all the three servers.

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

Note that if you are behind a firewall, the above command may not work. In that case, you will need to import the key manually.

2) Open /etc/apt/sources.list on all the three servers and add the following two lines:

deb http://repo.percona.com/apt precise main
deb-src http://repo.percona.com/apt precise main

3) Update the local cache on all the three servers.

sudo apt-get update

4) Install Percona XtraDB Cluster package on all the three servers.

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

5) Create 'sstuser' on each MySQL database. Provide reload, lock tables and relication access to this user.

mysql> CREATE USER ’sstuser’@’localhost’ IDENTIFIED BY ’sstpassword’;
mysql> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO ’sstuser’@’localhost’;
mysql> FLUSH PRIVILEGES;

6) Create /etc/mysql/my.cnf file on all the three servers and add the following lines. Replace <server-1-ip>, <server-2-ip> and <server-3-ip> by the IP addresses of the three MySQL nodes. Replace <server-ip> by the IP address of the current node.

[mysqld]

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

# Cluster connection URL
wsrep_cluster_address=gcomm://<server-1-ip>,<server-2-ip>,<server-3-ip>

# 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:sstpassword"

# Data directory
datadir=/var/lib/mysql

# Temp directory
tmpdir=/tmp

# MySQL User
user=mysql

# Node #1 address
wsrep_node_address=<server-ip>

# SST method
wsrep_sst_method=xtrabackup-v2

# Cluster name
wsrep_cluster_name=s2p_prod_cluster

7) Restart MySQL server 1 using the following command:

sudo service mysql restart-bootstrap

8) See the values of wresp_local_state, wresp_local_state_comment, wresp_cluster_size, wresp_cluster_status, wresp_connected and wresp_ready on MySQL server 1.

mysql> show status like 'wsrep%';
+------------------------------+-------------------------------------+
| Variable_name             | Value
+------------------------------+-------------------------------------+
| wsrep_local_state_uuid    | 8e4863b3-3c48-11e4-8c63-2be5c89c6340
| wsrep_local_state         | 1
| wsrep_local_state_comment | Synced
| wsrep_cluster_size        | 1
| wsrep_cluster_status      | Primary
| wsrep_connected           | ON
| wsrep_ready               | ON
+------------------------------+-------------------------------------+

If you get output something like above, it means that MySQL server 1 bootstrapped successfully.

8) Restart MySQL servers 2 and 3 using the following command:

sudo service mysql restart

9) See the values of wresp_local_state, wresp_local_state_comment, wresp_cluster_size, wresp_cluster_status, wresp_connected and wresp_ready on any MySQL server.

mysql> show status like 'wsrep%';
+------------------------------+-------------------------------------+
| Variable_name             | Value
+------------------------------+-------------------------------------+
| wsrep_local_state_uuid    | 8e4863b3-3c48-11e4-8c63-2be5c89c6340
| wsrep_local_state         | 1
| wsrep_local_state_comment | Synced
| wsrep_cluster_size        | 3
| wsrep_cluster_status      | Primary
| wsrep_connected           | ON
| wsrep_ready               | ON
+------------------------------+-------------------------------------+

If the output is something like above, that means that the cluster is up. Verify that wsrep_clister_size is the actual number of nodes in the cluster.

10) Now create a database on one of the servers. Verify that it got replicated on the remaining 2 servers.

PS: Note that if you have SELinux installed, you will need to disable it for XtraDB Cluster to work. Also if you had installed MySQL on the server, you would most likely have an AppArmor profile which will prevent XtraDB Cluster nodes from communicating with each other. Click here to see the instructions on how to disable AppArmor's MySQL profile.