Databases, General, MySQL

How-to Setup Mysql Cluster on Linux

This post will show you how to set up Mysql Cluster on Linux.
Assume we have 3 machines named db1, db2 and db3 structured as below:

  • db1 is the management node.
  • db2 and db3 are datanodes and sql nodes as well.

First of all, download mysql cluster from here and choose the “linux generic” platform (the version at the time of this post is 7.4.10). Also make sure you have installed libaio1 on all machines.

  1. Install the management node
    • on db1 unzip the archive

      cd /usr/local
      tar xvf mysql-cluster-gpl-7.4.10-linux-glibc2.5-x86_64.tar.gz
      ln -s mysql-cluster-gpl-7.4.10-linux-glibc2.5-x86_64/ mysql
      
    • create a directory for the config

      mkdir -p /var/lib/mysql-cluster/49
      cd /var/lib/mysql-cluster/49
      vim config.ini
      

      The config.ini should look like this:

      [NDB_MGMD DEFAULT]
      Portnumber=1186
      
      [NDB_MGMD]
      NodeId=49
      HostName=db1
      DataDir=/var/lib/mysql-cluster/49/
      Portnumber=1186
      
      [TCP DEFAULT]
      SendBufferMemory=8M
      ReceiveBufferMemory=8M
      
      [NDBD DEFAULT]
      BackupMaxWriteSize=1M
      BackupDataBufferSize=16M
      BackupLogBufferSize=4M
      BackupMemory=20M
      BackupReportFrequency=10
      MemReportFrequency=30
      LogLevelStartup=15
      LogLevelShutdown=15
      LogLevelCheckpoint=8
      LogLevelNodeRestart=15
      DataMemory=777M
      IndexMemory=138M
      MaxNoOfTables=4096
      MaxNoOfTriggers=3500
      NoOfReplicas=2
      StringMemory=25
      DiskPageBufferMemory=64M
      SharedGlobalMemory=20M
      LongMessageBuffer=32M
      MaxNoOfConcurrentTransactions=16384
      BatchSizePerLocalScan=512
      FragmentLogFileSize=256M
      NoOfFragmentLogFiles=4
      RedoBuffer=64M
      MaxNoOfExecutionThreads=8
      StopOnError=false
      LockPagesInMainMemory=1
      TimeBetweenEpochsTimeout=32000
      TimeBetweenWatchdogCheckInitial=60000
      TransactionInactiveTimeout=60000
      HeartbeatIntervalDbDb=15000
      HeartbeatIntervalDbApi=15000
      
      [NDBD]
      NodeId=1
      HostName=db2
      DataDir=/var/lib/mysql-cluster/1/
      
      [NDBD]
      NodeId=2
      HostName=db3
      DataDir=/var/lib/mysql-cluster/2/
      
      [MYSQLD DEFAULT]
      
      [MYSQLD]
      NodeId=50
      HostName=db2
      
      [MYSQLD]
      NodeId=51
      HostName=db2
      
      [MYSQLD]
      NodeId=52
      HostName=db3
      
      [MYSQLD]
      NodeId=53
      HostName=db3
      
  2. Install the data node on db2

    • unzip the archive

      cd /usr/local
      tar xvf mysql-cluster-gpl-7.4.10-linux-glibc2.5-x86_64.tar.gz
      ln -s mysql-cluster-gpl-7.4.10-linux-glibc2.5-x86_64/ mysql
      
    • create a directory for the first data node

      mkdir -p /var/lib/mysql-cluster/1
      
  3. Install the first and second sql node on db2

    • check whether there is already a mysql user and group, if not create them:

      groupadd mysql
      useradd -g mysql mysql
      
    • create the directory for the first sql node:

      mkdir /var/lib/mysql-cluster/50/
      cd /var/lib/mysql-cluster/50/
      vim my.cnf
      

      The my.cnf should look like this:

      [mysqld]
      log-error=mysqld.50.err
      datadir="/var/lib/mysql-cluster/50/"
      tmpdir="/var/lib/mysql-cluster/50/tmp"
      basedir="/usr/local/mysql/"
      port=3306
      ndbcluster=on
      ndb-nodeid=50
      ndb-connectstring=db1:1186,
      socket="/var/lib/mysql-cluster/50/mysql.socket"
      default-storage-engine="ndbcluster"
      
    • create the system databases:

      cd /usr/local/mysql
      scripts/mysql_install_db --no-defaults --datadir=/var/lib/mysql-cluster/50/ --basedir=/usr/local/mysql/ --user=mysql
      
    • create the directory for the second sql node:

      mkdir /var/lib/mysql-cluster/51/
      cd /var/lib/mysql-cluster/51/
      vim my.cnf
      

      The my.cnf should look like this:

      [mysqld]
      log-error=mysqld.51.err
      datadir="/var/lib/mysql-cluster/51/"
      tmpdir="/var/lib/mysql-cluster/51/tmp"
      basedir="/usr/local/mysql/"
      port=3307
      ndbcluster=on
      ndb-nodeid=51
      ndb-connectstring=db1:1186,
      socket="/var/lib/mysql-cluster/51/mysql.socket"
      default-storage-engine="ndbcluster"
      
    • create the system databases:

      cd /usr/local/mysql
      scripts/mysql_install_db --no-defaults --datadir=/var/lib/mysql-cluster/51/ --basedir=/usr/local/mysql/ --user=mysql
      
  4. Repeat steps 2. and 3. on db3 using /var/lib/mysql-cluster/2/ for the second data node and /var/lib/mysql-cluster/52/ and /var/lib/mysql-cluster/53/ for the third and fourth sql nodes.

Now let’s start the services:

  • on db1 run

    /usr/local/mysql/bin/ndb_mgmd --initial --ndb-nodeid=49 --config-dir=/var/lib/mysql-cluster/49/ --config-file=/var/lib/mysql-cluster/49/config.ini
    
  • on db2 run:

    /usr/local/mysql/bin/ndbmtd --initial --ndb-nodeid=1 --ndb-connectstring=db1:1186
    sudo -u mysql -H /usr/local/mysql/bin/mysqld_safe --defaults-file=/var/lib/mysql-cluster/50/my.cnf &
    sudo -u mysql -H /usr/local/mysql/bin/mysqld_safe --defaults-file=/var/lib/mysql-cluster/51/my.cnf &
    
  • on db3 run:

    /usr/local/mysql/bin/ndbmtd --initial --ndb-nodeid=2 --ndb-connectstring=db1:1186
    sudo -u mysql -H /usr/local/mysql/bin/mysqld_safe --defaults-file=/var/lib/mysql-cluster/52/my.cnf &
    sudo -u mysql -H /usr/local/mysql/bin/mysqld_safe --defaults-file=/var/lib/mysql-cluster/53/my.cnf &
    

That’s all. Now you can now run the management console on db1 to see whether the cluster is working correctly, it should show something like this:

root@db1>/usr/local/mysql/bin/ndb_mgm

-- NDB Cluster -- Management Client --
ndb_mgm> show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=1    @10.10.10.131  (mysql-5.6.28 ndb-7.4.10, Nodegroup: 0)
id=2    @10.10.10.132  (mysql-5.6.28 ndb-7.4.10, Nodegroup: 0, *)

[ndb_mgmd(MGM)] 1 node(s)
id=49   @10.10.10.1  (mysql-5.6.28 ndb-7.4.10)

[mysqld(API)]   4 node(s)
id=50   @10.10.10.131  (mysql-5.6.28 ndb-7.4.10)
id=51   @10.10.10.131  (mysql-5.6.28 ndb-7.4.10)
id=52   @10.10.10.132  (mysql-5.6.28 ndb-7.4.10)
id=53   @10.10.10.132  (mysql-5.6.28 ndb-7.4.10)

Easy. Or you could run /usr/local/mysql/bin/ndb_setup.py in the first place 😛

General, PHP, Programming, Web Development

Fetch geocode data from address using Zend Framework

The following class fetches geocode information about a given address, using the HTTP client provided by the Zend Framework. Enjoy!

namespace your\namespace\here;

use Zend\Http\Client;
use Zend\Http\Exception\RuntimeException;

class Http {
    protected static $url = 'http://maps.google.com/maps/api/geocode/json?address=%s&sensor=false';

    public static function fetchRemoteJson($address) {
        try {
            $url = sprintf(self::$url, urlencode($address));
            $client = new Client($url);
            $json = json_decode($client->send()->getBody());
            $result = (false == $json) ? array('results' => array(), 'status' => 'JSON_ERROR') : $json;
        } catch (RuntimeException $e) {
            $result = array('results' => array(), 'status' => 'HTTP_CLIENT_ERROR');
        } catch (\Exception $e) {
            $result = array('results' => array(), 'status' => 'UNKNOWN_ERROR');
        }

        return $result;
    }
}
General, PHP, Programming, Web Development

Raise “404 Page Not Found” programmatically in Concrete5

Ok, so you are using Concrete5 and want to raise “404 page not found” from your controller. Here is a piece of code that does just that:

namespace your\namespace\here;

use Page;

class Http {
    public static function raise404() {
        $date = new \DateTime('now', new \DateTimeZone('UTC'));
        $dateString = $date->sub(new \DateInterval('P1M'))->format('D, d M Y H:i:s') . ' GMT';
        header(sprintf('HTTP/%s %s %s', '1.0', 404, 'Not Found Cache-Control: no-cache Date:' . $dateString), true, 404);

        $v = Page::getByPath('/page_not_found')->getPageController()->getViewObject();
        die($v->render());
    }
}
General

Backup your PHP projects

With this script, it is easy to backup your data from a remote server or local file system.
In this post I will show you how to backup your PHP/MySQL projects which are on the same file system:

  • clone the package: git clone https://github.com/skywebro/php-backup.git
  • cd php-backup
  • cp directories_and_mysql.ini.dist directories_and_mysql.ini and edit to fit your settings
  • touch directories_and_mysql and add the csv records, made out of the path to the project and the mysql info (see directories_and_mysql.dist for the csv file format).
  • run the script ./php-backup -i directories_and_mysql.ini

Easy!

General, Sys admin, Web Development

Qt Webkit HTML Converter – Install wkhtmltopdf

JavaScript, Web Development

jQuery UI Ajax Dialog (aka Dialogue)

Recently I needed to add Ajax functionality to a regular jQuery UI dialog so I came out with the following code:

(function ($) {
    var counter = 1;

    $.dialogue = function(url, options) {
        var options = $.extend({
            id: 'uix-dialogue-' + counter++,
            autoResize: true,
            width: 'auto',
            height: 'auto',
            position: { my: 'center', at: 'center', of: window },
            modal: true,
            dialogClass: 'uix-dialogue',
            title: 'Ajax Dialog',
            data: '',
            open: function(event, ui) {
            },
            close: function(event, ui) {
                $(this).dialog('destroy').remove();
            },
            complete: function(response, status, xhr) {
                /* A callback function that is executed when the request completes. */
            }
        }, options);

        var content = $('<div id="' + options.id + '" class="' + options.dialogClass + '-content" style="display:none;"></div>').appendTo('body');
        content.load(url, options.data, function(response, status, xhr) {
            setTimeout(function() {
                content.dialog(options);
                options.complete.call($(this), response, status, xhr);
            }, 100);
        });

        return content;
    };
}(jQuery));

Great, job done!

Databases, MySQL

Howto setup MySQL Cluster (beginners tutorial)

This HOWTO is designed for a classic setup of two servers behind a load-balancer. The aim is to have true redundancy – either server can be unplugged and yet the site will remain up.

Read more here []

Databases, General, Linux, MySQL, Sys admin

MySQL load balancing and read-write splitting with MySQL Proxy

MySQL Proxy is a simple program that sits between your client and MySQL server(s) that can monitor, analyze or transform their communication. Its flexibility allows for unlimited uses; common ones include: load balancing; failover; query analysis; query filtering and modification; and many more.

Read more here: []

General, Linux, Sys admin

How To Set Your VirtualBox 4.2 VM to Automatically Startup

A new feature added in VirtualBox 4.2 is autostart, this feature is available in Linux, Solaris and MacOSX hosts only. Let’s say there is a VirtualBox VM you start everytime you startup your system, why not make it startup as soon as you start VirtualBox.

Read more here []