Blog

How to enable Sphinx Search Engine to MySQL

Tags: MySQLpluginsphinx

Published on: August 18, 2014 by Irshad K.

How to enable Sphinx Search Engine to MySQL

Scenario:

Sphinx is a Linux and Windows based search engine service which allows full text searching of extremely large databases in a very efficient and quick manner We are here providing the steps for sphinx mysql integration . 

1. Identify the current version of MySQL

# type mysql
mysql is hashed (/usr/bin/mysql)
# rpm -qf /usr/bin/mysql
mysql-5.5.31-1.el6.remi.x86_64
The current version is  mysql-5.5.31-1.el6.remi.x86_64
2. Download the MySQL source file 

As you already know, the current MySQL lacks Sphinx  support and our aim is to make the installation with the features of Sphinx. To enable an additional feature, we need to customize the core binary of the application. Which can only be achieved through configuration of the source files available as .tar.gz file or as Source RPMs. Since the customization in this case is to be done on the installed version, we should use the exact version of  MySQL Source RPM to fetch the source file locations.

I got the Source RPM  from the site  and the search keyword used was “mysql-5.5.31-1 src”

3. Source RPM installation
 Once you got the source rpms, the next step of course is install it.  Let us have a brief look into the architecture of Source RPMs to get a better understanding about the following steps.

An  SRPM contains a specfile (it will typically have an extension .spec, which provides information about the software being packaged) and the original sources, such as .tar.gz files from the original developers.  By default  the .spec files are placed in /usr/src/redhat/SPECS and the source files will be placed in the /usr/src/Redhat/SOURCES .

 Now let us check the SRPM for all these files

# rpm -qpl MySQL-5.5.31-2.el6.src.rpm
mysql-5.5.31.tar.gz
mysql.5.5.31.spec

The above command doesn’t reveal the location of the source files or the .spec file. Increasing the  verbosity of the installation command revealed the source location as /root/rpmbuild/SOURCES/.  I am pasting the relevant parts of the detailed output for your reference

# rpm -ivvvvh MySQL-5.5.31-2.el6.src.rpm
:
:
D: fini      100644  1 (   0,  10)  24588168 /root/rpmbuild/SOURCES/mysql-5.5.31.tar.gz;51893cd8 unknown
D: fini      100644  1 (   0,  10)     81051 /root/rpmbuild/SPECS/mysql.5.5.31.spec;51893cd8 unknown
GZDIO:    3012 reads, 24669604 total bytes in 0.080828 secs
:
:

Now it is clear that the installation has placed the files into the folder  /root/rpmbuild/SOURCES/.
4 . Extraction of source file

You need to descend to the source file directory to access the compressed file


#cd /root/rpmbuild/SOURCES/

Once descended, extract the source file mysql-5.5.31.tar.gz and get into the directory “storage”
# tar xzf mysql-5.5.31.tar.gz
# cd mysql-5.5.31
# cd storage/
The following steps will explain you  how Sphinx engine is configured
 5. Download the Sphinx storage engine
 Here we are going to download the Sphinx storage engine and add the SphinxSE as MySQL plugin, so that the SphinxSE can interact with Sphinx Server, searched.
 
# wget http://sphinxsearch.com/files/sphinx-2.0.6-release.tar.gz
# tar xzf sphinx-2.0.6-release.tar.gz
# cd sphinx-2.0.6-release
# mv mysqlse/ /root/rpmbuild/SOURCES/mysql-5.5.31/storage/sphinx
6. Sphinx Configuration
Now let us Build the library, ha_sphinx.so. Steps to be followed. 
Please be careful, NOT to run “make install” at the end of the below steps.
# cd /root/rpmbuild/SOURCES/mysql-5.5.31/
# sh BUILD/autorun.sh
# ./configure
# make

Make sure that the ha_sphinx.so is created inside the storage/sphinx folder. If it didn’t, may be we did something wrong. You need to check out on that.

Luckily for us, it is created here
[root@server sphinx]# cd /root/rpmbuild/SOURCES/mysql-5.5.31/storage/sphinx
[root@server sphinx]# ls
CMakeFiles      INSTALL   Makefile.am          gen_data.php  ha_sphinx.h   make-patch.sh  snippets_udf.cc     sphinx.5.0.27.diff  sphinx.5.0.91.diff
CMakeLists.txt  Makefile  cmake_install.cmake  ha_sphinx.cc  ha_sphinx.so  plug.in        sphinx.5.0.22.diff  sphinx.5.0.37.diff

6. Copy the .so file to plugin directory

Next step is to copy the compiled shared object / library from the storage/sphinx to the MySQL plugin folder.
[root@server sphinx]# cp ha_sphinx.so /usr/lib64/mysql/plugin/
 7. Plugin installation
Now we have to install the plugin. For that login to the MySQL server as root and execute the command
 INSTALL PLUGIN sphinx SONAME ‘ha_sphinx.so’;
mysql >  INSTALL PLUGIN sphinx SONAME 'ha_sphinx.so';
Query OK, 0 rows affected (0.03 sec)
8. Verification
Verify whether the storage engine is enabled or not 
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| SPHINX | YES | Sphinx storage engine 2.0.6-release | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
10 rows in set (0.00 sec)
mysql> Bye</div>
<div class="line number1 index0 alt2">
You may use the following command to confirm the activation

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


That’s it!!!

Category : Howtos, Linux, MySQL, Troubleshooting

Irshad K.

Irshad K.

You may also read:

Comments

Add new commentSIGN IN

Let's Connect

Get new updates

Categories

$0.000 items