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
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
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"apimadmin123"; mysql > GRANT ALL ON shared_db.* TO sharedadmin IDENTIFIED BY "sharedadmin123";IDENTIFIED BY
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.
You may quickly check if the download connector has a Driver class – With MySQL Connector/J, the name of this class is
$ 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.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"
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