Use Sphinx as Mysql Storage Engine (Sphinxse)

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

How Use Sphinx as Mysql Storage Engine (Sphinxse) Works

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 app 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.

Sign 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)

When needed 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'

Next, manually delete each sphinx plugin component manually in the mysql. plugin table.