MariaDB Sphinx

MariaDB Sphinx

环境:

CentOS7.1 x64

mariadb-10.1.13

sphinx-2.2.10

安装SphinxSE

(jlive)[isfdb]>INSTALL SONAME 'ha_sphinx';

Query OK, 0 rows affected (0.01 sec)

(jlive)[isfdb]>SHOW storage engines;

+--------------------+---------+--------------------------------------------------------------------------------------------------+--------------+------+------------+

+--------------------+---------+--------------------------------------------------------------------------------------------------+--------------+------+------------+

| MRG_MyISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |

| CSV | YES | CSV storage engine | NO | NO | NO |

| Aria | YES | Crash-safe tables with MyISAM heritage | NO | NO | NO |

| MyISAM | YES | MyISAM storage engine | NO | NO | NO |

| SPHINX | YES | Sphinx storage engine 2.2.6-release | NO | NO | NO |

| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |

| InnoDB | YES | Percona-XtraDB, Supports transactions, row-level locking, foreign keys and encryption for tables | YES | YES | YES |

| SEQUENCE | YES | Generated tables filled with sequential values | YES | NO | YES |

| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |

| TokuDB | DEFAULT | Percona TokuDB Storage Engine with Fractal Tree(tm) Technology | YES | YES | YES |

| CONNECT | YES | Management of External Data (SQL/MED), including many file formats | NO | NO | NO |

+--------------------+---------+--------------------------------------------------------------------------------------------------+--------------+------+------------+

11 rows in set (0.15 sec)

(jlive)[isfdb]>SHOW STATUS LIKE 'sphinx_%';

+--------------------+-------+

| Variable_name | Value |

+--------------------+-------+

| Sphinx_error | |

| Sphinx_time | |

| Sphinx_total | |

| Sphinx_total_found | |

| Sphinx_word_count | |

| Sphinx_words | |

+--------------------+-------+

6 rows in set (0.00 sec)

(jlive)[isfdb]>SHOW ENGINE SPHINX STATUS;

Empty set (0.15 sec)

安装Sphinx daemon

http://sphinxsearch.com/downloads/release/

wget http://sphinxsearch.com/files/sphinx-2.2.10-1.rhel7.x86_64.rpm

rpm -ivh sphinx-2.2.10-1.rhel7.x86_64.rpm

service searchd start

root@jlive:lnmp#rpm -ivh sphinx-2.2.10-1.rhel7.x86_64.rpm

准备中... ################################# [100%]

正在升级/安装...

1:sphinx-2.2.10-1.rhel7 ################################# [100%]

[/usr/lib/tmpfiles.d/searchd.conf:1] Unknown user 'sphinx'.

Sphinx installed!

Now create a full-text index, start the search daemon, and you're all set.

To manage indexes:

editor /etc/sphinx/sphinx.conf

To rebuild all disk indexes:

sudo -u sphinx indexer --all --rotate

To start/stop search daemon:

service searchd start/stop

To query search daemon using MySQL client:

mysql -h 0 -P 9306

mysql> SELECT * FROM test1 WHERE MATCH('test');

See the manual at /usr/share/doc/sphinx-2.2.10 for details.

For commercial support please contact Sphinx Technologies Inc at

http://sphinxsearch.com/contacts.html

root@jlive:lnmp#service searchd start

Starting searchd (via systemctl): [ OK ]

配置Sphinx

1.创建sphinx管理用户

CREATE USER 'sphinx'@localhost IDENTIFIED BY 'sphinxsecretpassword';

GRANT SELECT on test.* to 'sphinx'@localhost;

2.修改配置文件

cp /etc/sphinx/sphinx.conf{,.default}

source src1

{

type = mysql

sql_host = localhost

sql_user = sphinx

sql_pass = sphinxsecretpassword

sql_db = test

sql_port = 3306 # optional, default is 3306

sql_sock = /tmp/mysql.sock

sql_query =

SELECT id, UNIX_TIMESTAMP(date_added) AS date_added, title, content

FROM documents

sql_attr_timestamp = date_added

}

... ...

root@jlive:lnmp#netstat -tunlp|grep searchd

tcp 0 0 0.0.0.0:9306 0.0.0.0:* LISTEN 12722/searchd

tcp 0 0 0.0.0.0:9312 0.0.0.0:* LISTEN 12722/searchd

提示: mariadb和sphinx可以在不同的主机上

使用searching

1.先创建示例表

USE test;

CREATE TABLE documents (

id SERIAL PRIMARY KEY,

date_added TIMESTAMP,

title VARCHAR(256),

content TEXT

);

INSERT INTO documents VALUES

(1,UNIX_TIMESTAMP(date),'bash','Bash is an sh-compatible command language interpreter that executes commands read from the standard input or from a file. Bash also incorporates useful features from the Korn and C shells (ksh and csh).'),

(2,UNIX_TIMESTAMP(date),'sed',"Sed is a stream editor. A stream editor is used to perform basic text transformations on an input stream (a file or input from a pipeline). While in some ways similar to an edi‐tor which permits scripted edits (such as ed), sed works by making only one pass over the input(s), and is consequently more efficient. But it is sed's ability to filter text in a pipeline which particularly distinguishes it from other types of editors."),

(2,UNIX_TIMESTAMP(date),'awk',"Gawk is the GNU Project's implementation of the AWK programming language. It conforms to the definition of the language in the POSIX 1003.1 Standard. This version in turn is based on the description in The AWK Programming Language, by Aho, Kernighan, and Wein‐ berger. Gawk provides the additional features found in the current version of UNIX awk and a number of GNU-specific extensions.")

2.生成sphinx索引

root@jlive:~#/etc/init.d/searchd restart

Restarting searchd (via systemctl): [ 确定 ]

root@jlive:~#indexer --rotate --all

Sphinx 2.2.10-id64-release (2c212e0)

using config file '/etc/sphinx/sphinx.conf'...

indexing index 'test1'...

collected 0 docs, 0.0 MB

total 0 docs, 0 bytes

total 0.005 sec, 0 bytes/sec, 0.00 docs/sec

skipping non-plain index 'testrt'...

total 1 reads, 0.000 sec, 0.0 kb/call avg, 0.0 msec/call avg

total 7 writes, 0.000 sec, 0.0 kb/call avg, 0.0 msec/call avg

rotating indices: successfully sent SIGHUP to searchd (pid=14641).

root@jlive:api#ls /var/lib/sphinx/

binlog.001 test1.spa test1.sph test1.spl test1.sps testrt.lock

binlog.lock test1.spd test1.spi test1.spm test1.tmp.tmps testrt.meta

binlog.meta test1.spe test1.spk test1.spp testrt.kill testrt.ram

3.sphinx本地查询

search -q nosql

提示:sphinx-2.2.10没有search这个命令，有待进一步确认

4.SphinxSE接口查询

USE test;

CREATE TABLE documents_search (

id BIGINT UNSIGNED NOT NULL,

weight INT NOT NULL,

query VARCHAR(3072) NOT NULL,

INDEX(query)

) ENGINE=SPHINX;

SELECT * FROM documents_search WHERE query='sphinx';

SELECT * FROM documents_search WHERE query='nosql';

停掉mariadb后直接连sphinx

service mysql stop

jlive@MacBook-Pro:~ $mysql -ujlive -p -h 192.168.130.254 -P 9306

Enter password:

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or g.

Your MySQL connection id is 1

Server version: 2.2.10-id64-release (2c212e0)

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

(jlive)[isfdb]>SELECT * FROM test1 WHERE MATCH('bash');

Empty set (0.00 sec)

Sphinx API

http://sphinxsearch.com/docs/current.html#sphinxql-reference

http://sphinxsearch.com/blog/2013/07/23/from-api-to- sphinxql-and-back-again/

root@jlive:~#ls /usr/share/sphinx/api/

java/ sphinxapi.php test2.php test.php

lgpl-3.0.txt sphinxapi.py test2.py test.py

libsphinxclient/ sphinxapi.pyc test2.pyc test.pyc

ruby/ sphinxapi.pyo test2.pyo test.pyo