Sphinx As MySQL Storage Engine (SphinxSE)

Want to support HowtoForge? Become a subscriber!
 
Submitted by grungy (Contact Author) (Forums) on Wed, 2010-05-19 16:22. :: CentOS | Debian | MySQL

Sphinx As MySQL Storage Engine (SphinxSE)

By Nedim Hadzimahmutovic <h.nedim@gmail.com>
Version: v1.0
Last Change: May 9, 2010

SphinX is a great full-text search engine for MySQL. Installing the Sphinx daemon was straightforward as you can compile it from the source or use a .DEB/.RPM package but SphinxSE was a little bit tricky since it needed to be installed as a plugin on a running MySQL server.

So if you use Debian or Centos and install your MySQL from a .deb or .rpm package this is how you do it.

1. This requires MySQL 5.1 since it supports plugins and you will not have to recompile MySQL to have support for SphinxSE.

2. Check the version of your MySQL package:

For Debian:

# dpkg --list | grep mysql-server ii mysql-server-5.1 5.1.45-0.dotdeb.0 MySQL database server binaries

For CentOS:

# rpm -qa | grep mysql-server mysql-server-5.1.45-1.el5.remi

3. Download MySQL source code, the exact same version as installed on your server, from http://www.mysql.com/downloads/mysql/. The verions must match! Download Sphinx source code.

# cd /tmp/
# wget 'http://mysql.mirrors.hoobly.com/Downloads/MySQL-5.1/mysql-5.1.45.tar.gz'
# wget 'http://sphinxsearch.com/downloads/sphinx-0.9.9.tar.gz'

Extract both archives:

# tar -xzvf mysql-5.1.45.tar.gz
# tar -xzvf sphinx-0.9.9.tar.gz

4. Copy the mysqlse directory from sphinx to mysql:

# cp -R sphinx-0.9.9/mysqlse/ mysql-5.1.45/storage/sphinx

Build:

cd mysql-5.1.45 sh BUILD/autorun.sh; ./configure; make

5. Take a break, this will take a while.

6. Copy the Sphinx .SO files to your MySQL plugin directory:

# cp storage/sphinx/.libs/ha_sphinx.* /usr/lib64/mysql/plugin

The plugin directory may differ on your system.

7. Login to mysql console as root user. Install the Sphinx plugin.

# mysql -u root -p -h localhost mysql> INSTALL PLUGIN sphinx SONAME 'ha_sphinx.so';

Check if Sphinx engine is enabled:

mysql> show engines;

mysql> show engines;
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                        | Transactions | XA   | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE  | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV        | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| SPHINX     | YES     | Sphinx storage engine 0.9.9                                    | NO           | NO   | NO         |
| FEDERATED  | YES     | Federated MySQL storage engine                                 | NO           | NO   | NO         |
| ARCHIVE    | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         | NO           | NO   | NO         |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+

That's it!

Another way to check if the plugin was installed is with following command:

mysql> select * from mysql.plugin;

mysql> select * from mysql.plugin;
+--------+--------------+
| name   | dl           |
+--------+--------------+
| sphinx | ha_sphinx.so |
+--------+--------------+
1 row in set (0.00 sec)

If you need to uninstall the sphinx plugin for some reason later on, this is how you do it:

mysql> UNINSTALL PLUGIN sphinx;

Notes:

* Please have in mind there is a bug in some MySQL versions - when installing plugins, the plugin identifier is not handled consistently with respect to letter case.

* If you get the following error:

mysql> INSTALL PLUGIN sphinx SONAME 'ha_sphinx.so'; ERROR 1062 (23000): Duplicate entry 'sphinx' for key 'PRIMARY'

you will have to manually delete the sphinx plugin entry from the mysql.plugin table. You may get this error after an upgrade.


Please do not use the comment function to ask for help! If you need help, please use our forum.
Comments will be published after administrator approval.
Submitted by gadelkareem (not registered) on Thu, 2012-03-15 23:53.
Great Tutorial! Please check my post for a newer version ... Install Sphinx 2.0.4 on CentOS 6.2
Submitted by sufehmi (not registered) on Sat, 2011-10-22 09:45.

I was just about to install the whole Sphinx search - when I realized that the daemon is already available as a package in Ubuntu 10.04 LTS / Lucid.

 It should be as simple as :

sudo apt-get install sphinxsearch

 Hope it helps someone.

Submitted by Manny (not registered) on Tue, 2011-07-05 20:03.

I followed the instructions above but then I get this:

make[2]: Leaving directory `/app/tmp/mysql/storage/myisam'
Making all in sphinx
make[2]: Entering directory `/app/tmp/mysql/storage/sphinx'
make[2]: *** No rule to make target `all'.  Stop.
make[2]: Leaving directory `/app/tmp/mysql/storage/sphinx'
make[1]: *** [all-recursive] Error 1
make[1]: Leaving directory `/app/tmp/mysql/storage'
make: *** [all-recursive] Error 1
 

 Any ideas on how to fix?

Submitted by Anonymous (not registered) on Wed, 2011-12-07 12:35.

I'm having the same error, any ideas how to fix it? 

 Making all in myisam

make[2]: Entering directory `/tmp/mysql-5.1.58/storage/myisam'

make[2]: Nothing to be done for `all'.

make[2]: Leaving directory `/tmp/mysql-5.1.58/storage/myisam'

Making all in sphinx

make[2]: Entering directory `/tmp/mysql-5.1.58/storage/sphinx'

make[2]: *** No rule to make target `all'. Stop.

make[2]: Leaving directory `/tmp/mysql-5.1.58/storage/sphinx'

make[1]: *** [all-recursive] Error 1

make[1]: Leaving directory `/tmp/mysql-5.1.58/storage'

make: *** [all-recursive] Error 1


Submitted by Alexey (not registered) on Mon, 2011-02-21 19:29.

Thanks a lot, man!! Followed this instruction to build SphinxSE plugin though not on Linux but on FreeBSD.

It worked like a charm! I was worrying about the fact that I do manipulations on LIVE server, but it's allright. :)

Submitted by Yogendra (not registered) on Thu, 2010-07-29 12:39.

This article was extremely helpful in installing SPHINX plugin.

It is more clear and helpfull than the SPHINX documentation.

Great work!

Thanks a Lot.

Submitted by Anonymous (not registered) on Wed, 2010-06-02 15:16.

Thx for this article.

On ubuntu 10.04 LTSn we need to add rules for apparmor in order to install plugin in MySQL.

We  did have to update the apparmor profile /etc/apparmor.d/usr.sbin.mysqld and add this line:

/usr/lib/mysql/plugin/* rm,

and restart apparmor.