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;

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

| Engine             | Support | Comment                                                                                          | Transactions | XA   | Savepoints |

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

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

Copyright (c) 2001-2015, Andrew Aksyonoff

Copyright (c) 2008-2015, Sphinx Technologies Inc (http://sphinxsearch.com)

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)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

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