Tag Archives: handlersocket

Configruing HandlerSocket plugin for MySQL

21 Dec

This post assumes you are installing HandlerSocket plugin on MySQL cluster. But the steps should be applicable for the non-cluster MySQL installation as well.

Download and Extract MySQL Cluster Source Code

  • IMPORTANT: If you are installing HandlerSocket plugin on MySQL Cluster, download the source code to the instance that contains the SQL/Mgmt Server node.

Download, Extract and Build HandlerSocket Source Code

  • cd /opt/mysqlcluster/src
  • wget –no-check-certificate https://github.com/ahiguti/HandlerSocket-Plugin-for-MySQL/tarball/master
  • tar xvf master
  • ln -s ahiguti-HandlerSocket-Plugin-for-MySQL-0986a75 handler_src
  • cd handler_src
  • ./autogen.sh
  • ./configure –with-mysql-source=/opt/mysqlcluster/src/mysqlc_src –with-mysql-bindir=/opt/mysqlcluster/home/mysqlc/bin –with-mysql-plugindir=/opt/mysqlcluster/home/mysqlc/lib/plugin
    • IF YOU GET THE FOLLOWING ERROR:
      ...
      checking mysql source... yes: Using /opt/mysqlcluster/src/mysqlc_src, version 5.5.15
      checking mysql binary... yes: Using /opt/mysqlcluster/home/mysqlc/bin/mysql_config, version 5.5.15-ndb-7.2.1
      configure: error: MySQL source version does not match MySQL binary version
      ...
      • According to the install docs https://github.com/ahiguti/HandlerSocket-Plugin-for-MySQL/blob/master/docs-en/installation.en.txt: –with-mysql-source refers to the top of MySQL source directory (which
        contains the VERSION file or the configure.in file)
      • The VERSION file contains:
        MYSQL_VERSION_MAJOR=5
        MYSQL_VERSION_MINOR=5
        MYSQL_VERSION_PATCH=15
        MYSQL_VERSION_EXTRA=-ndb-7.2.1
      • But looking at the generated configure script, only the first 3 entries are checked with the output of mysql_config –version which returns the EXTRA information in addition to the 3 entries. This causes the match to fail
    • AS A WORKAROUND we do the following:
  • cd /opt/mysqlcluster/src/mysqlc_src
  • cp VERSION VERSION.bkup
  • gedit VERSION and append the EXTRA value to the PATCH value as follows:
    MYSQL_VERSION_MAJOR=5
    MYSQL_VERSION_MINOR=5
    MYSQL_VERSION_PATCH=15-ndb-7.2.1
    MYSQL_VERSION_EXTRA=-ndb-7.2.1
    • Re-run the configure command above
  • Compile and install HandlerSocket:
    • make && make install

Configure mysqld to use the HandlerSocket plugin

  • Shutdown any running mysqld process
    • mysqladmin -u myuser -h 127.0.0.1 -p shutdown
      • Note if you don’t have the myuser user setup in your environment then use:
        • mysqladmin -u root -h 127.0.0.1 shutdown
  • Edit the my.cnf file to add the setting for HandlerSocket.
    • cd /opt/mysqlcluster/deploy/conf
    • gedit my.cnf and should look like this:
      [mysqld]
      ndbcluster
      datadir=/opt/mysqlcluster/deploy/mysqld_data
      basedir=/opt/mysqlcluster/home/mysqlc
      port=3306
      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 mysqld
    • mysqld –defaults-file=/opt/mysqlcluster/deploy/conf/my.cnf –user=root &
  • Install the plugin
    • mysql -h 127.0.0.1 -u myuser -p OR mysql -h 127.0.0.1 -u root depending on your environment
    • mysql> install plugin handlersocket soname ‘handlersocket.so’;
      • You should see a bunch of messages and then a confirmation that the plugin was installed.
    • mysql> show processlist ;
      • This should list the handlersocket process
  • If handlersocket.so is successfully installed, it starts accepting connections on port 9998 (Reads) and 9999 (Writes).
Advertisements

Installing the PHP HandlerSocket Client

18 Dec

Download and Extract HandlerSocket Source Code

  • NOTE: If you are in a development environment where the Server-Side MySQL HandlerSocket service and the Apache/PHP service are in the
    same box, then you can skip this step.

Compile and Build HandlerSocket Client Code

  • cd /opt/mysqlcluster/src/handler_src
  • ./autogen.sh
  • ./configure –disable-handlersocket-server
  • make && make install

Install/Setup PHP HandlerSocket Client

  1. mkdir -p /opt/php_hs
  2. cd /opt/php_hs
  3. wget http://php-handlersocket.googlecode.com/files/php-handlersocket-0.3.0.tar.gz
  4. tar xvf php-handlersocket-0.3.0.tar.gz
  5. cd cd php-handlersocket
  6. Ensure the phpize command from /usr/bin is executed.
    1. export PATH=/usr/bin:$PATH
  7. make clean (If this is your first time building PHP HandlerSocket, this command will fail and that is ok)
  8. phpize
  9. ./configure
  10. make
  11. make install
    • This will copy handlersocket.so to /usr/lib64/php/modules on 64 bit OS (production systems)
    • Will copy handlersocket.so /usr/lib/php/modules on 32 bit OS (Dev systems)
  12. Specify handlersocket.so as a PHP extension
    1. echo “extension=handlersocket.so” > /etc/php.d/handlersocket.ini
  13. Restart Apache
    1. service httpd restart

Setup Sample Database

  • Setup a test database and innodb table:
    • mysql -h 127.0.0.1 -u myuser -p OR mysql -h 127.0.0.1 -u root depending on your environment.
    • mysql> create database hstestdb;
    • mysql> CREATE TABLE user (user_id INT UNSIGNED PRIMARY KEY,user_name VARCHAR(50),user_email VARCHAR(255),created Timestamp DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB;
    • mysql> insert into user values (101, “Hulk Hogan”, “hulk@hogan.com”, current_timestamp());
    • mysql> insert into user values (102, “The Rock”, “the@rock.com”, current_timestamp());
    • mysql> SELECT user_name, user_email, created FROM user WHERE user_id=101;

Sample PHP Code to access the above Database/Table using HandlerSocket API:

Client API

SELECT example

$host = 'ec2-50-17-110-253.compute-1.amazonaws.com';
$port = 9998;
$port_wr = 9999;
$dbname = 'hstestdb';
$table = 'user';

// SELECT SINGLE AND SELECT MULTI $hs = new \HandlerSocket($host, $port);
if (!($hs->openIndex(1, $dbname, $table, \HandlerSocket::PRIMARY, 'user_name,user_email,created')))
{

    echo $hs->getError(), PHP_EOL; die();
}

$retval = $hs->executeSingle(1, '=', array('101'), 1, 0);

var_dump($retval);

$retval = $hs->executeMulti(
    array(array(1, '=', array('101'), 1, 0),
          array(1, '=', array('102'), 1, 0)));

var_dump($retval);

unset($hs);

UPDATE Example

$host = 'ec2-50-17-110-253.compute-1.amazonaws.com';
$port = 9998;
$port_wr = 9999;
$dbname = 'hstestdb';
$table = 'user';
// UPDATE $hs = new \HandlerSocket($host, $port_wr);
if (!($hs->openIndex(2, $dbname, $table, '', 'user_name')))
{
    echo $hs->getError(), PHP_EOL;
    die();
}

if ($hs->executeUpdate(2, '=', array('101'), array('Hulkier Hogan'), 1, 0) === false)
{
    echo $hs->getError(), PHP_EOL;
    die();
}

unset($hs);

INSERT EXAMPLE

$host = 'ec2-50-17-110-253.compute-1.amazonaws.com';
$port = 9998;
$port_wr = 9999;
$dbname = 'hstestdb';
$table = 'user';
// INSERT $hs = new \HandlerSocket($host, $port_wr);
if (!($hs->openIndex(3, $dbname, $table, '', 'user_id,user_name,user_email')))
{
    echo $hs->getError(), PHP_EOL;
    die();
}

if ($hs->executeInsert(3, array(103,'Mr. Perfect', 'mr@perfect.com')) === false)
{
    echo $hs->getError(), PHP_EOL;
}

unset($hs);

exit;

DELETE EXAMPLE

$host = 'ec2-50-17-110-253.compute-1.amazonaws.com';
$port = 9998;
$port_wr = 9999;
$dbname = 'hstestdb';
$table = 'user';
//DELETE $hs = new \HandlerSocket($host, $port_wr);
if (!($hs->openIndex(4, $dbname, $table, '', '')))
{
    echo $hs->getError(), PHP_EOL;
    die();
}

if ($hs->executeDelete(4, '=', array(103)) === false)
{
    echo $hs->getError(), PHP_EOL;
    die();
}

References