DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones AWS Cloud
by AWS Developer Relations
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones
AWS Cloud
by AWS Developer Relations
Securing Your Software Supply Chain with JFrog and Azure
Register Today

Trending

  • Top Six React Development Tools
  • A React Frontend With Go/Gin/Gorm Backend in One Project
  • Step Into Serverless Computing
  • What to Pay Attention to as Automation Upends the Developer Experience

Trending

  • Top Six React Development Tools
  • A React Frontend With Go/Gin/Gorm Backend in One Project
  • Step Into Serverless Computing
  • What to Pay Attention to as Automation Upends the Developer Experience
  1. DZone
  2. Data Engineering
  3. Databases
  4. Changing the Default DB to MySQL in WSO2 APIM 3.1.0

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.

Suman Mohan user avatar by
Suman Mohan
·
Sep. 21, 20 · Tutorial
Like (4)
Save
Tweet
Share
2.06K Views

Join the DZone community and get the full member experience.

Join For Free

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.

starting MySQL command-line client

           

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.

Plain Text
 




xxxxxxxxxx
1
13


 
1
[database.apim_db]
2
type = "mysql"
3
driver="com.mysql.cj.jdbc.Driver"
4
url = "jdbc:mysql://localhost:3306/WSO2AM_DB?useSSL=false"
5
username = "wso2carbon"
6
password = "wso2carbon"
7

          
8
[database.shared_db]
9
type = "mysql"
10
driver="com.mysql.cj.jdbc.Driver"
11
url = "jdbc:mysql://localhost:3306/WSO2SHARED_DB?useSSL=false"
12
username = "wso2carbon"
13
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

MySQL Database

Opinions expressed by DZone contributors are their own.

Trending

  • Top Six React Development Tools
  • A React Frontend With Go/Gin/Gorm Backend in One Project
  • Step Into Serverless Computing
  • What to Pay Attention to as Automation Upends the Developer Experience

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com

Let's be friends: