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 程序文件：