Changing the Default DB to MySQL in WSO2 APIM 3.1.0

This article showcases how to change the default DB from H2 to MySQL in WSO2 when the WSO2 product and MySQL server are running on the same machine.

• Prerequisite: Install MySQL server and command-line client on the machine where the WSO2 product is setup.

The following steps describe how to set up a MySQL database to replace the default H2 database in your WSO2 product.

1. Setting Up the Database and Users:

i) Start MySQL command-line client and enter the password that you had provided while installing.

ii) Create the apim database “WSO2AM_DB” and registry and user manager database         “WSO2SHARED_DB” using the below commands.

• create database WSO2AM_DB;
• create database WSO2SHARED_DB;

 iii) Create and give authorization to a user named “wso2carbon” using the below commands for accessing the above-created databases.

• create user 'wso2carbon'@'localhost' identified by 'wso2carbon';
• grant all on WSO2AM_DB.* TO 'wso2carbon'@'localhost';
• grant all on WSO2SHARED_DB.* TO 'wso2carbon'@'localhost';

 iv) Verify if the databases and the user was created properly and the authorization was provided using the below commands.

• to list all the databases currently present. It should contain “wso2am_db” and “wso2shared_db”.

       show databases;

• to find out the existing users. It should contain the user “wsocarbon”.

       select user from user; 

• to verify if “wso2carbon” has access on “wso2am_db” and “wso2shared_db”. The below queries should return “wso2carbon” in the result.

       select user FROM mysql.db WHERE db = 'wso2am_db';

       select user FROM mysql.db WHERE db = 'wso2shared_db';

2. Setting Up the Drivers: 

i) Download the MySQL Java connector JAR file, and extract it.

ii) Copy it to the <API-M_HOME>/repository/components/lib/ directory. 

3. Executing DB Scripts To Create Tables on MySQL Database:

i) To create tables in the registry and user manager database (WSO2SHARED_DB), execute the below commands.

• use wso2shared_db;
• source <API-M_HOME>/dbscripts/mysql.sql;

 ii) To create tables in the apim database (WSO2AM_DB), execute the below commands.

• use wso2am_db;
• source <API-M_HOME>/dbscripts/apimgt/mysql.sql;

iii) Verify if the required tables got created in both the databases using the below commands.

• To verify tables in wso2shared_db.

           use wso2shared_db;           

           show tables;

• To verify tables in wso2am_db.

           use wso2am_db;

           show tables;

4. Creating the datasource Connection to MySQL:

i) Open the <API-M_HOME>/repository/conf/deployment.toml configuration file and locate the [database.shared_db] and [database.apim_db] configuration elements.

ii) You simply have to update the URL pointing to your MySQL database, the username, and password required to access the database and the MySQL driver details as shown below.

xxxxxxxxxx

[database.apim_db]

type = "mysql"

driver="com.mysql.cj.jdbc.Driver"

url = "jdbc:mysql://localhost:3306/WSO2AM_DB?useSSL=false"

username = "wso2carbon"

password = "wso2carbon"

[database.shared_db]

type = "mysql"

driver="com.mysql.cj.jdbc.Driver"

url = "jdbc:mysql://localhost:3306/WSO2SHARED_DB?useSSL=false"

username = "wso2carbon"

password = "wso2carbon"

NOTE: (If you are using MySQL version - 8.0.x, you should add the driver name in the configuration as: driver="com.mysql.cj.jdbc.Driver". As, I am using 8.0.x, so, I have added that line).

5. Restart the WSO2 Server