Use Sphinx as MySQL Storage Engine (SphinxSE)

TipsMake.com - SphinX is one of the most powerful text search engines for MySQL . Installing Sphinx daemon is as simple as when you compile from the source code using the .DEB / .RPM package, but using SphinxSE has been a bit more complicated since installing and becoming a plugin on MySQL server system.

So if you are managing a Debian or Centos system, installing MySQL from the .deb or .rpm package will look like this:

Minimum requirement is MySQL 5.1 and above to better support SphinxSE.

Check the version of MySQL in use:

with Debian :

# dpkg --list | grep mysql-server ii mysql-server-5.1 5.1.45-0.dotdeb.0 MySQL database server binaries

with CentOS :

# rpm -qa | grep mysql-server mysql-server-5.1.45-1.el5.remi

Download MySQL source code, the same version with the application on the system here. Simultaneously, download the source code of Sphinx:

# 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'

Decompression:

# tar -xzvf mysql-5.1.45.tar.gz
# tar -xzvf sphinx-0.9.9.tar.gz

Copy mysqlse directory from sphinx to mysql:

# cp -R sphinx-0.9.9 / mysqlse / mysql-5.1.45 / storage / sphinx

Type the following command:

cd mysql-5.1.45 sh BUILD / autorun.sh; ./configure; make

This process will take about 10-20 minutes to complete. Next, copy the .SO files of Sphinx to the MySQL plugin directory:

# cp storage / sphinx / .libs / ha_sphinx. * / usr / lib64 / mysql / plugin

Note that for each system these paths will be different.

Log in to mysql control panel with root account and install Sphinx plugin:

# mysql -u root -p -h localhost mysql> INSTALL PLUGIN sphinx SONAME 'ha_sphinx.so';

Check that the Sphinx engine mechanism is enabled:

mysql> show engines;

mysql> show engines;
+ ------------ + --------- + -------------------------- -------------------------------------- + ----------- --- + ------ + ------------ +
| Engine | Support | Comment | Transactions | XA | Savepoints |
+ ------------ + --------- + -------------------------- -------------------------------------- + ----------- --- + ------ + ------------ +
| InnoDB | YES | Supports transaction, 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 bạn ghi vào nó 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 | Đã dùng hash, lưu trữ trong bộ nhớ, useful cho tạm thời gian NO | NO | NO |
| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO |
+ ------------ + --------- + -------------------------- -------------------------------------- + ----------- --- + ------ + ------------ +

Besides, there is another way to check with the following statement:

mysql> select * from mysql.plugin;

mysql> select * from mysql.plugin;
+ -------- + -------------- +
| name | dl |
+ -------- + -------------- +
| sphinx | ha_sphinx.so |
+ -------- + -------------- +
1 row trong đặt (0.00 sec)

If you want to remove the sphinx plugin, use the following command:

mysql> UNINSTALL PLUGIN sphinx;

If the following error occurs:

mysql> INSTALL PLUGIN sphinx SONAME 'ha_sphinx.so'; ERROR 1062 (23000): Duplicate entry 'sphinx' for key 'PRIMARY'

then you need to manually delete each sphinx plugin component manually in the mysql.plugin table.