Setting Up Percona, Galera and SphinxSE

In the last project I worked on, I had a chance to play with what I would consider improvements to the typical MySQL install that most of us use. There were three aspects to what we were looking for:

1. Naturally we were looking for a system that had the reliability of MySQL, but at the same time we were itching to try some of the forks that have evolved from that codebase.
2. We were hoping to get a better multimaster redundancy system setup than the traditional process.
3. We were hoping to get SphinxSE involved.

I'll probably talk about these three points separately some other time, so for now I will just touch on them lightly...

As far as the MySQL fork is concerned: there are a number of projects that are based off of MySQL that have either improved or replaced the main InnoDB storage engine. These generally provide faster services than the traditional MySQL but some also add some extra features. For this, we wanted to try the Percona Server.

Multimaster replication in MySQL generally is a hassle to setup: not so much on the 'creation' side but on the redundancy side. It's pretty easy to set it up to run, but setting it up to fail 'correctly' generally involves other systems. We had heard of Galera and figured that this might be a good time to try it. Galera is basically a system that links in with the MySQL server/s, but at the same time talks amongst itself to ensure that all of the servers are in sync. If a new server comes up or an old server goes down, Galera is there talking, making sure that everything is still organised correctly.

Sphinx is basically a super fast full text searching service: it is much faster than, and more capable than, the traditional MySQL text searches. While Sphinx has a couple of different ways it can be interacted with, the Sphinx Storage Engine intrigued us. SphinxSE allows you to essentially tunnel through from a 'normal' MySQL table right through to Sphinx allowing you to do the Sphinx searching as 'just another' INNER JOIN in your query.

So with that in mind, we went searching for a solution that was already made :)

Unfortunately we weren't able to find anything complete... they were either missing the Galera component or the SphinxSE part.

So, for anyone interested in setting something like this up themselves, I've put together a quick little hacky guide to try. Hopefully everything makes as much sense as it can. If you read the instructions you'll see that we are using some custom (and possibly strange) directories for things like where the Sphinx data will go, as well as the MySQL/Percona data/configuration etc. You will also notice that there's a point where you download a patch file to apply to Sphinx: this is based on an older patch file for Percona that was aimed at an older version of Sphinx. It just makes sure that the SE will build.

Some final notes: I did all of this a while ago and so some things have changed since then. Galera has been updated since I wrote these instructions, as has Sphinx. I also just saw that Percona is expecting a beta release at the end of February 2012 of their 'Percona XtraDB Cluster' server which, from what I understand, is Percona with Galera built into it. So with that in mind, this is really just some notes on how to get things working if you're super keen on at least playing with this stuff. Oh yeah, and these notes are all based off of Ubuntu 11.10 Server. It assumes that you haven't already installed MySQL, as that gets to be a problem (as you're basically going to be trying to replace it). And with that in mind, here goes...

Be nice to yourself: become root...

sudo -s

Make sure that for all of the main repositories (at least) you not only have the deb repos, but also the deb-src repos. This can be checked in /etc/apt/sources.list.

Update and get some things you'll need...

apt-get update
apt-get install dpkg-dev bzr cmake cmake-curses-gui chkconfig libssl0.9.8
apt-get build-dep mysql-server

Create a MySQL user: this would normally be done by the MySQL installation.

groupadd mysql
useradd -g mysql mysql

Get some directories ready.

mkdir /etc/mysql
mkdir /etc/mysql/conf.d
mkdir /var/lib/mysql
mkdir /var/lib/mysql/data

chown -R mysql:mysql /etc/mysql
chown -R mysql:mysql /etc/mysql/conf.d
chown -R mysql:mysql /var/lib/mysql

mkdir /etc/sphinx
mkdir /var/lib/sphinx
mkdir /var/lib/sphinx/log
mkdir /var/lib/sphinx/data

Start getting source...

cd /usr/local/src
wget http://launchpad.net/galera/1.x/21.1.0/+download/galera-21.1.0-amd64.deb
dpkg -i galera-21.1.0-amd64.deb

bzr branch lp:~vadim-tk/percona-server/percona-5.5.15-galera
wget http://www.sphinxsearch.com/files/sphinx-2.0.1-beta.tar.gz
tar -zxvf sphinx-2.0.1-beta.tar.gz
wget http://files.codefromaway.net/sphinx-2.0.1-beta-to-sphinx-2.0.1-beta-5.5.15.patch
cd sphinx-2.0.1-beta
patch -p1 < ../sphinx-2.0.1-beta-to-sphinx-2.0.1-beta-5.5.15.patch
cp -R mysqlse ../percona-5.5.15-galera/storage

Build and install Percona

cd ../percona-5.5.15-galera
ccmake .

You will be given a UI at this point... press 'c' for configure, then 'e' to exit help then finally 't' to toggle to advanced mode. Now, scroll through the list and edit the following values (setting them to the values listed):

CMAKE_INSTALL_PREFIX /usr/bin/mysql
INSTALL_MYSQLDATADIR /var/lib/mysql/data
MYSQL_DATADIR  /var/lib/mysql/data
SYSCONFDIR  /etc/mysql
WITH_READLINE  ON
WITH_WSREP  ON

Now press 'c' for configure, 'e' to exit help and then 'g' to finally generate the configuration file.
Make your newly configured application:

make
make test
make install

Build and install Sphinx

cd /usr/local/src/sphinx-2.0.1-beta
./configure --prefix=/usr/bin/sphinx --sysconfdir=/etc/sphinx --libdir=/usr/libs --datarootdir=/var/lib/sphinx --with-mysql --with-mysql-includes=/usr/bin/mysql/include --with-mysql-libs=/usr/bin/mysql/lib
make install

Prepare some shared libraries. Create /etc/ld.so.conf.d/mysql.conf and then:

echo "/usr/bin/mysql/lib" > /etc/ld.so.conf.d/mysql.conf
ldconfig

Start getting Percona ready by copying configuration files as well as the init script for the service:

cd /usr/bin/mysql
cp ./support-files/my-medium.cnf /etc/mysql/my.cnf
cp ./support-files/wsrep.cnf /etc/mysql/conf.d/wsrep.cnf
cp ./support-files/mysql.server /etc/init.d

Edit the /etc/mysql/conf.d/wsrep.cnf file and set the following values:

wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://"
wsrep_sst_auth=wsrep_sst:some_random_password

NOTE: the wsrep_cluster_address line is initially commented out! you have to remove the commenting hash AS WELL as make the gcomm change... Also note that the 'some_random_password' will get used towards the end of this whole process, so remember it!

Now, we enable Galera...
Edit /etc/mysql/my.cnf: add the following lines to the end of the file:

binlog_format=row
!includedir /etc/mysql/conf.d

Edit /etc/bash.bashrc and add the following lines:

PATH=$PATH:/usr/bin/mysql/bin:/usr/bin/sphinx/bin
export PATH

Reload bashrc so that the MySQL binaries and Sphinx binaries are now in your path:

source /etc/bash.bashrc
source ~/.bashrc

Prepare the MySQL database and finally start the service!

./scripts/mysql_install_db --basedir=/usr/bin/mysql --user=mysql --datadir=/var/lib/mysql/data
/etc/init.d/mysql.server start

Use the following two lines to get MySQL to load on startup:

chkconfig --list mysql.server
chkconfig mysql.server on --level 2,3,4,5

Restart the server now, just to be sure :)

Enable the WSREP user so that it can read from and write to the databases:

mysql -u root -e "SET wsrep_on=OFF; DELETE FROM mysql.user WHERE user='';"
mysql -u root -e "SET wsrep_on=OFF; GRANT ALL ON *.* TO wsrep_sst@'%' IDENTIFIED BY 'some_random_password';"

'some_random_password' is what we remembered back up towards the middle of this process.
Finally, run MySQL from the command line to get the CLI and run:

INSTALL PLUGIN sphinx SONAME 'ha_sphinx.so';

You should now have Percona up and running with Galera replication and SphinxSE on the side. Have fun... I hope it helps *someone*!