MySQL 使用 HandlerSocket 實現 NOSQL 功能

一、安裝 
1、下載 mysql-5.1.54.tar.gz 解壓至 /root/mysql/mysql-5.1.54 目錄 
2、下載 ahiguti-HandlerSocket-Plugin-for-MySQL-1.0.6-94-g98b14c3.tar.gz 解壓至 /root/mysql/ahiguti-HandlerSocket-Plugin-for-MySQL-98b14c3 目錄 
3、以動態編譯的形式編譯安裝 mysql 到 /usr/local/mysql5.1 
4、編譯 HandlerSocket 外掛程式，在 /root/mysql/ahiguti-HandlerSocket-Plugin-for-MySQL-98b14c3 目錄 
# ./autogen.sh 
# ./configure --with-mysql-source=/root/mysql/mysql-5.1.54 --with-mysql-bindir=/usr/local/mysql5.1/bin --with-mysql-plugindir=/usr/local/mysql5.1/lib/mysql/plugin 
with-mysql-source 表示MySQL原始碼目錄，with-mysql-bindir 表示MySQL二進位可執行檔目錄（也就是 mysql_config 所在目錄），with-mysql-plugindir 表示MySQL外掛程式目錄 
如果不清楚這個目錄在哪，可以按如下方法查詢： 
mysql> SHOW VARIABLES LIKE 'plugin%'; 
+---------------+-----------------------+ 
| Variable_name | Value                 | 
+---------------+-----------------------+ 
| plugin_dir    | /usr/lib/mysql/plugin | 
+---------------+-----------------------+ 
編譯和安裝 
# make && make install 

二、配置 MySQL 
# vi /etc/my.cnf 
[mysqld] 
loose_handlersocket_port = 9998 
# the port number to bind to (for read requests) 
loose_handlersocket_port_wr = 9999 
# the port number to bind to (for write requests) 
loose_handlersocket_threads = 16 
# the number of worker threads (for read requests) 
loose_handlersocket_threads_wr = 1 
# the number of worker threads (for write requests) 
open_files_limit = 65535 
# to allow handlersocket accept many concurren connections, make open_files_limit as large as possible. 

啟用HandlerSocket外掛程式，在用戶端命令執行 
mysql> install plugin handlersocket soname 'handlersocket.so'; 
查看 handlersocket 進程 
mysql>  show processlist 
+----+-------------+-----------------+---------------+---------+------+-------------------------------------------+------------------+ 
| Id | User        | Host            | db            | Command | Time | State                                     | Info             | 
+----+-------------+-----------------+---------------+---------+------+-------------------------------------------+------------------+ 
|  1 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             | 
|  2 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             | 
|  3 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             | 
|  4 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             | 
|  5 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             | 
|  6 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             | 
|  7 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             | 
|  8 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             | 
|  9 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             | 
| 10 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             | 
| 11 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             | 
| 12 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             | 
| 13 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             | 
| 14 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             | 
| 15 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             | 
| 16 | system user | connecting host | NULL          | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL             | 
| 17 | system user | connecting host | handlersocket | Connect | NULL | handlersocket: mode=wr, 0 conns, 0 active | NULL             | 

查看系統進程佔用的連接埠 
# lsof -i :9998 
COMMAND  PID  USER   FD   TYPE DEVICE SIZE NODE NAME 
mysqld  2731 mysql   11u  IPv4 571386       TCP *:9998 (LISTEN) 
# lsof -i :9999 
COMMAND  PID  USER   FD   TYPE DEVICE SIZE NODE NAME 
mysqld  2731 mysql   29u  IPv4 571403       TCP *:9999 (LISTEN) 

三、測試使用 

在資料庫 test 建立測試表 
CREATE TABLE `user` (  
`user_id` INT(10) UNSIGNED NOT NULL,  
`user_name` VARCHAR(50) DEFAULT NULL,  
`user_email` VARCHAR(255) DEFAULT NULL,  
`created` DATETIME DEFAULT NULL,  
PRIMARY KEY (`user_id`),  
KEY `INDEX_01` (`user_name`)  
) ENGINE=INNODB  

插入測試資料 
INSERT INTO USER VALUES(1, "John", "john@test.com", CURRENT_TIMESTAMP);  
INSERT INTO USER VALUES(2, "Kevin", "Kevin@test.com", CURRENT_TIMESTAMP);  
INSERT INTO USER VALUES(3, "Dino", "Dino@test.com", CURRENT_TIMESTAMP);  

編譯 Perl 用戶端，在 /root/mysql/ahiguti-HandlerSocket-Plugin-for-MySQL-98b14c3 目錄執行 
# ./autogen.sh 
# ./configure --disable-handlersocket-server 
# make && make install 
# cd perl-Net-HandlerSocket 
# perl Makefile.PL 
# make && make install 

perl 程式檔案： 
Php代碼  
#!/usr/bin/perl     
     
use strict;     
use warnings;     
use Net::HandlerSocket;     
     
#1. establishing a connection     
my $args = { host => 'localhost', port => 9998 };     
my $hs = new Net::HandlerSocket($args);     
     
#2. initializing an index so that we can use in main logics.     
# MySQL tables will be opened here (if not opened)     
my $res = $hs->open_index(0, 'test', 'user', 'INDEX_01', 'user_name,user_email,created');     
die $hs->get_error() if $res != 0;     
     
#3. main logic     
#fetching rows by id     
#execute_single (index id, cond, cond value, max rows, offset)     
$res = $hs->execute_single(0, '=', [ 'kevin' ], 1, 0);     
die $hs->get_error() if $res->[0] != 0;     
shift(@$res);     
for (my $row = 0; $row < 1; ++$row) {     
  my $user_name= $res->[$row + 0];     
  my $user_email= $res->[$row + 1];     
  my $created= $res->[$row + 2];     
  print "$user_name\t$user_email\t$created\n";     
}     
     
#4. closing the connection     
$hs->close();    

#!/usr/bin/perl  
  
use strict;  
use warnings;  
use Net::HandlerSocket;  
  
#1. establishing a connection  
my $args = { host => 'localhost', port => 9998 };  
my $hs = new Net::HandlerSocket($args);  
  
#2. initializing an index so that we can use in main logics.  
# MySQL tables will be opened here (if not opened)  
my $res = $hs->open_index(0, 'test', 'user', 'INDEX_01', 'user_name,user_email,created');  
die $hs->get_error() if $res != 0;  
  
#3. main logic  
#fetching rows by id  
#execute_single (index id, cond, cond value, max rows, offset)  
$res = $hs->execute_single(0, '=', [ 'kevin' ], 1, 0);  
die $hs->get_error() if $res->[0] != 0;  
shift(@$res);  
for (my $row = 0; $row < 1; ++$row) {  
  my $user_name= $res->[$row + 0];  
  my $user_email= $res->[$row + 1];  
  my $created= $res->[$row + 2];  
  print "$user_name\t$user_email\t$created\n";  
}  
  
#4. closing the connection  
$hs->close();  

下載 hs4j-0.1 軟體包。 
Java 程式檔案： 
Java代碼  
import java.sql.ResultSet;   
  
import com.google.code.hs4j.HSClient;   
import com.google.code.hs4j.HSClientBuilder;   
import com.google.code.hs4j.IndexSession;   
import com.google.code.hs4j.impl.HSClientBuilderImpl;   
  
public class TestNoSQL {   
    public static void main(String[] args) throws Exception {   
        HSClientBuilder hscb = new HSClientBuilderImpl();   
        hscb.setServerAddress("10.10.10.2", 9999);   
        HSClient hsc = hscb.build();   
        IndexSession is = hsc.openIndexSession(1,"test", "user", "INDEX_01", new String[]{"user_name","user_email","created"});   
        // 插入   
        is.insert(new String[]{"ezerg", "ezerg@126.com", "2011-10-12 13:04:33"});   
        final String[] keys = { "ezerg"};   
        // 查詢   
        ResultSet rs = is.find(keys);   
        while(rs.next()) {   
            System.out.println("name="+rs.getString(1));   
            System.out.println("email="+rs.getString(2));   
        }   
        // 刪除   
        // is.delete(keys);   
        // 更新   
        // is.update(keys, new String[] { "ezerg", "ezerg@163.com", "2011-10-12 13:04:33" }, FindOperator.EQ);    
        // 關閉串連   
        hsc.shutdown();   
    }   
}