Tag Archives: mysql

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

Installing Apache, MySQL, PHP and Memcached on CentOS

10 Jun

Introduction

This page covers installation and configuration of Apache, MySQL, PHP and Memcached.

Configure Additional Yum Repositories

  1. Install the EPEL YUM Repository
  1. Install the REMI Yum Repository
  2. For more information see EPEL and REMI

MySQL installation

Install MySQL packages

    1. yum –enablerepo=remi install mysql-server mysql mysql-devel
    2. chkconfig mysqld on (enables /etc/init.d/mysqld to run at startup)
    3. /etc/init.d/mysqld start OR service mysqld start

Setup MYSQL users:

    1. Setup a user/password for localhost. Replace ‘XXXXX’ with password.
      1. /usr/bin/mysqladmin -u root password ‘XXXXX’
    2. Setup remote users. Replace ‘XXXXX’ with password

      Setup Localhost user

      #mysql -p
      Enter password:
      Welcome to the MySQL monitor.  Commands end with ; or \g.
      Your MySQL connection id is 6
      Server version: 5.0.77 Source distribution
      
      Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
      
      mysql>
      mysql>
      mysql> CREATE USER 'root'@'10.16.35.211' IDENTIFIED BY 'XXXXX';
      Query OK, 0 rows affected (0.00 sec)
      
      mysql> CREATE USER 'root'@'10.16.35.212' IDENTIFIED BY 'XXXXX';
      Query OK, 0 rows affected (0.00 sec)
      
      mysql> GRANT ALL PRIVILEGES ON * . * TO 'root'@'10.16.35.211' IDENTIFIED BY 'XXXXX' WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
      Query OK, 0 rows affected (0.01 sec)
      
      mysql> GRANT ALL PRIVILEGES ON * . * TO 'root'@'10.16.35.211' IDENTIFIED BY 'XXXXX' WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
      Query OK, 0 rows affected (0.00 sec)
      
      mysql> GRANT ALL PRIVILEGES ON * . * TO 'root'@'10.16.35.212' IDENTIFIED BY 'XXXXX' WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
      Query OK, 0 rows affected (0.00 sec)

Memcached (server-side) installation

Install Memcached packages

    1. yum install libevent
    2. yum install memcached
    3. chkconfig memcached on (enables /etc/init.d/memcached to run at startup)

To start Memcached

  1. Add memcached startup options to /etc/sysconfig/memcached

    /etc/sysconfig/memcached

    PORT="11211"
    USER="root"
    MAXCONN="1024"
    CACHESIZE="2048"
    OPTIONS=""
    1. To start memcached:
      1. /etc/init.d/memcached start OR service memcached on
    2. Listens on port 11211 with memory size of 2GG

Apache Installation

  • yum install httpd
  • chkconfig httpd on (enables /etc/init.d/httpd to run at startup)
  • /etc/init.d/httpd start OR service httpd start

PHP Installation

The following PHP packages will be installed:

  • php
  • mysql (Mysql CLI)
  • php-mysql
  • php-bcmath
  • php-xml
  • APC
    • php-pear
    • php-devel
    • httpd-devel
  • Memcache (client side)
    • zlib-devel (this is a prerequisite for the pecl memcache client library)

Install the PHP packages

Install all packages except APC.
  1. yum –enablerepo=remi install php mysql php-mysql php-bcmath php-xml
  1. To upgrade to the latest PHP/PHP-FPM packages: yum –enablerepo=remi upgrade
Install APC support.
  1. yum –enablerepo=remi install php-pear php-devel httpd-devel
  2. pear install pecl/apc-beta
  3. echo “extension=apc.so” > /etc/php.d/apc.ini
Install Memcache (client side) support.
  1. yum install zlib-devel
  2. pear install pecl/memcache
  3. echo “extension=memcache.so” > /etc/php.d/memcache.ini
Install Memcached (client-side) support.
  1. These three packages are required for the next step to build the libmemcached source code:
    1. yum install libevent memcached gcc-c++
  2. Download the latest version from http://libmemcached.org:
    1. mkdir /opt/libmemcached
    2. cd /opt/libmemcached
    3. wget http://launchpad.net/libmemcached/1.0/0.42/+download/libmemcached-0.42.tar.gz
    4. tar xvfz libmemcached-0.42.tar.gz
    5. cd libmemcached-0.42
    6. ./configure
    7. make
    8. make install (installs the libmemcached files to /usr/local)
  3. Next install the pecl/memcached client
    1. yum install zlib-devel (not required if you already installed it as part of memcache (client-side) support)
    2. pear install pecl/memcached
    3. echo “extension=memcached.so” > /etc/php.d/memcached.ini
Install PCRE
  1. yum install pcre pcre-devel
Compile and Install Bstrlib
  1. Download and extract the bstrlib source code from http://bstring.sourceforge.net/
  2. After extracting run the following commands to compile and install the package
    1. gcc -c -fPIC -O3 bstrlib.c -o bstrlib.o
      1. Note: -fPIC is required for 64bit systems. Optional otherwise.
    2. ar rcs libbstrlib.a bstrlib.o
    3. cp libbstrlib.a /usr/lib

Installing rpc.rstatd for remote performance monitoring

  1. yum install rusers-server
  2. chkconfig rstatd on
  3. /etc/init.d/rstatd start
  • We’ll be using HP LoadRunner for performance testing. rstatd is required by Loadrunner for Unix kernel performance metrics.
  • See http://www.pirzyk.org/?p=504 for more details on rstatd

Configuring SELinux on CentOS to enable Apache remote networking

This step is required otherwise PHP won’t be able to connect to a remote MySQL instance.

  • setsebool -P httpd_can_network_connect true