Our Blog

Recent Posts

Categories

27Apr

Sphinx and SphinxSE

Recently a client requested to setup SphinX, 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


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;
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| 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;
+--------+--------------+
| 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:

* If you get following error
sphinx ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (111)
then your sphinx plugin was not built with the same options as your mysql. If you are using a distribution get the source of the mysql packages your are running on your system instead of downoading the mysql installation tarball from mysql.com. In Debian you would use the follwoing command:
apt-get source mysql-server
* 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.
Categorised under: Sphinx