Deploying WSO2 Products on MySQL Cluster
- Kasun Indrasiri
- Director - Integration Technologies - WSO2
Applies To
| WSO2 Carbon 3.2.0 based products | 3.2.0 |
Table of Contents
- Introduction
- MySQL Cluster in a nutshell
- MySQL Cluster - Quick Install Guide
- Deploying WSO2 Governance Registry on MySQL Cluster
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.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. https://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 https://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
- https://dev.mysql.com/doc/refman/5.0/en/mysql-cluster.html
- https://www.mysql.com/products/cluster/mysql-cluster-datasheet.pdf
- https://www.mysql.com/products/cluster/faq.html
- https://wso2.org/project/esb/java/4.0.3/docs/deployment_guide.html
Author
Kasun Indrasiri, Associate Technical Lead, WSO2 Inc