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