Configure WSO2 API Manager with MySQL Database

In continuation to our previous article on “Installation of WSO2 API Manager on CentOS“, today we will discuss configuring WSO2 with MySQL database. By default, WSO2 API Manager uses the embedded H2 Database for storing data for the functioning of the gateway. However, it’s advised to integrate it with the MySQL database for the production environment. Remember, you may continue to use the Carbon with the H2 database as it is not necessary to change it. With that, let’s move on with the tutorial.

How to Configure WSO2 API Manager with MySQL Database

Installing MySQL

You will need to install MySQL Server and clients before moving on to the next section. Here’s a guide on MySQL Installation.

Setup database and users

Step 1: Set hostname for configuring database permissions in /etc/hosts file.

<MYSQL-SERVER-IP> carbondb.mysql-tg.com

Step 2: Start the database server

# systemctl start mysqld

Step 3: Log in to Mysql as root user and create ‘apimadmin‘ and ‘sharedadmin‘ user accounts.

mysql > CREATE USER 'apimadmin'@'localhost' IDENTIFIED BY 'apimadmin123';

mysql > CREATE USER 'sharedadmin'@'localhost' IDENTIFIED BY 'sharedadmin123';

Step 4: Create databases as shown below

mysql > create database apim_db;

mysql > create database shared_db;

Step 5: Grant privilege to the users for the respective databases.

mysql > GRANT ALL ON apim_db.* TO apimadmin@localhost IDENTIFIED BY "apimadmin123";

mysql > GRANT ALL ON shared_db.* TO sharedadmin@localhost IDENTIFIED BY "sharedadmin123";

Step 6: Yes, we are done with this. Let’s reload all the privileges and exit MySQL.

mysql > FLUSH PRIVILEGES;

Setup MySQL driver for WSO2 API-M

Before continuing with this section, make sure you already have WSO2 API-M installed and configured. If not, jump to this link to set up WSO2 API-M and get back here.

Step 7: Download MySQL Java Connector JAR file and extract it into the below folder.

<API-M_HOME>/repository/components/lib/

You may quickly check if the download connector has a Driver class – With MySQL Connector/J, the name of this class is com.mysql.cj.jdbc.Driver.

$ jar tvf mysql-connector-java-8.0.26.jar

Populate databases with WSO2 DB scripts

Step 8: Populate both the databases with the WSO2 DB scripts.

$ mysql -u sharedadmin -p -Dshared_db < '<API-M_HOME>/dbscripts/mysql.sql';

The above command will set up the shared_db  with necessary tables.

$ mysql -u apimadmin -p -Dapim_db < '<API-M_HOME>/dbscripts/apimgt/mysql.sql';

The above command will setup apim_db with necessary tables.

Remember to input the password when asked with the one created in step 5.

Configuring WSO2 with MySQL data sources

The data sources are used to establish a connection to a database. This needs to be configured in deployment.toml file as shown below.

Step 9: Open the <API-M_HOME>/repository/conf/deployment.toml configuration file and look out for [database.shared_db] and [database.apim_db] configuration elements.

Step 10: Change the configuration elements with necessary values as shown below:

[database.shared_db]
type = "mysql"
url = "jdbc:mysql://localhost:3306/shared_db?useSSL=false"
username = "sharedadmin"
password = "sharedadmin123"
driver="com.mysql.cj.jdbc.Driver"


[database.apim_db]
type = "mysql"
url = "jdbc:mysql://localhost:3306/apim_db?useSSL=false"
username = "apimadmin"
password = "apimadmin123"
driver="com.mysql.cj.jdbc.Driver"

What is useSSL in the database connection URL?

useSSL argument at the end of the database connection URL disables the SSL connection. You need to use this argument only if you want to disable the SSL connection to the database or you ended up with the below error while the API manager is started.

Caused by: com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure

Here’s a solution to fix the Communication link failure errors.

Step 10: That’s it. Restart the WSO2 API Manager.

$ ./bin/api-manager.sh -restart

So that’s it. Access all the URLs of WSO2 to confirm everything is working fine.

• https://<IP or Hostname>:9443/carbon

• https://<IP or Hostname>:9443/publisher

• https://<IP or Hostname>:9443/devportal