MySQL Fanboy

Installing HandlerSocket (NoSQL plugin) into MiariaDB on CentOS 5.5.

December 21, 2010

The title says it all. If you don’t know what HandlerSocket is or why you would want to use it you need to reads Yoshinori Matsunobu’s blog post.

Lets get started.

 login as root

Make sure you have these packages installed:

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

If you haven’t already, install MariaDB and it’s source do that first and make sure it works. Look here http://askmonty.org/wiki/MariaDB:Download#Packages for the packages.

I’m using CentOS 5.5 x64 so I used:

 wget http://ftp.osuosl.org/pub/mariadb/mariadb-5.2.4/kvm-rpm-centos5-amd64/rpms/MariaDB-client-5.2.4-94.el5.x86_64.rpm 
 wget http://ftp.osuosl.org/pub/mariadb/mariadb-5.2.4/kvm-rpm-centos5-amd64/rpms/MariaDB-server-5.2.4-94.el5.x86_64.rpm
 wget http://ftp.osuosl.org/pub/mariadb/mariadb-5.2.4/kvm-rpm-centos5-amd64/rpms/MariaDB-shared-5.2.4-94.el5.x86_64.rpm
 wget http://ftp.osuosl.org/pub/mariadb/mariadb-5.cd /usr/src/redhat/SOURCES/2.4/kvm-rpm-centos5-amd64/rpms/MariaDB-test-5.2.4-94.el5.x86_64.rpm
 wget http://ftp.osuosl.org/pub/mariadb/mariadb-5.2.4/kvm-rpm-centos5-amd64/srpms/MariaDB-5.2.4-94.el5.src.rpm

My minimum install didn’t create the SOURCE directory so I have to.

 mkdir /usr/src/redhat/
 mkdir /usr/src/redhat/SOURCES

I used RPM -i to install each of these.

The source package drops a compressed source file in the SOURCES directory. We need all the files.

 cd /usr/src/redhat/SOURCES
 unzip mariadb-5.2.4.tar.gz

Now that we have the envelopment set we need the HandlerSocket source. I didn’t find a way to just wget the file so I used links to download the tar.gz file.

 cd ~
 mkdir Downloads
 cd Downloads
 git clone https://github.com/ahiguti/HandlerSocket-Plugin-for-MySQL.git
 cd HandlerSocket-Plugin-for-MySQL

The build script has a little bug and thinks everyone is running MySQL. Quick fix by editing the configure.ac file.

MYSQL_SOURCE_VERSION=`cat $ac_mysql_source_dir/configure.in | \
grep "\[MySQL Server\]" | sed -e "s|.*\([0-9]\+\.[0-9]\+\.[0-9]\+[0-9a-zA-Z\_\-]*\).*|\1|"`

MYSQL_SOURCE_VERSION=`cat $ac_mysql_source_dir/configure.in | \
grep "\[MariaDB Server\]" | sed -e "s|.*\([0-9]\+\.[0-9]\+\.[0-9]\+[0-9a-zA-Z\_\-]*\).*|\1|"`
We are ready to build. We need to tell config the directors for our mariadb source and where the binaries go.
 ./autogen.sh
 ./configure --with-mysql-source=/usr/src/redhat/SOURCES/mariadb-5.1.51 --with-mysql-bindir=/usr/bin
 make
 make install
Now we need tell MariaDB about the new 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.
Restart mysql – /etc/rc.d/mysql restart
Run mysql and check for the plugin.
MariaDB [(none)]> show plugins;
+--------------------------------+--------+--------------------+------------------+---------+
| Name                           | Status | Type               | Library          | License |
+--------------------------------+--------+--------------------+------------------+---------+
| binlog                         | ACTIVE | STORAGE ENGINE     | NULL             | GPL     |
| MyISAM                         | ACTIVE | STORAGE ENGINE     | NULL             | GPL     |
| partition                      | ACTIVE | STORAGE ENGINE     | NULL             | GPL     |
| ARCHIVE                        | ACTIVE | STORAGE ENGINE     | NULL             | GPL     |
| BLACKHOLE                      | ACTIVE | STORAGE ENGINE     | NULL             | GPL     |
| CSV                            | ACTIVE | STORAGE ENGINE     | NULL             | GPL     |
| FEDERATED                      | ACTIVE | STORAGE ENGINE     | NULL             | GPL     |
| MEMORY                         | ACTIVE | STORAGE ENGINE     | NULL             | GPL     |
| MARIA                          | ACTIVE | STORAGE ENGINE     | NULL             | GPL     |
| MRG_MYISAM                     | ACTIVE | STORAGE ENGINE     | NULL             | GPL     |
| PBXT                           | ACTIVE | STORAGE ENGINE     | NULL             | GPL     |
| PBXT_STATISTICS                | ACTIVE | INFORMATION SCHEMA | NULL             | GPL     |
| InnoDB                         | ACTIVE | STORAGE ENGINE     | NULL             | GPL     |
| INNODB_RSEG                    | ACTIVE | INFORMATION SCHEMA | NULL             | GPL     |
| INNODB_BUFFER_POOL_PAGES       | ACTIVE | INFORMATION SCHEMA | NULL             | GPL     |
| INNODB_BUFFER_POOL_PAGES_INDEX | ACTIVE | INFORMATION SCHEMA | NULL             | GPL     |
| INNODB_BUFFER_POOL_PAGES_BLOB  | ACTIVE | INFORMATION SCHEMA | NULL             | GPL     |
| INNODB_TRX                     | ACTIVE | INFORMATION SCHEMA | NULL             | GPL     |
| INNODB_LOCKS                   | ACTIVE | INFORMATION SCHEMA | NULL             | GPL     |
| INNODB_LOCK_WAITS              | ACTIVE | INFORMATION SCHEMA | NULL             | GPL     |
| INNODB_CMP                     | ACTIVE | INFORMATION SCHEMA | NULL             | GPL     |
| INNODB_CMP_RESET               | ACTIVE | INFORMATION SCHEMA | NULL             | GPL     |
| INNODB_CMPMEM                  | ACTIVE | INFORMATION SCHEMA | NULL             | GPL     |
| INNODB_CMPMEM_RESET            | ACTIVE | INFORMATION SCHEMA | NULL             | GPL     |
| INNODB_TABLE_STATS             | ACTIVE | INFORMATION SCHEMA | NULL             | GPL     |
| INNODB_INDEX_STATS             | ACTIVE | INFORMATION SCHEMA | NULL             | GPL     |
| XTRADB_ADMIN_COMMAND           | ACTIVE | INFORMATION SCHEMA | NULL             | GPL     |
| INNODB_SYS_TABLES              | ACTIVE | INFORMATION SCHEMA | NULL             | GPL     |
| INNODB_SYS_INDEXES             | ACTIVE | INFORMATION SCHEMA | NULL             | GPL     |
| INNODB_SYS_STATS               | ACTIVE | INFORMATION SCHEMA | NULL             | GPL     |
| XTRADB_ENHANCEMENTS            | ACTIVE | INFORMATION SCHEMA | NULL             | GPL     |
| handlersocket                  | ACTIVE | DAEMON             | handlersocket.so | BSD     |
+--------------------------------+--------+--------------------+------------------+---------+
32 rows in set (0.00 sec)
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)
http://openpear.org/repository/Net_HandlerSocket/trunk/Net/HandlerSocket.php
Given you have a simple database like this.  Maybe in your test databasa…
CREATE TABLE user (
 user_id INT UNSIGNED PRIMARY KEY,
 user_name VARCHAR(50),
 user_email VARCHAR(255),
 created DATETIME
) ENGINE=InnoDB;
You can telnet to localhost and talk to HandlerSocket with a simple protocol.
$ telnet localhost 9998
Connected to localhost.
Escape character is '^]'.
P       0       test    user    PRIMARY user_name,user_email,created
0       1
0       =       1       1
0       3       First Last        first.last@here.com    2010-12-21 00:00:00
And there you have it.  Happiness is a new plug-in.
Up next, examples on how to use the HandlerSocket from different languages.

Tweet