Wso2is

MySQL

How to setup MySQL database with WSO2 Identity Server

4 min readMar 8, 2022

By default , WSO2 Identity Server uses a H2 database to store data.In this tutorial I am going to guide you how to setup MySQL database with WSO2 Identity Server.Let’s get started.

Prerequisites

Download and unzip the latest version of WSO2 Identity Server from here.
Download and install MySQL
Download corresponding JDBC connector from here .

Navigate into the unzipped WSO2 Identity Server folder and let’s call it <IS_HOME>.

Copy the downloaded JDBC connector to <IS_HOME>/repository/components/lib.

Start MySQL client

If you have installed MySQL correctly you can start the MySQL client using the below command.

mysql -u root -p

Then you will need to enter the password.If everything is OK, you will connect to the MySQL client.

Datasource configurations

DataSource is a name given to the connection set up to a database from a server. By default there are 2 datasources which are used to connect to the H2 database.

WSO2_SHARED_DB — stores registry and user management data.
WSO2_IDENTITY_DB — stores identity related data

Create databases

Now we are going to create two databases called wso2shared_db and wso2identity_db as our WSO2 Identity Server databases.We are going to store registry and user management data in wso2shared_db and store identity related data in wso2identity_db.

create database wso2shared_db;create database wso2identity_db;

Note : If your MySQL version is 8.0 or later , you have to create the database with charset latin1 as shown below.

create database wso2shared_db character set latin1;create database wso2identity_db character set latin1;

Now we have created our databases.

In order to create necessary tables and populate them in our newly created databases, we have to run some scripts with them.

First I’m going to use wso2identity_db.

use wso2identity_db;

Now let’s run the scripts using below commands.

source <path to IS_HOME>/dbscripts/identity/mysql.sql;source <path to IS_HOME>/dbscripts/identity/uma/mysql.sql;source <path to IS_HOME>/dbscripts/consent/mysql.sql;

Example :-

Remember that you have to include the path to <IS-HOME> directory when running this scripts.In my case , I have started mysql server in <IS-HOME> directory.Therefore I didn’t want to add the path to the <IS-HOME> in the command.

Now we are going to use wso2shared_db and run relevant scripts to create tables and populate them.

use wso2shared_db;source <path to IS-HOME>/dbscripts/mysql.sql;

Changing datasources to MySQL

Now we are going to point WSO2_SHARED_DB and WSO2_IDENTITY_DB datasources to the newly created MySQL database.

Navigate to <IS-HOME>/repository/conf/ and open deployment.toml using a text editor.

Add below lines to the file to connect datasources to the MySQL database.

[database.shared_db]type = "mysql"url= "jdbc:mysql://127.0.0.1:3306/wso2shared_db?useSSL=false"username = "<MySQL username>"password="<MySQL password>"

and,

[database.identity_db]type = "mysql"url= "jdbc:mysql://127.0.0.1:3306/wso2identity_db?useSSL=false"username = "<MySQL username>"password="<MySQL password>"

Remove or comment below lines to the file to remove datasources from the H2 database.

[database.identity_db]type = "h2"url = "jdbc:h2:./repository/database/WSO2IDENTITY_DB;DB_CLOSE_ON_EXIT=FALSE;LOCK_TIMEOUT=60000"username = "wso2carbon"password = "wso2carbon"

and,

[database.shared_db]type = "h2"url = "jdbc:h2:./repository/database/WSO2SHARED_DB;DB_CLOSE_ON_EXIT=FALSE;LOCK_TIMEOUT=60000"username = "wso2carbon"password = "wso2carbon"