WSO2Con 2013 CFP Banner

Deploying WSO2 Products on MySQL Cluster

Discuss this article on Stack Overflow
By Kasun Indrasiri
  • 30 Apr, 2013
  • Level: Advanced
  • Reads: 2608

The main objective of this article is to give the user a comprehensive understanding about the deployment of WSO2 Products in a MySQL Cluster. Also, the concepts related to MySQL Clustering are discussed in the process.

kasun's picture
Kasun Indrasiri
Architect
WSO2 Inc

Applies To

WSO2 Carbon 3.2.0 based products 3.2.0

Table of Contents

Introduction

High availability (HA) in SOA is defined as the constant availability of a service regardless of the status of the hosting or dependent servers on which it runs. In the context of an SOA platform, the Governance Registry plays a critical role as it contains all the metadata and configuration data of various systems in your SOA. When it comes to WSO2 Platform, WSO2 Governance Registry provides repository features as well as features related to governance. WSO2 GReg, by default, uses embedded H2 as the database; however, in the production environment, we recommend running a dedicated database, such as MySQL. Therefore, the high availability of the backing database is very critical. Various RDBMSs use various techniques to achieve this, but in this article, we discuss the MySQL Cluster.

So, before moving on to the details of the MySQL Cluster, lets consider the concept of a ‘cluster’. A cluster is two or more interconnected nodes that create a solution to provide higher availability or/and higher scalability. The advantage of clustering servers for high availability is seen if one node fails, another node in the cluster can consume the workload of the failed node, and users see no interruption of access. The advantages of clustering servers for scalability include increased application performance and a greater number of users that can be supported. Therefore, the the context of RDBMS clustering denotes the very same functionalities discussed above.

MySQL Cluster in a Nutshell

The MySQL Cluster is a fault tolerant in-memory clustered database designed for high availability (99.999%) and fast automatic fail overall running on cost-effective commodity hardware. It enables clustering of in-memory databases in a shared-nothing system (in a shared-nothing system, each component is expected to have its own memory and disk, and the use of shared storage mechanisms such as network shares and network file systems). The shared-nothing architecture enables the system to work with very inexpensive hardware, and with minimum specific requirements for hardware or software.

source: dev.mysql.comsource: dev.mysql.com

MySQL Cluster is designed so that it integrates the standard MySQL server with an in-memory clustered storage engine - NDB (Network DataBase) or NDB Cluster.

Components of MySQL Cluster

Data Nodes (ndbd)

Data nodes are used to store data in an MySQL Cluster environment. Data is replicated between data nodes to ensure data is continuously available in case one or more storage nodes fail. Data is visible to all MySQL servers connected to the cluster and all database transactions are handled by data nodes. However, some MySQL special data, such as the permissions and stored procedures, are not stored in the cluster and must be updated on each MySQL server attached to the cluster.

Management Server Nodes (ndb_mgmd)

Management server nodes handle the system configuration and are used to reconfigure the cluster. Therefore, the management server node is required to be running only during the startup and system re-configure times. The other components of an MySQL Cluster can operate without depending on management server nodes.

Management Client (ndb_mgm)

The client program to manage the cluster. This provides all the administration functionalities such as starting and stopping nodes, getting status information and starting backups.

MySQL Server Nodes (mysqld)

These nodes are running on the MySQL server, which can access clustered storage. Multiple MySQL servers can connect to one cluster. This provides redundancy and increased performance due to parallelism. When an update is performed on one MySQL server, it is immediately viewable from other MySQL servers attached to the cluster.

Applications

In this particular scenario, WSO2 products reside in the scope of ‘application’ in the MySQL Cluster. Applications connect to the MySQL Cluster via an MySQL server exactly the same way as they would if they were using any other MySQL storage engine. Using the cluster is more or less transparent to the application. However, there are cases where applications specifically need to handle cluster-specific features. (eg: Creating tables with ENGINE=NDB etc).

MySQL Cluster - Quick Install Guide

In what follows, is a quick installation guide on mysql cluster with 1-management node, 2 data nodes and one sql node.

Distribution

The installation steps are for Red Hat Linux

  • MySQL- Cluster-gpl-7.1.8-linux-x86_64-glibc23.tar.gz (Linux Genric)
  • MySQL-Cluster-gpl-server-7.1.8-1.rhel5.x86_64.rpm
  • MySQL-Cluster-gpl-client-7.1.8-1.rhel5.x86_64.rpm

Installing Data Nodes and MySQL Nodes

SQL Nodes

  • Create mysql group and user
  • groupadd mysql
  • useradd -g mysql mysql
  • rpm -Uhv MySQL-Cluster-gpl-client-7.1.8-1.rhel5.x86_64.rpm
  • rpm -Uhv MySQL-Cluster-gpl-server-7.1.8-1.rhel5.x86_64.rpm
  • chkconfig --add mysql

Data Nodes

  • Create mysql group and user
  • groupadd mysql
  • useradd -g mysql mysql
  • Unpack mysql-cluster-gpl-7.1.8-linux-x86_64-glibc23.tar.gz to /opt
  • tar -zxf mysql-cluster-gpl-7.1.8-linux-x86_64-glibc23.tar.gz -C /opt
  • ln -s /opt/mysql-cluster-gpl-7.1.8-linux-x86_64-glibc23 /usr/local/mysql
  • add to/etc/bashrc -> export PATH=$PATH:/usr/local/mysql/bin
  • Run the script to create the databasese (cd /usr/local/mysql/scripts)
  • ./mysql_install_db --basedir=/usr/local/mysql --user=mysql
  • Set the permissions

    • chown -R root .
    • chown -R mysql data
    • chgrp -R mysql .

Management Nodes

  • Create mysql group and user
  • groupadd mysql
  • useradd -g mysql mysql
  • Unpack mysql-cluster-gpl-7.1.8-linux-x86_64-glibc23.tar.gz to /opt tar -zxf mysql-cluster-gpl-7.1.8-linux-x86_64-glibc23.tar.gz -C /opt
  • ln -s /opt/mysql-cluster-gpl-7.1.8-linux-x86_64-glibc23 /usr/local/mysql
  • cp /usr/local/mysql/bin/ndb* /usr/local/bin
  • chmod +x /usr/local/bin/ndb_mgm*

Cluster Configuration

SQL Nodes and Data Node Configuration

Create file “/etc/my.cnf" in all the sqld nodes and data nodes and add following content to "/etc/my.cnf" in all the sqld nodes and data nodes.

    [mysqld]
    # Options for mysqld process:
    ndbcluster # run NDB storage engine
    ndb-connectstring=175.157.78.39 # location of management server
    [mysql_cluster]
    # Options for ndbd process:
    ndb-connectstring=175.157.78.39

Management Node Configuration

Create '/var/lib/mysql-cluster/' and add following content to "/var/lib/mysql-cluster/config.ini"

    [ndbd default]
    # Options affecting ndbd processes on all data nodes:

    NoOfReplicas=2     # Number of replicas
    DataMemory=80M      # Number of replicas
    IndexMemory=18M    # How much memory to allocate for index storage

    MaxNoOfOrderedIndexes=2048
    MaxNoOfAttributes=6000

    # For DataMemory and IndexMemory, we have used the

    # default values. This is because we don't expand these
    #testing databases. Check with the memory needed for registry and user_mgmt databases
    #in real environments.
    [tcp default]
    # TCP/IP options:
    Port=2202    # This the default; however, you can use any

    # port that is free for all the hosts in the cluster

    # Note: It is recommended that you do not specify the port

    # number at all and simply allow the default value to be used

    # instead

    [ndb_mgmd]
    # Management process options:
    hostname=175.157.78.39        # Hostname or IP address of MGM node
    datadir=/var/lib/mysql-cluster # Directory for MGM node log files
    [ndbd]
    # Options for data node "stgdb1": # (one [ndbd] section per data node)
    hostname=175.157.78.46        # Hostname or IP address
    datadir=/usr/local/mysql/data    # Directory for this data node's data files
    [ndbd]
    # Options for data node "stgdb2":
    hostname=175.157.78.47        # Hostname or IP address
    datadir=/usr/local/mysql/data    # Directory for this data node's data files

    [mysqld]
    # SQL node options:
    hostname=175.157.78.4  # Hostname or IP address : ESB1
                       # (additional mysqld connections can be
                       # specified for this node for various
                       # purposes such as running ndb_restore)

    [mysqld]
    # SQL node options:
    hostname=175.157.78.33 # Hostname or IP address : ESB2

    [mysqld]
    # SQL node options:
    hostname=175.157.78.2  # Hostname or IP address : DSS1

    [mysqld]
    # SQL node options:
    hostname=175.157.78.3  # Hostname or IP address : DSS2

    [mysqld]
    # SQL node options:
    hostname=175.157.78.39  # Hostname or IP address : BAM1

    [mysqld]
    # SQL node options:
    hostname=175.157.78.45  # Hostname or IP address : BAM2

Starting the mysql cluster

Starting Management Node

  • ndb_mgmd -f /var/lib/mysql-cluster/config.ini --configdir=/var/lib/mysql-cluster

Starting Data Nodes

  • /usr/local/mysql/bin/ndbd

Starting SQL Nodes

  • /etc/init.d/mysql start

Testing the mysql cluster

In management node

  • ndb_mgm
    [root@cspstgbam1:~ ] $ ndb_mgm
    -- NDB Cluster -- Management Client --
    ndb_mgm> show
    Connected to Management Server at: 175.157.78.39:1186
    Cluster Configuration
    ---------------------
    [ndbd(NDB)]     2 node(s)
    id=2    @175.157.78.46  (mysql-5.1.47 ndb-7.1.8, Nodegroup: 0, Master)
    id=3    @175.157.78.47  (mysql-5.1.47 ndb-7.1.8, Nodegroup: 0)

    [ndb_mgmd(MGM)] 1 node(s)
    id=1    @175.157.78.39  (mysql-5.1.47 ndb-7.1.8)

    [mysqld(API)]   6 node(s)
    id=4    @175.157.78.4  (mysql-5.1.47 ndb-7.1.8)
    id=5    @175.157.78.33  (mysql-5.1.47 ndb-7.1.8)
    id=6    @175.157.78.2  (mysql-5.1.47 ndb-7.1.8)
    id=7    @175.157.78.3  (mysql-5.1.47 ndb-7.1.8)
    id=8    @175.157.78.39  (mysql-5.1.47 ndb-7.1.8)
    id=9    @175.157.78.45  (mysql-5.1.47 ndb-7.1.8)
    ndb_mgm>

If you have changed the management node configuration in the config.ini, you have to use --reload while restarting the cluster management node. Please refer the following guide for further details on installing MySQL Cluster. http://wso2.org/library/knowledge-base/2010/11/setting-mysql-cluster-use-wso2-products

Deploying WSO2 Governance Registry on MySQL Cluster

MySQL Cluster Limitations

  • Foreign key constraints : The foreign key construct is ignored
  • Index prefixes : Prefixes on indexes are not supported for NDBCLUSTER tables. If a prefix is used as part of an index specification in a statement such as CREATE TABLE, ALTER TABLE, or CREATE INDEX, the prefix is ignored.
  • Savepoints and rollbacks to savepoints are ignored
  • There are no durable commits on disk. Commits are replicated, but there is no guarantee that logs are flushed to disk on commit.
  • Replication : Statement-based replication is not supported.
  • For the complete set of MySQL Cluster limitations, please refer http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-limitations.html

Install WSO2 GReg on MySQL Cluster

When it comes to deploying WSO2 GReg on mysql cluster, we have to adhere to the above mentioned limitations. Therefore we need to modify the existing database script that is shipped with WSO2 GReg.

The mysql cluster dbscripts for Carbon 4.0.0 based products can be found at the following location. https://svn.wso2.org/repos/wso2/carbon/kernel/trunk/distribution/kernel/carbon-home/dbscripts/mysql_cluster.sql

And for all the other carbon 3.x based products, the existing myssql db script can be modified as follows.

The mysql db script is located in wso2greg-3.6.0/dbscripts/mysql.sql. In order to deploy WSO2 GReg on mysql cluster, we need to modify the mysql.sql dbscript as shown below.

    CREATE TABLE IF NOT EXISTS REG_CLUSTER_LOCK (
             REG_LOCK_NAME VARCHAR (20),
             REG_LOCK_STATUS VARCHAR (20),
             REG_LOCKED_TIME TIMESTAMP,
             REG_TENANT_ID INTEGER DEFAULT 0,
             PRIMARY KEY (REG_LOCK_NAME)
)ENGINE NDB;

CREATE TABLE IF NOT EXISTS REG_LOG (
             REG_LOG_ID INTEGER AUTO_INCREMENT,
             REG_PATH VARCHAR (750),
             REG_USER_ID VARCHAR (31) NOT NULL,
             REG_LOGGED_TIME TIMESTAMP NOT NULL,
             REG_ACTION INTEGER NOT NULL,
             REG_ACTION_DATA VARCHAR (500),
             REG_TENANT_ID INTEGER DEFAULT 0,
             PRIMARY KEY (REG_LOG_ID, REG_TENANT_ID)
)ENGINE NDB;

-- The REG_PATH_VALUE should be less than 767 bytes, and hence was fixed at 750.
-- See CARBON-5917.

CREATE TABLE IF NOT EXISTS REG_PATH(
             REG_PATH_ID INTEGER NOT NULL AUTO_INCREMENT,
             REG_PATH_VALUE VARCHAR(750) NOT NULL,
             REG_PATH_PARENT_ID INTEGER,
             REG_TENANT_ID INTEGER DEFAULT 0,
             CONSTRAINT PK_REG_PATH PRIMARY KEY(REG_PATH_ID, REG_TENANT_ID)
)ENGINE NDB;

-- CREATE INDEX REG_PATH_IND_BY_PATH_VALUE USING HASH ON REG_PATH(REG_PATH_VALUE, REG_TENANT_ID);
-- CREATE INDEX REG_PATH_IND_BY_PATH_PARENT_ID USING HASH ON REG_PATH(REG_PATH_PARENT_ID, REG_TENANT_ID);

CREATE TABLE IF NOT EXISTS REG_CONTENT (
             REG_CONTENT_ID INTEGER NOT NULL AUTO_INCREMENT,
             REG_CONTENT_DATA LONGBLOB,
             REG_TENANT_ID INTEGER DEFAULT 0,
             CONSTRAINT PK_REG_CONTENT PRIMARY KEY(REG_CONTENT_ID, REG_TENANT_ID)
)ENGINE NDB;

CREATE TABLE IF NOT EXISTS REG_CONTENT_HISTORY (
             REG_CONTENT_ID INTEGER NOT NULL,
             REG_CONTENT_DATA LONGBLOB,
             REG_DELETED   SMALLINT,
             REG_TENANT_ID INTEGER DEFAULT 0,
             CONSTRAINT PK_REG_CONTENT_HISTORY PRIMARY KEY(REG_CONTENT_ID, REG_TENANT_ID)
)ENGINE NDB;

CREATE TABLE IF NOT EXISTS REG_RESOURCE (
            REG_PATH_ID         INTEGER NOT NULL,
            REG_NAME            VARCHAR(256),
            REG_VERSION         INTEGER NOT NULL AUTO_INCREMENT,
            REG_MEDIA_TYPE      VARCHAR(500),
            REG_CREATOR         VARCHAR(31) NOT NULL,
            REG_CREATED_TIME    TIMESTAMP NOT NULL,
            REG_LAST_UPDATOR    VARCHAR(31),
            REG_LAST_UPDATED_TIME    TIMESTAMP NOT NULL,
            REG_DESCRIPTION     VARCHAR(1000),
            REG_CONTENT_ID      INTEGER,
            REG_TENANT_ID INTEGER DEFAULT 0,
            CONSTRAINT PK_REG_RESOURCE PRIMARY KEY(REG_VERSION, REG_TENANT_ID)
)ENGINE NDB;



-- CREATE INDEX REG_RESOURCE_IND_BY_NAME USING HASH ON REG_RESOURCE(REG_NAME, REG_TENANT_ID);
-- CREATE INDEX REG_RESOURCE_IND_BY_PATH_ID_NAME USING HASH ON REG_RESOURCE(REG_PATH_ID, REG_NAME, REG_TENANT_ID);

CREATE TABLE IF NOT EXISTS REG_RESOURCE_HISTORY (
            REG_PATH_ID         INTEGER NOT NULL,
            REG_NAME            VARCHAR(256),
            REG_VERSION         INTEGER NOT NULL,
            REG_MEDIA_TYPE      VARCHAR(500),
            REG_CREATOR         VARCHAR(31) NOT NULL,
            REG_CREATED_TIME    TIMESTAMP NOT NULL,
            REG_LAST_UPDATOR    VARCHAR(31),
            REG_LAST_UPDATED_TIME    TIMESTAMP NOT NULL,
            REG_DESCRIPTION     VARCHAR(1000),
            REG_CONTENT_ID      INTEGER,
            REG_DELETED         SMALLINT,
            REG_TENANT_ID INTEGER DEFAULT 0,
            CONSTRAINT PK_REG_RESOURCE_HISTORY PRIMARY KEY(REG_VERSION, REG_TENANT_ID)
)ENGINE NDB;



-- CREATE INDEX REG_RESOURCE_HISTORY_IND_BY_NAME USING HASH ON REG_RESOURCE_HISTORY(REG_NAME, REG_TENANT_ID);
-- CREATE INDEX REG_RESOURCE_HISTORY_IND_BY_PATH_ID_NAME USING HASH ON REG_RESOURCE(REG_PATH_ID, REG_NAME, REG_TENANT_ID);

CREATE TABLE IF NOT EXISTS REG_COMMENT (
            REG_ID        INTEGER NOT NULL AUTO_INCREMENT,
            REG_COMMENT_TEXT      VARCHAR(500) NOT NULL,
            REG_USER_ID           VARCHAR(31) NOT NULL,
            REG_COMMENTED_TIME    TIMESTAMP NOT NULL,
            REG_TENANT_ID INTEGER DEFAULT 0,
            CONSTRAINT PK_REG_COMMENT PRIMARY KEY(REG_ID, REG_TENANT_ID)
)ENGINE NDB;

CREATE TABLE IF NOT EXISTS REG_RESOURCE_COMMENT (
            REG_COMMENT_ID          INTEGER NOT NULL,
            REG_VERSION             INTEGER,
            REG_PATH_ID             INTEGER,
            REG_RESOURCE_NAME       VARCHAR(256),
            REG_TENANT_ID INTEGER DEFAULT 0
)ENGINE NDB;



-- CREATE INDEX REG_RESOURCE_COMMENT_IND_BY_PATH_ID_AND_RESOURCE_NAME USING HASH ON REG_RESOURCE_COMMENT(REG_PATH_ID, REG_RESOURCE_NAME, REG_TENANT_ID);
-- CREATE INDEX REG_RESOURCE_COMMENT_IND_BY_VERSION USING HASH ON REG_RESOURCE_COMMENT(REG_VERSION, REG_TENANT_ID);

CREATE TABLE IF NOT EXISTS REG_RATING (
            REG_ID     INTEGER NOT NULL AUTO_INCREMENT,
            REG_RATING        INTEGER NOT NULL,
            REG_USER_ID       VARCHAR(31) NOT NULL,
            REG_RATED_TIME    TIMESTAMP NOT NULL,
            REG_TENANT_ID INTEGER DEFAULT 0,
            CONSTRAINT PK_REG_RATING PRIMARY KEY(REG_ID, REG_TENANT_ID)
)ENGINE NDB;

CREATE TABLE IF NOT EXISTS REG_RESOURCE_RATING (
            REG_RATING_ID           INTEGER NOT NULL,
            REG_VERSION             INTEGER,
            REG_PATH_ID             INTEGER,
            REG_RESOURCE_NAME       VARCHAR(256),
            REG_TENANT_ID INTEGER DEFAULT 0
)ENGINE NDB;



-- CREATE INDEX REG_RESOURCE_RATING_IND_BY_PATH_ID_AND_RESOURCE_NAME USING HASH ON REG_RESOURCE_RATING(REG_PATH_ID, REG_RESOURCE_NAME, REG_TENANT_ID);
-- CREATE INDEX REG_RESOURCE_RATING_IND_BY_VERSION USING HASH ON REG_RESOURCE_RATING(REG_VERSION, REG_TENANT_ID);


CREATE TABLE IF NOT EXISTS REG_TAG (
            REG_ID         INTEGER NOT NULL AUTO_INCREMENT,
            REG_TAG_NAME       VARCHAR(500) NOT NULL,
            REG_USER_ID        VARCHAR(31) NOT NULL,
            REG_TAGGED_TIME    TIMESTAMP NOT NULL,
            REG_TENANT_ID INTEGER DEFAULT 0,
            CONSTRAINT PK_REG_TAG PRIMARY KEY(REG_ID, REG_TENANT_ID)
)ENGINE NDB;

CREATE TABLE IF NOT EXISTS REG_RESOURCE_TAG (
            REG_TAG_ID              INTEGER NOT NULL,
            REG_VERSION             INTEGER,
            REG_PATH_ID             INTEGER,
            REG_RESOURCE_NAME       VARCHAR(256),
            REG_TENANT_ID INTEGER DEFAULT 0
)ENGINE NDB;



-- CREATE INDEX REG_RESOURCE_TAG_IND_BY_PATH_ID_AND_RESOURCE_NAME USING HASH ON REG_RESOURCE_TAG(REG_PATH_ID, REG_RESOURCE_NAME, REG_TENANT_ID);
-- CREATE INDEX REG_RESOURCE_TAG_IND_BY_VERSION USING HASH ON REG_RESOURCE_TAG(REG_VERSION, REG_TENANT_ID);

CREATE TABLE IF NOT EXISTS REG_PROPERTY (
            REG_ID         INTEGER NOT NULL AUTO_INCREMENT,
            REG_NAME       VARCHAR(100) NOT NULL,
            REG_VALUE        VARCHAR(1000),
            REG_TENANT_ID INTEGER DEFAULT 0,
            CONSTRAINT PK_REG_PROPERTY PRIMARY KEY(REG_ID, REG_TENANT_ID)
)ENGINE NDB;

CREATE TABLE IF NOT EXISTS REG_RESOURCE_PROPERTY (
            REG_PROPERTY_ID         INTEGER NOT NULL,
            REG_VERSION             INTEGER,
            REG_PATH_ID             INTEGER,
            REG_RESOURCE_NAME       VARCHAR(256),
            REG_TENANT_ID INTEGER DEFAULT 0
)ENGINE NDB;



-- CREATE INDEX REG_RESOURCE_PROPERTY_IND_BY_PATH_ID_AND_RESOURCE_NAME USING HASH ON REG_RESOURCE_PROPERTY(REG_PATH_ID, REG_RESOURCE_NAME, REG_TENANT_ID);
-- CREATE INDEX REG_RESOURCE_PROPERTY_IND_BY_VERSION USING HASH ON REG_RESOURCE_PROPERTY(REG_VERSION, REG_TENANT_ID);

-- CREATE TABLE IF NOT EXISTS REG_ASSOCIATIONS (
-- SRC_PATH_ID     INTEGER,
-- SRC_RESOURCE_NAME    VARCHAR(256),
-- SRC_VERSION     INTEGER,
-- TGT_PATH_ID     INTEGER,
-- TGT_RESOURCE_NAME    VARCHAR(256),
-- TGT_VERSION     INTEGER
-- )ENGINE NDB;
--
-- ALTER TABLE REG_ASSOCIATIONS ADD CONSTRAINT REG_ASSOCIATIONS_FK_BY_SRC_PATH_ID FOREIGN KEY (SRC_PATH_ID) REFERENCES REG_PATH (PATH_ID);
-- ALTER TABLE REG_ASSOCIATIONS ADD CONSTRAINT REG_ASSOCIATIONS_FK_BY_TGT_PATH_ID FOREIGN KEY (TGT_PATH_ID) REFERENCES REG_PATH (PATH_ID);
-- CREATE INDEX REG_ASSOCIATIONS_IND_BY_SRC_VERSION ON REG_ASSOCIATIONS(SRC_VERSION);
-- CREATE INDEX REG_ASSOCIATIONS_IND_BY_TGT_VERSION ON REG_ASSOCIATIONS(TGT_VERSION);
-- CREATE INDEX REG_ASSOCIATIONS_IND_BY_SRC_RESOURCE_NAME ON REG_ASSOCIATIONS(SRC_RESOURCE_NAME);
-- CREATE INDEX REG_ASSOCIATIONS_IND_BY_TGT_RESOURCE_NAME ON REG_ASSOCIATIONS(TGT_RESOURCE_NAME);



CREATE TABLE IF NOT EXISTS REG_ASSOCIATION (
            REG_ASSOCIATION_ID INTEGER AUTO_INCREMENT,
            REG_SOURCEPATH VARCHAR (750) NOT NULL,
            REG_TARGETPATH VARCHAR (750) NOT NULL,
            REG_ASSOCIATION_TYPE VARCHAR (2000) NOT NULL,
            REG_TENANT_ID INTEGER DEFAULT 0,
            PRIMARY KEY (REG_ASSOCIATION_ID, REG_TENANT_ID)
)ENGINE NDB;

CREATE TABLE IF NOT EXISTS REG_SNAPSHOT (
            REG_SNAPSHOT_ID     INTEGER NOT NULL AUTO_INCREMENT,
            REG_PATH_ID            INTEGER NOT NULL,
            REG_RESOURCE_NAME      VARCHAR(255),
            REG_RESOURCE_VIDS     LONGBLOB NOT NULL,
            REG_TENANT_ID INTEGER DEFAULT 0,
            CONSTRAINT PK_REG_SNAPSHOT PRIMARY KEY(REG_SNAPSHOT_ID, REG_TENANT_ID)
)ENGINE NDB;

-- CREATE INDEX REG_SNAPSHOT_IND_BY_PATH_ID_AND_RESOURCE_NAME USING HASH ON REG_SNAPSHOT(REG_PATH_ID, REG_RESOURCE_NAME, REG_TENANT_ID);




-- ################################
-- USER MANAGER TABLES
-- ################################

CREATE TABLE UM_TENANT (
			UM_ID INTEGER NOT NULL AUTO_INCREMENT,
	    UM_DOMAIN_NAME VARCHAR(255) NOT NULL,
            UM_EMAIL VARCHAR(255),
            UM_ACTIVE BOOLEAN DEFAULT FALSE,
			PRIMARY KEY (UM_ID),
			UNIQUE(UM_DOMAIN_NAME)
)ENGINE NDB;

CREATE TABLE UM_USER (
             UM_ID INTEGER NOT NULL AUTO_INCREMENT,
             UM_USER_NAME VARCHAR(255) NOT NULL,
             UM_USER_PASSWORD VARCHAR(255) NOT NULL,
             UM_SALT_VALUE VARCHAR(31),
             UM_REQUIRE_CHANGE BOOLEAN DEFAULT FALSE,
             UM_CHANGED_TIME TIMESTAMP NOT NULL,
             UM_TENANT_ID INTEGER DEFAULT 0,
             PRIMARY KEY (UM_ID, UM_TENANT_ID),
             UNIQUE(UM_USER_NAME, UM_TENANT_ID)
)ENGINE NDB;

CREATE TABLE UM_ROLE (
             UM_ID INTEGER NOT NULL AUTO_INCREMENT,
             UM_ROLE_NAME VARCHAR(255) NOT NULL,
             UM_TENANT_ID INTEGER DEFAULT 0,
             PRIMARY KEY (UM_ID, UM_TENANT_ID),
             UNIQUE(UM_ROLE_NAME, UM_TENANT_ID)
)ENGINE NDB;

CREATE TABLE UM_PERMISSION (
             UM_ID INTEGER NOT NULL AUTO_INCREMENT,
             UM_RESOURCE_ID VARCHAR(255) NOT NULL,
             UM_ACTION VARCHAR(255) NOT NULL,
             UM_TENANT_ID INTEGER DEFAULT 0,
             PRIMARY KEY (UM_ID, UM_TENANT_ID)
)ENGINE NDB;

-- CREATE INDEX INDEX_UM_PERMISSION_UM_RESOURCE_ID_UM_ACTION
--                    ON UM_PERMISSION (UM_RESOURCE_ID, UM_ACTION, UM_TENANT_ID);

CREATE TABLE UM_ROLE_PERMISSION (
             UM_ID INTEGER NOT NULL AUTO_INCREMENT,
             UM_PERMISSION_ID INTEGER NOT NULL,
             UM_ROLE_NAME VARCHAR(255) NOT NULL,
             UM_IS_ALLOWED SMALLINT NOT NULL,
             UM_TENANT_ID INTEGER DEFAULT 0,
             PRIMARY KEY (UM_ID, UM_TENANT_ID)
)ENGINE NDB;

-- REMOVED UNIQUE (UM_PERMISSION_ID, UM_ROLE_ID)
CREATE TABLE UM_USER_PERMISSION (
             UM_ID INTEGER NOT NULL AUTO_INCREMENT,
             UM_PERMISSION_ID INTEGER NOT NULL,
             UM_USER_NAME VARCHAR(255) NOT NULL,
             UM_IS_ALLOWED SMALLINT NOT NULL,
             UM_TENANT_ID INTEGER DEFAULT 0,
             PRIMARY KEY (UM_ID, UM_TENANT_ID)
)ENGINE NDB;

-- REMOVED UNIQUE (UM_PERMISSION_ID, UM_USER_ID)
CREATE TABLE UM_USER_ROLE (
             UM_ID INTEGER NOT NULL AUTO_INCREMENT,
             UM_ROLE_ID INTEGER NOT NULL,
             UM_USER_ID INTEGER NOT NULL,
             UM_TENANT_ID INTEGER DEFAULT 0,
             UNIQUE (UM_USER_ID, UM_ROLE_ID, UM_TENANT_ID),
             PRIMARY KEY (UM_ID, UM_TENANT_ID)
)ENGINE NDB;


CREATE TABLE UM_USER_ATTRIBUTE (
            UM_ID INTEGER NOT NULL AUTO_INCREMENT,
            UM_ATTR_NAME VARCHAR(255) NOT NULL,
            UM_ATTR_VALUE VARCHAR(1024),
            UM_PROFILE_ID VARCHAR(255),
            UM_USER_ID INTEGER,
            UM_TENANT_ID INTEGER DEFAULT 0,
            PRIMARY KEY (UM_ID, UM_TENANT_ID)
)ENGINE NDB;



CREATE TABLE UM_DIALECT(
            UM_ID INTEGER NOT NULL AUTO_INCREMENT,
            UM_DIALECT_URI VARCHAR(255) NOT NULL,
            UM_TENANT_ID INTEGER DEFAULT 0,
            UNIQUE(UM_DIALECT_URI, UM_TENANT_ID),
            PRIMARY KEY (UM_ID, UM_TENANT_ID)
)ENGINE NDB;

CREATE TABLE UM_CLAIM(
            UM_ID INTEGER NOT NULL AUTO_INCREMENT,
            UM_DIALECT_ID INTEGER NOT NULL,
            UM_CLAIM_URI VARCHAR(255) NOT NULL,
            UM_DISPLAY_TAG VARCHAR(255),
            UM_DESCRIPTION VARCHAR(255),
            UM_MAPPED_ATTRIBUTE VARCHAR(255),
            UM_REG_EX VARCHAR(255),
            UM_SUPPORTED SMALLINT,
            UM_REQUIRED SMALLINT,
            UM_DISPLAY_ORDER INTEGER,
            UM_TENANT_ID INTEGER DEFAULT 0,
            UNIQUE(UM_DIALECT_ID, UM_CLAIM_URI, UM_TENANT_ID),
            PRIMARY KEY (UM_ID, UM_TENANT_ID)
)ENGINE NDB;

CREATE TABLE UM_PROFILE_CONFIG(
            UM_ID INTEGER NOT NULL AUTO_INCREMENT,
            UM_DIALECT_ID INTEGER NOT NULL,
            UM_PROFILE_NAME VARCHAR(255),
            UM_TENANT_ID INTEGER DEFAULT 0,
            PRIMARY KEY (UM_ID, UM_TENANT_ID)
)ENGINE NDB;

CREATE TABLE UM_CLAIM_BEHAVIOR(
            UM_ID INTEGER NOT NULL AUTO_INCREMENT,
            UM_PROFILE_ID INTEGER,
            UM_CLAIM_ID INTEGER,
            UM_BEHAVIOUR SMALLINT,
            UM_TENANT_ID INTEGER DEFAULT 0,
            PRIMARY KEY (UM_ID, UM_TENANT_ID)
)ENGINE NDB;

CREATE TABLE UM_HYBRID_ROLE(
            UM_ID INTEGER NOT NULL AUTO_INCREMENT,
            UM_ROLE_NAME VARCHAR(255),
            UM_TENANT_ID INTEGER DEFAULT 0,
            PRIMARY KEY (UM_ID, UM_TENANT_ID)
)ENGINE NDB;

CREATE TABLE UM_HYBRID_USER_ROLE(
            UM_ID INTEGER NOT NULL AUTO_INCREMENT,
            UM_USER_NAME VARCHAR(255),
            UM_ROLE_ID INTEGER NOT NULL,
            UM_TENANT_ID INTEGER DEFAULT 0,
            UNIQUE (UM_USER_NAME, UM_ROLE_ID, UM_TENANT_ID),
            PRIMARY KEY (UM_ID, UM_TENANT_ID)
)ENGINE NDB;


We use the same db script across all WSO2 Products, so the same db script can be reused across all products.

Our next step to setup the clustered database and create tables to be used by WSO2 GReg. Lets create a database called ‘amagregdb’ in mysql cluster.

  • After creating the database, we need to modify the registry.xml and user-mgt.xml in GReg instance.
  • Open the GREG_HOME/repository/conf/registry.xml file remove the original dbConfig element and add the database configuration below. (IP addresses and database urls have to change acccording to your setup).
               wso2registry
               false
               /
               
                   jdbc:mysql://x.x.x.x:3306/amagregdb?autoReconnect=true
                   root
                   root123
                   com.mysql.jdbc.Driver
                   50
                   60000
                   5
               
            
  • Open the GREG_HOME/repository/conf/user-mgt.xml file and change the database configuration as below(IP addresses and urls have to change according to your setup).
                jdbc:mysql://x.x.x.x:3306/amagregdb?autoReconnect=true
                root
                root123
                com.mysql.jdbc.Driver
                50
                60000
                5
            
  • Copy mysql jdbc driver library to GREG_HOME/repository/components/lib direcotry since by default we do not ship the mysql drivers in GREG.
  • Place the modified mysql.sql (NDB) script in GREG_HOME/dbscripts/mysql.sql.
  • Now start the GREG instance with -Dsetup. (This will create tables for user manager database and registry database (pick the database configuration from currentDBConfig element in registry.xml since there might be a number of database configurations in your registry.xml which we put to perform mounting from other databases but only one database configuration is configured as currentDBConfig)).
  • That’s all we have to do to get WSO2 GReg up and running on the MySQL Cluster. You can always check the fail over scenarios and check whether data is being replicated across all data nodes by using the MySQL cluster management console.
  • The same procedure can be used to all the other WSO2 Products to deploy them on mysql cluster.

Troubleshooting

If following error codes are thrown by mysql cluster while executing dbscripts etc,

  • Cluster error : 708 - Increase : MaxNoOfAttributes=6000
  • Cluster error : 136 - Increase : MaxNoOfOrderedIndexes=2048

If the data nodes or the mysql nodes fail to connect to Management Node, try to add the following line into "/var/lib/mysql-cluster/config.ini"

  • LockPagesInMainMemory=2

References

  1. http://dev.mysql.com/doc/refman/5.0/en/mysql-cluster.html
  2. http://www.oracle.com/pls/ebn/swf_viewer.load?p_shows_id=11464419
  3. https://www.mysql.com/products/cluster/mysql-cluster-datasheet.pdf
  4. http://www.mysql.com/products/cluster/faq.html
  5. http://wso2.org/project/esb/java/4.0.3/docs/deployment_guide.html

Author

Kasun Indrasiri, Associate Technical Lead, WSO2 Inc

WSO2Con 2014 USA