Mounting WSO2 ESB registry partitions into MySQL

By default WSO2 ESB comes with an embedded H2 database where the config and governance partitions of the registry is embedded there. In most of the production scenarios it is required to externalize these partitions to out of ESB for the convenience of managing registry resources. This post is a step by step guide on how to mount ESB registry partitions into a MySQL database.

1. Download MySQL JDBC connector
1. Installing MySQL
2. Create new registry database
3. Configure ESB to use externalized MySQL database

1. Download MySQL JDBC connector
The jdbc connector for MySQL can be downloaded by the link provided here. 

[https://dev.mysql.com/downloads/connector/j/5.0.html]
Extract the zip file and obtain the mysql-connector-java-5.0.8-bin.jar file.

2. Installing MySQL

There are two ways to install MySQL server in Windows 7. One is by downloading the MSI installer and the other option is downloading the mysql zip file.
I have used the option two below as it was quick. You can download the mysql ""Windows (x86, 64-bit), ZIP Archive"" using this link. 

[https://dev.mysql.com/downloads/mysql/5.5.html#downloads]

Once downloaded, extract it and go to \mysql-5.5.43-winx64\bin. Run the below command to start mysql server. This will start the MySQL service.

> mysqld.exe

After that execute below command to log in to mysql as root user. The default password is empty, so once prompted for password you can just press enter and login. If you need set a new root password later for increased security.

> mysql.exe -u root -p
Enter password: [Press Enter here as there is no password to type]
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.43 MySQL Community Server (GPL)

That is all, you can use any mysql commands now to work with databases here.

3. Create a new database in MySQL server, which will be used as the external database for WSO2 ESB. I have given the name reg_db for this.

mysql> create database reg_db;
Query OK, 1 row affected (0.00 sec)

4. Configure ESB to use externalized MySQL database

The last step to do is pointing ESB to use this newly created MySQL database. Here we are going to link ESB with new sql database, so the entries written to config and governance spaces will be stored in reg_db mysql database from here onwards.

4.1  Copy the previously download jdbc connector jar into ESB_HOME/repository/components/lib folder.
4.2  Goto ESB_HOME/repository/conf/datasources and open the master-datasources.xml file. Add the below entry for a new datasource. Do not remove any existing entries too.
     We have to give username and password for the database. Since my MySQL password is empty i have kept <password></password>  without any value here.

        <datasource> 
            <name>WSO2_CARBON_DB_Reg</name> 
            <description>External DB used for registry and config spaces</description> 
            <jndiConfig> 
                <name>jdbc/WSO2CarbonDB_Reg</name> 
            </jndiConfig> 
            <definition type="RDBMS"> 
                <configuration> 
                    <url>jdbc:mysql://localhost:3306/reg_db</url> 
                    <username>root</username> 
                    <password></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>

4.3 Goto ESB_HOME/repository/conf/registry.xml file and add a new dbConfig for remote MySQL database.

    <dbConfig name="mounted_registry"> 
        <dataSource>jdbc/WSO2CarbonDB_Reg</dataSource> 
    </dbConfig>
   
4.4 Add a new remoteInstance entry to the same registry.xml.

    <remoteInstance url="https://localhost:9443/registry"> 
        <id>instanceid</id> 
        <dbConfig>mounted_registry</dbConfig> 
        <readOnly>false</readOnly> 
        <enableCache>true</enableCache> 
        <registryRoot>/</registryRoot> 
        <cacheId>root@jdbc:mysql://localhost:3306/reg_db</cacheId> 
    </remoteInstance>   

4.5 Add the mount configurations to registry.xml as below.

    <mount path="/_system/config" overwrite="true"> 
        <instanceId>instanceid</instanceId> 
        <targetPath>/_system/nodes</targetPath> 
    </mount> 
    <mount path="/_system/governance" overwrite="true"> 
        <instanceId>instanceid</instanceId> 
        <targetPath>/_system/governance</targetPath> 
    </mount>
   
That is all. Now when you restart the ESB server, go to the Registry menu from the admin console. When you 'Browse' the registry the icons for config and governance spaces would look like below.

 

The blue arrow in the icons mean those partitions are now pointed into an external database.

Now if you goto the mysql console and try to see the tables that in the 'reg_db' database, it is now loaded with list of new tables as below.

mysql> use reg_db;
Database changed
mysql>
mysql> show tables;
+-----------------------+
| Tables_in_reg_db     |
+-----------------------+
| reg_association       |
| reg_cluster_lock      |
| reg_comment           |
| reg_content           |
| reg_content_history   |
+-----------------------+
39 rows in set (0.00 sec)