This documentation is for WSO2 API Manager 2.1.0. View documentation for the latest release.

All docs This doc

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: GW UM_DB config Multi-tenancy

...

  1. Unzip the WSO2 API Manager pack. Let's call it <API-M_HOME>.

  2. Download and install MySQL Server.

  3. Download the MySQL JDBC driver.

  4. Unzip the downloaded MySQL driver archive, and copy the MySQL JDBC driver JAR (mysql-connector-java-x.x.xx-bin.jar) into the <API-M_HOME>/repository/components/lib directory in all the nodes in the cluster.

  5. Anchor
    stepDB5
    stepDB5
    Define the hostname for configuring permissions for the new database by opening the /etc/hosts file and adding the following:

    Warning

    Do this step only if your database is not on your local machine and on a separate server.

    Code Block
    <MYSQL-DB-SERVER-IP> carbondb.mysql-wso2.com
  6. Install mysql-client in each of the API-M servers in which WSO2 API-M is deployed.
    You need to do this in order to check if the servers can access the MySQL database.

    Code Block
    sudo apt install mysql-client
    mysql -h <mysqldb_host_ip> -u username -p
  7. Enter the following command in a command prompt, where username is the username that you used to access the databases.

    Code Block
    mysql -u username -p
  8. When prompted, specify the password that will be used to access the databases with the username you specified.

  9. Create the databases using the following commands, where <API-M_HOME> is the path to any of the API Manager instances you installed, and username and password are the same as those you specified in the previous steps.

    Note

    WSO2 API Manager is shipped with an H2 database. This embedded H2 database is suitable for development and testing environments. However, for production environments, it is recommended to use an industry-standard RDBMS such as Oracle, PostgreSQL, MySQL, MS SQL, etc. The following steps explain how to create MySQL DBs.

    Info
    titleAbout using MySQL in different operating systems

    For users of Microsoft Windows, when creating the database in MySQL, it is important to specify the character set as latin1. Failure to do this may result in an error (error code: 1709) when starting your cluster. This error occurs in certain versions of MySQL (5.6.x) and is related to the UTF-8 encoding. MySQL originally used the latin1 character set by default, which stored characters in a 2-byte sequence. However, in recent versions, MySQL defaults to UTF-8 to be friendlier to international users. Hence, you must use latin1 as the character set as indicated below in the database creation commands to avoid this problem. Note that this may result in issues with non-latin characters (like Hebrew, Japanese, etc.). The following is how your database creation command should look.

    mysql> create database <DATABASE_NAME> character set latin1;

    For users of other operating systems, the standard database creation commands are sufficient. For these operating systems, the following is how your database creation command should look.

    mysql> create database <DATABASE_NAME>;
    Note

    If you are using MySQL to configure your datasources, we recommend that you use a case sensitive database collation. For more information, see the MySQL Official Manual. The default database collation, which is latin1_swedish_ci , is case insensitive. However, you need to maintain case sensivity for database collation, because when the database or table has a case-insensitive collation in MySQL 5.6 or 5.7, if a user creates an API with letters using mixed case, deletes the API, and then creates another API with the same name, but in lower case letters, then the later created API loses its permission information, because when deleting the API, it keeps the Registry collection left behind.

    This issue could be avoided if you use a case sensitive collation for database and tables. In that case, when creating the second API (which has the same name, but is entirely in lowercase letters), it will create a new record with the lowercase name in the UM_PERMISSION table.

    Warning
    titleAdditional notes
    • Ensure that MySQL is configured so that all nodes can connect to it. 
    • From WSO2 API Manager 2.0.0 onwards there are two MySQL DB scripts available in the product distribution. Click here to identify as to which version of the MySQL script to use. 
    • Table creation of the statistics database is handled by the Analytics scripts when you configure APIM Analytics, so you will create the statistics database in this step but will not specify a source script.
    • To access the databases from remote instances, its required to grant permission to the relevant username defined in the <API-M_HOME>/repository/conf/datasources/master-datasources.xml file, by using the grant command. See the following sample commands.
    Localtab Group
    Localtab
    titleFormat
    Code Block
    languagenone
    mysql> create database apimgtdb;
    mysql> use apimgtdb;
    mysql> source <API-M_HOME>/dbscripts/apimgt/<MySQL-script-file>;
    mysql> grant all on apimgtdb.* TO '<username>'@'%' identified by '<password>';
    
    mysql> create database userdb;
    mysql> use userdb;
    mysql> source <API-M_HOME>/dbscripts/<MySQL-script-file>;
    mysql> grant all on userdb.* TO '<username>'@'%' identified by '<password>';
    
    mysql> create database regdb;
    mysql> use regdb;
    mysql> source <API-M_HOME>/dbscripts/<MySQL-script-file>;
    mysql> grant all on regdb.* TO '<username>'@'%' identified by '<password>';
     
    mysql> create database statdb;
    mysql> use statdb;
    mysql> grant all on statdb.* TO '<username>'@'%' identified by '<password>';
     
    mysql> create database mbstoredb;
    mysql> use mbstoredb;
    mysql> source <API-M_HOME>/dbscripts/mb-store/mysql-mb.sql;
    mysql> grant all on mbstoredb.* TO '<username>'@'%' identified by '<password>';

    <MySQL-script-file> - As there are two MySQL script files, make sure to refer the warning message above to identify as to which MySQL script file that you need to use.

    Localtab
    idEg-MySQL
    titleExample when using mysql.sql
    Code Block
    mysql> create database apimgtdb;
    mysql> use apimgtdb;
    mysql> source <API-M_HOME>/dbscripts/apimgt/mysql.sql;
    mysql> grant all on apimgtdb.* TO 'wso2user'@'%' identified by 'wso2admin123';
    
    mysql> create database userdb;
    mysql> use userdb;
    mysql> source <API-M_HOME>/dbscripts/mysql.sql;
    mysql> grant all on userdb.* TO 'wso2user'@'%' identified by 'wso2admin123';
    
    mysql> create database regdb;
    mysql> use regdb;
    mysql> source <API-M_HOME>/dbscripts/mysql.sql;
    mysql> grant all on regdb.* TO 'wso2user'@'%' identified by 'wso2admin123';
    
    mysql> create database statdb;
    mysql> use statdb; 
    mysql> grant all on statdb.* TO 'wso2user'@'%' identified by 'wso2admin123';
     
    mysql> create database mbstoredb;
    mysql> use mbstoredb;
    mysql> source <API-M_HOME>/dbscripts/mb-store/mysql-mb.sql;
    mysql> grant all on mbstoredb.* TO 'wso2user'@'%' identified by 'wso2admin123';
    Note

    In the sample commands above, its assumed that the username and password defined in the datasource configurations in <API-M_HOME>/repository/conf/datasources/master-datasources.xml file is wso2user and wso2admin123 respectively,

  10. Configure the data sources for the five databases as follows:

    1. Open the <API-M_HOME>/repository/conf/datasources/master-datasources.xml file.
      This file contains the different datasources used by WSO2 API Manager. By default, the API Manager connects to the local H2 database and it is recommended to use a separate RDBMS server for a production deployment.

      • If you are configuring API-M in a single node, open the master-datasources.xml in the single WSO2 API-M instance.
      • If you are configuring API-M in a distributed setup, open the master-datasources.xml in all five WSO2 API-M components.
      Tip

      For more information, see Configuring master-datasources.xml in the Administration Guide.

      Note

      Note: When configuring clustering, ignore the WSO2_CARBON_DB data source configuration.

    2. Enable the components to access the WSO2 API Manager database by modifying the WSO2AM_DB data source in the master-datasources.xml file by changing the URL as indicated below. Make sure to also replace db.mysql-wso2.com with the hostname you specified in step 5 (carbondb.mysql-wso2.com). 

      • If you are configuring API-M in a single node, open the master-datasources.xml in the single WSO2 API-M instance.
      • If you are configuring API-M in a distributed setup, open the master-datasources.xml in the Publisher, Store, and Key Manager nodes.
      Code Block
      languagexml
      <datasource>
       <name>WSO2AM_DB</name>
       <description>The datasource used for the API Manager database</description>
       <jndiConfig>
         <name>jdbc/WSO2AM_DB</name>
       </jndiConfig>
       <definition type="RDBMS">
         <configuration>
           <url>jdbc:mysql://db.mysql-wso2.com:3306/apimgtdb?autoReconnect=true</url>
           <username>user</username>
           <password>password</password>
           <defaultAutoCommit>false</defaultAutoCommit>
           <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>
    3. Enable the Key Manager, Publisher, and Store components to access the user management database.
      You need to do this by adding the following code in the master-datasources.xml file and changing db.mysql-wso2.com to carbondb.mysql-wso2.com in order  to configure the  WSO2UM_DB data source.

      • If you are configuring API-M in a single node, open the master-datasources.xml in the single WSO2 API-M instance.I
      • f

        If you are configuring API-M in a distributed setup, open the master-datasources.xml

         in

         file in the Publisher, Store, and Key Manager nodes, and in a multi-tenant setup you need to open the master-datasources.xml file in the Gateway node.

        Localtab Group
        Localtab
        titleSingle Tenant Setup
        Panel

        The configuration needs to be added to API Publisher, Store and Key Manager components.

        Localtab
        titleMulti Tenanted Setup
        Panel

        The configuration needs to be added to API Publisher, Store, Key Manager, and Gateway components.

      Code Block
      languagexml
      <datasource>
       <name>WSO2UM_DB</name>
       <description>The datasource used by user manager</description>
       <jndiConfig>
         <name>jdbc/WSO2UM_DB</name>
       </jndiConfig>
       <definition type="RDBMS">
         <configuration>
           <url>jdbc:mysql://db.mysql-wso2.com:3306/userdb?autoReconnect=true</url>
           <username>user</username>
           <password>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. Enable access to registry databases by adding the  WSO2REG_DB  data sources related configuration in their master-datasources.xml files as follows. The components that need to access the registry database differs, based on whether the setup is multi tennanted or not.

      Localtab Group
      Localtab
      titleSingle Tenant Setup
      Panel

      The configuration needs to be added to API Publisher and Store components.

      Localtab
      titleMulti Tenanted Setup
      Panel

      The configuration needs to be added to API Publisher, Store, Key Manager, and Gateway components.

      Code Block
      languagexml
      <datasource>
       <name>WSO2REG_DB</name>
       <description>The datasource used by the registry</description>
       <jndiConfig>
         <name>jdbc/WSO2REG_DB</name>
       </jndiConfig>
       <definition type="RDBMS">
         <configuration>
           <url>jdbc:mysql://db.mysql-wso2.com:3306/regdb?autoReconnect=true</url>
           <username>user</username>
           <password>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> 
    5. Enable the Publisher and Store components to access the statistics databases by configuring the  WSO2AM_STATS_DB data sources in their master-datasources.xml files as follows:

      Note

      When deploying API-M in a distributed deployment, you need to share the WSO2AM_STATS_DB with the following components:

      • Publisher and the Store - to be able to read from the WSO2AM_STATS_DB.

      Code Block
      languagexml
      <datasource>
       <name>WSO2AM_STATS_DB</name>
       <description>The datasource used for getting statistics to API Manager</description>
       <jndiConfig>
         <name>jdbc/WSO2AM_STATS_DB</name>
       </jndiConfig>
       <definition type="RDBMS">
         <configuration>
           <url>jdbc:mysql://db.mysql-wso2.com:3306/statdb?autoReconnect=true</url>
           <username>user</username>
           <password>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> 
    6. Enable the Traffic Manager component to access the Message Broker database by configuring the WSO2_MB_STORE_DB data source, which is in its master-datasources.xml file as follows:

      Warning
      • Do not share the WSO2_MB_STORE_DB database among the nodes in an Active-Active set-up or Traffic Manager HA scenario, because each node should have its own local WSO2_MB_STORE_DB database to act as separate Traffic Managers.
      • The latter mentioned DBs can be either H2 DBs or any RDBMS such as MySQL.

      • If the database gets corrupted then you need to replace the database with a fresh database that is available in the product distribution.

      Code Block
      languagexml
      <datasource>
       <name>WSO2_MB_STORE_DB</name>
       <description>The datasource used for message broker database</description>
       <jndiConfig>
         <name>WSO2MBStoreDB</name>
       </jndiConfig>
       <definition type="RDBMS">
         <configuration>
           <url>jdbc:mysql://db.mysql-wso2.com:3306/mbstoredb?autoReconnect=true</url>
           <username>user</username>
           <password>password</password>
           <driverClassName>com.mysql.jdbc.Driver</driverClassName>
           <maxActive>50</maxActive>
           <maxWait>60000</maxWait>
           <testOnBorrow>true</testOnBorrow>
           <validationQuery>SELECT 1</validationQuery>
           <validationInterval>30000</validationInterval>
      	 <defaultAutoCommit>false</defaultAutoCommit>
         </configuration>
       </definition>
      </datasource> 
  11. To give the Key Manager, Publisher, and Store components access to the user management database with shared permissions, open the <API-M_HOME>/repository/conf/user-mgt.xml file in each of these three components and add or modify the dataSource property that corresponds to the <configuration> element as follows:
    For more information, see Configuring User Stores .

    Code Block
    languagexml
    <configuration> 
    ...
    	<Property name="dataSource">jdbc/WSO2UM_DB</Property>
    </configuration>
     
    <UserStoreManager class="org.wso2.carbon.user.core.jdbc.JDBCUserStoreManager">
    	<Property name="TenantManager">org.wso2.carbon.user.core.tenant.JDBCTenantManager</Property>
        <Property name="ReadOnly">false</Property>
        <Property name="MaxUserNameListLength">100</Property>
        <Property name="IsEmailUserName">false</Property>
        <Property name="DomainCalculation">default</Property>
        <Property name="PasswordDigest">SHA-256</Property>
        <Property name="StoreSaltedPassword">true</Property>
        <Property name="ReadGroups">true</Property>
        <Property name="WriteGroups">true</Property>
        <Property name="UserNameUniqueAcrossTenants">false</Property>
        <Property name="PasswordJavaRegEx">^[\S]{5,30}$</Property>
        <Property name="PasswordJavaScriptRegEx">^[\S]{5,30}$</Property>
        <Property name="UsernameJavaRegEx">^[^~!#$;%^*+={}\\|\\\\&lt;&gt;,\'\"]{3,30}$</Property>
        <Property name="UsernameJavaScriptRegEx">^[\S]{3,30}$</Property>
        <Property name="RolenameJavaRegEx">^[^~!#$;%^*+={}\\|\\\\&lt;&gt;,\'\"]{3,30}$</Property>
        <Property name="RolenameJavaScriptRegEx">^[\S]{3,30}$</Property>
        <Property name="UserRolesCacheEnabled">true</Property>
        <Property name="MaxRoleNameListLength">100</Property>
        <Property name="MaxUserNameListLength">100</Property>
        <Property name="SharedGroupEnabled">false</Property>
        <Property name="SCIMEnabled">false</Property>
    </UserStoreManager>
    Note

    If you are using the WSO2UM_DB to store users, remember to change the administrator's username and password. For more information, see  Maintaining Logins and Passwords .

  12. To enable access to the registry database, open the <API-M_HOME>/repository/conf/registry.xml file in each of these components and configure them as follows. The components that need to mount the governance registry space differs based on whether the setup is multi tennanted or not.

    Localtab Group
    Localtab
    titleSingle Tenant Setup
    Panel

    Configuration needs to be added to API Publisher and Store components.

    Localtab
    titleMulti Tenanted Setup
    Panel

    Configuration needs to be added to API Publisher, Store, Key Manager, and Gateway components.

    Note

    Do not replace the following configuration when adding in the mounting configurations mentioned below. The registry mounting configurations mentioned in the following steps must be added beneath the following entry, which is already in the configuration file. 

    Code Block
    <dbConfig name="wso2registry">
    	<dataSource>jdbc/WSO2CarbonDB</dataSource>
    </dbConfig>

    This configuration points to the local H2 database. This configuration is necessary and must always exist in this file.

    1. In the Publisher component's registry.xml file, add or modify the dataSource attribute of the <dbConfig name="govregistry"> element as follows: 

      Code Block
      languagenone
      <dbConfig name="govregistry">
        <dataSource>jdbc/WSO2REG_DB</dataSource>
      </dbConfig>
      <remoteInstance url="https://localhost:9443/registry"> 
         <id>gov</id>
         <cacheId>[email protected]:mysql://db.mysql-wso2.com:3306/regdb</cacheId>
         <dbConfig>govregistry</dbConfig>
         <readOnly>false</readOnly>
         <enableCache>true</enableCache>
         <registryRoot>/</registryRoot>
      </remoteInstance>
      <mount path="/_system/governance" overwrite="true">
         <instanceId>gov</instanceId>
         <targetPath>/_system/governance</targetPath>
      </mount>
      <mount path="/_system/config" overwrite="true">
         <instanceId>gov</instanceId>
         <targetPath>/_system/config</targetPath>
      </mount>
    2. In the Store component's registry.xml file, add or modify the dataSource attribute of the <dbConfig name="govregistry"> element as follows (note that this configuration is almost identical to the previous step except for the remoteInstance URL):

      Code Block
      languagenone
      <dbConfig name="govregistry">
        <dataSource>jdbc/WSO2REG_DB</dataSource>
      </dbConfig>
      <remoteInstance url="https://localhost:9443/registry"> 
         <id>gov</id>
         <cacheId>[email protected]:mysql://db.mysql-wso2.com:3306/regdb</cacheId> 
         <dbConfig>govregistry</dbConfig>
         <readOnly>false</readOnly>
         <enableCache>true</enableCache>
         <registryRoot>/</registryRoot>
      </remoteInstance>
      <mount path="/_system/governance" overwrite="true">
         <instanceId>gov</instanceId>
         <targetPath>/_system/governance</targetPath>
      </mount>
      <mount path="/_system/config" overwrite="true">
         <instanceId>gov</instanceId>
         <targetPath>/_system/config</targetPath>
      </mount>

      CacheId is a unique identification of remote instance. When you configure the remote instance, WSO2 recommends that you modify the <cacheId> with the corresponding values of your setup, based on the following format : <username>@<JDBC_URL to_registry_database>

      Note
      • You do not need to specify the remoteInstance URL in the above configuration because WS mounting in not used in WSO2 API-M 2.1.0 onward.

      • In the above code snippet the governance registry and the config registry are pointed to the same database. If required, you can use two databases for the two registries that are shared with Publisher and Store nodes.

  13. Skip caching.
    Add the following configuration under the  <indexingConfiguration> element in the <API-M_HOME>/repository/conf/registry.xml file.

    Note

    In a WSO2 API-M distributed deployment, you need to add this configuration in all the Publisher and Store nodes. By adding this configuration, you avoid facing caching related issues in the Store and Publisher nodes by directly getting the API information from the database.

    Code Block
    <skipCache>true</skipCache>