If you don’t know what HandlerSocket is read Yoshinori Matsunobu’s blog post.

Because I’m starting with a minimum install of CentOS 6.2,  You’ll need to make sure you have a few utilities and development tools installed:

yum install git perl openssl-clients wget telnet lsof  
yum install gcc gcc-c++ libtool make openssl-devel perl-DBI perl-DBD-MySQL.x86_64

Installing the MySQL source

You’ll also need the MySQL source and a few more supporting packages.

wget http://vault.centos.org/6.2/os/Source/SPackages/mysql-5.1.52-1.el6_0.1.src.rpm 
yum install rpm-build gperf readline-devel ncurses-devel time perl-Time-HiRes 
rpm -i mysql-5.1.52-1.el6_0.1.src.rpm

Now you can make sure MySQL compiles.

cd /root/rpmbuild/SPECS 
rpmbuild -ba mysql.spec --define='runselftest 0'

If everything builds, you’ll have a fresh RPM in /root/rpmbuild/RPMS/x86_64.  If you’re installed MySQL is not the save version as the source you’ll need to install the compiled version.

cd /root/rpmbuild/RPMS/x86_64  
rpm -i mysql-5.1.52-1.el6.1.x86_64.rpm mysql-server-5.1.52-1.el6.1.x86_64.rpm mysql-libs-5.1.52-1.el6.1.x86_64.rpm

Building the HandlerSocket Plugin

With all the MySQL source in place,  we need the HandlerSocket source.

cd ~ mkdir Downloads   
cd Downloads  
git clone https://github.com/ahiguti/HandlerSocket-Plugin-for-MySQL.git  
cd HandlerSocket-Plugin-for-MySQL 
./autogen.sh  
./configure --with-mysql-source=/root/rpmbuild/BUILD/mysql-5.1.52 --with-mysql-bindir=/usr/bin 
make  
make install

To tell MySQL about the plug-in, add these instructions to your /etc/my.cnf file under the [mysqld] header.

plugin-load=handlersocket.so
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 concurrent
 # connections, make open_files_limit as large as
 # possible.

And, restart MySQL.

/etc/rc.d/mysqld restart

Run mysql and check for the plugin.

mysql> show plugins;
+---------------+--------+----------------+------------------+---------+
| Name          | Status | Type           | Library          | License |
+---------------+--------+----------------+------------------+---------+
| binlog        | ACTIVE | STORAGE ENGINE | NULL             | GPL     |
| partition     | ACTIVE | STORAGE ENGINE | NULL             | GPL     |
| CSV           | ACTIVE | STORAGE ENGINE | NULL             | GPL     |
| MEMORY        | ACTIVE | STORAGE ENGINE | NULL             | GPL     |
| InnoDB        | ACTIVE | STORAGE ENGINE | NULL             | GPL     |
| MyISAM        | ACTIVE | STORAGE ENGINE | NULL             | GPL     |
| MRG_MYISAM    | ACTIVE | STORAGE ENGINE | NULL             | GPL     |
| handlersocket | ACTIVE | DAEMON | handlersocket.so         | BSD     |
+---------------+--------+----------------+------------------+---------+

You should also see two new ports for MySQL in an lsof command.

lsof -i -P
COMMAND     PID    USER   FD   TYPE DEVICE SIZE NODE NAME
portmap    3297     rpc    3u  IPv4   9500       UDP *:111
portmap    3297     rpc    4u  IPv4   9501       TCP *:111 (LISTEN)
sshd       3641    root    3u  IPv6  11654       TCP *:22 (LISTEN)
heartbeat  3928  nobody    7u  IPv4  12165       UDP *:37988
heartbeat  3928  nobody    8u  IPv4  12166       UDP *:694
heartbeat  3929  nobody    7u  IPv4  12165       UDP *:37988
heartbeat  3929  nobody    8u  IPv4  12166       UDP *:694
sshd      18054    root    3u  IPv6 409453       TCP db2.grennan.com:22->192.168.2.11:59037 (ESTABLISHED) 
mysqld    19426   mysql   15u  IPv4 411158       TCP *:9998 (LISTEN)  
mysqld    19426   mysql   33u  IPv4 411175       TCP *:9999 (LISTEN)
mysqld    19426   mysql   36u  IPv4 411179       TCP *:3306 (LISTEN)

If ports 9998 and 9999 don’t show up.  Make sure SELinux is not running.  (sestatus)

The telnet test

Given you have a simple database like this.  Maybe in your test database…

CREATE TABLE user (
 user_id INT UNSIGNED PRIMARY KEY,
 user_name VARCHAR(50),
 user_email VARCHAR(255),
 created DATETIME
) ENGINE=InnoDB;

And some data

insert into test.user (user_id, user_name, user_email, created) values (1, "mark", "mark@grennan.com", now());

insert into test.user (user_id, user_name, user_email, created) values (2, "jim", "jim@grennan.com", now());

You can telnet to localhost and talk to HandlerSocket with a simple protocol.  (Type the part in bold and press enter.)

# telnet localhost 9998
Trying ::1…
telnet: connect to address ::1: Connection refused
Trying 127.0.0.1…
Connected to localhost.
Escape character is ‘^]’.
P 0 test user PRIMARY user_name,user_email,created
0 1
0 = 1 1
0 3 mark mark@grennan.com 2012-07-02 15:47:04

And there you have it.  Happiness is a new plug-in.

Tweet