Percona XtraDB Cluster 5.6, HAProxy and Debian Wheezy 7.0

Written by Mike

May 3, 2014

Percona XtraDB Cluster 5.6, HAProxy and Debian Wheezy 7.0

Percona XtraDB Cluster provides fantastic performance, reliability and multi-master replication out of the box. XtraDB Cluster is a combination of Percona Server, Galera and Percona XtraBackup.

In this short guide I will go through the steps in configuring a three-node XtraDB Cluster, a load balancer (HAProxy) and a front-end application server (Apache and a simple PHP page).

Three Node XtraDB Cluster Setup

Getting Started:

Create 5 Linux Servers (In this guide I'm using Debian Wheezy 7.0, if you use a different distribution then the steps may vary slightly)
Setup the networking such that 4 of the servers reside on a non-public facing network and the remaining server with a public IP (or publicly accessible otherwise e.g. NAT/Port Forwarding)
SSH into all 5 servers

Install XtraDB Cluster:

Follow these steps on three of the private servers

Add Percona's key to apt

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

Update your apt sources to include Percona's repositories

echo -e "\n\ndeb http://repo.percona.com/apt wheezy main\ndeb-src http://repo.percona.com/apt wheezy main\n\n" | sudo tee -a /etc/apt/sources.list

Pin the packages

echo -e "Package: *\nPin: release o=Percona Development Team\nPin-Priority: 1001\n\n" | sudo tee -a /etc/apt/preferences.d/00XtraDB.pref

Run apt-get update

sudo apt-get update

Install XtraDB Cluster

sudo apt-get -y install percona-xtradb-cluster-5.6

Set a root password when prompted

Configure the Cluster:

On each server create a /etc/mysql/my.cnf config file with the following contents:

[mysqld]
datadir=/var/lib/mysql
user=mysql

binlog_format=ROW

wsrep_provider=/usr/lib/libgalera_smm.so
wsrep_cluster_address=gcomm://IPADDRESS1,IPADDRESS2,IPADDRESS3

wsrep_slave_threads=2
wsrep_cluster_name=testcluster
wsrep_sst_method=rsync
wsrep_node_name=dbnode1

innodb_locks_unsafe_for_binlog=1
innodb_autoinc_lock_mode=2

Replace the IP addresses with all three IP addresses of your database servers. For each server change wsrep_node_name=dbnode1 to dbnode1, dbnode2 and dbnode3 respectively.

On the first database node (dbnode1) execute the following

sudo /etc/init.d/mysql bootstrap-pxc

On the other two database nodes run

sudo /etc/init.d/mysql start

Or if MySQL is already running

sudo /etc/init.d/mysql restart

Let's see if the nodes are synced

sudo cat /var/lib/mysql/*.err

You should see something along the lines of

2014-05-03 18:22:03 9062 [Note] WSREP: Synchronized with group, ready for connections

Create some test data on any node:

mysql -u root -p
CREATE DATABASE testcluster;
USE testcluster;
CREATE TABLE testcluster (answer INT);
INSERT INTO testcluster SET answer=42;

See if the test data syncs to the other nodes:

mysql -u root -p
SHOW DATABASES;

You should see a database named testcluster

USE testcluster;
SELECT * FROM testcluster;

You should also see something similar to:

+--------+
| answer |
+--------+
|     42 |
+--------+
1 row in set (0.00 sec)

Setup the Load Balancer

Great, we now have a MySQL Cluster. The problem is that traditional applications will expect to read from and write to a single database. Using a load balancer such as HAProxy we can work around this, the application will operate as though it is backed onto a single database, the Load Balancer will distribute the workload amongst the nodes and deal with any server failures.

Installing HA Proxy

Head over to the last server on the private network.

sudo apt-get update
sudo apt-get -y install haproxy

Edit /etc/defaults/haproxy and set ENABLED=1

Edit /etc/haproxy/haproxy.cfg and update the contents to the following (Update the IP addresses to match your servers):

global
	log /dev/log    local0
	log /dev/log    local1 notice
	chroot /var/lib/haproxy
	user haproxy
	group haproxy
	daemon

defaults
	log     global
	mode    http
	option  tcplog
	option  dontlognull
	option  redispatch
	retries 3
	maxconn 2000
	contimeout 5000
	clitimeout 50000
	srvtimeout 50000

listen test-cluster 0.0.0.0:3306
	mode tcp
	balance roundrobin
	option tcpka
	server dbnode1 IPADDRESS1:3306 check
	server dbnode2 IPADDRESS2:3306 check
	server dbnode3 IPADDRESS3:3306 check

Restart the HAProxy service

sudo /etc/init.d/haproxy restart

Let's test the HAProxy service

sudo apt-get -y install mysql-client

Run this command a few times

mysql -u root -p -h 127.0.0.1 -e "SHOW VARIABLES LIKE 'wsrep_node_name';"

You should see the output reporting which node is running the query - which changes each time

+-----------------+---------+
| Variable_name   | Value   |
+-----------------+---------+
| wsrep_node_name | dbnode1 |
+-----------------+---------+

Creating a Test Application

We could leave it here for this guide but as a quick demonstration of how this could be used with any application we will create a small PHP script.

On the last server (the one on the public network) we will install Apache and PHP.

sudo apt-get update
sudo apt-get -y install apache2 php5 php5-mysql

Test the installation of the above by putting the public IP of the server in your favourite web browser. You should see "It works!"

sudo rm /var/www/index.html

Create /var/www/index.php with the following contents (include the root password you setup earlier)

<?php

    $user = "root";
    $pass = "YOURPASSWORD";

    $db_handle = new PDO("mysql:host=HAPROXYSERVERIP;dbname=testcluster", $user, $pass);

    foreach ($db_handle->query("SELECT answer FROM testcluster") as $row) {
            print "The answer is " . $row['answer'] . "<br />";
    }

    foreach ($db_handle->query("SHOW VARIABLES LIKE 'wsrep_node_name'") as $row) {
            print "This was returned by " . $row['Value'];
    }

    $db_handle = null;

?>

Reload the test page from earlier and you should see something like the following

The answer is 42
This was returned by dbnode1

Keep refreshing the page and you should see the node returning the result change

Mike Mead