Elijah Paul

Percona recently released the beta version of their MySQL cluster offering. Percona XtraDB Cluster (Beta).

Features:

• Synchronous replication
• Multi-master replication support
• Parallel replication
• Automatic node provisioning

I've been using Percona XtraDB Server as a replacement for regular old plain MySQL deployments more and more recently. And I have to say that I've definitely seen a performance increase with a number of different scenarios.

Percona XtraDB Cluster is built on MySQL 5.5 and Galera 2.0 GA. It's setup such that through "parallel replication", each node in a cluster contains a full (current) copy of data. Queries can be written to any node in a cluster and the data is synchronized to all nodes, thus maintaining data consistency. Since write queries are either committed on all nodes or not committed at all, the consistency of your data is guaranteed.

Installing Percona XtraDB Cluster in an Amazon EC2 environment is covered in the provided documentation. However I did encounter a few issues when attempting to setup a cluster using CentOS 6.2 images (This is after all a beta release).

First, create a security group for your EC2 instances with the following open ports: 22 (SSH), 3306 (MySQL Default), 4444, 4567, 4568

Now, create 3 new EC2 images using your favourite CentOS 6.0/6.2 AMI. For this tutorial I used RightScales CentOS 6.0 64bit EBS AMI's to create three (of Amazons new m1.medium) instances, each with a 30GB EBS root volume. Run the yum update command to update your instances to CentOS 6.2 (Final).

</

1. Now install Percona's regular and testing repositories:

rpm -Uhv http://repo.percona.com/testing/centos/6/os/noarch/percona-testing-0.0-1.noarch.rpm
rpm -Uhv http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm

</
2. Run the following command to install the shared-compat packages. (I found that this works around a bug you encounter after you've installed Percona XtraDB Cluster and attempt to start the service on a node, as it prevents conflicts with the mysql-lib package that many CentOS packages depend on. The bug is documented here: Bug #947410)

yum install Percona-Server-shared-compat.x86_64

</
3. Install the Percona XtraDB Cluster packages:

yum install Percona-XtraDB-Cluster-server Percona-XtraDB-Cluster-client

</
4. Create your data directories:

mkdir -p /mnt/data
mysql_install_db --datadir=/mnt/data

</
5. I found the install once again (in my case) failed to start unless you gave the mysql user ownership of your data directory. Run the following command to do so:

chown -R mysql.mysql /mnt/data

</

From this point onwards, following the Percona XtraDB Cluster provided instructions didn't produce any further hicups with the install or starting of the service.

Once you've joined your 2nd and 3rd node to the 1st node you can test the clusters Multi-master replication feature, by creating a database on any one of the three nodes. The resulting database should be synchronized automatically to the remaining two nodes.

For High Availability and loadbalancing testing, I placed an HAproxy server infront of the 3 node cluster (I'll go more into how I setup and configured HAproxy in another post.), and tested by shutting down first one, and then another node. As expected, my test application (Wordpress) continued to function unimpeded by the shutdown of any two nodes in the cluster. And once these nodes were rebooted, they automatically fetched any changes in data made during their downtime from the node that remained up. The method by which the nodes perform this function is described in Percona's documentation.

It's worth noting that the Percona XtraDB Cluster (Beta) does have a few limitations. One being that currently, replication works only with the InnoDB storage engine. Any writes made to tables of other types, including system (mysql.*) tables , are not replicated. Percona has stated that some of these limitations will be eliminated in future releases.