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
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
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();
}
}
