Configure MySQL database for WSO2 ESB 5.0.0 cluster

Posted by holstead on Mon, 21 Feb 2022 02:29:07 +0100

For cluster configuration, see: WSO2 ESB 5.0.0 cluster configuration

1, Cluster database

Each Carbon based product uses a database to store user management details and registry data. All nodes in the cluster must use a central database for configuration and governance registry mount.

H2 is not recommended for production.
It is not recommended to use embedded H2 database in enterprise test and production environment. It has low performance, cluster limitations, and may cause file corruption failure. Please use industry standard RDBMS instead, such as Oracle, PostgreSQL, MySQL or MS SQL.
The embedded H2 database can be used in the development environment, or it can be used as the local registry in the registry mount.

Create the following databases and associated data sources

database	describe
WSO2_USER_DB	JDBC user storage and authorization Manager
REGISTRY_DB	Install the shared database used to configure and govern the registry in the product node
REGISTRY_LOCAL1	Local registration space in management node
REGISTRY_LOCAL2	Local registration space in work node

The following figure illustrates how these databases are connected to the management and work nodes

2, Create database

Perform the following steps to create the necessary databases. MySQL is used here as an example, and any suitable database can be used instead.

1. Download and install MySQL server.
2. Download MySQL JDBC driver.
3. Unzip the downloaded MySQL driver package and copy the MySQL JDBC driver JAR (mysql-connector-java-x.x.xx-bin.jar) to < product of the management node and work node_ Home > / repository / components / lib directory.
4. Define the host name used to configure the new database permissions by opening the / etc/hosts file and adding the following line: < mysql-db-server-ip > carbondb mysql-wso2. COM, this step is only necessary if the database is not on the local computer and a separate server.
5. Enter the following command in the terminal / command window, where username is the user name to be used to access the database: mysql -u username -p
6. When prompted, use the specified user name to specify the password used to access the database.
7. Use the following command to create a database where < product_ Home > is the path of any installed product instance. The user name and password are the same as those specified in the previous steps:

About using MySQL in different operating systems

For Microsoft Windows users, it is important to specify the character set as latin1 when creating a database in MySQL. Failure to do so may result in an error when starting the cluster (error code: 1709). This error occurs in some versions of MySQL (5.6.x) and is related to UTF-8 encoding. MySQL initially uses the latin1 character set by default, which stores characters in a 2-byte sequence. However, in the latest version, MySQL uses UTF-8 by default in order to be more friendly to international users. Therefore, you must use latin1 as the character set in the database creation command, as shown below to avoid this problem. Please note that this may cause problems with non Latin characters such as Hebrew, Japanese, etc. The following is the sql of the database creation command.

mysql> create database <DATABASE_NAME> character set latin1;

For users of other operating systems, standard database creation commands are sufficient. For these operating systems, the following is the sql of the database creation command.

mysql> create database <DATABASE_NAME>;

create database WSO2_USER_DB character set latin1;
use WSO2_USER_DB;
source <PRODUCT_HOME>\dbscripts\mysql5.7.sql;
source <PRODUCT_HOME>\dbscripts\identity\mysql-5.7+.sql;
grant all on *.* TO regadmin@"%" identified by "regadmin";
 
create database REGISTRY_DB character set latin1;
use REGISTRY_DB;
source <PRODUCT_HOME>\dbscripts\mysql5.7.sql;
grant all on *.* TO regadmin@"%" identified by "regadmin";
 
create database REGISTRY_LOCAL1 character set latin1;
use REGISTRY_LOCAL1;
source <PRODUCT_HOME>\dbscripts\mysql5.7.sql;
grant all on *.* TO regadmin@"%" identified by "regadmin";
  
create database REGISTRY_LOCAL2 character set latin1;
use REGISTRY_LOCAL2;
source <PRODUCT_HOME>\dbscripts\mysql5.7.sql;
grant all on *.* TO regadmin@"%" identified by "regadmin";

alter user 'regadmin'@'%' identified by 'regadmin';
flush privileges;

Starting from Carbon kernel 4.4.6, the product will come with two MySQL scripts, as shown below:

mysql.sql: this script is used for MySQL versions before 5.7.

mysql5.7.sql: this script is used for MySQL 5.7 and later versions.

Note that if you use the - DSetup option to automatically create the database during server startup, mysql.xml will be used by default SQL script to set up the database. Therefore, if MySQL version 5.7 is set for the server, be sure to do the following before starting the server:

First, the existing mysql Change the SQL file to a different file name.
Set < product_ HOME>/dbscripts/mysql5. 7. Change the SQL script to MySQL sql.
Set < product_ HOME>/dbscripts/identity/mysql5. 7. Change the SQL script to MySQL sql.

MySQL 5.7 is only recommended for products based on Carbon 4.4.6 or later.

3, Configuration management node

1. On the manager node, open < product_ HOME>/repository/conf/datasources/master-datasource. XML file and configure the data source to point to REGISTRY_LOCAL1,WSO2_REGISTRY_DB and WSO2_USER_DB database, as shown below (change user name, password and URL required for database environment).

<datasources-configuration xmlns:svns="http://org.wso2.securevault/configuration">
     <providers>
        <provider>org.wso2.carbon.ndatasource.rdbms.RDBMSDataSourceReader</provider>
    </providers>
    <datasources>
        <datasource>
            <name>REGISTRY_LOCAL1</name>
            <description>The datasource used for registry- local</description>
            <jndiConfig>
                <name>jdbc/WSO2CarbonDB</name>
            </jndiConfig>
            <definition type="RDBMS">
                <configuration>
                    <url>jdbc:mysql://carbondb.mysql-wso2.com:3306/REGISTRY_LOCAL1?autoReconnect=true&amp;useUnicode=true&amp;characterEncoding=utf8&amp;useSSL=false&amp;allowPublicKeyRetrieval=true&amp;serverTimezone=GMT%2B8</url>
                    <username>regadmin</username>
                    <password>regadmin</password>
                    <driverClassName>com.mysql.jdbc.Driver</driverClassName>
                    <maxActive>50</maxActive>
                    <maxWait>60000</maxWait>
                    <testOnBorrow>true</testOnBorrow>
                    <validationQuery>SELECT 1</validationQuery>
                    <validationInterval>30000</validationInterval>
                </configuration>
            </definition>
        </datasource>
        <datasource>
            <name>REGISTRY_DB</name>
            <description>The datasource used for registry- config/governance</description>
            <jndiConfig>
                <name>jdbc/WSO2RegistryDB</name>
            </jndiConfig>
            <definition type="RDBMS">
                <configuration>
                    <url>jdbc:mysql://carbondb.mysql-wso2.com:3306/REGISTRY_DB?autoReconnect=true&amp;useUnicode=true&amp;characterEncoding=utf8&amp;useSSL=false&amp;allowPublicKeyRetrieval=true&amp;serverTimezone=GMT%2B8</url>
                    <username>regadmin</username>
                    <password>regadmin</password>
                    <driverClassName>com.mysql.jdbc.Driver</driverClassName>
                    <maxActive>50</maxActive>
                    <maxWait>60000</maxWait>
                    <testOnBorrow>true</testOnBorrow>
                    <validationQuery>SELECT 1</validationQuery>
                    <validationInterval>30000</validationInterval>
                </configuration>
            </definition>
        </datasource>
         <datasource>
            <name>WSO2_USER_DB</name>
            <description>The datasource used for registry and user manager</description>
            <jndiConfig>
                <name>jdbc/WSO2UMDB</name>
            </jndiConfig>
            <definition type="RDBMS">
                <configuration>
                    <url>jdbc:mysql://carbondb.mysql-wso2.com:3306/WSO2_USER_DB?useUnicode=true&amp;characterEncoding=utf8&amp;useSSL=false&amp;allowPublicKeyRetrieval=true&amp;serverTimezone=GMT%2B8</url>
                    <username>regadmin</username>
                    <password>regadmin</password>
                    <driverClassName>com.mysql.jdbc.Driver</driverClassName>
                    <maxActive>50</maxActive>
                    <maxWait>60000</maxWait>
                    <testOnBorrow>true</testOnBorrow>
                    <validationQuery>SELECT 1</validationQuery>
                    <validationInterval>30000</validationInterval>
                </configuration>
            </definition>
        </datasource>
   </datasources>
</datasources-configuration>

Make sure to replace the user name and password with the MySQL database user name and password.

2. To configure the data source, update < product of the manager node_ HOME>/repository/conf/user-mgt. The dataSource attribute in XML is as follows:

<Property name="dataSource">jdbc/WSO2UMDB</Property>

3. The < product of the management node must be updated_ HOME>/repository/conf/registry. The dataSource attribute in the XML file, as shown below.

<dbConfig name="sharedregistry">   
    <dataSource>jdbc/WSO2RegistryDB</dataSource>
</dbConfig>

4, Configure work node

1. On the work node, open < product_ HOME>/repository/conf/datasources/master-datasource. XML file and configure the data source to point to REGISTRY_LOCAL2,WSO2_REGISTRY_DB and WSO2_USER_DB database, as shown below (change the user name, password and database URL according to the needs of your environment):

<datasources-configuration xmlns:svns="http://org.wso2.securevault/configuration">
     <providers>
        <provider>org.wso2.carbon.ndatasource.rdbms.RDBMSDataSourceReader</provider>
    </providers>
    <datasources>
        <datasource>
            <name>REGISTRY_LOCAL2</name>
            <description>The datasource used for registry- local</description>
            <jndiConfig>
                <name>jdbc/WSO2CarbonDB</name>
            </jndiConfig>
            <definition type="RDBMS">
                <configuration>
                    <url>jdbc:mysql://carbondb.mysql-wso2.com:3306/REGISTRY_LOCAL2?autoReconnect=true&amp;useUnicode=true&amp;characterEncoding=utf8&amp;useSSL=false&amp;allowPublicKeyRetrieval=true&amp;serverTimezone=GMT%2B8</url>
                    <username>regadmin</username>
                    <password>regadmin</password>
                    <driverClassName>com.mysql.jdbc.Driver</driverClassName>
                    <maxActive>50</maxActive>
                    <maxWait>60000</maxWait>
                    <testOnBorrow>true</testOnBorrow>
                    <validationQuery>SELECT 1</validationQuery>
                    <validationInterval>30000</validationInterval>
                </configuration>
            </definition>
        </datasource>
        <datasource>
            <name>REGISTRY_DB</name>
            <description>The datasource used for registry- config/governance</description>
            <jndiConfig>
                <name>jdbc/WSO2RegistryDB</name>
            </jndiConfig>
            <definition type="RDBMS">
                <configuration>
                    <url>jdbc:mysql://carbondb.mysql-wso2.com:3306/REGISTRY_DB?autoReconnect=true&amp;useUnicode=true&amp;characterEncoding=utf8&amp;useSSL=false&amp;allowPublicKeyRetrieval=true&amp;serverTimezone=GMT%2B8</url>
                    <username>regadmin</username>
                    <password>regadmin</password>
                    <driverClassName>com.mysql.jdbc.Driver</driverClassName>
                    <maxActive>50</maxActive>
                    <maxWait>60000</maxWait>
                    <testOnBorrow>true</testOnBorrow>
                    <validationQuery>SELECT 1</validationQuery>
                    <validationInterval>30000</validationInterval>
                </configuration>
            </definition>
        </datasource>
         <datasource>
            <name>WSO2_USER_DB</name>
            <description>The datasource used for registry and user manager</description>
            <jndiConfig>
                <name>jdbc/WSO2UMDB</name>
            </jndiConfig>
            <definition type="RDBMS">
                <configuration>
                    <url>jdbc:mysql://carbondb.mysql-wso2.com:3306/WSO2_USER_DB?useUnicode=true&amp;characterEncoding=utf8&amp;useSSL=false&amp;allowPublicKeyRetrieval=true&amp;serverTimezone=GMT%2B8</url>
                    <username>regadmin</username>
                    <password>regadmin</password>
                    <driverClassName>com.mysql.jdbc.Driver</driverClassName>
                    <maxActive>50</maxActive>
                    <maxWait>60000</maxWait>
                    <testOnBorrow>true</testOnBorrow>
                    <validationQuery>SELECT 1</validationQuery>
                    <validationInterval>30000</validationInterval>
                </configuration>
            </definition>
        </datasource>
   </datasources>
</datasources-configuration>

2. Configure data source and update < product of work node_ HOME>/repository/conf/user-mgt. The dataSource attribute in the XML file, as shown below.

<Property name="dataSource">jdbc/WSO2UMDB</Property>

3. The < product of the work node must be updated_ HOME>/repository/conf/registry. The dataSource attribute in the XML file, as shown below.

<dbConfig name="sharedregistry">   
    <dataSource>jdbc/WSO2RegistryDB</dataSource>
</dbConfig>

5, Mount the registry on the management and work nodes

Perform this step to ensure that the shared registry for governance and configuration is installed on both nodes. This database is REGISTRY_DB.

< product in the management node_ HOME>/repository/conf/registry. Configure the shared registry database and mount details in the XML file, as shown below:

Note: do not delete an existing dbConfig named wso2registry when adding the following configuration.

<dbConfig name="sharedregistry">
    <dataSource>jdbc/WSO2RegistryDB</dataSource>
</dbConfig>
 
<remoteInstance url="https://localhost:9443/registry">
    <id>instanceid</id>
    <dbConfig>sharedregistry</dbConfig>
    <readOnly>false</readOnly>
    <enableCache>true</enableCache>
    <registryRoot>/</registryRoot>
    <cacheId>regadmin@jdbc:mysql://carbondb.mysql-wso2.com:3306/REGISTRY_DB?autoReconnect=true&amp;useUnicode=true&amp;characterEncoding=utf8&amp;useSSL=false&amp;allowPublicKeyRetrieval=true&amp;serverTimezone=GMT%2B8</cacheId>
</remoteInstance>
 
<mount path="/_system/config" overwrite="true">
    <instanceId>instanceid</instanceId>
    <targetPath>/_system/config</targetPath>
</mount>
 
<mount path="/_system/governance" overwrite="true">
    <instanceId>instanceid</instanceId>
    <targetPath>/_system/governance</targetPath>
</mount>

< product in the work node_ HOME>/repository/conf/registry. Configure the shared registry database and mount details in XML as follows:

<dbConfig name="sharedregistry">
    <dataSource>jdbc/WSO2RegistryDB</dataSource>
</dbConfig>
 
<remoteInstance url="https://localhost:9443/registry">
    <id>instanceid</id>
    <dbConfig>sharedregistry</dbConfig>
    <readOnly>false</readOnly>
    <enableCache>true</enableCache>
    <registryRoot>/</registryRoot>
    <cacheId>regadmin@jdbc:mysql://carbondb.mysql-wso2.com:3306/REGISTRY_DB?autoReconnect=true&amp;useUnicode=true&amp;characterEncoding=utf8&amp;useSSL=false&amp;allowPublicKeyRetrieval=true&amp;serverTimezone=GMT%2B8</cacheId>
</remoteInstance>
 
<mount path="/_system/config" overwrite="true">
    <instanceId>instanceid</instanceId>
    <targetPath>/_system/config</targetPath>
</mount>
 
<mount path="/_system/governance" overwrite="true">
    <instanceId>instanceid</instanceId>
    <targetPath>/_system/governance</targetPath>
</mount>

Here are some important points to note when adding these configurations:

The dataSource specified under the < dbconfig name = "sharedregistry" > tab must be the same as the master datasources of manager and worker The jndiConfig name specified in the XML file does not match.
The registry mount path is used to identify the type of registry. For example/_ system/config refers to the configuration registry/_ system/governance refers to the governance registry.
The dbconfig entry enables you to identify your data source in master - datasources The data source configured in the XML file. Use the unique name sharedregistry to reference the data source entry.
The remoteInstance section refers to the external registry mount. You can specify the read-only / read-write nature of this instance, as well as the cache configuration and registry root location. If it is a work node, the readOnly attribute should be true. If it is a manager node, this attribute should be set to false.
In addition, you must specify the cacheID, which enables the cache to function properly in a clustered environment. Note that the cacheID is the same as the JDBC connection URL of the registry database. This value is the cacheID of the remote instance. The cacheID here should be $database_username@$database_ The format of the URL, where $database_username is the user name of the remote instance database, $database_url is the URL of the remote instance database. This cacheID identifies the cache to look for when caching is enabled. In this case, the database we should connect to is REGISTRY_DB, which is a database shared by all master/workers nodes. You can identify it by looking at the installation configuration that uses the same data source.
A unique name ID must be defined for each remote instance and then referenced from the mount configuration. In the above example, the unique ID of the remote instance is instanceId.
In each mount configuration, specify the actual mount path and the target mount path. targetPath can be any meaningful name. In this case, it is/_ system/config.