Keycloak with MySQL Database
Keycloak (WildFly) comes with its own embedded H2 database to persist data by default. H2 is a Java SQL relational database. Keycloak uses the H2 database so that it can run instantly. The H2 is an in-memory database and it is suitable only for use in a test environment. In a production environment, it is recommended that a more mature relational database must be used.
To configure latest Keycloak (Quarkus) version with MySQL, see here
In this tutorial, you will learn how to configure Keycloak to use with MySQL database instead of its default H2 database.
What You Need
- About 15 minutes
- JDK 1.8 or later
- Keycloak 16.1.1
- MySQL
Complete the steps below to set up Keycloak with MySQL:
Create New MySQL Database with a New User for Keycloak
Let's starts by creating a new MySQL database for Keycloak:
- Sign-in to your MySQL Server using your root MySQL credentials.
- Next, run the following MySQL commands one by one. The first command creates a new database keycloak, the second command creates a new user keycloak, and the third command grants all privileges to user keycloak:
CREATE DATABASE keycloak;
CREATE USER 'keycloak'@'localhost' IDENTIFIED BY 'Keycloak123$';
GRANT ALL PRIVILEGES ON keycloak.* TO 'keycloak'@'localhost';
Add MySQL JDBC driver as a Keycloak module
Download the MySQL JDBC driver. The latest JDBC version at the moment of writing this tutorial is 8.0.21.
You can find the latest version of the MySQL Driver in the Maven Repository.
On the download page, there is a jar link to download the file as shown in the image below:
I assume that you have already downloaded and extracted the Keycloak Server (Distribution powered by WildFly) bundle.
Next, go to the Keycloak folder and navigate to location keycloak > modules > system > layers > keycloak > com. Inside the com folder create a new folder mysql and inside the mysql folder create another folder main.
Copy the downloaded MySQL driver jar file into the newly created main folder. Also create a new module.xml file in the same main folder and add the configuration as follows:
module.xml
Replace the resource-root path tag value with your JDBC driver JAR filename. The module name to be used in the above file should match with the directory structure of this module. The directory structure "/com/mysql" maps to "com.mysql".
Declare JDBC Driver in the Deployment Profile
Next, we need to declare the JDBC Driver in the deployment profile so that the JDBC Driver becomes available when the Keycloak server boots up.
If you are planning to deploy Keycloak in the standard mode, then edit /standalone/configuration/standalone.xml file.
If you want to deploy Keycloak in the domain mode, then edit /domain/configuration/domain.xml file. In domain mode, make sure you edit within the profile that you are about to use which is either auth-server-standalone or auth-server-clustered.
This example is based on standard mode, so I will be editing /standalone/configuration/standalone.xml
In the standalone.xml file, look for drivers XML block. There must be a predefined JDBC driver declared for H2 database as shown below:
standalone.xml
Now, add JDBC driver for MySQL within the drivers XML block. The value for xa-datasource-class may vary for different versions of JDBC driver for MySQL. Here is how the updated drivers element should look like after adding MySQL driver to it:
standalone.xml
Add New Datasource in Deployment Profile
Now, within the same standalone.xml file, search for <datasources> element. There you will see the currently used Datasource for H2 database. Comment out the KeycloakDS Datasource and create a new Datasource for MySQL database.
Do not comment out ExampleDS Datasource. Also do not forget to update the JDBC connection-url, the username and password elements to make them relevant with your project.
standalone.xml
Start the Keycloak Server
Go to the bin folder of the Keycloak Server distribution and run the standalone boot script to start the Keycloak Server:
Windows
....\bin\standalone.bat
Linux/Ubuntu/Unix
$ cd bin
$ ./standalone.sh
The configuration for Keycloak to use MySQL database is complete. Now, the Keycloak will use MySQL database instead of it's default H2 database when it is run.
Some List of Errors and their Solutions
Caused by: com.ctc.wstx.exc.WstxParsingException: Received non-all-whitespace CHARACTERS or CDATA event in nextTag()
Solution: Do not copy the configuration from the Keycloak document page as it may contain invisible white space characters. Duplicate existing configuration and modify it for the MySQL.
ERROR [org.jboss.msc.service.fail] (MSC service thread 1-3) MSC000001: Failed to start service org.wildfly.clustering.infinispan. cache-container-configuration.keycloak: org.jboss.msc.service.StartException in service org.wildfly.clustering.infinispan.cache-container-configuration .keycloak: java.lang.OutOfMemoryError: Metaspace
Solution: Edit the following entry in /bin/standalone.conf file (ubuntu or linux) or /bin/standalone.conf.bat file (Windows). change MaxMetaspaceSize value to 1GB.Change From:
To:
ERROR [org.jboss.as.controller.management-operation] (Controller Boot Thread) WFLYCTL0348: Timeout after [300] seconds waiting for service container stability. Operation will roll back. Step that first updated the service container was 'add' at address '[ ("core-service" => "management"), ("management-interface" => "http-interface")
Solution: Increase timeout by adding the following line at the bottom of the file /bin/standalone.conf file (in ubuntu or linux) or /bin/standalone.conf.bat file (Windows).
ERROR [org.jboss.msc.service.fail] (ServerService Thread Pool -- 57) MSC000001: Failed to start service jboss.deployment.unit. "keycloak-server.war".undertow-deployment: org.jboss.msc.service.StartException in service jboss.deployment.unit."keycloak-server.war".undertow-deployment: java.lang.RuntimeException: RESTEASY003325: Failed to construct public org.keycloak.services.resources.KeycloakApplication()
Solution: Add default-timeout="5000" to coordinator-environment XML block in the /standalone/configuration/standalone.xml file for standard mode. If you are using in different mode then edit in the mode's respective file.
Before:
After update:
