In this document, we will check how to change the keycloak database to MySQL from h2.
Keycloak comes with its own embedded Java-based relational database called H2. This is the default database that Keycloak will use to persist data and really only exists so that you can run the authentication server out of the box. It’s fine for development purposes, but for the production, it is recommended to replace it with a more production-ready external database.


Prerequisites

* Keycloak server, if not check this link
* Running MySQL server, if you don’t have please check this link.

Configuration

Step 1: Create a database for keycloak

mysql -uroot -p
CREATE USER 'keycloak'@'%' IDENTIFIED BY 'keycloak';
CREATE DATABASE keycloak CHARACTER SET utf8 COLLATE utf8_unicode_ci;
GRANT ALL PRIVILEGES ON keycloak.* TO 'keycloak'@'%';

Step 2: Login to keycloak server and download the MySQL connector.

wget -P ~/tmp/ https://dev.mysql.com/get/Downloads/Connector-J/mysql-connector-java-5.1.42.zip
unzip ~/tmp/mysql-connector-java-5.1.42.zip -d ~/tmp

Step 3: Now configure the Mysql Connector module in Keycloak server.

sudo mkdir -p /opt/keycloak/modules/system/layers/keycloak/com/mysql/main
sudo cd /opt/keycloak/modules/system/layers/keycloak/com/mysql/main
sudo cp ~/tmp/mysql-connector-java-5.1.42/mysql-connector-java-5.1.42-bin.jar .
sudo touch module.xml

Step 4: Now add the module configuration to module.xml file.

vi module.xml

 

<?xml version="1.0" ?>
<module xmlns="urn:jboss:module:1.3" name="com.mysql">
<resources>
<resource-root path="mysql-connector-java-5.1.42-bin.jar" />
</resources>
<dependencies>
<module name="javax.api"/>
<module name="javax.transaction.api"/>
</dependencies>
</module>

Step 5: Now add the Mysql driver. Edit the file /opt/keycloak/standalone/configuration/standalone.xml, Add the below section in drivers section.

<driver name="mysql" module="com.mysql">
<driver-class>com.mysql.jdbc.Driver</driver-class>
</driver>

Step 6: Now add the data source configuration to change from h2 database to MySQL.

<datasource jndi-name="java:/jboss/datasources/KeycloakDS" pool-name="KeycloakDS" enabled="true">
<connection-url>jdbc:mysql://10.0.0.113:3306/keycloak?useSSL=false&characterEncoding=UTF-8</connection-url>
<driver>mysql</driver>
<pool>
<min-pool-size>5</min-pool-size>
<max-pool-size>15</max-pool-size>
</pool>
<security>
<user-name>keycloak</user-name>
<password>keycloak</password>
</security>
<validation>
<valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLValidConnectionChecker"/>
<validate-on-match>true</validate-on-match>
<exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLExceptionSorter"/>
</validation>
</datasource>

comment the h2 datasource configuration with <!– –>

My Sample configuration looks like this.

<!-- <datasource jndi-name="java:jboss/datasources/KeycloakDS" pool-name="KeycloakDS" enabled="true" use-java-context="true" statistics-enabled="${wildfly.datasources.statistics-enabled:${wildfly.statistics-enabled:false}}">
<connection-url>jdbc:h2:${jboss.server.data.dir}/keycloak;AUTO_SERVER=TRUE</connection-url>
<driver>h2</driver>
<security>
<user-name>sa</user-name>
<password>sa</password>
</security>
</datasource>
-->
<datasource jndi-name="java:/jboss/datasources/KeycloakDS" pool-name="KeycloakDS" enabled="true">
<connection-url>jdbc:mysql://10.0.0.111:3306/keycloak?useSSL=false&characterEncoding=UTF-8</connection-url>
<driver>mysql</driver>
<pool>
<min-pool-size>5</min-pool-size>
<max-pool-size>15</max-pool-size>
</pool>
<security>
<user-name>keycloak</user-name>
<password>keycloak</password>
</security>
<validation>
<valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLValidConnectionChecker"/>
<validate-on-match>true</validate-on-match>
<exception-sorter class

NOTE: make sure that you changed the database server IP, Username and Password for the database server as per your configurations.

Step 7: Now restart the keycloak application

service keycloak restart

© 2019, Techrunnr. All rights reserved.

#1
#2
#3
Questions Answered
Articles Written
Overall Points

Prabhin Prabharkaran

He is Technical professional. He is a person who loves to share tricks and tips on the Internet. He Posts what he does!!

0 Comments

Leave a Reply

Please wait...

Subscribe to our newsletter

Want to be notified when our article is published? Enter your email address and name below to be the first to know.