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!!!

About+

Hire a service+

Tech Stack+

SupportSages

IP block: Common Reasons and Resolutions

Magento : Version Identification

Magento: Common Issues and Resolutions

Reset SSH server to defaults using WHM

Posts by Irshad K.

About

Hire a service

Tech Stack