MySQLはHandlerSocketを使用してNOSQL機能を実現
一、据付
1、mysql-5.154.tar.gzをダウンロードして/root/mysql/mysql-5.154ディレクトリに解凍する
2、ahighuti-HandlerSocket-Plugin-for-MySQL-1.0.6-94-g 98 b 14 c 3.tar.gzをダウンロード/root/mysql/ahighuti-HandlerSocket-Plugin-for-MySQL-98 b 14 c 3ディレクトリに解凍
3、動的コンパイル形式でmysqlを/usr/local/mysql 5.1にコンパイルする
4.HandlerSocketプラグインをコンパイル、/root/mysql/ahiguuti-HandlerSocket-Plugin-for-MySQL-98 b 14 c 3ディレクトリ
# ./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", "[email protected]", CURRENT_TIMESTAMP);
INSERT INTO USER VALUES(2, "Kevin", "[email protected]", CURRENT_TIMESTAMP);
INSERT INTO USER VALUES(3, "Dino", "[email protected]", CURRENT_TIMESTAMP);
Perlクライアントのコンパイル、/root/mysql/ahigui-HandlerSocket-Plugin-for-MySQL-98 b 14 c 3ディレクトリで実行
# ./autogen.sh
# ./configure --disable-handlersocket-server
# make && make install
# cd perl-Net-HandlerSocket
# perl Makefile.PL
# make && make install
perlプログラムファイル:
hs 4 j-0.1パッケージをダウンロードします.
Javaプログラムファイル:
1、mysql-5.154.tar.gzをダウンロードして/root/mysql/mysql-5.154ディレクトリに解凍する
2、ahighuti-HandlerSocket-Plugin-for-MySQL-1.0.6-94-g 98 b 14 c 3.tar.gzをダウンロード/root/mysql/ahighuti-HandlerSocket-Plugin-for-MySQL-98 b 14 c 3ディレクトリに解凍
3、動的コンパイル形式でmysqlを/usr/local/mysql 5.1にコンパイルする
4.HandlerSocketプラグインをコンパイル、/root/mysql/ahiguuti-HandlerSocket-Plugin-for-MySQL-98 b 14 c 3ディレクトリ
# ./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", "[email protected]", CURRENT_TIMESTAMP);
INSERT INTO USER VALUES(2, "Kevin", "[email protected]", CURRENT_TIMESTAMP);
INSERT INTO USER VALUES(3, "Dino", "[email protected]", CURRENT_TIMESTAMP);
Perlクライアントのコンパイル、/root/mysql/ahigui-HandlerSocket-Plugin-for-MySQL-98 b 14 c 3ディレクトリで実行
# ./autogen.sh
# ./configure --disable-handlersocket-server
# make && make install
# cd perl-Net-HandlerSocket
# perl Makefile.PL
# make && make install
perlプログラムファイル:
#!/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
";
}
#4. closing the connection
$hs->close();
hs 4 j-0.1パッケージをダウンロードします.
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", "[email protected]", "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", "[email protected]", "2011-10-12 13:04:33" }, FindOperator.EQ);
//
hsc.shutdown();
}
}