- 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.
- We were hoping to get a better multimaster redundancy system setup than the traditional process.
- We were hoping to get SphinxSE involved.
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 -sMake 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
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-serverCreate a MySQL user: this would normally be done by the MySQL installation.
groupadd mysql useradd -g mysql mysqlGet 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/dataStart 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/storageBuild 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 ONNow 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 installBuild 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 installPrepare some shared libraries. Create
echo "/usr/bin/mysql/lib" > /etc/ld.so.conf.d/mysql.conf ldconfigStart 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.dEdit the
/etc/mysql/conf.d/wsrep.cnffile and set the following values:
wsrep_provider=/usr/lib/galera/libgalera_smm.so wsrep_cluster_address="gcomm://" wsrep_sst_auth=wsrep_sst:some_random_passwordNOTE: 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...
/etc/mysql/my.cnf: add the following lines to the end of the file:
binlog_format=row !includedir /etc/mysql/conf.dEdit
/etc/bash.bashrcand add the following lines:
PATH=$PATH:/usr/bin/mysql/bin:/usr/bin/sphinx/bin export PATHReload bashrc so that the MySQL binaries and Sphinx binaries are now in your path:
source /etc/bash.bashrc source ~/.bashrcPrepare 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 startUse the following two lines to get MySQL to load on startup:
chkconfig --list mysql.server chkconfig mysql.server on --level 2,3,4,5Restart 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*!