MySQL Handlersocket Plug-in installation configuration detailed

System Information Conventions:

System version: CentOS 6.3 X86
PHP installation directory:/usr/local/webserver/php5318
MySQL installation directory:/usr/local/webserver/mysql5520
Handlersocket Installation path:/usr/local/webserver/handlersocket
Installation Configuration Handlersocket

Prior to installation, it is recommended that you install the relevant support and component packages as needed:

Yum install gcc gcc-c++ libtool make Openssl-devel perl-dbi (if 64bit OS attention perl-dbd-mysql)
Yum install rpm-build gperf readline-devel ncurses-devel time Perl-time-hires

installation

If you use Percona server version of MySQL is simple, because it has built-in handlersocket support, but in view of its built-in version is not new, there are some already fixed bugs, so it is best to use source code compilation. Handlersocket is based on the MySQL database, so before installing handersocket, you need to deploy the MySQL service in the usual way, and you need to pay attention to the source of MySQL when Handlersocket. therefore also need MySQL source code compilation method installs.

[Root@iredmail opt]# git clone https://github.com/ahiguti/HandlerSocket-Plugin-for-MySQL.git
[Root@iredmail opt]# CD Handlersocket-plugin-for-mysql
[Root@iredmail handlersocket-plugin-for-mysql]#./autogen.sh
[Root@iredmail handlersocket-plugin-for-mysql]#./configure--prefix=/usr/local/webserver/handlersocket-- with-mysql-source=/opt/mysql-5.5.20--with-mysql-bindir=/usr/local/webserver/mysql5520/bin-- With-mysql-plugindir=/usr/local/webserver/mysql5520/lib/mysql/plugin
Tips:

--with-mysql-source Specify MySQL source directory
--with-mysql-bindir represents the MySQL binary executable directory
--with-mysql-plugindir Specify the storage path for the MySQL plug-in, if you do not know where the directory is, you can query the following methods:

Mysql> Show variables like ' plugin% ';
+---------------+-------------------------------------------+
| variable_name | Value |
+---------------+-------------------------------------------+
| Plugin_dir | /usr/local/webserver/mysql5520/lib/plugin |
+---------------+-------------------------------------------+
1 row in Set (0.00 sec)

[Root@iredmail handlersocket-plugin-for-mysql]# make

Common errors:

Libtool:link:only Absolute run-paths are allowed
MAKE[2]: * * * [handlersocket.la] Error 1
MAKE[2]: Leaving directory '/opt/handlersocket-plugin-for-mysql/handlersocket '
MAKE[1]: * * * [all-recursive] Error 1
MAKE[1]: Leaving directory '/opt/handlersocket-plugin-for-mysql '
Make: * * * [ALL] Error 2

Workaround:

[Root@iredmail handlersocket-plugin-for-mysql]# VI handlersocket/makefile
Line 301:
$ (Handlersocket_la_link)-rpath $ (pkgplugindir) $ (handlersocket_la_objects) $ (handlersocket_la_libadd) $ (LIBS)
-->
$ (Handlersocket_la_link)-rpath/opt/handlersocket-plugin-for-mysql/handlersocket $ (Handlersocket_la_OBJECTS) $ ( Handlersocket_la_libadd) $ (LIBS)
[Root@iredmail Handlersocket-plugin-for-mysql] #make Install
After completion, the Mysql-plugindir directory should have handlersocket related documents

Configure MySQL

To modify the MY.CNF configuration file:

[Root@iredmail handlersocket-plugin-for-mysql]# vi/etc/my.cnf
[Mysqld]
Plugin-load=handlersocket.so (Plugin-load may be slightly unworthy)
Loose_handlersocket_port = 9998 # Specifies the read request port number
# The port number to bind to (for read requests)
LOOSE_HANDLERSOCKET_PORT_WR = 9999 # Specifies the write request port number
# The port number to bind to (for write requests)
Loose_handlersocket_threads = 16 # Specifies the number of read threads
# The number of worker threads (for read requests)
LOOSE_HANDLERSOCKET_THREADS_WR = 1 # Specifies the number of write threads
# 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.
Tips:innodb's innodb_buffer_pool_size, or MyISAM's key_buffy_size, and so on, are related to caching index options as large as possible, so that the potential of the handlersocket can be played.

login to MySQL and activate the Handlersocket plugin :

[Root@iredmail handlersocket-plugin-for-mysql]# Mysql-uroot-p
mysql> Install plugin handlersocket soname ' handlersocket.so ';
ERROR 1126 (HY000): Can ' t open shared library '/usr/local/webserver/mysql5520/lib/plugin/handlersocket.so ' (errno:2 Cannot open shared object File:no such file or directory)
Note: This tip does not find the handlersocket.so extension file, please see if the extension file exists.

mysql> Install plugin handlersocket soname ' handlersocket.so ';
Query OK, 0 rows Affected (0.00 sec)

Mysql> quit;
At this point, the Handlersocket plug-in installation is complete.

Restart the MySQL service

[Root@iredmail handlersocket-plugin-for-mysql]# Service mysqld Restart
Handlersocket State test

QQ screenshot 20130628083800

QQ screenshot 20130628083810

You can also confirm that the installation was successful by querying whether the port that you just configured is already occupied by MySQL:

[Root@iredmail handlersocket-plugin-for-mysql]# lsof-i-P | grep mysqld
Mysqld 26871 MySQL 11u IPv4 72467 0t0 TCP *:9998 (LISTEN)
Mysqld 26871 mysql 29u IPv4 72469 0t0 TCP *:9999 (LISTEN)
Mysqld 26871 mysql 31u IPv4 72474 0t0 TCP *:3306 (LISTEN)
Tips:if ports 9998 and 9999 don ' t show up. Make sure SELinux are not running.

Installation configuration Php-handlersocket Extension Module

Installing Php-handlersocket Extensions

[Root@iredmail opt]# wget http://php-handlersocket.googlecode.com/files/php-handlersocket-0.3.1.tar.gz
[Root@iredmail opt]# TAR-ZXVF php-handlersocket-0.3.1.tar.gz
[Root@iredmail opt]# CD handlersocket/
[Root@iredmail handlersocket]#/usr/local/webserver/php5318/bin/phpize
[Root@iredmail handlersocket]#./configure--with-php-config=/usr/local/webserver/php5318/bin/php-config
./configure can be added parameters:

Implemented Configure Options source file
Hsclient None (default) handlersocket.cc
Native--disable-handlersocket-hsclient handlersocet.c
Tips:if you a error:
Configure:error:Can ' t find hsclient headers,please install libhsclient first,or./ Configure--disable-handlersocket-hsclient--with-php-config=/usr/local/webserver/php5318/bin/php-config Use Native type.

[Root@iredmail Handlersocket] #make && make install
A successful install would have created handlersocket.so and put it into the PHP extensions directory. You'll need to and adjust php.ini and add a extension=handlersocket.so line before your can use the extension.

[Root@iredmail handlersocket]# Vi/usr/local/webserver/php5318/etc/php.ini
Extension=handlersocket.so
Now that the PHP extension installation is complete, let's ask the Php.info page and we can see that the handlersocket extension has been successfully loaded

Php-handlersocket Use Example

/*
* String $host: MySQL IP;
* String $port: The Handlersocket plug-in's listening port, which has two ports to choose from: one for reading, one for writing
*/
$HS = new Handlersocket ($host, $port);
Open a data table:
/*
* Int $index: This number corresponds to the handle in the file operation, and all other methods of Handlersocket are based on this number to manipulate the table opened by this openindex,
* String $dbname: library name
* String $table: Table name
* String $key: A table's primary key (Handlersocket::P rimary) or "index name" as the Search key field, which means that the table must have a primary key or index
* Personal Understanding: The key field to be used as the where condition, so you can think that Handlersocket has only one where condition
* String $column: The fields of the table opened by ' Column1,column2 ' (separated by commas), which means that the other fields of the $table table are not manipulated
*/
$hs->openindex ($index, $dbname, $table, $key, $column);
Inquire:
/*
* Int $index: $index for Openindex ()
* String $operation: the operator used in the $key field specified in the Openindex method, currently supports ' = ', ' >= ', ' < = ', ' > ', and ' < '; can be understood as where condition
* Array $value
* Int $number (default is 1): Gets the maximum number of bars for the result; the second parameter of limit in SQL
* Int $skip (default is 0): Skip past several; equivalent to the first parameter of limit in SQL
*/
$retval = $hs->executesingle ($index, $operation, $value, $number, $skip);
Insert (note: The openindex here is to use $PORT_WR, read-write port):
/*
* Int $index: $index for Openindex ()
* Array $arr: The number of number elements is the same as Openindex $column
*/
$retval = $hs->executeinsert ($index, $arr);
Delete (Note: The openindex here is to use $PORT_WR, read-write port):
/*
* Int $index: $index for Openindex ()
* String $operation: the operator used in the $key field specified in the Openindex method, currently supports ' = ', ' >= ', ' < = ', ' > ', and ' < '; can be understood as where condition
* Array $value
* Int $number (default is 1): Gets the maximum number of bars for the result; the second parameter of limit in SQL
* Int $skip (default is 0): Skip past several; equivalent to the first parameter of limit in SQL
*/
$retval = $hs->executedelete ($index, $operation, $value, $number, $skip);
Update (note: The openindex here is to use $PORT_WR, read-write port):
/*
* Int $index: $index for Openindex ()
* String $operation: the operator used in the $key field specified in the Openindex method, currently supports ' = ', ' >= ', ' < = ', ' > ', and ' < '; can be understood as where condition
* Array $value
* Int $number (default is 1): Gets the maximum number of bars for the result; the second parameter of limit in SQL
* Int $skip (default is 0): Skip past several; equivalent to the first parameter of limit in SQL
*/
$retval = $hs->executeupdate ($index, $operation, $value, $number, $skip);
Example:

Test library hstestdb, test table hstesttbl:

CREATE TABLE ' Hstesttbl ' (
' id ' int (one) not NULL auto_increment,
' K ' char (6) DEFAULT NULL,
' V ' char (6) DEFAULT NULL,
PRIMARY KEY (' id '),
KEY ' Idx_hstesttbl_k ' (' K ')
) Engine=innodb auto_increment=7 DEFAULT Charset=utf8;
PHP Test Code:

$host = ' localhost ';
$port = 9998;
$port _WR = 9999;
$dbname = ' Hstestdb ';
$table = ' hstesttbl ';

Get
$HS = new Handlersocket ($host, $port);
if (!) ( $hs->openindex (1, $dbname, $table, Handlersocket::P rimary, ' k,v ')) {
echo $hs->geterror (), Php_eol;
Die ();
}

$retval = $hs->executesingle (1, ' = ', Array (' K1 '), 1, 0);
Var_dump ($retval);

$retval = $hs->executemulti (
Array
Array (1, ' = ', Array (' K1 '), 1, 0),
Array (1, ' = ', Array (' K2 '), 1, 0)
)
);
Var_dump ($retval);
Unset ($HS);

UPDATE
$HS = new Handlersocket ($host, $port _WR);
if (!) ( $hs->openindex (2, $dbname, $table, ', ' V ')) {
echo $hs->geterror (), Php_eol;
Die ();
}

if ($hs->executeupdate (2, ' = ', Array (' K1 '), Array (' V1 '), 1, 0) = = False) {
echo $hs->geterror (), Php_eol;
Die ();
}

Unset ($HS);

INSERT
$HS = new Handlersocket ($host, $port _WR);
if (!) ( $hs->openindex (3, $dbname, $table, ', ' k,v ')) {
echo $hs->geterror (), Php_eol;
Die ();
}

if ($hs->executeinsert (3, Array (' K2 ', ' v2 ')) = = False) {
echo $hs->geterror (), Php_eol;
}
if ($hs->executeinsert (3, Array (' K3 ', ' v3 ')) = = False) {
Echo ' A ', $hs->geterror (), Php_eol;
}
if ($hs->executeinsert (3, Array (' K4 ', ' v4 ')) = = False) {
Echo ' B ', $hs->geterror (), Php_eol;
}

Unset ($HS);

DELETE
$HS = new Handlersocket ($host, $port _WR);
if (!) ( $hs->openindex (4, $dbname, $table, ', ')) {
echo $hs->geterror (), Php_eol;
Die ();
}

if ($hs->executedelete (4, ' = ', Array (' K2 ')) = = False) {
echo $hs->geterror (), Php_eol;
Die ();
}
?>

Tips: Theoretically handlersocket supports various engines such as MYISAM,INNODB, but it is recommended to use InnoDB.
Tips:to avoid the Insert error,please remember set storage Engine:innodb.
Tips: A common misconception about handlersocket is that only the primary type of KV query can be implemented, in fact, as long as the support index, the general simple query it can be competent, here is not much to say, the official document has introduced.

Handlersocket's flaws:

(1) Write operation does not eliminate the query cache-if the write operation through Handlersocket, because there is no invalid query cache, then you may read the old data from MySQL;
(2) Automatic increment is not supported-the increment value cannot be automatically obtained from the self-added column when inserting.

In view of the above problems, to avoid weaknesses, using its merged query operation, play its nosql performance to obtain the MySQL InnoDB type table data, the specific operations are as follows:

Written at the end of:

The

MySQL5.6 provides the native memcached API, is actually the KV type NoSQL, but Handlersocket is not limited to the KV form, therefore still has the living space.