Page Comparison

How to migrate from H2 to MySQL

When going into production, it is not recommended to use the H2 database. The following are instructions on how to configure migrate from H2 databases MySQL databases:

1. Install MySQL.
   sudo apt-get install mysql-server mysql-client
2. Set the MySQL root password.
3. Start the MySQL service.
   sudo /etc/init.d/mysql start

4. Log in to the MySQL client as the root user
   mysql -u root -p

   You will be prompted to enter the password. In most systems, the default root password is blank. Press Enter without typing anything, if you have not changed the default root password.

5. Create the WSO2_EMM_DB as follows:

   1. In the MySQL prompt, create the database.
      create database WSO2_EMM_DB;

   2. Give authorization for the database to the admin user as follows. Thereafter, exit the MySQL Console.
      GRANT ALL ON WSO2_EMM_DB.* TO admin@localhost IDENTIFIED BY "<MYSQL ADMIN PASSWORD>";

   3. Run the MySQL scripts as follows:
      mysql -u admin -p -DWSO2_EMM_DB < <PRODUCT_HOME>/dbscripts/emm/mysql.sql;

   4. Download the MySQL connector for Java, from http://dev.mysql.com/downloads/connector/j/ and copy the JAR file downloaded to the <PRODUCT_HOME>/repository/components/lib/ directory.

   5. Update the following configuration (i.e., username, password, and more) in the master-datasources.xml file, which is in the <PRODUCT_HOME>/repository/confg/datasources/ directory.

      Code Block

      <datasource> <name>WSO2_EMM_DB</name> <description>The datasource used for EMM</description> <jndiConfig> <name>jdbc/WSO2EMMDB</name> </jndiConfig> <definition type="RDBMS"> <configuration> <url>jdbc:mysql://localhost:3306/WSO2EMM_DB</url> <username>[MySQL username]</username> <password>[MySQL 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. Create the WSO2_CARBON_DB as follows:

   1. In the MySQL prompt, create the database.
      create database WSO2_CARBON_DB;

   2. Give authorization for the database to the admin user as follows. Thereafter, exit the MySQL Console.

      Info
      As the user named admin was created in step 5, we do not need to create a user again in this step.

      GRANT ALL ON WSO2_CARBON_DB.* TO admin@localhost;

   3. Run the MySQL scripts as follows:
      mysql -u admin -p -DWSO2_CARBON_DB < <PRODUCT_HOME>/dbscripts/mysql.sql;

   4. Update the following configuration (i.e., username, password, and more) in the master-datasources.xml file, which is in the <PRODUCT_HOME>/repository/confg/datasources/ directory.

      Code Block

      <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/WSO2_CARBON_DB</url> <username>[MySQL username]</username> <password>[MySQL 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>

7. Create the WSO2_IDENTITYthe WSO2AM_DB as  as follows:

   1. In the MySQL prompt, create the database.
      create database WSO2_IDENTITYWSO2AM_DB;

   2. Give authorization for the database to the admin user as follows. Thereafter, exit the MySQL Console.

      Info
      As the user named admin was created in step 5, we do not need to create a user again in this step.

      GRANT ALL ON WSO2ON WSO2AM_IDENTITY_DB.* TO admin@localhost;

   3. In the MySQL prompt, create the database.
      create database WSO2AM_DB;

   4. Give authorization for the database to the admin user as follows. Thereafter, exit the MySQL Console.

      Info
      As the user named admin was created in step 5, we do not need to create a user again in this step.

      Run the MySQL scripts as follows:
      mysql -u admin -p -DWSO2_IDENTITY_DB < <PRODUCT_HOME>/dbscripts/identity/mysql.sql;

   5. Update the following configuration (i.e., username, password, and more) in the master-datasources.xml file, which is in the  <PRODUCT_HOME>/repository/confg/datasources/ directory.

      Code Block

      <datasource> <name>WSO2_IDENTITY_DB</name> <description>The datasource used for identity configurations</description> <jndiConfig> <name>jdbc/WSO2IdentityDB</name> </jndiConfig> <definition type="RDBMS"> <configuration> <url>jdbc:mysql://localhost:3306/WSO2_IDENTITY_DB</url> <username>[MySQL username]</username> <password>[MySQL 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>

   Create the WSO2AM_DB as follows:

   1. GRANT ALL ON WSO2AM_DB.* TO admin@localhost;Run the MySQL scripts as follows:
      mysql -u admin -p -DWSO2AM_DB < <PRODUCT_HOME>/dbscripts/apimgt/mysql.sql;

   2. Update the following configuration (i.e., username, password, and more) in the master-datasources.xml file, which is in the  <PRODUCT_HOME>/repository/confg/datasources/ directory.

      Code Block

      <datasource> <name>WSO2AM_DB</name> <description>The datasource used for API manager database</description> <jndiConfig> <name>jdbc/WSO2AM_DB</name> </jndiConfig> <definition type="RDBMS"> <configuration> <defaultAutoCommit>false</defaultAutoCommit> <url>jdbc:mysql://localhost:3306/WSO2AM_DB</url> <username>[MySQL username]</username> <password>[MySQL 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>

Going into production

Create tenants and test WSO2 EMM throughly. Once the tests are run successfully, it is safe to consider that the upgrade is ready for production. However, it is advised to test any features that are being used in production.