How to configure WSO2 App Manager with MySQL ?

All WSO2 products comes with H2 internal database/s by default. Any of these product can be configured with different databases other than the H2 DB. App Manager product contains several databases unique only to App Manager that is not common with other WSO2 products. If you are familiar with changing default database of any of WSO2 product, concept is similar for App Manager as well.  Anyway, here I'm explaining step by step, what each of these database in App Manager is doing and how we can configure them to use MySQL.

We have following five datasources defined in the {AppM_Home}/repository/conf/datasources/master-datasources.xml file.

[1] <name>jdbc/WSO2CarbonDB</name> - Use for registry data storage, user store, permission store
[2] <name>jdbc/WSO2AM_DB</name> - Use to store App details that created from App Manager
[3] <name>jdbc/WSO2AM_STATS_DB</name> - Stats storage. Needed only when Data Analytic Server is configured
[4] <name>jdbc/ES_Storage</name> - Use to store thumbnail/banner images of apps
[5] <name>jdbc/WSO2SocialDB</name> - Use to store comments and ratings added for apps by store users


This guide explains configuring App Manager standalone instance with MySQL DB. If it is a cluster deployment, there are some additional configurations required other than explains in this post.

Step 0: Login to the MySQL server and create 4 databases.
mysql> create database appm_regdb;
Query OK, 1 row affected (0.01 sec)

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

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

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

Step 1: Change  datasources mentioned previously (other than the STATS_DB) as follows by pointing to above created MySQL databases. You need to replace the config with  valid database username/password.
Note that <defaultAutoCommit>false</defaultAutoCommit> property is mandatory for "jdbc/WSO2AM_DB" datasource.

File location: {AppM_Home}/repository/conf/datasources/master-datasources.xml

     <datasource>
            <name>WSO2_CARBON_DB</name>
            <description>The datasource used for registry and user manager</description>
            <jndiConfig>
                <name>jdbc/WSO2CarbonDB</name>
            </jndiConfig>
            <definition type="RDBMS">
                <configuration>
                   <url>jdbc:mysql://localhost:3306/appm_regdb</url>
                  <username>root</username>
                   <password>root</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>WSO2AM_DB</name>
            <description>The datasource used for APP Manager database</description>
            <jndiConfig>
                <name>jdbc/WSO2AM_DB</name>
            </jndiConfig>
            <definition type="RDBMS">
                <configuration>
                    <url>jdbc:mysql://localhost:3306/appm_appdb</url>
                    <username>root</username>
                    <password>root</password>
                    <driverClassName>com.mysql.jdbc.Driver</driverClassName>
                    <defaultAutoCommit>false</defaultAutoCommit>
                    <maxActive>50</maxActive>
                    <maxWait>60000</maxWait>
                    <testOnBorrow>true</testOnBorrow>
                    <validationQuery>SELECT 1</validationQuery>
                    <validationInterval>30000</validationInterval>
                </configuration>
            </definition>
        </datasource>

<datasource>
           <name>JAGH2</name>
           <description>The datasource used for by the Jaggery Storage Manager</description>
           <jndiConfig>
               <name>jdbc/ES_Storage</name>
           </jndiConfig>
           <definition type="RDBMS">
               <configuration>
                   <url>jdbc:mysql://localhost:3306/appm_esdb</url>
                   <username>root</username>
                   <password>root</password>
                    <driverClassName>com.mysql.jdbc.Driver</driverClassName>
                   <maxActive>50</maxActive>
                   <maxWait>60000</maxWait>
               </configuration>
           </definition>
       </datasource>

<datasource>
            <name>WSO2_SOCIAL_DB</name>
            <description>The datasource used for social framework</description>
            <jndiConfig>
                <name>jdbc/WSO2SocialDB</name>
            </jndiConfig>
            <definition type="RDBMS">
                <configuration>
                    <url>jdbc:mysql://localhost:3306/appm_socialdb</url>
                    <username>root</username>
                    <password>root</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>

Step 2: Copy MySQL jdbc driver to {AppM_Home}/repository/components/lib directory

Step 3: Start the server with -Dsetup option. This will create the required tables in above four databases.
sh wso2server.sh -Dsetup

That's all.