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
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”
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 .
# 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 : :
You need to descend to the source file directory to access the compressed file
#cd /root/rpmbuild/SOURCES/
# tar xzf mysql-5.5.31.tar.gz # cd mysql-5.5.31 # cd storage/
# 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
# 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.
[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
[root@server sphinx]# cp ha_sphinx.so /usr/lib64/mysql/plugin/
mysql > INSTALL PLUGIN sphinx SONAME 'ha_sphinx.so'; Query OK, 0 rows affected (0.03 sec)
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">
mysql> select * from mysql.plugin; +--------+--------------+ | name | dl | +--------+--------------+ | sphinx | ha_sphinx.so | +--------+--------------+ 1 row in set (0.00 sec)
